SQL Server 2005 (onwards): SELECT * FROM sys.traces SQL Server 2000 : USE msdb SELECT * FROM fn_trace_getinfo(default); Ref: fn_trace_getinfo Column descriptions for sys.traces DMV can be found here: sys.traces
The problem with the former is that what you’re doing is USE ‘myDB’ rather than USE myDB. you’re passing a string; but USE is looking for an explicit reference. The latter example works for me. DECLARE @sql varchar(20) SELECT @sql=”USE myDb” EXEC sp_sqlexec @Sql — Also works SELECT @sql=”USE [myDb]” EXEC sp_sqlexec @Sql
You can’t use a function directly as a stored procedure parameter. You can do the following: DECLARE @now DateTime SET @now = GETDATE() DECLARE @return_value int EXEC @return_value = my_stored_procedure @MyId = 1, @MyDateField = @now SELECT ‘Return Value’ = @return_value GO
If your production SQL server and test SQL server can talk, you could just do in with a SQL insert statement. first run the following on your test server: Execute sp_addlinkedserver PRODUCTION_SERVER_NAME Then just create the insert statement: INSERT INTO [PRODUCTION_SERVER_NAME].DATABASE_NAME.dbo.TABLE_NAME (Names_of_Columns_to_be_inserted) SELECT Names_of_Columns_to_be_inserted FROM TABLE_NAME
Example query below, here are some details on how I solved it. Using DATEDIFF(WK, …) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how … Read more
select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as number from Task order by RuleId
SELECT Id ‘PatientId’, ISNULL(CONVERT(varchar(50),ParentId),”) ‘ParentId’ FROM Patients ISNULL always tries to return a result that has the same data type as the type of its first argument. So, if you want the result to be a string (varchar), you’d best make sure that’s the type of the first argument. COALESCE is usually a better function … Read more
As you’ve noticed, the WHERE clause doesn’t allow you to use aggregates in it. That’s what the HAVING clause is for. HAVING t1.field3=MAX(t1.field3)
I don’t see where you will have much success exporting html to csv – it’s really not what csv is meant for. You would be better off using an xml format, where the html code can be enclosed in a cdata element. That said, you could try using the Replace function to remove the line … Read more
Update: This article in my blog summarizes both my answer and my comments to another answers, and shows actual execution plans: IN vs. JOIN vs. EXISTS SELECT * FROM a WHERE a.c IN (SELECT d FROM b) SELECT a.* FROM a JOIN b ON a.c = b.d These queries are not equivalent. They can yield … Read more