The following code works fine when executed directly in Sql server: DECLARE @cmd sysname set @cmd = 'dir "C:\A_Projects"' EXEC master..xp_cmdshell @cmd but when I try to create a stored procedure as follows: create procedure zz (@cmdin varchar(255)) EXEC master..xp_cmdshell @cmdin I get a message 'Incorrect syntax near the keyword '...
SQL Server 2005. Is there a sql query that will return a text field containing the same type of schema info as you would find in doing a right click table -> Script Table As -> Create To (or Alter To) from SQL Server Management Studio ? I'm looking for a single/flat format that describes the entire table, including constraints, indices...
I have a table with varchar(128) column with data like: word1, word2, , word3, word4 word1, word2, word3, , word1,,,, ; word2 ;word1 word2, word3 I have to update this table, making words reverse order: word4, ,word3,,word2 ,word1 ,,word3, ,word2, word1 Could you help me to do it using only single sql query?? ...
I am trying to become more efficient in my SQL programming. I am trying to run a loop to repeat an update command on field names that only change by a numerical suffix. For example, instead of writing out x_1, y_1, then x_2, y_2 for each update: DECLARE @a INT DECLARE @b VARCHAR SET @a = 1 WHILE @a < 30 set @b = @a BEGIN UP...
If I have several SPs SP1 SP2 some_inline_queries how do I ensure that they are all run at once without interruption from other threads? Is it possible to do this from SQL Server level? edit: Let's say we have a main script with 3 major actions: sp1 scans table t1 to generate a random string that is unique to column c1 in t1; s...
I would like to pass USER VARIABLES from my package into Script Tranformation Editor. How do I add this into my Row.Reference1 below? Here's the script that I use: Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptM...
It is said this table is contained in 'master', but I couldn't find it. Do somebody know the reason? How to fix it? ...
I recall that the import tool in sql server 2000 would copy indexes, relationships, etc. In sql server 2005/2008 the import tool in SSMS will only create the tables, copy the data, but the keys, indexes, relationships are missing. I can find no option in the import wizard to enable this? What am I missing here? Is this not possible anym...
I have an SQL 2005 database and I know that in the database there is a table which has got some xml strings in it. How can I find this table(s)? ...
hi guys, I have 2 dates. I want to get number of days between 2 dates in storedprocedure. ...
How do I delete a column from an existing table? ...
I'm currently looking at disaster recovery plans and restoring backups of master/msdb/model. In theory if I restore master, msdb, model and the user databases onto a fresh server install (with the same service packs etc) then I will have an exact copy of the original server, with all the maintenance plans and jobs and everything. But w...
I'm struggling with a T-SQL procedure and I am hoping you can help. I need to know if A row exists in a table for a given ID If one (or more) does exist then the latest one has another ID set to 5. So the first table we need to get the row out of has two relevant ID's: The CaseID and LocationID, these are both integers. The second ...
I have a table with column ID that is identity one. Next I create new non-identity column new_ID and update it with values from ID column + 1. Like this: new_ID = ID + 1 Next I drop ID column and rename new_ID to name 'ID'. And how to set Identity on this new column 'ID'? I would like to do this programmatically! ...
OK, I'm trying to make an "empty" version of a database on another instance of SQL Server 2005. To do this, I use the Scripting wizard to make a big script that CREATEs all the tables, views, SPs, etc., adds some users, and grants permissions for them. Now, in the permission section of the script, I have (among other things) use [mast...
Hi, I'm not sure how to resolve this error: *Procedure or Function 'sp_executesql' expects parameter '@statement', which was not supplied.* for this query: DECLARE @a INT DECLARE @b VARCHAR SET @a = 1 WHILE @a < 30 BEGIN set @b = @a exec sp_executesql update source_temp set pmt_90_day = pmt_90_day + convert(money,'trans_total_'+...
I have a small SQL Server database that I need to copy on command -- I need to be able to take the mfd and ldf files at any given moment, copy them, zip them, and make them available to an end user. Right now this is possible by manually: 1) Logging onto the SQL server via Remote Desktop 2) Detaching the database via SQL Management St...
There is a process that seems to be running all the time in SQL Server 2005 activity monitor. Double clicking on it produces the following query: CREATE TABLE #tmpDBCCinputbuffer ([EVENT TYPE] NVARCHAR(512), [PARAMETERS] INT, [EVENT Info] NVARCHAR(512)) INSERT INTO #tmpDBCCinputbuffer EXEC ('DBCC INPUTBUFFER(58)') SELECT [EVEN...
Given two date/times: @start_date = '2009-04-15 10:24:00.000' @end_date = '2009-04-16 19:43:01.000' Is it possible to calculate the time elapsed between the two dates in the following format 1d 9h 19m ...
I have two Visual Studio database projects. Both build fine on my local dev environment. On the TFS build server, with a TFS build solution containing both projcets, one builds fine and the other fails with this error: Task "SqlBuildTask" Creating a model to represent the project... C:\Program Files\MSBuild\Microsoft\VisualStudio\v9.0...