Tagged Questions
2
votes
2answers
80 views
Cannot rebuild index, but there's no reason why not?
I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
1
vote
1answer
40 views
How can we get tablename from inside of Trigger Code?
I have requirement that, need to pass tablename from trigger to procedure.
ALTER TRIGGER [dbo].[Trg_ProjectCreation]
ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- ...
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 ...
1
vote
3answers
83 views
How to handle update or insert situations
From time to time my stored procedures looks like
create procedure handle_data
@fk int
,@value varchar(10)
as
begin
if exists (select * from my_table ...
0
votes
0answers
46 views
T-SQL Script for segmenting data into finer categories based on percentage measure in another table
I have a SQL Server 2008 R2 database with two tables. The first table - Shops - has a list of establishments and the number of shoppers it attracts segmented by criteria A (2 categories) and B (2 ...
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 ...
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
...
10
votes
1answer
343 views
What exactly does “No Join Predicate” mean in SQL Server?
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".
But unfortunately it does not seem to be as easy as that.
For example, very ...
0
votes
1answer
293 views
how to return table on sql function
I am very new to sql create function
i wanted to check data column value at table
the purpose is to return full table where data is null , <0 , >1
the problem is i am trying make data is ...
2
votes
1answer
179 views
return percentage of records based on value in column
I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column. The percentage is given by the user. A stripped down version of the query is below
...
0
votes
0answers
118 views
RAND in T-sql function
Can any one tell why t-sql not allowing using RAND function in side a function
SELECT CAST (RAND() * 10000000 as varchar(20))
the error is
Invalid use of a side-effecting operator 'rand' ...
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 = ...
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 ...
2
votes
2answers
274 views
How to map another server through SQL Server Management Studio
I am trying to map another server by giving the command
EXEC xp_cmdshell 'NET USE H:\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'
I got an error with this network path not ...
1
vote
0answers
202 views
Query tuning help needed (Hash Match and Table Scans)
I need help optimizing the following query (returning ~8k rows):
SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
FROM (
...
5
votes
1answer
247 views
Query performance while using sqlcmd [duplicate]
Possible Duplicate:
Running queries with SQLCMD vs. Running queries with SSMS
I have a number of scripts that query the data from different tables and consequently update them. I have ...
1
vote
1answer
360 views
Trying to Build TreeView Query in T-SQL
I have parent records in two tables, Table1 and Table2. Some records in Table3 are children of Table1, and some are children of Table2.
I would like to build a query that I can use to populate a ...
6
votes
1answer
257 views
CATCH block is triggered when it should not
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
5
votes
3answers
405 views
Total of orders open on a given date for each date in a date range
This question is similar to Running total with count?, but please allow me to explain some further twists on the issue.
I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
3
votes
2answers
5k views
Writing select result to a csv file
We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.
I ...
5
votes
3answers
1k views
RETURN statement before DEALLOCATE CURSOR
Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure
Consider this example:
CREATE PROCEDURE [dbo].[test]
@ReturnEarly BIT = 0
AS
BEGIN
SET NOCOUNT ...
1
vote
1answer
173 views
SQL Server 2008 R2 - Weekly / Monthly Statistics
I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were ...
3
votes
1answer
94 views
Selecting 'edge' records in a sequence of data
I have a table of items which run sequentially by date with both a start and end for each entry:
ID | Start | End
----------------------------------------
1 | 2012-08-20 00:00 | ...
8
votes
2answers
148 views
Non-Clustered Indexes - keys and nonkeys
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.
Here's my theory from the query I've just optimised, through a process of trial and ...
4
votes
4answers
483 views
Speeding up COUNT(*) - WHERE clause slowing query
The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
2
votes
3answers
2k views
Questions about handling NULLs and empty strings in nvarchar and numeric fields
I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I ...
2
votes
1answer
699 views
sp_execute expects parameter '@handle' of type 'int'
I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
2
votes
2answers
387 views
Is there any way to speed up a query with 3x UNION on the same large table?
Assuming that the following query is legitimate and executes successfully, is there any way to speed it up?
SELECT foo from T1 --T1 is a huge (>5e6 rows) table with no indexes
UNION
SELECT bar ...
8
votes
2answers
427 views
Why does SELECT 1/2 return 0?
As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0.
I am using SQL Server 2008.
2
votes
2answers
458 views
Bulk grant execute permissions for scalar functions & stored procedures
We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
11
votes
2answers
998 views
Why do wildcards in GROUP BY statements not work?
I am trying to make the following SQL statement work, but I get a syntax error:
SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*
Here, A is a wide table ...
9
votes
5answers
570 views
UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL)
I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID ...
3
votes
1answer
312 views
DENY EXECUTE on one schema object hides other objects in same schema
I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give ...
2
votes
1answer
330 views
Looking for a Trigger to change inserting strings?
I have an MSSQL Server 2008 Database.
There are some tables in my Database like below :
table1, table2, table3, ....
each table has a NVARCHAR(50) field (Name)
Now, I wanna change the strings that ...
3
votes
3answers
2k views
Add a new Column and define its position in a table
I have Table A with 5 columns:
TableA
--
Name
Tel
Email
Address
I want to add a new column (mobile) in between Tel & Email:
TableA
--
Name
Tel
Mobile
Email
Address
If I use
ALTER TABLE ...
2
votes
2answers
156 views
Design best practices for last tracking with indexed view
My tables structure is below :
TbDoc (ID int , ...)
TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)
I want to have indexed view to get last TbDocActions columns for ...
5
votes
1answer
196 views
difference of left join and left outer join
In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
2
votes
1answer
304 views
New foreign key and merge replication
We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this ...
3
votes
2answers
129 views
can I convert an integer to base-2 without a function
Here's a bit of a challenge. I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek. I may not be using the proper terminology here, but this is an integer from ...
7
votes
3answers
986 views
TSQL performance - JOIN on value BETWEEN min and max
I have two tables in which I store:
an IP range - country lookup table
a list of requests coming from different IPs
The IPs were stored as bigints to improve lookup performance.
This is the table ...
5
votes
2answers
596 views
Grouping records based on intervals of time
I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
2
votes
1answer
56 views
How to consolidate script duplication?
I just want to know how you deal with long scripts and redundant information. Here is an example:
Suppose you have three tables: table1, table2, and sometable.
And you have
SELECT
id = 1
org ...
1
vote
1answer
630 views
TSQL Get minute part from time data type
With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
8
votes
2answers
856 views
In Sql Server's RANK function, how to limit the results to just the first 3 ranks
In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC)
to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
3
votes
2answers
325 views
How can I create different sets of sequential values based on another column in a table?
I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.
CREATE TABLE Notes (
Id int IDENTITY(1,1) NOT ...
1
vote
1answer
225 views
Why is the output of this query being returned like this?
I have a T-SQL query :
select CAST(0 as float(53)),CONVERT(nvarchar(max),CAST(0 as float(53)),128)
the output of which is
0,0.0E0
Can some one explain why it is not 0,0 ? The database is SQL ...
1
vote
2answers
669 views
Should I put the error handler inside the loop, or the loop inside the error handler?
When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
2
votes
1answer
3k views
Table-Valued Parameter as Output parameter for stored procedure
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
id int ...
2
votes
2answers
462 views
How to check when statistics was last executed?
We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
