N.B. – this question and answer relate to the 2000 version of SQL Server. In later versions, the restriction on INSERT INTO @table_variable … EXEC … were lifted and so it doesn’t apply for those later versions. You’ll have to switch to a temp table: CREATE TABLE #tmp (code varchar(50), mount money) DECLARE @q nvarchar(4000) … Read more
The way I have done this in the past is with an infinite loop package called from SQL Server Agent, for example; This is my infinite loop package: Set 3 Variables: IsFileExists – Boolean – 0 FolderLocation – String – C:\Where the file is to be put in\ IsFileExists Boolean – 0 For the For … Read more
SELECT DATEADD(day,DATEDIFF(day,’19000107′,DATEADD(month,DATEDIFF(MONTH,0,GETDATE() /*YourValuehere*/),30))/7*7,’19000107′) Edit: A correct, final, working answer from my colleague.
You are trying to restore a SQL Server 2000 database on SQL Server 2012. This is not supported. You will need to restore your database on an instance of SQL Server 2005, 2008 or 2008 R2 first, then back it up from there, then restore the new backup on SQL Server 2012. Microsoft explains this … Read more
Here is how you can solve this using a single WHERE clause: WHERE (@myParm = value1 AND MyColumn IS NULL) OR (@myParm = value2 AND MyColumn IS NOT NULL) OR (@myParm = value3) A naïve usage of the CASE statement does not work, by this I mean the following: SELECT Field1, Field2 FROM MyTable WHERE … Read more
A date is a point in time, not a time span. ’12/31/2010′ is a point, too. Namely, it’s the midnight of the 31st of December. Everything that happened after this point is ignored. That’s exactly the behaviour you want (even if you haven’t realised that yet). Do not think that when you choose to omit … Read more
i can do this already… i select form table “Sysjobserver” in database “msdb” for read status, date, time of job that i want. use this code public void GetJobsAndStatus() { string sqlJobQuery = “select j.job_id, j.name, j.enabled, jh.run_status,” + ” js.last_outcome_message, jh.run_date, jh.step_name, jh.run_time” + ” from sysjobs j left join sysjobhistory jh on (j.job_id … Read more
Some hacking required but we can do this with LIKE, PATINDEX, LEFT AND RIGHT and good old string concatenation. create table test ( id int identity(1, 1) not null, val varchar(25) not null ) insert into test values (‘< <- ok, < <- nok’) while 1 = 1 begin update test set val = left(val, … Read more
Without more details, it’s hard to give direct examples, but here is the basic idea: First, outside of the stored procedure, the host server (the server the stored procedure will be on) has to know about the second server, including (possibly) login information. On your main server, run the sp_addlinkedserver stored procedure. This only has … Read more
I believe this would be somewhere close. INSERT INTO Files (FileId, FileData) SELECT 1, * FROM OPENROWSET(BULK N’C:\Image.jpg’, SINGLE_BLOB) rs Something to note, the above runs in SQL Server 2005 and SQL Server 2008 with the data type as varbinary(max). It was not tested with image as data type.