Tagged Questions
0
votes
0answers
73 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
0
votes
0answers
82 views
How to create table set from several XSD in SQL Server 2012 or with VS 2012
Is there any solution in T-SQL or in C# how to create XML schema collection in T-SQL or C#?
Problem is I cannot use:
USE Database_test
GO
CREATE XML SCHEMA COLLECTION Collection_from_XSD AS
N'
...
1
vote
1answer
49 views
Can I use Try_Cast to give me a query result column with different datatypes?
I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need).
I am trying to make a view for this table that allows the value to display ...
0
votes
3answers
64 views
DB comparer field by field for all tables
I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added ...
0
votes
1answer
41 views
Disable trigger still fired
I've just run the following t-sql to disable a trigger (with a deploy script wrapper around osql):
disable trigger dbo.trExampleTableInsert ON dbo.ExampleTable
In SSMS I could see the trigger was ...
0
votes
3answers
130 views
Easier way to handle so many isnull() situation
Is there any good way to avoid writing so many times isnull() function inside sproc ?
I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
2
votes
2answers
77 views
Executing a table type parameter stored procedure or function within a select clause
Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
-2
votes
1answer
64 views
how cursor implementations are different for each cursor type in sql server
In Oracle, there are only 2 types of cursor i.e. Implicit and Explicit cursor. Which is easy to understand. But, in SQL Server there are 4 Cursor AFAIK i.e. Static,Dynamic,Forward Only and Scroll. ...
1
vote
1answer
51 views
how to show only changed rows
if want querying the following table :
EventDateTime (DateTime), Host (guid), Flag ( bit)
2013-04-01 05:00, {id-x}, 0
2013-04-01 05:01, {id-x}, 0
2013-04-01 05:02, {id-x}, 0
2013-04-01 05:03, {id-x}, ...
0
votes
1answer
70 views
Why is there such a huge performance difference in these two join statements?
I have a query which i first wrote as (query has been simplified a bit) :
select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = ...
0
votes
5answers
262 views
The DELETE statement conflicted with the REFERENCE constraint
I'm trying to delete all users but getting the error:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict ...
0
votes
1answer
36 views
Need help in converting this cursor to a set based operation
I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 has 8 million rows. Can you suggest ...
5
votes
1answer
104 views
Unexpected Table Scan with Parameterized LIKE
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
-8
votes
2answers
127 views
How can I check for SQL Server performance problems using T-SQL? [closed]
I know how to use the Profiler, Performance Monitor, and Activity Monitor. I want to find a way to troubleshoot performance using T-SQL. How can I check for Microsoft SQL Server performance problems ...
2
votes
3answers
82 views
Programmatically find indexes that cannot be rebuilt online
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.
Is ...
1
vote
1answer
119 views
Backup stored procedure for full, differential and log backup
I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure:
Alter PROCEDURE BackupDB
(
@BaseLocation varchar(1024),
@BackupType ...
3
votes
2answers
80 views
Update strings for all cmdexec Jobs
Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?
We'd like to change our logging location, and in principle I'd think this could be ...
2
votes
1answer
106 views
Execute sp_WhoIsActive as a query
I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?
1
vote
1answer
155 views
how to add attachment(text file) to database mail?
I have scenario
Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates
I can send a mail to client that job is ...
8
votes
3answers
162 views
Unique Identifier with Extra Characters Still Matching in Select
We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.
For ...
0
votes
1answer
32 views
Copy Permission From Windows Based user To Local user
I want to know if it is possible to copy the permissions of a windows user to a locally created user on SQL server?
0
votes
2answers
67 views
Need a way to query a table, and JOIN it with the TOP 1 related record from an other table
As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.
As stated, this query does return me 95% of the data I need --
SELECT ...
2
votes
2answers
77 views
Why do I need to use a sub query to filter down a grouped select?
If I do this --
SELECT dv.Name
,MAX(hb.[DateEntered]) as DE
FROM
[Devices] as dv
INNER JOIN
[Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name
...
2
votes
2answers
125 views
Help with tricky update statement
I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.
I have two table variables:
DECLARE
...
2
votes
2answers
175 views
difference between UPDLOCK and FOR UPDATE
given the following code:
Set conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
conn.BeginTrans
...
//This is the line it is about
RS.Open "SELECT a,b FROM c FOR ...
0
votes
2answers
141 views
Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP
I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.
I have a web based form with multiple ...
6
votes
2answers
262 views
Is there any difference between putting a column alias at the start or the end of the column definition?
I've always seen and written my column aliases as
SELECT 1 as ColumnName
but today came across a query that used
SELECT ColumnName = 1
Is there any difference in how these two queries get ...
3
votes
2answers
217 views
Subquery in the VALUES clause
Here's a real-world schema:
CREATE TABLE [dbo].[Setting]
(
[ID] [BIGINT] NOT NULL,
[Module] [NVARCHAR](400) NOT NULL,
[Property] [NVARCHAR](400) NOT NULL,
[Value] ...
3
votes
3answers
267 views
Choosing the right algorithm in HashBytes function
We need to create hash value of nvarchar data for comparison purposes.
There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
-1
votes
1answer
141 views
How to set the timeoffset in a Datetimeoffset column without changing the date part
Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well?
In the post below, we can see the SWITCHOFFSET will ...
3
votes
1answer
177 views
LIKE character length limitation
The background to this question can be found here.
The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
4
votes
1answer
125 views
Is it possible in SQL Server to change default “order by” direction from asc to desc?
This question came up in a discussion at work... given the following T-SQL statement:
select firstname, lastname from users order by lastname;
the default sort order on the order by clause is asc. ...
0
votes
1answer
71 views
Changing data type from DATE to CHAR(10) with a query
I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query.
I ...
0
votes
1answer
75 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
2
votes
1answer
50 views
Synchronize Queries Retrieving Sessions And Locks From DMVs
I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
3
votes
2answers
194 views
Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode
We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.
We need ...
1
vote
1answer
63 views
Reference the database from a stored proc?
I'm using this T-SQL to get index stats (edited for brevity):
SET @db_id = DB_ID(N'TestDB');
SET @object_id = OBJECT_ID(N'TestDB.dbo.Users');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, ...
7
votes
1answer
92 views
Trouble with SQL and aggregates
With this query:
SELECT SUM(qty) AS sumQty,
cnetprodid
FROM quoteitem
WHERE ordered = 1
AND sageSOPOrderReturnLineID IS NOT NULL
AND LEN(LTRIM(RTRIM(cnetprodid))) > 0
...
3
votes
1answer
100 views
Dissecting Joins
Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
0
votes
3answers
253 views
Non-Clustered-Index on a temporary table
I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is ...
6
votes
4answers
257 views
Returning rows where all non-NULL values are equal
Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say:
WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL
this ...
1
vote
2answers
90 views
How to link each column in sql?
I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase ...
1
vote
1answer
66 views
sp_startpublication_snapshot Parameter(s)
I am creating a stored procedure that:
Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
(If specified in the SP's parameter) Creates ...
3
votes
1answer
55 views
SP for Restoring Database and performing other tasks on it
I am creating a stored procedure for our QA dept that will do the following:
Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
0
votes
1answer
103 views
Having trouble with a T-SQL scalar variable during a create table [closed]
I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table. Can anyone tell me what is wrong with this script?
-- T-SQL MSSQL
SET NOCOUNT ON
DECLARE @tt = ...
3
votes
2answers
104 views
Find top by values by percents using range in SQL Server
What will be easier way to group records by range of percents.
Here what I try to accomplish
Select top percents between 50 and 75 * from myTable order by PKI
Here is way I did this but I have ...
2
votes
3answers
388 views
Inserting rows into other table whilst preserving IDENTITY
I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
4
votes
2answers
176 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
0
votes
1answer
440 views
How to split the comma separated list in the stored procedure
I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
6
votes
1answer
578 views
MERGE a subset of the target table
I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:
It ...
