Tagged Questions
12
votes
6answers
319 views
Trying to find the last time that a value has changed
I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.
Records are inserted into this table periodically. The ID will always stay the same but ...
0
votes
1answer
56 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
0
votes
1answer
46 views
EXPORT specific columns to INSERT statements
Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements?
I am trying to generate these for unit testing based on a large production table so using ...
4
votes
2answers
79 views
Difference Between SP with BEGIN/END and without BEGIN/END
I have found some SP(stored procedure) written as,
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
BEGIN
--- Actual Work
END
and some as
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
--- ...
0
votes
1answer
20 views
Searching for keywords in fulltext indexes using the “contains” function
Lets assume "and" is not a stopword, in my language it should not be.
How do I search for it using the contains function?
It is a keyword, but how do I escape the keywordness?
No luck with:
SELECT ...
1
vote
0answers
14 views
Fulltext search limiting users possibilities
We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure.
CREATE PROCEDURE [dbo].[SearchPages]
...
2
votes
3answers
99 views
Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase
Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
2
votes
1answer
63 views
Create a sub query for sum data as a new column in SQL Server
Suppose that I have a table named tblTemp which has this data:
| ID | AMOUNT |
----------------
| 1 | 10 |
| 1-1 | 20 |
| 1-2 | 30 |
| 1-3 | 40 |
| 2 | 50 |
| 3 | 60 |
...
-1
votes
1answer
49 views
Good example of long stored procedure? [SQL Server] [closed]
I have a 400-line stored procedure that I need to copy and refactor for my own purposes. I'm new to SQL but I do think that the stored procedure that I'm working with can be cleaned up. I have been ...
0
votes
0answers
52 views
Error 0xc02020f4: Data Flow Task 1: The column cannot be processed because more than one code page [closed]
SQL SERVER :
i executed an query and i got result of 8 million data. I have to create a table out of this result output.
SO i simply save the result out to table1.csv.
Now when i try to import ...
1
vote
1answer
49 views
Sql Server Server Group query
Scenario
I have 4 front-end sql server.
All of them have the same db instances. Data are replicated from back-end databases.
Issue
In SQL Management Studio I have registered the servers and I have ...
0
votes
1answer
49 views
How to add custom sequence number in table which is going to be populated automatically
I have table something like this
CREATE TABLE [dbo].[ado_test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[tip] [varchar](10) NOT NULL,
[datum] [datetime] NOT NULL,
[doc_number] [int] ...
1
vote
1answer
66 views
Autogrow of database1_log file in database database1 took 1010871 milliseconds
in past i had a problem of that query been executed for 7 days. i couldnt find any solution. so i created again new database with indexes.
Now i am running a simple query:
UPDATE query
query has ...
-2
votes
1answer
37 views
Create a Leveling of Intermediaries [closed]
I have a table called Intermediary, which have some columns like
CREATE TABLE Intermediary
(
IntermediaryPK INT ,
IntermediaryID NVARCHAR(20),
IntermediaryName NVARCHAR(200),
IntermediaryTypeID ...
-1
votes
1answer
64 views
validate where each parent intermediary is also a parent to itself [duplicate]
I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any.
I need to do is first of all get all parents ...
0
votes
0answers
55 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
4
votes
1answer
80 views
Is it “legal” to CREATE and DROP #SomeTable more than once?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:
CREATE TABLE #WidgetSetting
...
0
votes
0answers
113 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
186 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
58 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
74 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
59 views
Disabled trigger still fired [closed]
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
165 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
110 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
85 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
57 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
72 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
594 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
40 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
117 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
166 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
98 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
168 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
99 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
141 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
227 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 ...
9
votes
3answers
193 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
33 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
74 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
96 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
130 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
226 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
158 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
284 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
255 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
382 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
176 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
186 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
134 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
84 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 ...
