February 9, 2015 at 5:21 am
Hi All,
I am trying to insert the queries in a table such as the following, not able to insert it. Please help me out for the same.
create table test (txt Varchar(200))
insert into test values('select *from master.dbo.sysprocesses WHERE status NOT IN('sleeping','background')')
Thanks in advance.
M.I
________________________________________
M.I.
[font="Times New Roman"]
February 9, 2015 at 5:34 am
I would make the VARCHAR field a little bigger especially if you are going to build the contents of the TXT column dynamically,
With the statement you provided the string in the VALUES part evaluated to.
select *from master.dbo.sysprocesses WHERE status NOT IN(
The engine has no idea what the word Sleeping is, I'm guessing it was a syntax error.
As the single quote would typically note the end of the string, by using two single quotes the engine knows to treat it as a literal string.
The code below should fix the problem
create table test (txt Varchar(200))
insert into
test
values('select *from master.dbo.sysprocesses WHERE status NOT IN(''sleeping'',''background'')')
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 9, 2015 at 6:03 am
Thanks Jason for your reply.
I am trying to achive to capture all the transactions which is goin on my database in a different database table. In that case i dont have any idea of any query which is having single quote string in it or not. I just want to capture that and store in a table.
________________________________________
M.I.
[font="Times New Roman"]
February 9, 2015 at 7:12 am
sysprocesses is an old compatibility view from the sql2000 days. Stop using it.
Use sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_exec_connections instead.
Don't re-invent the wheel. There's a great stored procedure by Adam Machanic called sp_WhoIsActive which does this and much more. Use this if possible.
If you want to store active sessions at regular intervals, the Data Collector might be for you. Have you tried it?
-- Gianluca Sartori
February 10, 2015 at 1:53 am
Thanks Gianluca for your kind reply. It is just for example i quoted the script. I achieved my requirement. I am going to implement a centralize monitoring in our environment. I cant use the Adam's procedure in my environment as per organization policy. Anyway its good chat with all of you.
Thank you all for your help. 🙂
________________________________________
M.I.
[font="Times New Roman"]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply