Tag Info

Hot answers tagged

8

This might give some insight until one of the smarter folks on the site chimes in. I put the random results into a temporary table and I consistently get 4 result regardless of the join type. /* Works as expected -- always four rows */ DECLARE @Rando table ( RandomNumber int ); INSERT INTO @Rando ( RandomNumber ) -- This generates 4 random ...


7

You can't pass a variable to USE. You can do this inside the dynamic SQL, or you can build a string that prepends each object name with the database prefix. A cleaner dynamic SQL approach than the latter approach, IMHO, since it doesn't require you to inject the database name in front of every object in the dynamic SQL (and also, in this case, allows you to ...


6

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn. You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably ...


5

By adding the additional SELECT it pushes the compute scalar evaluation deeper into the plan and gives the join predicate, the compute scalar at the top then references the earlier one. SELECT rando.RandomNumber, d.database_id FROM (SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber FROM sys.databases WHERE database_id <= 4) AS ...


5

Yes, this is a limitation in T-SQL. There are several scenarios where you can't build up a string dynamically, but rather have to do so beforehand. For example: RAISERROR('String' + @variable,1,0); EXEC dbo.procedure @param = 'String' + @variable; There are other cases where it is valid, but usually just for assignment, not for passing or doing anything ...


5

You can't, ish, is the short answer. You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component. I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether ...


4

You can't. Take a look at the reference/syntax on the USE statement in BOL, this doesn't accept an expression: USE { database } It appears as though you are doing some form of database sharding, and you want to be able to be in the context of a particular database programmatically. You have two options here: Dynamic SQL or some form of ...


4

As Aaron Bertrand mentioned, expressions are very tricky to predict. If you dare go there, you could try to gain some insight using the following snippet: DECLARE @number SQL_VARIANT SELECT @number = 0.15 / 360 SELECT @number SELECT SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType, SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength, ...


3

You can follow a master slave model .. where in you would create a master job that drives the jobs. so create a master job with below steps --- step 1 EXEC dbo.sp_start_job N'Your_JOB_1' ; GO --- To be 100% accurate, msdb.dbo.sysjobactivity can be queried to make sure a particular job is completed before starting another one. --- step 2 EXEC ...


3

Try this Query; Select s.ItineraryID, R.ShortName as RoadTripName, sum(EstimatedMiles) as TotalMiles, count(itineraryid) as TotalDays, SUM(case DayType.DayTypeCode when 'XX' then 1 else 0 END ) as 'Planning/Prep', SUM(case DayType.DayTypeCode when 'SE' then 1 else 0 END ) as 'Short Event', SUM(case DayType.DayTypeCode when 'LE' then 1 else 0 END ) as 'Long ...


3

From the comments it sounds like you already have this fixed but I thought I would put in my 2 cents anyway. You have a handful of problems based on the code you have listed. @DimCount is declared as an int where you are trying to use it as a string. You are putting quotes around @dimtablename. I understand what you are trying to do there but you ...


3

You should use the value() method of the XML data type. If your XML structure looks like this: <DLR> <siteID>12345</siteID> </DLR> Your query would be select msg.value('(/DLR/siteID/text())[1]', 'int') as DOMAIN from dbo.YourTable But it does however not look like you have an XML column at all since msg like '<DLR%' is ...


2

I have dealt with some long running jobs in the past and the best way I have found to identify them is to run a separate job that checks on that sort of thing. Thomas LaRock put together a nice script that does that, and you can modify it to either email you or if you wanted you could have it just stop the job as well. In my case, the job literally just ...


2

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant ...


2

Are you familiar with the SELECT .. INTO syntax? It's a useful trick for deconstructing situations like this because it creates a table on the fly with just the right data types for the given SELECT list. You can break up your calculation into its constituent steps, applying SQL Servers' precedence rules as you go, to see how the definition changes. ...


2

Notwithstanding the excellent answers already added to this question, there is an explicitly defined order of precedence for conversion of data types in SQL Server. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the ...


2

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases. In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables: SELECT c.name, c.collation_name FROM sys.all_columns AS c INNER ...


2

Yes, it's possible: select Id, -- Remains as in table cast(MandatoryIntCol as int) as [MandatoryIntCol], -- Makes nullable isnull(OptionalIntCol, 0) as [OptionalIntCol] -- Makes mandatory from dbo.MyTable; Got myself the hassle of creating similar wrappers recently to stop Entity Framework messing with views' keys...


2

From SQL 2012 onwards, there is the WITH RESULT SETS option, which makes it easy to use the output from the OUTPUT clause with no sticky issues in SSIS not understanding the metadata for the resultset, eg you can use the output from an OUTPUT clause in SSIS Source components, eg OLE DB Source. I set up a simple example using a stored procedure with MERGE ...


1

select * from dbo.Sales s cross apply ( select top (1) * from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date order by ph.Date desc ) ca;


1

I figured it out. (Apologies to those of you who tried to help me. There was no way you could have figured this out.) After a lot of experimentation, I noticed that dynamic queries (Connection.createStatement()) were returning results but parameterized queries (Connection.prepareStatement()) were returning an empty set. You had no way of knowing this ...


1

You can also do SELECT...INTO, Use the table in a join or use APPLY, eg SELECT * INTO #tmp FROM [dbo].[ufn_MyFunction] ( 1 ) SELECT * FROM [dbo].[ufn_MyFunction] ( 1 ) f INNER JOIN someOtherTable t ON f.RowId = t.rowId SELECT * FROM someOtherTable t CROSS APPLY [dbo].[ufn_MyFunction] ( t.rowId ) f


1

Sure you can, you just define a table variable or a temp table and pipe the output from the select into that. Then you can use that in your code. So, something like this; --Declare some working variables declare @t Table ( rowId INT, Value nvarchar(250)) declare @id int --Gather the output from the function insert into @t SELECT * FROM ...


1

Doing string manipulation in SQL is non-optimal for the engine; it's not meant for that kind of row by row processing. Your option of using temporary tables to hold intermediary results is going to be your best bet on solving the performance problem. At least that way it's only split once. But there are solutions that may work better for you. Can you ...


1

Trying to guess your requirements here, You want to "remove" rows that are part of div-Purchase pairs. If by remove you mean don't select them, i.e. filter them out in a where clause, then something like this might work: with TestCTE as ( select ID, [Partner], [Type], Amount, LEAD([Type], 1) OVER (ORDER BY ID) NextType, LAG([Type], 1) ...


1

Join via snapshots_internal to source_info_internal which has the server instance name, eg SELECT du.*, sii.instance_name FROM [snapshots].[disk_usage] du INNER JOIN core.[snapshots_internal] si ON du.snapshot_id = si.snapshot_id INNER JOIN [core].[source_info_internal] sii ON si.source_id = sii.source_id WHERE du.database_name = 'tempdb'


1

An option which I've used before is to create the temp table using some known fields that will always be the same, then alter the temp table using dynamic SQL to add in the additional columns that you only need in specific cases. This way the temp table is created within scope, then altered via dynamic SQL. CREATE TABLE #MyTable (ID int identity(1,1) ...


1

If you are just doing some maintenance scripts (not stored procedures) then you can do this type of thing using SQLCMD Mode ( Query > SQLCMD Mode ), eg :setvar databaseName yourDbName EXEC msdb.dbo.sp_help_schedule @schedule_name = 'FullBackup_$(databaseName)' Variables have a batch scope and can even pass through to other scripts called with the :r ...


1

The following code will restore the database to the desired location: RESTORE DATABASE x FROM DISK = 'Filepathofbackupfile.bak' WITH RECOVERY, MOVE 'logicalDatafilename' TO 'Newfilepath.mdf', MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'


1

From Books Online database_default - Causes the COLLATE clause to inherit the collation of the current database. If you are executing your second query from the common database then the value from master.dbo.sysdatabases is being coerced into common's collation, not the other way around as you suppose.



Only top voted, non community-wiki answers of a minimum length are eligible