List all tables that are currently published for replication MS-SQL
Yes: SELECT * FROM sys.tables WHERE is_replicated = 1 From MSDN for is_replicated field: 1 = Table is published using snapshot replication or transactional replication.
sql-server
How to do very fast inserts to SQL Server 2008
ExecuteNonQuery with an INSERT statement, or even a stored procedure, will get you into thousands of inserts per second range on Express. 4000-5000/sec are easily achievable, I know this for a fact. What usually slows down individual updates is the wait time for log flush and you need to account for that. The easiest solution … Read more
Why there is an extra “(1 row(s) affected)”
That usually means you have the actual execution plan option turned on. The execution plan is sent as an extra rowset, resulting in an extra (1 row(s) affected) message. To disable actual execution plan press Ctrl+M.
Unable to load adalsql.dll error when calling `Invoke-sqlcmd`
So, this problem vexed me as well. I’m unclear as to how it happened, but maybe it’s just coincidence that it happened when I installed the latest version of SSMS. My fix was to: Navigate to Add or Remove Programs In the little search window type sql, or just go find: “Active Directory Authentication Library … Read more
SQL Server ‘select * into’ versus ‘insert into ..select *
The select * into table1 from table2 where 1=1 creates table1 and inserts the values of table2 in them. So, if the table is already created that statement would give an error. The insert into table1 select * from table2 only inserts the values of table2 in table1.
How to get the logical name of the transaction log in SQL Server 2005
You can use: SELECT name FROM sys.master_files WHERE database_id = db_id() AND type = 1 Log files have type = 1 for any database_id and all files for all databases can be found in sys.master_files. EDIT: I should point out that you shouldn’t be shrinking your log on a routine basis. Your transaction log should … Read more
Easy way to convert exec sp_executesql to a normal query?
I spent a little time making an simple script that did this for me. It’s a WIP, but I stuck a (very ugly) webpage in front of it and it’s now hosted here if you want to try it: http://execsqlformat.herokuapp.com/ Sample input: exec sp_executesql N’SELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = @level’, N’@level tinyint’, @level … Read more
Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
Why doesn’t this work? I believe the default behaviour of SQL Server is to release shared locks as soon as they are no longer needed. Your sub-query will result in a short-lived shared (S) lock on the table, which will be released as soon as the sub-query completes. At this point there is nothing to … Read more
SQL to return list of years since a specific year
This gets all years from 2004 to the present, using a recursive CTE: with yearlist as ( select 2004 as year union all select yl.year + 1 as year from yearlist yl where yl.year + 1 <= YEAR(GetDate()) ) select year from yearlist order by year desc;
How to use Switch in SQL Server
The CASE is just a “switch” to return a value – not to execute a whole code block. You need to change your code to something like this: SELECT @selectoneCount = CASE @Temp WHEN 1 THEN @selectoneCount + 1 WHEN 2 THEN @selectoneCount + 1 END If @temp is set to none of those values … Read more