SET NOCOUNT OFF disregarded in SQLCMD

I want to see how many rows are affected for each DDL statement that is run by a query, so I set SET NOCOUNT OFF at the start of each query that is run. Sample query: SET NOCOUNT OFF; GO BEGIN TRY BEGIN TRANSACTION UPDATE dbo.tbProvClause ...
more »

2017-11-29 18:11 (3) Answers

MS SQL Pivot Dynamic Headers

I have the following two tables: Serial Key Value ABC Attr1 ABC1 ABC Attr2 ABC2 ABC Attr3 ABC3 XYZ Attr1 XYZ1 XYZ Attr2 XYZ2 XYZ Attr3 XYZ3 and Key Include IncludeOrder Attr1 Y 2 Attr2 NULL ...
more »

2017-10-26 16:10 (1) Answers

Connection string "Login failed for user"

I'm trying to connect to my database using the following connection string: Data Source=LORDKANELSNEGLE\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\Database.mdf";Integrated Security=True;MultipleA...
more »

2017-09-10 20:09 (1) Answers

How to check if field is NULL and blank?

I'm trying to remove the possibility of blank spaces by a value not existing in the database when creating the view for my lookup. The issue I'm having is that my CASE statement isn't working quite right when I'm trying to check for a NULL or blank v...
more »

2017-09-01 10:09 (5) Answers

Can I use T-SQL to launch notepad or other UI app?

Nothing important or fancy, I am creating a demo jpeg in SQL Server and don't want to take the extra step of having to launch mspaint directly after running my script in order to see it. It would be nice if the script could just do the launching for...
more »

2017-08-06 00:08 (1) Answers

Deadlock due to keylock involving 3 processes

I'm trying to determine how this deadlock occurred and what fix I need to do to prevent it from happening again. I've attached deadlock graph image from SSMS, resolution provided by SSMS image is not good, I apologize. What is going on here is 3 p...
more »

2017-08-05 01:08 (2) Answers

Address Parsing - Floor #

I have some address data that needs parsing. Most of the addresses info is coming in a way that works for me except for floor data. For floor data I get coming in different ways, I might get the following formats: "123 Main Street Floor 2" OR "123...
more »

2017-08-01 18:08 (2) Answers

Could not find stored procedure 'ÿþ' error

I am trying to execute a query, after reading the content from a SQL Script file, assigning it to a variable and then executing the content. Then I get this error saying Could not find stored procedure 'ÿþ'. Please help me understand the issue. Tha...
more »

2017-05-17 18:05 (1) Answers

show warning before execute sql code

I do a mistake update in sqlserver and my database be clear. I want add custom warning in Microsoft SQL SERVER software when I press execute button, for example if my update code haven't any where at his line the IDE(ms sql server 2014 or any versio...
more »

2017-04-09 11:04 (2) Answers

Join slowed by WHERE condition

One of our views, which ordinarily is pretty quick, is taking ages to run since midday yesterday. Nothing has been changed, but it is now taking minutes to run. SELECT T1.ITEMNO, ROUND(SUM(T3.TOTAL * V1.USDCOST), 3) USDTOTALCOST, ROUND(...
more »

2017-02-28 12:02 (2) Answers

Could not find SQL Server name while connecting

Cannot find SQL Server name while connecting SQL Server Management Studio: (Error-40 Couldn't open the connection to SQL Server) I tried to find server name in services of Configuration tool, but I found 'No Items' under Services. I tried by ...
more »

2017-01-08 20:01 (2) Answers

Can't rebuild indexes via EF ExecuteSqlCommand

I have the following script to rebuild indexes: DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name +']' AS Tab...
more »

2017-01-05 05:01 (1) Answers

Is there a table level collation?

In SQL server, you can set a default collation at the server and database level. You can also set a collation for a specific field but I couldn't find any reliable documentation about a default collation at the table level. I always assumed there...
more »

2016-12-08 18:12 (2) Answers

Wrong return value of OBJECT_ID?

On Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) - 12.0.5511.0 (X64) Aug 19 2016 14:32:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) with the...
more »

2016-11-09 11:11 (2) Answers

create and store very large XML output in SQL

I have a query that gives approximately four hundred thousand records [400000], up to 18mb. I parsed the output to XML output, using: for xml path('url'), Root('urlset') Now, query result doesn't show complete XML. when I try to view in XML SQL w...
more »

2016-10-22 12:10 (2) Answers

Track changes in Microsoft SQL Server database

I would like to know if there are any features in MS SQL Server that will give me the opportunity to track movement of data between tables? I am doing customer support on a database-driven application with hundreds of tables in one database. Data re...
more »

2016-08-10 13:08 (1) Answers