Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
0
votes
1answer
47 views
semaphore in SQL Server
I wrote a stored procedure in sql to take a number from table.
it has lots on records which has only column.
whenever a call it , is should take the first record and return it after that delete the ...
1
vote
0answers
29 views
SQL command in stored procedure continue on error
To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases ...
0
votes
1answer
51 views
SQL Case Sensitive Query
I have two databases which return different results based on the case of the query.
On the first database I run the query
FROM [Temp].[Team].[CLASS]
WHERE [TABLE_NAME] like '%Link%'
and on ...
1
vote
1answer
28 views
Case when missing line logic
I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting
DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int
SELECT @Val1 = 5 , @Val2= 2,@Val3= ...
5
votes
1answer
111 views
How to avoid a race condition with cross db proc calls? and other concerns
I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
3
votes
2answers
84 views
Multicolumn statistics on large table yielding poor estimations
I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
3
votes
1answer
46 views
Mapping of multiple server logins to one database user
This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).
Is it possible to log multiple ...
1
vote
2answers
85 views
Expose temp tables to SSRS in stored procedure
Problem: Compare data of a given date range to the same data of up to 3 previous years, and display the tabular data and a graph in an SSRS report. However currently, my stored procedure that I have ...
2
votes
1answer
21 views
What is the difference between node types and atomic types in XML
Im Studying TSQL and I came to the chapter regarding XML, and here a question that I cannot find an answer: "What is the difference between node types and atomic types"?
Thank you!
5
votes
1answer
386 views
Use of FROM keyword on DELETE statement
The BOL and many other sources point out:
FROM
Is an optional keyword that can be used between the DELETE keyword and the target
table_or_view_name, or rowset_function_limited.
I'm used to ...
0
votes
1answer
39 views
View scripts ran by a specific user?
I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query:
Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS ...
3
votes
2answers
132 views
T-SQL - Enforce Atomic Operations x Locks
I have a database model that works similar to a banking account (one table for operations, and a trigger to update the balance). I'm currently using SQL Server 2008 R2.
Table OPERATIONS:
VL_CREDIT ...
1
vote
1answer
71 views
TSQL Query - Efficient?
I want to output data that will display various results depending on Type and/or Quantity values.
Each query calls the same table and returns the same columns. I was thinking of creating a UNION on ...
3
votes
1answer
58 views
How to replace “set rowcount”
Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release:
http://technet.microsoft.com/en-us/library/ms188774.aspx
We have some legacy code ...
2
votes
1answer
48 views
Getting each status change in a table
I have a table that holds status changes per item. The item can go in and out of a status multiple times.
ItemTable (ItemId int, Status varchar(50), DateOfChange date)
I need to pull the date of ...
2
votes
1answer
95 views
SQL Server TDE stuck encryption state 4
I'm trying to create a robust script that runs backups, backs up current certificate, creates a new certificate, backs up new certificate and regenerates database encryption keys with the new ...
1
vote
1answer
121 views
How can I create view with parameters in Sybase SQL Anywhere?
A view with Sybase cannot take parameters. Is there a way to do this?
3
votes
1answer
148 views
Get records not updated in last 30 minutes
I am trying to write a script that will get records that have not updated the table in more than 30 minutes.
Example records:
DNS_NAME LAST_PERF_TIME
esxnu01 2013-12-24 12:10:00.000
esxnu02 ...
2
votes
1answer
55 views
Get identity value of all rows inserted
I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement?
Example
insert ...
2
votes
1answer
194 views
How to changes or update local server connection in Maintenance plan job
Two days back our client changed one of our Dev Server name
After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch.
We are using sql server 2012 ...
4
votes
2answers
76 views
Why can't I view the SQL Server database created by a web application?
I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine. When the application first starts, it will create the database and tables it needs if necessary through Entity ...
0
votes
1answer
42 views
Identity Column Behaviour After Replication
Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B.
For one of the Tables, we have data as follows:
Server A - Server B
...
2
votes
1answer
88 views
Performance issue with TOP (1) on a nullable column
I need help to identify the reason for a performance issue.
Actually following query sits in a scalar-valued-function but that is not the reason for the issue since it needs the same time as query:
...
2
votes
1answer
49 views
Getting Information on active Restore in TSQL / SQL Server Management Studio
On my server a database is currently restoring according to the SQL Server Management Studio
In the history
SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'myDB'
...
1
vote
1answer
65 views
Finding rows where a column contains [ and ] characters
I need to get results where the column contains a string that includes [ ]. When I use LIKE, it takes the [] as a wildcard sequence, and doesn't work as I need.
For example, I want to search through ...
0
votes
1answer
67 views
SQL Server Full-Text Search: combining proximity term and thesaurus
Scenario
I am developing a full-text search over a products table.
The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code.
...
5
votes
2answers
97 views
In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?
SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value?
Is it UTC?
Is it local for the server the backup was taken?
Is it local for ...
0
votes
2answers
86 views
Can I improve this query?
SELECT *
FROM SameLogTable
WHERE ID_Table IN (SELECT ID_Table-1
FROM SameLogTable
WHERE <SameCondition>) OR
ID_Table IN (SELECT ID_Table
...
3
votes
4answers
160 views
How to rollback changes made to the database by a specific user?
In mssql, is it possible to rollback changes made by a specific user to the database?
For example if that user ran an update query and did not write begin transaction before, is it possible to revert ...
2
votes
1answer
98 views
Should I run a delete first before I execute index rebuild to a large table about 15 Million records
I need help for a better approach on how to approach this.
Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we ...
0
votes
2answers
72 views
Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)
I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012.
I am doing it for the first time and every thing is fine i.e. all data from a particular table ...
0
votes
1answer
556 views
Cannot find either column “dbo” or the user-defined function or aggregate “dbo.fnDiscountPrice”, or the name is ambiguous
i have created fnDiscountPrice() that references the item id so that it will find the discount price for one item now I'm needing to find the total price of all the products with that ID. so i have a ...
1
vote
2answers
131 views
Metric to standard unit conversion in SQL Server
I am giving simplified version of my two table which are as follow.
tblRecords
ID unit val
--- --- ---
1 KV 3
1 BAR 4
2 KV 7
2 KG 5
3 C 37
tblUnit
unit ...
1
vote
1answer
103 views
To use a cursor or something else in my while loop?
From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select ...
5
votes
1answer
515 views
Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?
I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT
on a SQL Server 2008 R2.
A table could look like this:
CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] (
...
1
vote
2answers
52 views
SP or query to dump “password policy” checkbox for all logins
sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for ...
1
vote
2answers
193 views
Should I load the data into Temp table or keep calling it from the normal table? [closed]
I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two ...
2
votes
1answer
83 views
Should you use the schema name in WHERE, ON, and other clauses?
I know it is good practice, and a performance boost, in SQL Server to prefix the table name with the schema (dbo.TableName) in the FROM clause of a SELECT statement but should you also use it on other ...
0
votes
1answer
94 views
Multibyte replace not working in SQL Server
Why does one work and the other doesn't?
SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI')
SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI')
My guess would be that in the ...
5
votes
1answer
156 views
Passing a literal ? to sp_MSforeachdb
I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search ...
0
votes
2answers
89 views
SQL Server compact- Query that return 2 constant columns even if query didn't return data
Please look at the following simple query:
Select CustomerID
From Customers c
Where c.CustomerId = "1234"
I want to write similar query that does the following:
Always return 2 coloumns with ...
1
vote
1answer
86 views
How to write this query in SQL Server Compact in more elegant way?
The query is :
Select
Case AStatus
When 1 then 'Success'
When 0 then 'Faliure'
END,
Case AStatus
When 1 then 'AStatus'
...
2
votes
1answer
171 views
Nonclustered Index still doing index scan
I have this simple query.
SELECT a.RecordID SubmissionID,
a.[DateSubmitted],
a.[FinalAction],
a.[FinalActionReason],
a.[FinalActionDate],
b.GradeSubmissionListID,
b.[Action],
...
0
votes
1answer
71 views
Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF
In my question over at Pull every other row, starting a 0, and concatenate them together user @AaronBertrand provided me with a chunk of code that worked fine in test but as soon as I put the ...
2
votes
2answers
122 views
Pull every other row, starting at 0, and concatenate them together
I have a temp table that will contain two columns ID and Text. The temp table will contain an unknown amount of rows but there will always be an even amount. What I need to do is select every other ...
3
votes
1answer
1k views
How to split/explode comma delimited string field into SQL query
I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216'
and I want to use it to search IN this field:
SELECT *
FROM table1
WHERE id IN (id_list)
id is integer
id_list is varchar/text
...
3
votes
3answers
124 views
Inserting two rows from single row
I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
0
votes
1answer
119 views
Can this query be optimized further?
I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
3
votes
1answer
152 views
Selecting the highest value in a column?
I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple ...
2
votes
1answer
179 views
How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used
I have a function that includes:
SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS
This is useful, for example, to remove accents in French; for example:
...
