Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,176 through 5,190 (of 59,101 total)

Reply To: sql server keeps looking for my User Defined Scalar Function in the Master DB
Shifting gears back to the function that was used, I've added a smidgen of additional bullet-proofing.
```
 CREATE OR ALTER FUNCTION dbo.ufnStringToPbase
      ...
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
June 11, 2021 at 3:35 pm

#3895029
Reply To: sql server keeps looking for my User Defined Scalar Function in the Master DB

brian.cook wrote:
I have a Visual Studio project web site that I have been using for years. The move to SQL server 2019, has apparently broken it.
When I call the dataadapter.Fill...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 11, 2021 at 2:15 pm

#3894996
Reply To: sql server keeps looking for my User Defined Scalar Function in the Master DB

As you said in your original post...
The move to SQL server 2019, has apparently broken it.
The reason the move broke it is now obvious... the function lived in the master...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 11, 2021 at 12:48 pm

#3894955
Reply To: UDF not working

Not only some representative data along with the expected output but how about a description of what the function is supposed to do, please.
I would also get into the habit...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 11, 2021 at 12:34 pm

#3894946
Reply To: Restricting DB storage growth within AWS instance

What IS the limit you're trying to set it to, how many files does it have, and how big is your largest table/index?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 11, 2021 at 1:12 am

#3894710
Reply To: MAXRECURSION 0 Option

Scott745618 wrote:
Hi Everyone,
I am trying to apply a Multi table function that uses a recursive CTE. When I apply it I get the error:
The statement terminated. The maximum recursion 100...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 10, 2021 at 2:50 pm

#3894470
Reply To: Export SSRS-Server contents as Reporting Services Project (Visual Studio)

I know it doesn't contribute to the conversation at hand but it's just fascinating to me that this type of thing is still an issue since the release in 2005...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 10, 2021 at 1:27 pm

#3894421
Reply To: how to get the entire sql statement while sql statment is over 4000 characters

892717952 wrote:
Jeff Moden wrote:
So your saying that the following code that you posted took over 5 hours?
SELECT sm.Object_ID, Definition = ls.LongString FROM sys.SQL_Modules sm CROSS APPLY dbo.ShowLongString(sm.Definition) ls
I just ran that...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 10, 2021 at 1:18 pm

#3894420
Reply To: Tables are empty but catalog views show that they have rows

Interesting. Thanks for the tips, Jeffrey.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 10, 2021 at 3:46 am

#3894183
Reply To: Tables are empty but catalog views show that they have rows

Jeffrey Williams wrote:
I wonder - what happens if you run DBCC UPDATEUSAGE on that cloned database?
I believe the OP said they tried that with no success.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 9, 2021 at 4:20 pm

#3893992
Reply To: The Tech Blame Game

David.Poole wrote:
People forget that DevOps is about building a culture of collaboration between people with different disciplines. It is not a bunch of technologies, buzzword bingo or fancy job titles.
Absolutely...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 9, 2021 at 4:19 am

#3893701
Reply To: how to get the entire sql statement while sql statment is over 4000 characters

So your saying that the following code that you posted took over 5 hours?
SELECT sm.Object_ID, Definition = ls.LongString
FROM sys.SQL_Modules sm
CROSS APPLY dbo.ShowLongString(sm.Definition) ls
I just ran that code on my prod...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 9, 2021 at 4:13 am

#3893700
Reply To: Tables are empty but catalog views show that they have rows

SQL Guy 1 wrote:
Tried TRUNCATE - no errors. Tried DELETE - 0 rows affected.
But I probably found the reason, this is likely to be a cloned database, that was created with DBCC...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 8, 2021 at 10:49 pm

#3893633
Reply To: HASHBYTES giving different resaults than yesterday

astrid 69000 wrote:
I tried, but we are still having records that are unmatched.
Then you have actually having records that don't match because the function is not capable of creating different outputs...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 8, 2021 at 6:36 pm

#3893558
Reply To: Convert string containing ''yyyyMMdd_HHmm' back to Datetime format (dd-mm-yyyy

Another way... the formula in the CROSS APPLY converts the substring to a DATETIME2 just in case you want to see how to do that. The WHERE clause ensures that...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

June 8, 2021 at 4:05 pm

#3893502

Viewing 15 posts - 5,176 through 5,190 (of 59,101 total)