Tagged Questions
3
votes
1answer
28 views
SP with Encryption Read/Writes Stats
If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
3
votes
2answers
72 views
How can I search the full text of a stored procedure for a value?
I use the following script to search the text of all stored procedures when I want to find specific values.
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION ...
2
votes
2answers
64 views
The multi-part identifier “xxx” could not be bound
The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);
HAVING (SELECT COUNT(*) FROM
(
SELECT *
FROM ...
0
votes
2answers
99 views
From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads
I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0.
Now I am using features of SQL Server like:
Common Table Expressions (I have a table ...
2
votes
2answers
81 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 ...
5
votes
1answer
82 views
Best practice for copying tables from server to server
Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB)
...
0
votes
2answers
59 views
How to Drop Tables using a variable in SQL Server?
DETAILS:
I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.
WHY?:
In my Access Database I am running a ...
3
votes
5answers
226 views
Stored Procedures under Source Control, best practice
I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
5
votes
2answers
177 views
Finding stored procedures with missing indexes
How to scan for stored proc that need indexes to be created.
Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
4
votes
1answer
140 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
5
votes
3answers
205 views
Create procedure in else block on SQL Server
I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things.
I was able to generate ...
8
votes
4answers
478 views
Can I launch a stored procedure and immediately return without waiting for it to finish?
We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.
I have a second stored procedure that should be run after ...
4
votes
1answer
124 views
Stored procedure performance metrics
I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by ...
4
votes
2answers
193 views
Stored Procedure as Create Table proxy?
With SQL Server it was my understanding, I thought, that you could elevate a users permissions with specific constraints.
For example, you need a user to create a specific table object in a specific ...
0
votes
2answers
171 views
Migrate DB2 UDB (Iseries) to SQL Server
I'm looking for a tools that allows migration of DB2/UDB databases to SQL Server.
It's quite easy migrate schema and data using linked server or import export with SISS.
But what about stored ...
1
vote
0answers
203 views
How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?
This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
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 ...
6
votes
2answers
479 views
How to keep history of SQL Server stored procedure revisions
Note: I am not asking about full version control.
Is there any way automatically to keep a history of stored procedures on SQL Server.
Similar to how Google Docs automatically keeps a history of ...
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 ...
3
votes
2answers
61 views
With the advent of integrated source control, do stored procedure changes still need comments?
Back in the old days, my team had a policy that any stored procedure modification required comments in two places:
Top of the stored procedure with an overall summary of the changes
made
Comments ...
2
votes
3answers
186 views
Verify all Stored Procedures
How can I verify all stored procedures in a SQL Server database? I need to verify if all stored procedures still work, after some tables/views etc. have been deleted or changed.
1
vote
3answers
472 views
Support both stored procedures and functions with same TSQL code base?
We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends.
Given the following ...
0
votes
1answer
442 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.
2
votes
1answer
156 views
Error in restore database
I use this query in SQL Server 2008 R2 to restore DB1 database:
RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
The following error ...
0
votes
2answers
331 views
How to avoid using the IN clause in sql query used in a stored procedure
Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the ...
2
votes
1answer
76 views
Where to put utility (not administration) stored procedures in SQL Server? [duplicate]
Possible Duplicate:
Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?
I have some simple user-defined functions ...
5
votes
1answer
203 views
Conditional compilation of SQL Server stored procedure
Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010?
I'm using SQL Server Data Tools in ...
1
vote
1answer
213 views
How can I restore the sql_variant_property of baseType back to a table variable in SQL Server
Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008
I have a table that I am trying to use as a storage container. It has 3 columns:
reportID integer,
dataLabel ...
1
vote
1answer
79 views
Binding errors with dependent stored procedures
We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub ...
5
votes
2answers
1k views
Separate stored procedures for inserts and updates?
I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures:
InsertNewPerson
UpdatePertsonById
But I was thinking to write ...
2
votes
1answer
150 views
Only a single WITH MOVE clause should be specified for any logical file name
I'm trying to do a copy database by scripting a backup and restore. I have the backup procedure working correctly but I keep getting the error
Conflicting file relocations have been specified ...
11
votes
1answer
203 views
Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?
Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a ...
3
votes
1answer
199 views
“Thread was being aborted” error when inserting 25k rows
I need to store about 25k rows worth of data into a single table about once a week. To achieve this, I'm creating an object (created from parsing XML), and sending the data within that object to a ...
3
votes
1answer
680 views
How can I create a stored procedure using xp_fixeddrives?
I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008
ALTER PROCEDURE freespace
AS
...
0
votes
1answer
603 views
Calling stored procedure with select and update statement
I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like
create procedure testproc
as
select ...
2
votes
0answers
140 views
Stored procedure doesn't return results or any messages when executed as other privileged user [closed]
I have many stored procedures with...
EXECUTE permission given to role XXXUser.
Login XXXLogin is created and assigned to the role of XXXUser
There is one particular stored procedure that I have ...
4
votes
1answer
132 views
Detecting if bit set in numeric value
I have a tinyint column that represents a set of flags. Assuming I wanted to know if a specific bit was set or not, how can I do bitwise AND/OR in a stored procedure?
22
votes
1answer
459 views
In SQL Server, what is the purpose of grouping stored procedures?
One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by ...
3
votes
1answer
328 views
Stored procedure to check for missing fields in a table
I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... ...
0
votes
1answer
533 views
How to automatically regularly a week backup SQL Server database if changes exist in the database for the previous week
I´m new to SQL Server administration.
I´d like to create automatically backup database every sunday night, if there was any change in previous week (if not, backup is unnecessary).
Is there some way ...
5
votes
2answers
181 views
Why you want to avoid Dynamic SQL in stored procedure?
I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
2
votes
1answer
117 views
Find out what setting were used for a given stored procedure
As a follow-up to my previous question:
is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ?
It seems my ...
8
votes
3answers
256 views
Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?
I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.
I am not a real DBA, I'm a software developer who is ...
3
votes
1answer
95 views
can sql generate a good plan for this procedure?
I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
9
votes
1answer
2k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
5
votes
3answers
668 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
6
votes
2answers
462 views
Parameter Sniffing: Why Does This Become An Issue?
Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds).
After suspecting ...
4
votes
2answers
280 views
Using the correct database when calling a system stored procedure in SQL Server 2008
I think there's something I'm missing when it comes to which database is being used when running a custom system stored procedure. I have the following in my stored procedure (edited for brevity):
...
4
votes
3answers
325 views
SQL Server stored procedure keeps restoring to previous one
There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure ...
