Tag Info

New answers tagged

1

I found a script: SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName ,dosi.virtual_machine_type_desc ,Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi If you have a CMS configured, run the below Script from your CMS against multiple servers: SELECT ...


6

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model. OR If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio. DECLARE @result int EXEC @result = xp_cmdshell 'SYSTEMINFO' System Manufacturer: VMware, Inc. System Model: VMware ...


-1

Executed this script : DBCC CHECKIDENT (PixTest, NORESEED) Result says Identity value is NULL. Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Then we believe you need to follow either of the other two ways ...


-1

Or try this: SET IDENTITY_INSERT PixTest ON INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1') SET IDENTITY_INSERT PixTest off


2

The error isn't coming from the trigger. It's coming from your insert statement. INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1') It's getting parsed and returning an error before you ever get to the trigger. Change it to this and it works. INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')


0

As mskinner mentions, you can use Row Number and Partition By to remove duplicates of specific fields. Here's how you might do it using a Common Table Expresion. WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)as RowNumber FROM YourTable) DELETE FROM cte WHERE RowNumber> 1 Partition by the fields you want to ...


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) ...


0

Here's some test sql for all the styles. Make nvarchar(max) shorter to trim (e.g. nvarchar(10)) . DECLARE @now datetime SET @now = GETDATE() select convert(nvarchar(MAX), @now, 0) as output, 0 as style union select convert(nvarchar(MAX), @now, 1), 1 union select convert(nvarchar(MAX), @now, 2), 2 union select convert(nvarchar(MAX), ...


2

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 ...


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 ...


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 ...


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 ...


3

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 ...


6

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 ...


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 ...


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'


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 ...


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 ...


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 ...


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.


0

I have not yet found a notification or activation on job completion. The nearest I can think of is putting a tirgger on the job-related systems tables in MSDB. This is very dodgy, however, and not at all recommended, and may not be possible. In the past, when I've needed to merge two job streams that I can't edit, I've used a check-and-wait stored ...


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 ...


0

Have the last step of job 1 be to fire job 2, use this code: EXEC dbo.sp_start_job N'MyJob 2' Then, the last step of job 2 be to fire job 3, so on and so forth.


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;


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...


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


0

Change your query to this: SELECT pvt.ID ,[198] = isnull([198],0) ,[281] = isnull([281],0) ,[280] = isnull([280],0) ,[295] = isnull([295],0) ,[296] = isnull([296],0) ,[297] = isnull([297],0) FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c PIVOT ( SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], ...


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 ...



Top 50 recent answers are included