Execute table-valued function on multiple rows?
OUTER APPLY: SELECT Stuff.id ,Results.pn ,Results.s FROM stackoverflow_454945 AS Stuff OUTER APPLY dbo.Split(‘,’, Stuff.myColumn) AS Results WHERE ISNULL(Results.s,”) <> ”
sql-server-2005
Creating audit triggers in SQL Server
I just want to call out couple of points: Use code generators You can’t have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation … Read more
SQL Server triggers – order of execution
Using SetTriggerOrder is fine, but if your code depends on a specific sequence of execution, why not wrap all your triggers into stored procedures, and have the first one call the second, the second call the third, etc. Then you simply have the first one execute in the trigger. Someone in the future will be … Read more
How to check correctly if a temporary table exists in SQL Server 2005?
You’re darn close – you need to use two dots in your check: IF OBJECT_ID(‘tempdb..#tmpTable1′) IS NOT NULL ** | use two dots here! Basically, this is saying: check in the tempDB and I don’t care what schema the table is in As Joe rightfully said: this is not 100% correct: it doesn’t check in … Read more
Using variables for asc and desc in order by [duplicate]
without Dynamic SQL each option it’s clause for example: ORDER BY case when @var1 = ‘priority asc’ THEN priority END ASC , case when @var1 = ‘priority desc’ then priority end DESC, case when @var2 = ‘report_date asc’ then report_date end ASC, case when @var2 = ‘report_date desc’ then report_date end DESC
How do you find the last time a database was accessed?
To expand on James Allen’s answer: SELECT d.name, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i JOIN sys.databases AS d ON i.database_id=d.database_id GROUP BY d.name Use this modified version if you don’t want results per database context and wish to include the database name at the beginning … Read more
How to connect to SQL Server from another computer?
Disclamer This is just some additional information that might help anyone. I want to make it abundantly clear that what I am describing here is possibly: A. not 100% correct and B. not safe in terms of network security. I am not a DBA, but every time I find myself setting up a SQL Server … Read more
Generate a resultset of incrementing dates in T-SQL
If your dates are no more than 2047 days apart: declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = ‘P’ ) n where dateadd(day, number, @dt) < @dtEnd I updated my answer after several requests to do … Read more
Query the contents of stored procedures on SQL Server
SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE objectproperty(object_id,’IsProcedure’) = 1 AND definition like ‘%Foo%’
Using Encrypt=yes in a Sql Server connection string -> “provider: SSL Provider, error: 0 – The certificate’s CN name does not match the passed value.”
Your database connection can be configured to encrypt traffic and to accept any certificate from your server. Not a grand solution, but it worked for me. The resulting connection string should look like this: “[…];Encrypt=True;TrustServerCertificate=True”