We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.
We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'
this returns immediately.
Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.
There is no index on UDF3 and WOID is the primary key.
Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.
name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB
I'm having a problem with an update operation in a stored procedure. Itruns so slowly that it is unusable, unless I comment a part out in whichcase it is very fast. However, I need the whole thing :). I have atable of email addresses of people who want to get invited to parties.Each row contains information like email address, city, state, country,and preferences for what types of events are of interest.The primary key is an EMAILID, and has a unique constraint on the emailfield. The stored procedure receives the field data as arguments, andinserts the record if the email address passed is not in the database.This works perfectly. However, if the stored procedure is called for anemail address that already exists, it updates the existing row insteadof doing an insert. This way I can build a web page that lets peoplemodify their preferences, opt in and out of the list and so on.If I am doing an update, the stored procedure runs SUPER SLOW (and thepage times out) unless I comment out the part of the update statementfor city, state, country and zipcode. However, I really need to be ableto update this!My database has 29 million rows.Thank you for telling me anything about how I can speed up this update!Here is the SQL statement to run the stored procedure:declare @now datetime;set @now = GetUTCDate();EXEC usp_EMAIL_Subscribe @Email='dberman@sen.us', @OptOutDate=@now,@Opt_GenInterest=1, @Opt_DatePeople=0, @Opt_NewFriends=1,@Opt_OldFriends=0, @Opt_Business=1, @Opt_Couples=0, @OptOut=0,@Opt_Events=0, @City='Boston', @State='MA', @ZCode='02215',@Country='United States'Here is the stored procedure:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE [usp_EMAIL_Subscribe](@Email [varchar](50),@Opt_GenInterest [tinyint],@Opt_DatePeople [tinyint],@Opt_NewFriends [tinyint],@Opt_OldFriends [tinyint],@Opt_Business [tinyint],@Opt_Couples [tinyint],@OptOut [tinyint],@OptOutDate datetime,@Opt_Events [tinyint],@City [varchar](30), @State [varchar](20), @ZCode [varchar](10),@Country [varchar](20))ASBEGINdeclare @EmailID intset @EmailID = NULL-- Get the EmailID matching the provided email addressset @EmailID = (select EmailID from v_SENWEB_EMAIL_SUBSCRIBERS whereEmailAddress = @Email)-- If the address is new, insert the address and settings. Otherwise,UPDATE existing email profileif @EmailID is null or @EmailID = -1BeginINSERT INTO v_SENWEB_Email_Subscribers(EmailAddress, OptInDate, OptedInBy, City, StateProvinceUS, Country,ZipCode,GeneralInterest, MeetDate, MeetFriends, KeepInTouch, MeetContacts,MeetOtherCouples, MeetAtEvents)VALUES(@Email, GetUTCDate(), 'Subscriber', @City, @State, @Country, @ZCode,@Opt_GenInterest, @Opt_DatePeople,@Opt_NewFriends, @Opt_OldFriends, @Opt_Business, @Opt_Couples,@Opt_Events)EndElseBEGINUPDATE v_SENWEB_EMAIL_SUBSCRIBERSSET--City = @City,--StateProvinceUS = @State,--Country = @Country,--ZipCode = @ZCode,GeneralInterest = @Opt_GenInterest,MeetDate = @Opt_DatePeople,MeetFriends = @Opt_NewFriends,KeepInTouch = @Opt_OldFriends,MeetContacts = @Opt_Business,MeetOtherCouples = @Opt_Couples,MeetAtEvents = @Opt_Events,OptedOut = @OptOut,OptOutDate = CASEWHEN(@OptOut = 1)THEN @OptOutDateWHEN(@OptOut = 0)THEN 0ENDWHERE EmailID = @EmailIDENDreturn @@ErrorENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOFinally, here is the database schema for the table courtesy ofenterprise manager:CREATE TABLE [dbo].[EMAIL_SUBSCRIBERS] ([EmailID] [int] IDENTITY (1, 1) NOT NULL ,[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[OptinDate] [smalldatetime] NULL ,[OptedinBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[JobTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[WorkPhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[HomePhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AddressLine1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StateProvinceUS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StateProvinceOther] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[Country] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SubZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GeneralInterest] [tinyint] NULL ,[MeetDate] [tinyint] NULL ,[MeetFriends] [tinyint] NULL ,[KeepInTouch] [tinyint] NULL ,[MeetContacts] [tinyint] NULL ,[MeetOtherCouples] [tinyint] NULL ,[MeetAtEvents] [tinyint] NULL ,[OptOutDate] [datetime] NULL ,[OptedOut] [tinyint] NOT NULL ,[WhenLastMailed] [datetime] NULL) ON [PRIMARY]GOCREATE UNIQUE CLUSTERED INDEX [IX_EMAIL_SUBSCRIBERS_ADDR] ON[dbo].[EMAIL_SUBSCRIBERS]([EmailAddress]) WITH FILLFACTOR = 90 ON[PRIMARY]GOALTER TABLE [dbo].[EMAIL_SUBSCRIBERS] WITH NOCHECK ADDCONSTRAINT [DF_EMAIL_SUBSCRIBERS_OptedOut] DEFAULT (0) FOR [OptedOut],CONSTRAINT [DF_EMAIL_SUBSCRIBERS_WhenLastMailed] DEFAULT (null) FOR[WhenLastMailed],CONSTRAINT [PK_EMAIL_SUBSCRIBERS] PRIMARY KEY NONCLUSTERED([EmailID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_WhenLastMailed] ON[dbo].[EMAIL_SUBSCRIBERS]([WhenLastMailed] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptOutDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptOutDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptInDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptinDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_ZipCode] ON[dbo].[EMAIL_SUBSCRIBERS]([ZipCode]) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_STATEPROVINCEUS] ON[dbo].[EMAIL_SUBSCRIBERS]([StateProvinceUS]) ON [PRIMARY]GOMeet people for friendship, contacts,or romance using free instant messaging software! See a picture youlike? Click once for a private conversation with that person!<a href="http://www.sen.us"><imgsrc="http://www.sen.us/mirror/SENLogo_62_31.jpg"></a>*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
Hi, I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!! What's going on? Any thoughts highly appreciated. t.i.a.,ratjetoes.
I have a database that is about 300 gig. I am setting up replication to a reporting server. We are doing a series or mock loads and I will need drop the tables and reload the main database a few times before we go live. To do that I plan to stop replication and drop all the articles, drop the subscription, then load the new data, then reinitialize and restart replication.
The first time I tried to do this, when I drop the articles, it seems to be trying to "clean up" the distribution database on the reporting server and that is taking a couple of hours to do. The disruption database is about 40 gig.
Is this correct behavior in SQL2005 replication? Is there a way to avoid this? I have all the replication pieces scripted out and would like to just drop replication, reload, and then run my scripts to recreate replication. But this "clean up" is going to cause me a lot of headache if I don't figure out what is going on.
Am I going down the wrong road here? Is there an easier way to do this? Any comments would be great!!!!
Hello, When I try to display HIstory for one of my Maintenance Plan, it takes forever to bring me those results back (up to 15-20 minutes). What can be the problem? What should I check?
I used a stored procedure in my report. If I run the sp in Management Studio (on my pc, database is on a sql server) it takes only several minutes; but from reporting services (also on pc) I put it in the data tab and execute it, it takes forever, actually never finish. I want to know why it's taking so long to execute it from reporting services while it returns data instantly from Mgt Studio. There is cursor in the sp. I don't know whether this is the culprit. Anyone knows why? Thanks!
Below is the sp. --------------------------------------------------------------------
I have a row that is being used log track plays on our website.
Here's the table:
CREATE TABLE [dbo].[Music_BandTrackPlays]( [ListenDate] [datetime] NOT NULL DEFAULT (getdate()), [TrackId] [int] NOT NULL, [IPAddress] [varchar](20) ) ON [PRIMARY]
There's a CLUSTERED INDEX on ListenDate ASC and a NON CLUSTERED INDEX on the TrackId.
I have a TRIGGER on the Music_BandTrackPlays table that looks like the following:
CREATE TRIGGER [trig_Increment_Music_BandTrackPlays_PlayCount] ON [dbo].[Music_BandTrackPlays] AFTER INSERT AS UPDATE Music_BandTracks SET Music_BandTracks.PlayCount = Music_BandTracks.PlayCount + TP.PlayCount FROM (SELECT TrackId, COUNT(*) AS PlayCount FROM inserted GROUP BY TrackId) AS TP WHERE Music_BandTracks.TrackId = TP.TrackId
When a simple INSERT statement is done on the Music_BandTrackPlays table, it can take quite a long time. When I remove the TRIGGER the INSERTs are immediate. The Execution plan for the TRIGGER shows that a 'Inserted Scan' is taking up most of the resources.
How exactly is the pseudo 'inserted' table formed?
For now, I think the easiest thing to do is update my logging page so it performs 2 queries. One to UPDATE the Music_BandTracks table and increment the counter, and perform the INSERT into the Music_BandTrackPlays table seperately.
I'm ok with that solution but I would really like to understand why the TRIGGER is taking so long. The 'inserted' pseudo table will be 1 row 99% of the time. Does SQL Server perform a table scan on all 20 million rows in order to determine what's new and put it in the inserted pseudo table?
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.
Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy
Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf
I have a relatively simple update statement that runs fine from Query Analyzer and Enterprise Manager (two rows updated in less than 1 sec) but times out when I run it from a VBScript file.
Hi all,I am doing the change from having worked in Oracle for a long time toMS SQL server and am frustrated with a couple of simple SQL stmt's. Orat least they have always been easy.The SQL is pretty straightforward. I am updating a field with a Maxeffective dated row criteria. (PepopleSoft app)update PS_JOB as A set BAS_GROUP_ID = ' 'where EMPL_STATUS in ('D', 'L', 'R', 'S', 'T')and EFFDT = (select max(EFFDT) from PS_JOB where EMPLID = A.EMPLID)This stmt is not working. I am getting an error on the keyword 'as'. Ihave tried:update PS_JOB A set...update PS_JOB from PS_JOB A set...Same result, error on 'A' or error on 'from'.I also tried to add the table alias to the sub query, whichtechnically worked, but with wrong data result.So my question comes down to: How do I use a table alias in an updatestatement in MS SQL server?I worked around this by creating a temp table. But that does notfulfill my curiosity, nor is it an ideal solution.Thanks a lot,-OK
update xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit: Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit: Could it be to do with this linked server setting? Collation Compatible right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...
FROM Statements..ParticipantFundBalances pfb JOIN ( Select cp.PlanId, p.ParticipantId, @PeriodId Period, CASE WHEN a.FUND_ID = 'LOAN' Then 0 ELSEf.FundId END FundId, a.ACT_ID1, a.TOT_ACT1, a.ACT_ID2, a.TOT_ACT2, a.ACT_ID3, a.TOT_ACT3, a.ACT_ID4, a.TOT_ACT4, a.ACT_ID5, a.TOT_ACT5, a.ACT_ID6, a.TOT_ACT6, a.ACT_ID7, a.TOT_ACT7, a.ACT_ID8, a.TOT_ACT8, a.ACT_ID9, a.TOT_ACT9, a.ACT_ID10, a.TOT_ACT10, a.ACT_ID11, a.TOT_ACT11, a.ACT_ID12, a.TOT_ACT12, a.ACT_ID13, a.TOT_ACT13, a.ACT_ID14, a.TOT_ACT14, a.ACT_ID15, a.TOT_ACT15, a.ACT_ID16, a.TOT_ACT16, a.ACT_ID17, a.TOT_ACT17, a.ACT_ID18, a.TOT_ACT18, /*a.ACT_ID19, a.TOT_ACT19, a.ACT_ID20, a.TOT_ACT20, */ a.UNIT_OP, a.PRICE_OP, a.UNIT_CL, a.PRICE_CL, Cast(Rtrim(i.ALLOC_PER1) as decimal) as ALLOC_PER1 FROM ASDBF a -- Derive the unique PlanId from the Statements ClientPlan table INNER JOIN Statements..ClientPlan cp ON a.PLAN_NUM = cp.ClientPlanId AND cp.ClientId = @ClientId -- Derive the unique ParticipantId from the Statements Participant table INNER JOIN Statements..Participant p ON a.PART_ID = p.PartId--Derive the unique FundID from the Statements Fund Table...Left Outer JOIN Statements..Fund f ONa.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker OR a.FUND_ID = f.ClientFundId -- get the allocation percent from the INVSRC LEFT Outer JOIN INVSRC i ONa.FUND_ID = i.INV_ID AND a.PLAN_NUM = i.Plan_Number AND a.PART_ID = i.PART_ID
WHERE a.Import = 1 )a ON pfb.PlanId = a.PlanId AND pfb.ParticipantId = a.ParticipantId AND pfb.PeriodId = PeriodId AND pfb.FundId = a.FundId
While i insert data in my table i am checking if there are any loans in the ASDBF table and if there i am inserting a 0 in the particular i am trying to up date the with in 3 different plans in the same table..
Hi There,I have an update statement to update a field of a table (~15,000,000records). It took me around 3 hours to finish 2 weeks ago. After thatno one touched the server and no configuration changed. Untilyesterday, I re-ran it again and it took me more than 18hrs and stillnot yet finished!!!What's wrong with it? I can ran it successfully before. I have triedtwo times but the result was still the same.My SQL statement is:update [all_sales] aset a.accounting_month = b.accounting_monthfrom date_map bwhere a.sales_date >= b.start_date and a.sales_date < b.end_date;An index on [all_sales].sales_date is built successfully.A composite index on ([date_map].start_date, [date_map].end_date) isbuilt successfully.My server config is:SQL Server 2000 with Service Pack 3Windows 2000 with Service Pack 4DELL PowerEdge 6650 ServerDUAL XEON 1900MHz Processors2G RAM2G Page File on Drive C2G Page File on Drive DDELL Diagnostics on all SCSI harddisks were all PASSED.Any experts could simly give me a help????Thanks x 1,000,000,000
Could some body in microsoft database team explain this behavior? Problem is predominant when cardinality of a column is very high and a where clause is specified on that column. Both use the same index.
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition 3 x Quad-Core Xeon Processor Max Degree of Parallelism = 8 148 GB RAM
SQL Code:
Target Table: USE TPS; IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL DROP TABLE dbo.ParticipantResponse;
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.
SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME, SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME, SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE, SYSCOLUMNS.LENGTH, SYSCOLUMNS.XPREC AS PRECISIONS, SYSCOLUMNS.XSCALE AS SCALE, SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS
FROM SYSREFERENCES RIGHT OUTER JOIN SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE INNER JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON SYSREFERENCES.CONSTID = SYSOBJECTS.ID WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P' ORDER BY TABLE_NAME
I have a need to execute a SQL stmt on SQL 6.5 from a 7.0 box and vice versa. I know that in 7.0, you can create a remote server and execute statements across 7.0 servers, but can it be done from 6.5 to 7.0 and 7.0 to 6.5?
It's for a conversion project and time is the essence.
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.
I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.
does anyone have a script that cleans up profiler traces by removing the variables from tsql. thereby giving you the procedure text as written. I saw this a few months ago and havent had any luck in finding it.
I am using the kill statement to terminate a process, and this is done through my VB.net program. But I get the error
User does not have permission to use the KILL statement
This is the store procedure to call for kill statement..Is there any problem to execute it on dinamic sql?
CREATE PROCEDURE Kill_Process @DBName VARCHAR(100), @TableName VARCHAR(1000) AS SET NOCOUNT ON; DECLARE @spid smallint; DECLARE @spid2 smallint; DECLARE @loginame nchar(128); DECLARE @nsql NVARCHAR(4000); SET @loginame = 'xxxx'
EXEC Find_Lock_Info @DBName, @TableName
DECLARE ProcessCursor CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid = db_id(@dbname) AND loginame = rtrim(@loginame) AND spid <> @@spid AND spid IN (SELECT spid FROM dbo.tbl_Lock_Info where dbid = db_id(@DBName) AND OBJECT_NAME(ObjId) = @TableName)
OPEN ProcessCursor; FETCH NEXT FROM ProcessCursor INTO @spid; WHILE @@FETCH_STATUS = 0 BEGIN SET @nsql = 'KILL ' + CONVERT(nvarchar,@spid) + ''; EXEC sp_executesql @nsql; FETCH NEXT FROM ProcessCursor INTO @spid; END CLOSE ProcessCursor; DEALLOCATE ProcessCursor; GO
CREATE PROCEDURE Find_Lock_Info @DBName VARCHAR(100), @TableName VARCHAR(1000) AS
SET NOCOUNT ON
BEGIN CREATE TABLE #tmpLockInfo ( spid SMALLINT, dbid SMALLINT, ObjId INT, IndIdSMALLINT, Type NCHAR(4), ResourceNCHAR(32), ModeNVARCHAR(8), StatusNVARCHAR(5) )
INSERT INTO #tmpLockInfo ( spid, dbid, ObjId, IndId, Type, Resource, Mode, Status ) EXEC Lock_Info
TRUNCATE TABLE tbl_Lock_Info
INSERT tbl_Lock_Info SELECT spid, dbid, ObjId, Type, Status FROM #tmpLockInfo WHERE dbid = db_id(@DBName) AND ObjId <> 0 AND OBJECT_NAME(ObjId) = @TableName
I have a job that has several steps. One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action. Else exit from the job reporting failure.
I would like to know what sql stmts should i use to exit from the job (in the ELSE block) I tried to use EXIT in the else stmt but it is not exiting from the job step.
select uid, OrderID, Count(OrderID)As DupCnt from OrdDetails group by uid, OrderID having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
assume connection is established and photo cloumn as data type as image in sql table.str_insert = "INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"' " cmd = New SqlCommand(str_insert, conn)cmd.Connection = conncmd.ExecuteNonQuery()Error at insert stmt: Operator '&' is not defined for types 'string' and 'system.drawing.image'
I'm trying to execute the following stmt within a Stored Procedure on SQL Server 2005 - SELECT DISTINCT PkgActions.[PKG ID] AS PKG_ID, [APPR STATUS] AS Status, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment FROM [PkgActions] JOIN PkgApprovers ON PkgActions.[PKG ID] = PkgApprovers.[PKG ID] WHERE 1=1 and ([APPR STATUS] = 'approved' OR [APPR STATUS] = 'Denied')
It fails with the message - Incorrect syntax near 'approved'. However when I run the same stmt within the Query editor it works properly. I can't see what the SP doesn't like about this. Any thoughts?
I am trying to create a stored procedure for automating Bulk inserting into tables for one database to another. Is there any way i can pass the table name as variable to insert and select stmt
