Hi
I have a SP which queries a linked server using OpenQuery function.
The remote query includes a join and an IN clause to get the desire result. ( The linked server uses Transoft ODBC driver.)
The qry looks something like this:
select * from OpenQuery(SERVER1,
'select
distinct C.item, C.operation, C.STD_OPERATION,
D.operation_desc , C.operation_desc operation_desc
from TABLE1 C left join
(select distinct operation, operation_desc from TABLE1
where operation in (select distinct STD_OPERATION from TABLE1 where
item = ''9999999'' AND STD_OPERATION <> 0)
AND item = ''STANDARD'') D
on D.operation = C.STD_OPERATION where C.item = ''9999999'' ')
When I run this Qry I get the following error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Transoft][TSODBC][usqlsd]')' expected here (DISTINCT)]
Any help would be greatly appreciated.
thxIs the problem that Transoft can't handle the distinct keyword? It is SQL-92 compliant, but maybe the driver can't handle it? Have you tried removing distinct and running the query again?
If this is the problem, you should be able to work around the problem using a group by clause.
Hth.
Paul Barbin
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
OpenQuery using a variable
Hi,
Here's what I did:
1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @.CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @.CQUERY = 'SELECT ...'
Now, when I try to execute the OpenQuery method using that variable, it fails.
Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @.CQUERY)
I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@.CQUERY'.
Don't tell me I can't use a variable instead of a static query? What am I doing wrong?
Thanks,
Skip.i don't think you can do that, putting a variable in the from clause
you'll have to use dynamic sql
so put that statement in a EXEC(.....)|||Alright,
I tried it but I'm still having troubles with it. Here's my code (simplified version):
DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)
When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')
It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:
SELECT * FROM mytable WHERE last_name =
which is incorrect.
Any other suggestions?
Thanks again,
Skip.|||sorry if i mislead you the first time, what i meant is use EXEC if you are going to use a variable for openquery.
if you are not using a variable for openquery, then just do this:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = ''DOE''')|||Originally posted by Skippy_sc
Alright,
I tried it but I'm still having troubles with it. Here's my code (simplified version):
DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)
When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')
It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:
SELECT * FROM mytable WHERE last_name =
which is incorrect.
Any other suggestions?
Thanks again,
Skip.
Try this for instance:
DECLARE @.CQUERY varchar(8000)
SET @.CQUERY = 'SELECT * FROM OPENQUERY(MSSQL20,
''SELECT top 10 * FROM master.dbo.sysobjects where name=''+'sysobjects'+'')'
select @.CQUERY
EXECUTE(@.CQUERY)|||Thank you very much fattyacid, it works fine now!
Skipsql
Here's what I did:
1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @.CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @.CQUERY = 'SELECT ...'
Now, when I try to execute the OpenQuery method using that variable, it fails.
Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @.CQUERY)
I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@.CQUERY'.
Don't tell me I can't use a variable instead of a static query? What am I doing wrong?
Thanks,
Skip.i don't think you can do that, putting a variable in the from clause
you'll have to use dynamic sql
so put that statement in a EXEC(.....)|||Alright,
I tried it but I'm still having troubles with it. Here's my code (simplified version):
DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)
When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')
It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:
SELECT * FROM mytable WHERE last_name =
which is incorrect.
Any other suggestions?
Thanks again,
Skip.|||sorry if i mislead you the first time, what i meant is use EXEC if you are going to use a variable for openquery.
if you are not using a variable for openquery, then just do this:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = ''DOE''')|||Originally posted by Skippy_sc
Alright,
I tried it but I'm still having troubles with it. Here's my code (simplified version):
DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)
When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')
It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:
SELECT * FROM mytable WHERE last_name =
which is incorrect.
Any other suggestions?
Thanks again,
Skip.
Try this for instance:
DECLARE @.CQUERY varchar(8000)
SET @.CQUERY = 'SELECT * FROM OPENQUERY(MSSQL20,
''SELECT top 10 * FROM master.dbo.sysobjects where name=''+'sysobjects'+'')'
select @.CQUERY
EXECUTE(@.CQUERY)|||Thank you very much fattyacid, it works fine now!
Skipsql
Openquery syntax for function
There is a very complex query where I'm trying to call a function from a
linked server with input paramaters from the local database. This is the
general idea:
select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
from table1 t1
and table t2
where ...
I get a syntax error that doesn't recognize t1 and t2. How should I fix thi
s?
Thanks,Openquery() (i.e. ad-hoc/pass through function) only takes literal strings.
So, it's not possible to pass in any parameters.
Also, it's not possible to call a remote user-defined function in sqlserver
(i.e. srv.db.dbo.udf() is not allowed). So, you would have to create the
function locally.
-oj
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||Lisa
Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Statement
Table-Valued UDF?
Look at this technique written by Itzik Ben-Gan
CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
AS
BEGIN
RETURN(SELECT newinvid FROM OPENQUERY([server_name],
'SET NOCOUNT ON; DECLARE @.invid AS INT;
UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
SELECT @.invid AS newinvid;') AS O)
END
CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
AS
BEGIN
RETURN(
SELECT newinvid
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
INSERT INTO tempdb..Seq2 DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
END
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||I'm looking at more in line with your first function. but I have two
parameters (one an integer) and one a date that is being to the function tha
t
I want to use in the openquery statment. I can't get the sql right for it
though.
"Uri Dimant" wrote:
> Lisa
> Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Stateme
nt
> Table-Valued UDF?
> Look at this technique written by Itzik Ben-Gan
> CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
> AS
> BEGIN
> RETURN(SELECT newinvid FROM OPENQUERY([server_name],
> 'SET NOCOUNT ON; DECLARE @.invid AS INT;
> UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
> SELECT @.invid AS newinvid;') AS O)
> END
> CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
> AS
> BEGIN
> RETURN(
> SELECT newinvid
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> INSERT INTO tempdb..Seq2 DEFAULT VALUES
> ROLLBACK;
> SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
> END
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
>
>
linked server with input paramaters from the local database. This is the
general idea:
select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
from table1 t1
and table t2
where ...
I get a syntax error that doesn't recognize t1 and t2. How should I fix thi
s?
Thanks,Openquery() (i.e. ad-hoc/pass through function) only takes literal strings.
So, it's not possible to pass in any parameters.
Also, it's not possible to call a remote user-defined function in sqlserver
(i.e. srv.db.dbo.udf() is not allowed). So, you would have to create the
function locally.
-oj
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||Lisa
Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Statement
Table-Valued UDF?
Look at this technique written by Itzik Ben-Gan
CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
AS
BEGIN
RETURN(SELECT newinvid FROM OPENQUERY([server_name],
'SET NOCOUNT ON; DECLARE @.invid AS INT;
UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
SELECT @.invid AS newinvid;') AS O)
END
CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
AS
BEGIN
RETURN(
SELECT newinvid
FROM OPENQUERY(
[server_name],
'SET NOCOUNT ON;
INSERT INTO tempdb..Seq2 DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
END
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
> There is a very complex query where I'm trying to call a function from a
> linked server with input paramaters from the local database. This is the
> general idea:
> select openquery( linkserver, 'database.dbo.function( t1.c1, t2.c2 )'
> from table1 t1
> and table t2
> where ...
> I get a syntax error that doesn't recognize t1 and t2. How should I fix
> this?
> Thanks,|||I'm looking at more in line with your first function. but I have two
parameters (one an integer) and one a date that is being to the function tha
t
I want to use in the openquery statment. I can't get the sql right for it
though.
"Uri Dimant" wrote:
> Lisa
> Is that Scalar UDF? Is that Inline Table-Valued UDF? Is that Multi-Stateme
nt
> Table-Valued UDF?
> Look at this technique written by Itzik Ben-Gan
> CREATE FUNCTION dbo.fn_getinvid1() RETURNS int
> AS
> BEGIN
> RETURN(SELECT newinvid FROM OPENQUERY([server_name],
> 'SET NOCOUNT ON; DECLARE @.invid AS INT;
> UPDATE tempdb..Seq SET @.invid = val = val + 1; COMMIT;
> SELECT @.invid AS newinvid;') AS O)
> END
> CREATE FUNCTION dbo.fn_getinvid2() RETURNS int
> AS
> BEGIN
> RETURN(
> SELECT newinvid
> FROM OPENQUERY(
> [server_name],
> 'SET NOCOUNT ON;
> INSERT INTO tempdb..Seq2 DEFAULT VALUES
> ROLLBACK;
> SELECT SCOPE_IDENTITY() AS newinvid;') AS O)
> END
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:B9DE16A3-1346-424B-81C7-AD161EC8A848@.microsoft.com...
>
>
OPENQUERY Problem
Hi,
I have created a linked server to oracle.
I executed the query as
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
ora_owner.appointment where update_dtm > to_date(''2007-oct-11
18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
Its executing fine.
But I want to get the date from another table from my sql server.
How can I form the OPENQUERY with a variable(contains date)?
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
tie_owner.rtt_appointment where update_dtm > to_date(''+
@.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
This statement is giving error...
Incorrect sysntax at +
How do I get date in yyyy-mmm-dd hh:mm:ss format?
The same date I will form in the openquery.
This is struggling me a lot. Pls suggest an idea.
Thanks in advanceSome examples
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
<mrajanikrishna@.gmail.com> wrote in message
news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
> Hi,
> I have created a linked server to oracle.
> I executed the query as
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> Its executing fine.
> But I want to get the date from another table from my sql server.
> How can I form the OPENQUERY with a variable(contains date)?
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> tie_owner.rtt_appointment where update_dtm > to_date(''+
> @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> This statement is giving error...
> Incorrect sysntax at +
> How do I get date in yyyy-mmm-dd hh:mm:ss format?
> The same date I will form in the openquery.
> This is struggling me a lot. Pls suggest an idea.
> Thanks in advance
>|||On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Some examples
> DECLARE @.SQLx VARCHAR(500)
> DECLARE @.var VARCHAR(20)
> SET @.var = 'abcd'
> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
> EXEC(@.SQLx)
> <mrajanikris...@.gmail.com> wrote in message
> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have created a linked server to oracle.
> > I executed the query as
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> > Its executing fine.
> > But I want to get the date from another table from my sql server.
> > How can I form the OPENQUERY with a variable(contains date)?
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > tie_owner.rtt_appointment where update_dtm > to_date(''+
> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> > This statement is giving error...
> > Incorrect sysntax at +
> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
> > The same date I will form in the openquery.
> > This is struggling me a lot. Pls suggest an idea.
> > Thanks in advance- Hide quoted text -
> - Show quoted text -
Hi thank u for the reply,
What is the problem in my procedure...
DECLARE @.ApptLastUPdateDateTime varchar(30)
BEGIN
DECLARE @.sql_str VARCHAR(4000)
SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
120), FROM [LastUpdateDateTime]
SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
@.ApptLastUPDateDateTime + ''''
SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
REPLACE(@.sql_str, '''', ''') + ''')'
EXEC @.sql_str
END
I am getting error
The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
01:01:00''')' is not a valid identifier.
I am unable to fix this error.|||Replace EXEC (@.sql) with PRINT @.sql to see what script it creates in order
to debug
<mrajanikrishna@.gmail.com> wrote in message
news:1192715912.147689.145840@.i13g2000prf.googlegroups.com...
> On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Some examples
>> DECLARE @.SQLx VARCHAR(500)
>> DECLARE @.var VARCHAR(20)
>> SET @.var = 'abcd'
>> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
>> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
>> EXEC(@.SQLx)
>> <mrajanikris...@.gmail.com> wrote in message
>> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>>
>> > Hi,
>> > I have created a linked server to oracle.
>> > I executed the query as
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
>> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
>> > Its executing fine.
>> > But I want to get the date from another table from my sql server.
>> > How can I form the OPENQUERY with a variable(contains date)?
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > tie_owner.rtt_appointment where update_dtm > to_date(''+
>> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
>> > This statement is giving error...
>> > Incorrect sysntax at +
>> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
>> > The same date I will form in the openquery.
>> > This is struggling me a lot. Pls suggest an idea.
>> > Thanks in advance- Hide quoted text -
>> - Show quoted text -
>
> Hi thank u for the reply,
> What is the problem in my procedure...
> DECLARE @.ApptLastUPdateDateTime varchar(30)
> BEGIN
> DECLARE @.sql_str VARCHAR(4000)
> SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
> 120), FROM [LastUpdateDateTime]
>
> SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
> @.ApptLastUPDateDateTime + ''''
> SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
> REPLACE(@.sql_str, '''', ''') + ''')'
> EXEC @.sql_str
> END
> I am getting error
> The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
> tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
> 01:01:00''')' is not a valid identifier.
> I am unable to fix this error.
>
I have created a linked server to oracle.
I executed the query as
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
ora_owner.appointment where update_dtm > to_date(''2007-oct-11
18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
Its executing fine.
But I want to get the date from another table from my sql server.
How can I form the OPENQUERY with a variable(contains date)?
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
tie_owner.rtt_appointment where update_dtm > to_date(''+
@.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
This statement is giving error...
Incorrect sysntax at +
How do I get date in yyyy-mmm-dd hh:mm:ss format?
The same date I will form in the openquery.
This is struggling me a lot. Pls suggest an idea.
Thanks in advanceSome examples
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
<mrajanikrishna@.gmail.com> wrote in message
news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
> Hi,
> I have created a linked server to oracle.
> I executed the query as
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> Its executing fine.
> But I want to get the date from another table from my sql server.
> How can I form the OPENQUERY with a variable(contains date)?
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> tie_owner.rtt_appointment where update_dtm > to_date(''+
> @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> This statement is giving error...
> Incorrect sysntax at +
> How do I get date in yyyy-mmm-dd hh:mm:ss format?
> The same date I will form in the openquery.
> This is struggling me a lot. Pls suggest an idea.
> Thanks in advance
>|||On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Some examples
> DECLARE @.SQLx VARCHAR(500)
> DECLARE @.var VARCHAR(20)
> SET @.var = 'abcd'
> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
> EXEC(@.SQLx)
> <mrajanikris...@.gmail.com> wrote in message
> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have created a linked server to oracle.
> > I executed the query as
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> > Its executing fine.
> > But I want to get the date from another table from my sql server.
> > How can I form the OPENQUERY with a variable(contains date)?
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > tie_owner.rtt_appointment where update_dtm > to_date(''+
> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> > This statement is giving error...
> > Incorrect sysntax at +
> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
> > The same date I will form in the openquery.
> > This is struggling me a lot. Pls suggest an idea.
> > Thanks in advance- Hide quoted text -
> - Show quoted text -
Hi thank u for the reply,
What is the problem in my procedure...
DECLARE @.ApptLastUPdateDateTime varchar(30)
BEGIN
DECLARE @.sql_str VARCHAR(4000)
SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
120), FROM [LastUpdateDateTime]
SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
@.ApptLastUPDateDateTime + ''''
SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
REPLACE(@.sql_str, '''', ''') + ''')'
EXEC @.sql_str
END
I am getting error
The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
01:01:00''')' is not a valid identifier.
I am unable to fix this error.|||Replace EXEC (@.sql) with PRINT @.sql to see what script it creates in order
to debug
<mrajanikrishna@.gmail.com> wrote in message
news:1192715912.147689.145840@.i13g2000prf.googlegroups.com...
> On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Some examples
>> DECLARE @.SQLx VARCHAR(500)
>> DECLARE @.var VARCHAR(20)
>> SET @.var = 'abcd'
>> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
>> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
>> EXEC(@.SQLx)
>> <mrajanikris...@.gmail.com> wrote in message
>> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>>
>> > Hi,
>> > I have created a linked server to oracle.
>> > I executed the query as
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
>> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
>> > Its executing fine.
>> > But I want to get the date from another table from my sql server.
>> > How can I form the OPENQUERY with a variable(contains date)?
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > tie_owner.rtt_appointment where update_dtm > to_date(''+
>> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
>> > This statement is giving error...
>> > Incorrect sysntax at +
>> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
>> > The same date I will form in the openquery.
>> > This is struggling me a lot. Pls suggest an idea.
>> > Thanks in advance- Hide quoted text -
>> - Show quoted text -
>
> Hi thank u for the reply,
> What is the problem in my procedure...
> DECLARE @.ApptLastUPdateDateTime varchar(30)
> BEGIN
> DECLARE @.sql_str VARCHAR(4000)
> SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
> 120), FROM [LastUpdateDateTime]
>
> SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
> @.ApptLastUPDateDateTime + ''''
> SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
> REPLACE(@.sql_str, '''', ''') + ''')'
> EXEC @.sql_str
> END
> I am getting error
> The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
> tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
> 01:01:00''')' is not a valid identifier.
> I am unable to fix this error.
>
OPENQUERY on AS 2005 produces different results than AS 2000
I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:
With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])
Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.
Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]
This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]
As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?
Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance
Any ideas at all? I am still having this issue and it needs to be resolved.
Thanks
OPENQUERY on AS 2005 produces different results than AS 2000
I have an Analysis Server set up as a linked server I want to pass a query to from SQL. However, the number of columns returned differs. My query is as follows:
With Member Measures.PeerGroup As 'Model2CustomerRiskClass.currentmember.parent.parent.uniquename '
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{nonEmptyCrossjoin(Model2CustomerRiskClass.[Id].members ,[RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name, [RecvPay].[recvpay].Name on rows
from Model2 where ([bookdate].&[2007].&[1].&[1])
Executing this directly against the AS (on both AS 2000 and 2005) produces the same results, five columns. The first two are unnamed, but contain the Id Name and RecvPay Name. The other three are PeerGroup, BaseAmt. and Count.
Now, if I execute this statement from Query Analyzer using Select * from OpenQuery (SQL 2000 and AS 2000), I get the same five columns named as follows:
[Model2CustomerRiskClass].[Name]
[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]
This is fine as my insert statement accepts five columns in that order. However, here's the same result from the same query in Management Studio (SQL 2005 and AS 2005 both SP2 CTP): The columns are:
[Model2CustomerRiskClass].[RiskClass].[Name]
[Model2CustomerRiskClass].[GroupId].[Name]
[Model2CustomerRiskClass].[Id].[Name]
[RecvPay].[RecvPay].[Name]
[Measures].[PeerGroup]
[Measures].[BaseAmt]
[Measures].[Count]
As you see, there are two extra columns. I do not want RiskClass and GroupId levels to show up. Can I get rid of them somehow? I cannot specify my SQL select by column names since, as you see, some column names are also different between the two. I need a query which returns the same columns in both 2000 and 2005. Is this possible?
Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance
Any ideas at all? I am still having this issue and it needs to be resolved.
Thanks
OPENQUERY Informix Dirty Read
Has anyone know of a way to ensure that query on a remote database (Informix
in this case) using OPENQUERY [via a linked server] does not start a
transaction. If the query in is written directly on Informix, you would
issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
However in OPENQUERY you cannot issue:
SELECT *
FROM OPENQUERY(linkinfx,
' SET ISOLATION TO DIRTY READ
SELECT field1
FROM table1
'
I was wondering if an ODBC escape sequence might work:
SELECT *
FROM OPENQUERY(linkinfx,
' {SET ISOLATION TO DIRTY READ}
SELECT field1
FROM table1
'
All though it does not error, I am not sure that it does not start a
transaction. Unfortuanaly, I don't have a local Informix system to test
this against.
The Informix Linked server is set up via ODBC. Preferrable I would like to
set a session level setting on the Linked Server to set the transaction
isolation level to be a read uncommitted value.
Any suggestions would be appreciated.
MikeMichael
Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
"Michael McCallum" <mmccallum@.honovi.com> wrote in message
news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Has anyone know of a way to ensure that query on a remote database
> (Informix in this case) using OPENQUERY [via a linked server] does not
> start a transaction. If the query in is written directly on Informix, you
> would issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
> However in OPENQUERY you cannot issue:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' SET ISOLATION TO DIRTY READ
> SELECT field1
> FROM table1
> '
> I was wondering if an ODBC escape sequence might work:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' {SET ISOLATION TO DIRTY READ}
> SELECT field1
> FROM table1
> '
> All though it does not error, I am not sure that it does not start a
> transaction. Unfortuanaly, I don't have a local Informix system to test
> this against.
> The Informix Linked server is set up via ODBC. Preferrable I would like
> to set a session level setting on the Linked Server to set the transaction
> isolation level to be a read uncommitted value.
> Any suggestions would be appreciated.
> Mike
>|||In SQL Server ti would be Read Uncommitted, in Informix I believe that it is
Dirty Read.
In either case, I am trying to prevent the Informix system (and SQL Server)
from starting a transaction.
Thanks, Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekROn4lpFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Michael
> Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
>
> "Michael McCallum" <mmccallum@.honovi.com> wrote in message
> news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
>
in this case) using OPENQUERY [via a linked server] does not start a
transaction. If the query in is written directly on Informix, you would
issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
However in OPENQUERY you cannot issue:
SELECT *
FROM OPENQUERY(linkinfx,
' SET ISOLATION TO DIRTY READ
SELECT field1
FROM table1
'
I was wondering if an ODBC escape sequence might work:
SELECT *
FROM OPENQUERY(linkinfx,
' {SET ISOLATION TO DIRTY READ}
SELECT field1
FROM table1
'
All though it does not error, I am not sure that it does not start a
transaction. Unfortuanaly, I don't have a local Informix system to test
this against.
The Informix Linked server is set up via ODBC. Preferrable I would like to
set a session level setting on the Linked Server to set the transaction
isolation level to be a read uncommitted value.
Any suggestions would be appreciated.
MikeMichael
Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
"Michael McCallum" <mmccallum@.honovi.com> wrote in message
news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Has anyone know of a way to ensure that query on a remote database
> (Informix in this case) using OPENQUERY [via a linked server] does not
> start a transaction. If the query in is written directly on Informix, you
> would issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
> However in OPENQUERY you cannot issue:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' SET ISOLATION TO DIRTY READ
> SELECT field1
> FROM table1
> '
> I was wondering if an ODBC escape sequence might work:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' {SET ISOLATION TO DIRTY READ}
> SELECT field1
> FROM table1
> '
> All though it does not error, I am not sure that it does not start a
> transaction. Unfortuanaly, I don't have a local Informix system to test
> this against.
> The Informix Linked server is set up via ODBC. Preferrable I would like
> to set a session level setting on the Linked Server to set the transaction
> isolation level to be a read uncommitted value.
> Any suggestions would be appreciated.
> Mike
>|||In SQL Server ti would be Read Uncommitted, in Informix I believe that it is
Dirty Read.
In either case, I am trying to prevent the Informix system (and SQL Server)
from starting a transaction.
Thanks, Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekROn4lpFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Michael
> Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
>
> "Michael McCallum" <mmccallum@.honovi.com> wrote in message
> news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
>
Openquery from SQL Server to Oracle error
I want to insert records into an Oracle 8.03 database from MS SQL 2000. I have created a link and have used OPENQUERY to successfully query my Oracle tables. See example, DEV is the LINK name. I need to insert and update records from MS SQL to Oracle and also I need update MS SQL from
Oracle. Can you please give me a working example of insert and update?
example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
It makes sense that this update would work, but it got WORSE after running this:
update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
Connection Broken
From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.
The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.
Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
There are 3 articles on technet that may help or are just a wild goose chase.
PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:
FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:
FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:
UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1
Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):
UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.
I will try the syntax you suggested early next week (the server crashed and needs new drives).
What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.
In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.
SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.
Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.
FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106
Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.
Oracle. Can you please give me a working example of insert and update?
example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
It makes sense that this update would work, but it got WORSE after running this:
update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
Connection Broken
From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.
The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.
Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
There are 3 articles on technet that may help or are just a wild goose chase.
PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:
FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:
FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:
UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1
Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):
UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.
I will try the syntax you suggested early next week (the server crashed and needs new drives).
What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.
In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.
SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.
Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.
FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106
Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.
Openquery Error
Hi I am trying to connect to teradata using SQL Query Analyzer
Teradata is linked through our Linked Server
Below is the query I run
select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
mktg is schema is teradata database
The error it gives me is:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
returned 0x80040e14].
Did you try the exact command on your terradata ? Are you sure that you
are connected to the right entity (database etc, don=B4t know the
details of that one) ?
HTH; Jens Suessmeyer.
|||Jens wrote:
> Did you try the exact command on your terradata ? Are you sure that you
> are connected to the right entity (database etc, don=B4t know the
> details of that one) ?
> HTH; Jens Suessmeyer.
I can access the same teradata database using Queryman and also using
SAS no problems. I have access to three databases in Teradata and out
of three i can acess one easily using Query Analyzer but other two it
gives me error as described above.
Since I can connect to teradata through Queryman and SAS that means my
ODBC drivers and access to these database both are fine. But I am
failing to understand why Openquery is failing.
|||pradeep_raina@.hotmail.com (pradeep_raina@.hotmail.com) writes:
> Hi I am trying to connect to teradata using SQL Query Analyzer
> Teradata is linked through our Linked Server
> Below is the query I run
> select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
> mktg is schema is teradata database
> The error it gives me is:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDASQL'.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
> returned 0x80040e14].
The errors from queries to linked servers are often very difficult to
understand. Error 0x80040e14 is DB_E_ERRORSINCOMMAND, and the explanation
I find in the description for ICommandPrepare::Prepare is "The command text
contained one or more errors. Providers should use OLE DB error objects to
return details about the errors."
My interpretation is that the command fails for some reason.
Now, I don't know Teradata at all, but it looks a little funny
when you say that mktg is your schema, and then you say that you
have access to three databases on Teradata. Shouldn't you specify
the database as well? My guess is that your command fails, because
Teradata cannot find mktg.vtcsr in the database where it is looking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Teradata is linked through our Linked Server
Below is the query I run
select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
mktg is schema is teradata database
The error it gives me is:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
returned 0x80040e14].
Did you try the exact command on your terradata ? Are you sure that you
are connected to the right entity (database etc, don=B4t know the
details of that one) ?
HTH; Jens Suessmeyer.
|||Jens wrote:
> Did you try the exact command on your terradata ? Are you sure that you
> are connected to the right entity (database etc, don=B4t know the
> details of that one) ?
> HTH; Jens Suessmeyer.
I can access the same teradata database using Queryman and also using
SAS no problems. I have access to three databases in Teradata and out
of three i can acess one easily using Query Analyzer but other two it
gives me error as described above.
Since I can connect to teradata through Queryman and SAS that means my
ODBC drivers and access to these database both are fine. But I am
failing to understand why Openquery is failing.
|||pradeep_raina@.hotmail.com (pradeep_raina@.hotmail.com) writes:
> Hi I am trying to connect to teradata using SQL Query Analyzer
> Teradata is linked through our Linked Server
> Below is the query I run
> select * from openquery(teradata, 'SELECT * FROM mktg.vtcsr');
> mktg is schema is teradata database
> The error it gives me is:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDASQL'.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare
> returned 0x80040e14].
The errors from queries to linked servers are often very difficult to
understand. Error 0x80040e14 is DB_E_ERRORSINCOMMAND, and the explanation
I find in the description for ICommandPrepare::Prepare is "The command text
contained one or more errors. Providers should use OLE DB error objects to
return details about the errors."
My interpretation is that the command fails for some reason.
Now, I don't know Teradata at all, but it looks a little funny
when you say that mktg is your schema, and then you say that you
have access to three databases on Teradata. Shouldn't you specify
the database as well? My guess is that your command fails, because
Teradata cannot find mktg.vtcsr in the database where it is looking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
OPENQUERY end-of-file error
I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is
SELECT * FROM OPENQUERY( server, '
SELECT
*
FROM
(
WITH a AS
(
SELECT
a,
b,
c
FROM
table1
)
SELECT
x,
y,
z
FROM
a a1
INNER JOIN
table2 t2
ON a1.a = t2.a
)
')
When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?
Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.
I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.
Hope that helps,
John
|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?|||
Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.
|||Did this link offer you any help?
http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm
I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.
Thanks,
John
OPENQUERY end-of-file error
I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is
SELECT * FROM OPENQUERY( server, '
SELECT
*
FROM
(
WITH a AS
(
SELECT
a,
b,
c
FROM
table1
)
SELECT
x,
y,
z
FROM
a a1
INNER JOIN
table2 t2
ON a1.a = t2.a
)
')
When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?
Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.
I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.
Hope that helps,
John
|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?|||
Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.
|||Did this link offer you any help?
http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm
I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.
Thanks,
John
sqlopenquery datasource not working
Hi,
I hope someone can help with a query that's puzzling me.
I have this query that I can't understand.
SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
'SELECT createdate,loginname FROM Master.dbo.syslogins where
isntname=1'
) AS p
When I run it, it returns data, but not from the server I specified in
the Datasource. In fact I can change the Datasource to anything even
gibberish and it still returns the same set of records.On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> Hi,
> I hope someone can help with a query that's puzzling me.
> I have this query that I can't understand.
> SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> isntname=1'
> ) AS p
> When I run it, it returns data, but not from the server I specified in
> the Datasource. In fact I can change the Datasource to anything even
> gibberish and it still returns the same set of records.
The connection string that you are using is wrong. Instead of
datasource=server1 it should be server=server1.
Adi|||The argument is called "Data Source" not "datasource".
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/|||On Sep 18, 4:25 pm, Adi <adic...@.hotmail.com> wrote:
> On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> > Hi,
> > I hope someone can help with a query that's puzzling me.
> > I have this query that I can't understand.
> > SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> > Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> > 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> > isntname=1'
> > ) AS p
> > When I run it, it returns data, but not from the server I specified in
> > the Datasource. In fact I can change the Datasource to anything even
> > gibberish and it still returns the same set of records.
> The connection string that you are using is wrong. Instead of
> datasource=server1 it should be server=server1.
> Adi
Thanks, that did the trick.I can't think where I got the Datasource
from.|||On Sep 18, 4:30 pm, ML <M...@.discussions.microsoft.com> wrote:
> The argument is called "Data Source" not "datasource".
> ML
> --
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/
Thanks for your response. I did try Data Source but it did the same
thing. Problem resolved with Adi's response but thanks anyway.
I hope someone can help with a query that's puzzling me.
I have this query that I can't understand.
SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
'SELECT createdate,loginname FROM Master.dbo.syslogins where
isntname=1'
) AS p
When I run it, it returns data, but not from the server I specified in
the Datasource. In fact I can change the Datasource to anything even
gibberish and it still returns the same set of records.On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> Hi,
> I hope someone can help with a query that's puzzling me.
> I have this query that I can't understand.
> SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> isntname=1'
> ) AS p
> When I run it, it returns data, but not from the server I specified in
> the Datasource. In fact I can change the Datasource to anything even
> gibberish and it still returns the same set of records.
The connection string that you are using is wrong. Instead of
datasource=server1 it should be server=server1.
Adi|||The argument is called "Data Source" not "datasource".
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/|||On Sep 18, 4:25 pm, Adi <adic...@.hotmail.com> wrote:
> On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> > Hi,
> > I hope someone can help with a query that's puzzling me.
> > I have this query that I can't understand.
> > SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> > Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> > 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> > isntname=1'
> > ) AS p
> > When I run it, it returns data, but not from the server I specified in
> > the Datasource. In fact I can change the Datasource to anything even
> > gibberish and it still returns the same set of records.
> The connection string that you are using is wrong. Instead of
> datasource=server1 it should be server=server1.
> Adi
Thanks, that did the trick.I can't think where I got the Datasource
from.|||On Sep 18, 4:30 pm, ML <M...@.discussions.microsoft.com> wrote:
> The argument is called "Data Source" not "datasource".
> ML
> --
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/
Thanks for your response. I did try Data Source but it did the same
thing. Problem resolved with Adi's response but thanks anyway.
OPENQUERY and unicode from MySQL
Hi, all. Using SQL 2005. When I execute this query against a MySQL database set up as a linked server, cyrillic text stored in unicode in MySQL shows up as question marks (?).
SELECT *
FROM
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM documents
)
Clearly, some kind of unicode issue. I've spent a few hours reading in BOL and searching online. Must be something simple, but I'm not finding a solution. If anyone has suggestions, I'd be very grateful.
Stephen
Just to follow up, my testing this morning suggests that this is an issue with the MySQL OBDC driver, used to create the connection to the linked server in SQL Server. So if anyone has solved this issue before, of course I'd be happy to hear the answer. Has anyone used a different driver to connect to MySQL as a linked server? I don't think I can use the Cherry City Software OLE DB provider, because it doesn't seem to support large text columns (http://cherrycitysoftware.com/CCS/Providers/ProvMySQL.aspx). I need a way to connect to MySQL as a linked server, using unicode (multilingual text in different rows) and text storage in some row columns at 50,000 bytes or more.
Thanks for any suggestions.
Stephen
OPENQUERY and string
Hi
Does anyone know how to include a string in the statement of an open query?
I want to execute the following query:
select * from TEST where A like 'A'
But if use this it in an openquery like it follows
SELECT *
FROM OPENQUERY (MD_AS400, 'select * from TEST where A like 'A'')
The 'A' is not recognize like a string.
This is due to the single quote around 'A'
try this
''A'''
rule is if u need a quoted string put TWO quotes.
Gurpreet S. Gill
|||You should escape quote by putting another quote.
So your query would be
' select * from TEST where A like ''A'' '
|||Lot of lanugaues accepted the escape sequence char starts with \.
But in SQL Server (i remember in VB & MDX also) the same character will be repeated.
Code Snippet
SELECT *
FROM OPENQUERY (MD_AS400, 'select * from TEST where A like ''A''')
sqlWednesday, March 28, 2012
openning cursor inside trigger works in sql2000 but not in 2005
Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.
It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -
|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.
It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -
|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>
openning cursor inside trigger works in sql2000 but not in 2005
Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLSe...=2479
05
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.
4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLSe...=2479
05
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but ther
e
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.
4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -
|||thanks for your prompt responseredoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
>
>
openning cursor inside trigger works in sql2000 but not in 2005
Hello everyone,
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -:)|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
> >Hello everyone,
> >
> >I have a delete trigger on table, inside which there is cursor opened using
> >a dynamically generated query.
> >this worked fine on sql 2000, but on 2005, I get the following error:
> >Msg 16958
> >Could not complete cursor operation because the set options have changed
> >since the cursor was declared.
> >
> >for testing, I replaced the generated query by a static one and it worked
> >fine.
> >
> >any Ideas ?
> >
> >here is the code:
> >
> >----
> >select * into #TabTmp from deleted
> >
> >set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
> >
> >execute(@.req)
> >
> >OPEN CUR1
> >
> >FETCH CUR into @.val
> >---
> >
> >the error is generated on the "OPEN CUR1" statement.
> >I did a DBCC USEROPTIONS before and after the "execute" statement but there
> >was no change.
> >
> >thanks in advance.
>
I have a delete trigger on table, inside which there is cursor opened using
a dynamically generated query.
this worked fine on sql 2000, but on 2005, I get the following error:
Msg 16958
Could not complete cursor operation because the set options have changed
since the cursor was declared.
for testing, I replaced the generated query by a static one and it worked
fine.
any Ideas ?
here is the code:
----
select * into #TabTmp from deleted
set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
execute(@.req)
OPEN CUR1
FETCH CUR into @.val
---
the error is generated on the "OPEN CUR1" statement.
I did a DBCC USEROPTIONS before and after the "execute" statement but there
was no change.
thanks in advance.It sounds like you've hit this bug:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=247905
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||Maybe it's not the same bug - I missed the part where you
said you replaced the dynamic SQL and it worked.
So if that worked and then cursors inside triggers aren't
necessarily the best idea, if dynamic sql isn't necessarily
the best idea, then maybe it's better to just redo the logic
and change the code for the trigger?
-Sue
On Mon, 14 May 2007 12:16:02 -0700, r_samir
<rsamir@.discussions.microsoft.com> wrote:
>Hello everyone,
>I have a delete trigger on table, inside which there is cursor opened using
>a dynamically generated query.
>this worked fine on sql 2000, but on 2005, I get the following error:
>Msg 16958
>Could not complete cursor operation because the set options have changed
>since the cursor was declared.
>for testing, I replaced the generated query by a static one and it worked
>fine.
>any Ideas ?
>here is the code:
>----
>select * into #TabTmp from deleted
>set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
>execute(@.req)
>OPEN CUR1
>FETCH CUR into @.val
>---
>the error is generated on the "OPEN CUR1" statement.
>I did a DBCC USEROPTIONS before and after the "execute" statement but there
>was no change.
>thanks in advance.|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:cp6i43p2dgq16qcic4d36dodp6pl3pmq3q@.4ax.com...
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
A bug that should guide the user to a more logical
and socially accepted solution. Kewl spin-a-rama.
Why waste such talent on such a niche audience.
It's the government that really rewards such
artistic sophistry -:)|||thanks for your prompt response
redoing the logic is feasable but would take some time, we are currently
investigating this way.
but is there a way to verify whether we fall into that bug or not ?
"Sue Hoegemeier" wrote:
> Maybe it's not the same bug - I missed the part where you
> said you replaced the dynamic SQL and it worked.
> So if that worked and then cursors inside triggers aren't
> necessarily the best idea, if dynamic sql isn't necessarily
> the best idea, then maybe it's better to just redo the logic
> and change the code for the trigger?
> -Sue
> On Mon, 14 May 2007 12:16:02 -0700, r_samir
> <rsamir@.discussions.microsoft.com> wrote:
> >Hello everyone,
> >
> >I have a delete trigger on table, inside which there is cursor opened using
> >a dynamically generated query.
> >this worked fine on sql 2000, but on 2005, I get the following error:
> >Msg 16958
> >Could not complete cursor operation because the set options have changed
> >since the cursor was declared.
> >
> >for testing, I replaced the generated query by a static one and it worked
> >fine.
> >
> >any Ideas ?
> >
> >here is the code:
> >
> >----
> >select * into #TabTmp from deleted
> >
> >set @.req = 'declare CUR1 cursor for select ' + @.Cle + ' from #TabTmp'
> >
> >execute(@.req)
> >
> >OPEN CUR1
> >
> >FETCH CUR into @.val
> >---
> >
> >the error is generated on the "OPEN CUR1" statement.
> >I did a DBCC USEROPTIONS before and after the "execute" statement but there
> >was no change.
> >
> >thanks in advance.
>
opening up odbc data source in the query query inside of the server manager
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
You can set up linked servers on one of the SQL Servers and join the data.
http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx
hth
BobP
Opening SQL Server Management Studio for Query only
I finally noticed today that if I open a SQL script file on 2005 installation, it opens the query window ONLY in SQL Server Management Studio. Otherwise, no object explorer, ect.
Since MS will not provide independent Query Analyzer as in SQL 2000 and earlier - that I provide to power end users, I want to be able to give them call to "SqlWb.exe" to open only with query window.
I checked program opions (SqlWb.exe /?) with no answer.
Does anyone know how to open SQL Server Management Studio for Query window only?
This is a way we can give our power clients the "Query Analyzer" type toolset for SQL 2005 without them using full SQL 2005 Management Studio.
Create an empty SQL script file on your "H", etc. drive. Create new SHORTCUT on desktop using below string (edit if your path for EXE or SQL file is different)
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" H:\Empty_SQL.sql -nosplash
Opening Solution clears previous queries in SSMS
Yikes!
Does anyone know of a way that I can open a solution but keep my current
query windows open? Every time I go to File -> Open -> Project/Solution, it
kills any query windows I was working in.
And while I'm here, is there a way to set SSMS to open with Solution the way
you can have it open a query window when you connect?
Help is appreciated. Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Catadmin wrote:
> Yikes!
> Does anyone know of a way that I can open a solution but keep my current
> query windows open? Every time I go to File -> Open -> Project/Solution, it
> kills any query windows I was working in.
> And while I'm here, is there a way to set SSMS to open with Solution the way
> you can have it open a query window when you connect?
> Help is appreciated. Thanks,
> Catadmin
Launch another instance of SSMS?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yeah, but I'd kinda like to open up the solution in my current instance of
SSMS.
I have a solution where I keep my commonly used code templates to fix data.
During the course of a day, I might be working on new development when
suddenly I get help desk tickets asking for data fixes because someone broke
something. Rather than lose what I've got open, or have to save & reopen it,
or have to go to the whole Start->Programs hoo-ha (which is a pain when a
running query keeps pulls the focus back to SSMS while I'm trying to get to
something on the Start->Programs path), I'd like to just be able to open my
solution in Solution Explorer and have everything else remain the same.
Plus, if I open up another instance, I have to do cut-n-paste from one
instance to another. Bleargh. There's got to be a better way.
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Tracy McKibben" wrote:
> Catadmin wrote:
> > Yikes!
> >
> > Does anyone know of a way that I can open a solution but keep my current
> > query windows open? Every time I go to File -> Open -> Project/Solution, it
> > kills any query windows I was working in.
> >
> > And while I'm here, is there a way to set SSMS to open with Solution the way
> > you can have it open a query window when you connect?
> >
> > Help is appreciated. Thanks,
> >
> > Catadmin
> Launch another instance of SSMS?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Another option is to just keep those scripts in a particular
directory. Then you can create a menu item in SSMS that will
open that particular directory. That's about what I've done
at some places for the same reasons - to get quick access to
fire fighting scripts and not lose whatever else I have
open. Just go to Tools, External Tools and create a new
entry - Command Explorer and Argument of the script
directory. That might work for you.
-Sue
On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Yeah, but I'd kinda like to open up the solution in my current instance of
>SSMS.
>I have a solution where I keep my commonly used code templates to fix data.
> During the course of a day, I might be working on new development when
>suddenly I get help desk tickets asking for data fixes because someone broke
>something. Rather than lose what I've got open, or have to save & reopen it,
>or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>running query keeps pulls the focus back to SSMS while I'm trying to get to
>something on the Start->Programs path), I'd like to just be able to open my
>solution in Solution Explorer and have everything else remain the same.
>Plus, if I open up another instance, I have to do cut-n-paste from one
>instance to another. Bleargh. There's got to be a better way.|||Sue,
I see what you're talking about with the Tools -> External Tools, but I'm
not clear on what you mean by Command Explorer. Is there a particular .exe
that you're refering to with this?
Thanks,
Catadmin
"Sue Hoegemeier" wrote:
> Another option is to just keep those scripts in a particular
> directory. Then you can create a menu item in SSMS that will
> open that particular directory. That's about what I've done
> at some places for the same reasons - to get quick access to
> fire fighting scripts and not lose whatever else I have
> open. Just go to Tools, External Tools and create a new
> entry - Command Explorer and Argument of the script
> directory. That might work for you.
> -Sue
> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >SSMS.
> >
> >I have a solution where I keep my commonly used code templates to fix data.
> > During the course of a day, I might be working on new development when
> >suddenly I get help desk tickets asking for data fixes because someone broke
> >something. Rather than lose what I've got open, or have to save & reopen it,
> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >something on the Start->Programs path), I'd like to just be able to open my
> >solution in Solution Explorer and have everything else remain the same.
> >
> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >instance to another. Bleargh. There's got to be a better way.
>|||Sorry about that - it's not real clear from what I typed.
The exe is Explorer. For the Command, you reference:
%SystemRoot%\explorer.exe
so that it fires off Windows Explorer. It will start in
whatever directory you list for the Arguments.
-Sue
On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Sue,
>I see what you're talking about with the Tools -> External Tools, but I'm
>not clear on what you mean by Command Explorer. Is there a particular .exe
>that you're refering to with this?
>Thanks,
>Catadmin
>"Sue Hoegemeier" wrote:
>> Another option is to just keep those scripts in a particular
>> directory. Then you can create a menu item in SSMS that will
>> open that particular directory. That's about what I've done
>> at some places for the same reasons - to get quick access to
>> fire fighting scripts and not lose whatever else I have
>> open. Just go to Tools, External Tools and create a new
>> entry - Command Explorer and Argument of the script
>> directory. That might work for you.
>> -Sue
>> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
>> <goldpetalgraphics@.yahoo.com> wrote:
>> >Yeah, but I'd kinda like to open up the solution in my current instance of
>> >SSMS.
>> >
>> >I have a solution where I keep my commonly used code templates to fix data.
>> > During the course of a day, I might be working on new development when
>> >suddenly I get help desk tickets asking for data fixes because someone broke
>> >something. Rather than lose what I've got open, or have to save & reopen it,
>> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>> >running query keeps pulls the focus back to SSMS while I'm trying to get to
>> >something on the Start->Programs path), I'd like to just be able to open my
>> >solution in Solution Explorer and have everything else remain the same.
>> >
>> >Plus, if I open up another instance, I have to do cut-n-paste from one
>> >instance to another. Bleargh. There's got to be a better way.
>>|||Thank you, Sue, but that's still not quite what I want. This doesn't enable
me to open an entire solution in SSMS while keeping current query windows
open.
If anyone from MS is reading this post, I'm sure there are other people who
would like to do the same thing I want to do (hint, hint).
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Sue Hoegemeier" wrote:
> Sorry about that - it's not real clear from what I typed.
> The exe is Explorer. For the Command, you reference:
> %SystemRoot%\explorer.exe
> so that it fires off Windows Explorer. It will start in
> whatever directory you list for the Arguments.
> -Sue
> On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Sue,
> >
> >I see what you're talking about with the Tools -> External Tools, but I'm
> >not clear on what you mean by Command Explorer. Is there a particular .exe
> >that you're refering to with this?
> >
> >Thanks,
> >
> >Catadmin
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Another option is to just keep those scripts in a particular
> >> directory. Then you can create a menu item in SSMS that will
> >> open that particular directory. That's about what I've done
> >> at some places for the same reasons - to get quick access to
> >> fire fighting scripts and not lose whatever else I have
> >> open. Just go to Tools, External Tools and create a new
> >> entry - Command Explorer and Argument of the script
> >> directory. That might work for you.
> >>
> >> -Sue
> >>
> >> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> >> <goldpetalgraphics@.yahoo.com> wrote:
> >>
> >> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >> >SSMS.
> >> >
> >> >I have a solution where I keep my commonly used code templates to fix data.
> >> > During the course of a day, I might be working on new development when
> >> >suddenly I get help desk tickets asking for data fixes because someone broke
> >> >something. Rather than lose what I've got open, or have to save & reopen it,
> >> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >> >something on the Start->Programs path), I'd like to just be able to open my
> >> >solution in Solution Explorer and have everything else remain the same.
> >> >
> >> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >> >instance to another. Bleargh. There's got to be a better way.
> >>
> >>
>|||No it won't open a solution. Like I said it will allow you
to open scripts in a specific directory but not a solution.
I think it's the only way around not losing what queries you
already have open if you don't want to open another SSMS
session. You could always search the product feedback site
to see if others posted this or post the request yourself:
http://lab.msdn.microsoft.com/productfeedback/
-Sue
On Tue, 19 Sep 2006 03:51:01 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Thank you, Sue, but that's still not quite what I want. This doesn't enable
>me to open an entire solution in SSMS while keeping current query windows
>open.
>If anyone from MS is reading this post, I'm sure there are other people who
>would like to do the same thing I want to do (hint, hint).
>Catadmin
Does anyone know of a way that I can open a solution but keep my current
query windows open? Every time I go to File -> Open -> Project/Solution, it
kills any query windows I was working in.
And while I'm here, is there a way to set SSMS to open with Solution the way
you can have it open a query window when you connect?
Help is appreciated. Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Catadmin wrote:
> Yikes!
> Does anyone know of a way that I can open a solution but keep my current
> query windows open? Every time I go to File -> Open -> Project/Solution, it
> kills any query windows I was working in.
> And while I'm here, is there a way to set SSMS to open with Solution the way
> you can have it open a query window when you connect?
> Help is appreciated. Thanks,
> Catadmin
Launch another instance of SSMS?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yeah, but I'd kinda like to open up the solution in my current instance of
SSMS.
I have a solution where I keep my commonly used code templates to fix data.
During the course of a day, I might be working on new development when
suddenly I get help desk tickets asking for data fixes because someone broke
something. Rather than lose what I've got open, or have to save & reopen it,
or have to go to the whole Start->Programs hoo-ha (which is a pain when a
running query keeps pulls the focus back to SSMS while I'm trying to get to
something on the Start->Programs path), I'd like to just be able to open my
solution in Solution Explorer and have everything else remain the same.
Plus, if I open up another instance, I have to do cut-n-paste from one
instance to another. Bleargh. There's got to be a better way.
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Tracy McKibben" wrote:
> Catadmin wrote:
> > Yikes!
> >
> > Does anyone know of a way that I can open a solution but keep my current
> > query windows open? Every time I go to File -> Open -> Project/Solution, it
> > kills any query windows I was working in.
> >
> > And while I'm here, is there a way to set SSMS to open with Solution the way
> > you can have it open a query window when you connect?
> >
> > Help is appreciated. Thanks,
> >
> > Catadmin
> Launch another instance of SSMS?
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Another option is to just keep those scripts in a particular
directory. Then you can create a menu item in SSMS that will
open that particular directory. That's about what I've done
at some places for the same reasons - to get quick access to
fire fighting scripts and not lose whatever else I have
open. Just go to Tools, External Tools and create a new
entry - Command Explorer and Argument of the script
directory. That might work for you.
-Sue
On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Yeah, but I'd kinda like to open up the solution in my current instance of
>SSMS.
>I have a solution where I keep my commonly used code templates to fix data.
> During the course of a day, I might be working on new development when
>suddenly I get help desk tickets asking for data fixes because someone broke
>something. Rather than lose what I've got open, or have to save & reopen it,
>or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>running query keeps pulls the focus back to SSMS while I'm trying to get to
>something on the Start->Programs path), I'd like to just be able to open my
>solution in Solution Explorer and have everything else remain the same.
>Plus, if I open up another instance, I have to do cut-n-paste from one
>instance to another. Bleargh. There's got to be a better way.|||Sue,
I see what you're talking about with the Tools -> External Tools, but I'm
not clear on what you mean by Command Explorer. Is there a particular .exe
that you're refering to with this?
Thanks,
Catadmin
"Sue Hoegemeier" wrote:
> Another option is to just keep those scripts in a particular
> directory. Then you can create a menu item in SSMS that will
> open that particular directory. That's about what I've done
> at some places for the same reasons - to get quick access to
> fire fighting scripts and not lose whatever else I have
> open. Just go to Tools, External Tools and create a new
> entry - Command Explorer and Argument of the script
> directory. That might work for you.
> -Sue
> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >SSMS.
> >
> >I have a solution where I keep my commonly used code templates to fix data.
> > During the course of a day, I might be working on new development when
> >suddenly I get help desk tickets asking for data fixes because someone broke
> >something. Rather than lose what I've got open, or have to save & reopen it,
> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >something on the Start->Programs path), I'd like to just be able to open my
> >solution in Solution Explorer and have everything else remain the same.
> >
> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >instance to another. Bleargh. There's got to be a better way.
>|||Sorry about that - it's not real clear from what I typed.
The exe is Explorer. For the Command, you reference:
%SystemRoot%\explorer.exe
so that it fires off Windows Explorer. It will start in
whatever directory you list for the Arguments.
-Sue
On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Sue,
>I see what you're talking about with the Tools -> External Tools, but I'm
>not clear on what you mean by Command Explorer. Is there a particular .exe
>that you're refering to with this?
>Thanks,
>Catadmin
>"Sue Hoegemeier" wrote:
>> Another option is to just keep those scripts in a particular
>> directory. Then you can create a menu item in SSMS that will
>> open that particular directory. That's about what I've done
>> at some places for the same reasons - to get quick access to
>> fire fighting scripts and not lose whatever else I have
>> open. Just go to Tools, External Tools and create a new
>> entry - Command Explorer and Argument of the script
>> directory. That might work for you.
>> -Sue
>> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
>> <goldpetalgraphics@.yahoo.com> wrote:
>> >Yeah, but I'd kinda like to open up the solution in my current instance of
>> >SSMS.
>> >
>> >I have a solution where I keep my commonly used code templates to fix data.
>> > During the course of a day, I might be working on new development when
>> >suddenly I get help desk tickets asking for data fixes because someone broke
>> >something. Rather than lose what I've got open, or have to save & reopen it,
>> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
>> >running query keeps pulls the focus back to SSMS while I'm trying to get to
>> >something on the Start->Programs path), I'd like to just be able to open my
>> >solution in Solution Explorer and have everything else remain the same.
>> >
>> >Plus, if I open up another instance, I have to do cut-n-paste from one
>> >instance to another. Bleargh. There's got to be a better way.
>>|||Thank you, Sue, but that's still not quite what I want. This doesn't enable
me to open an entire solution in SSMS while keeping current query windows
open.
If anyone from MS is reading this post, I'm sure there are other people who
would like to do the same thing I want to do (hint, hint).
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Sue Hoegemeier" wrote:
> Sorry about that - it's not real clear from what I typed.
> The exe is Explorer. For the Command, you reference:
> %SystemRoot%\explorer.exe
> so that it fires off Windows Explorer. It will start in
> whatever directory you list for the Arguments.
> -Sue
> On Mon, 11 Sep 2006 04:36:02 -0700, Catadmin
> <goldpetalgraphics@.yahoo.com> wrote:
> >Sue,
> >
> >I see what you're talking about with the Tools -> External Tools, but I'm
> >not clear on what you mean by Command Explorer. Is there a particular .exe
> >that you're refering to with this?
> >
> >Thanks,
> >
> >Catadmin
> >
> >"Sue Hoegemeier" wrote:
> >
> >> Another option is to just keep those scripts in a particular
> >> directory. Then you can create a menu item in SSMS that will
> >> open that particular directory. That's about what I've done
> >> at some places for the same reasons - to get quick access to
> >> fire fighting scripts and not lose whatever else I have
> >> open. Just go to Tools, External Tools and create a new
> >> entry - Command Explorer and Argument of the script
> >> directory. That might work for you.
> >>
> >> -Sue
> >>
> >> On Thu, 7 Sep 2006 08:19:02 -0700, Catadmin
> >> <goldpetalgraphics@.yahoo.com> wrote:
> >>
> >> >Yeah, but I'd kinda like to open up the solution in my current instance of
> >> >SSMS.
> >> >
> >> >I have a solution where I keep my commonly used code templates to fix data.
> >> > During the course of a day, I might be working on new development when
> >> >suddenly I get help desk tickets asking for data fixes because someone broke
> >> >something. Rather than lose what I've got open, or have to save & reopen it,
> >> >or have to go to the whole Start->Programs hoo-ha (which is a pain when a
> >> >running query keeps pulls the focus back to SSMS while I'm trying to get to
> >> >something on the Start->Programs path), I'd like to just be able to open my
> >> >solution in Solution Explorer and have everything else remain the same.
> >> >
> >> >Plus, if I open up another instance, I have to do cut-n-paste from one
> >> >instance to another. Bleargh. There's got to be a better way.
> >>
> >>
>|||No it won't open a solution. Like I said it will allow you
to open scripts in a specific directory but not a solution.
I think it's the only way around not losing what queries you
already have open if you don't want to open another SSMS
session. You could always search the product feedback site
to see if others posted this or post the request yourself:
http://lab.msdn.microsoft.com/productfeedback/
-Sue
On Tue, 19 Sep 2006 03:51:01 -0700, Catadmin
<goldpetalgraphics@.yahoo.com> wrote:
>Thank you, Sue, but that's still not quite what I want. This doesn't enable
>me to open an entire solution in SSMS while keeping current query windows
>open.
>If anyone from MS is reading this post, I'm sure there are other people who
>would like to do the same thing I want to do (hint, hint).
>Catadmin
Subscribe to:
Posts (Atom)