Openquery with a sub-sql referening sqlserver table.

Hello.
I need to do a openquery to a linked server, and get record with id no in a sub select pointing to a table stored in SQLServer.
I have something like this:

select * into tmptable
from openquery (select * from linkedserverTable where id not in (select distinct(id) from sqlserverTable))

How to make sqserverTable not pointing to linked server, but sqlserver ?

Rgds

JCselect *
into tmptable
from openquery (linkedserver,'select * from linkedserverTable')
where id not in (select distinct id from sqlserverTable)

OPENQUERY vs. sp_executesql which is better?

I have recently seen the OPENQUERY function in a stored procedure which
was used to INSERT values into a table on a remote server. However, I
normally use the sp_executesql stored proc.
Can any one shed some light on which method is better and in what
cases?
The following code is from Microsoft website, not my own.
http://msdn.microsoft.com/library/d...br />
5xix.asp
<MS CODE>
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
</MS CODE>
VS.
<MYCODE>
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
-- OR
OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
</MYCODE>
Thanks for your time folks.
Johnny DWhy would you even consider sp_executesql in your case? There is no need to
use dynamic sql to access a linked server.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148649898.280530.174390@.j73g2000cwa.googlegroups.com...
>I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
> The following code is from Microsoft website, not my own.
> http://msdn.microsoft.com/library/d... />
z_5xix.asp
> <MS CODE>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
>
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
> </MS CODE>
> VS.
> <MYCODE>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
> -- OR
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
> </MYCODE>
>
> Thanks for your time folks.
>
> Johnny D
>|||Sorry Andrew, this was an oversight in me writing a simple query to
illustrate my question...
The reason for this is I would use a cursor to loop through my
different servernames
DECLARE @.rc INT
DECLARE @.v_sql NVARCHAR(4000)
DECLARE @.vc_servername VARCHAR(100)
DECLARE c_myservers CURSOR
FOR SELECT
servername
FROM listservers
WHERE active=1
FOR READ ONLY
OPEN c_myservers
FETCH NEXT FROM c_myservers INTO @.vc_servername
WHILE (@.@.FETCHSTATUS = 0 )
BEGIN
SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
'].dbname.joe.titles'
EXEC @.rc = sp_executesql @.v_sql
-- if @.rc <> 0
-- etc...
FETCH NEXT FROM c_myservers INTO @.vc_servername
END
CLOSE c_myservers
DEALLOCATE c_myservers
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'|||Well one key point of using OPENQUERY is that it passes the statement to the
other server where it is executed as is. That way the remote server can
choose the proper plan for that statement without regard to what the rest of
the statement is that was issued locally. Kind of hard to explain but it is
truly a pass-through query where as a linked server query may be influenced
by the rest of the statement. For instance a join to a local table.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148652836.663893.44660@.g10g2000cwb.googlegroups.com...
> Sorry Andrew, this was an oversight in me writing a simple query to
> illustrate my question...
> The reason for this is I would use a cursor to loop through my
> different servernames
> DECLARE @.rc INT
> DECLARE @.v_sql NVARCHAR(4000)
> DECLARE @.vc_servername VARCHAR(100)
> DECLARE c_myservers CURSOR
> FOR SELECT
> servername
> FROM listservers
> WHERE active=1
> FOR READ ONLY
> OPEN c_myservers
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> WHILE (@.@.FETCHSTATUS = 0 )
> BEGIN
> SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
> '].dbname.joe.titles'
> EXEC @.rc = sp_executesql @.v_sql
> -- if @.rc <> 0
> -- etc...
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> END
> CLOSE c_myservers
> DEALLOCATE c_myservers
>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
>|||Johnny D (john.dacosta@.gmail.com) writes:
> I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
I think they are as comparable as apples and oranges.
Apparently you loop over servers. That is nothing OPENQUERY can help
you with - the server name must be a constant.
Rather your choice is between:
SELECT @.sql = 'SELECT ... FROM ' + @.server + 'catalog.schema.tbl'
and
SELECT @.sql = 'SELECT ... FROM OPENQUERY(' + @.server + ', ' +
'''SELECT ... FROM catalog.schema.tbl'')'
That is, accessing the table in four-partnotation, or running a
passthrough query.

><MYCODE>
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
></MYCODE>
Ehum, I don't think you will find sp_executesql on Oracle...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

OPENQUERY vs 4-part-tablenames with linked server

Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that the server answers with correct results when I start the query on itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delete all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that the server answers with correct results when I start the query on itsself (as whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connection to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I suggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||In this case OPENQUERY should return the same result as the straight query...
Do as Tibor says, and check the query plan for both to see if you can learn anything from that...Also check/play with the collation order options on the linked server(although that should not matter with an integer comparison.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen
|||In this case OPENQUERY should return the same result as the straight query...
Do as Tibor says, and check the query plan for both to see if you can learn anything from that...Also check/play with the collation order options on the linked server(although that should not matter with an integer comparison.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where [id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen

OPENQUERY vs 4-part-tablenames with linked server

Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
JochenJochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connecti
on to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||Here are some additional information:
SQL 2000 SP3 - Build 8.00.818
The database is merge replicated, but the effect is still there when I delet
e all replicational stuff.
Some more effects:
[id]-clumn has data from von 1 - 8000. With
select * from server.database.owner.table where [id] < 6000
the result table ist still empty. With
select * from server.database.owner.table where [id] < 6001
all rows are given back with [id] < 60001. Further it is strange that th
e server answers with correct results when I start the query on itsself (as
whith OPENQUERY).
Jochen
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag news:Os4al2bXEHA.2520
@.TK2MSFTNGP12.phx.gbl...
Jochen
That's strange. I hace just tested it on my box and it works fine
As far as I know when we use OPENQUERY SQL Server opens an addition connecti
on to retrieve the data ( I am not sure for 100 percent)
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||OPENQUERY allow you to control exactly what is passed to the other DBMS. I s
uggest you use showplan
to see what is submitted to the other DBMS in both cases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message
news:udF3I4aXEHA.736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen|||In this case OPENQUERY should return the same result as the straight query..
.
Do as Tibor says, and check the query plan for both to see if you can learn
anything from that...Also check/play with the collation order options on the
linked server(although that should not matter with an integer comparison.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jochen Brggemann" <brueggemann@.ifap.de> wrote in message news:udF3I4aXEHA.
736@.TK2MSFTNGP10.phx.gbl...
Hi,
what is the difference between
a) select * from server.database.owner.table where [id] = 15
and
b) select * from openquery(server, 'select * from database.owner.table where
[id] = 15')
I have the effect that b) gives the correct result while a) has zero hits.
Can anybody help?
Jochen

OPENQUERY UPDATE Syntax help needed

Hi All

I am updating a local table based on inner join between local table
and remote table.

Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

Thanks
RS(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

I am updating a local table based on inner join between local table
and remote table.
>
Update LocalTable
SET Field1 = B.Field1
FROM LinkedServer.dbname.dbo.RemoteTable B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3
>
This query takes 18 minutes to run.
I am hoping to speed up the process by writing in OPENQUERY syntax.

UPDATE LocalTable
SET Field1 = B.Field1
FROM OPENQUERY(LINKEDSERVER,
'SELECT Field1, Field2, Field3 FROM dbname.dbo.RemoteTable) B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

I would not really expect this to perform better.

Distributed queries are always difficult, but it's difficult to suggest
anything without further knowledge about the table. How big are the
two tables?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,

Sorry but exiting problem!

The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.

Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).

I'm importing data from Excel. Before import, I want to validate it against my master list values.

[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]

In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]

I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.

I think you got what the situation is!! Woh!

Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!

- The Code -

---

create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx

create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')

-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine

---

Thanks in advance...

Hi,

normally the OLEDB provider is expecting to get something back from the query. So try to put in either a return 0 or a simple Select or string stating ('Statement executed') at the bottom of the query.

BTW, why don′t you just use the select statement rather than creating tables and so on.. select 1 union select 2 ?

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de

sql

Openquery q

Hi,
INSERT INTO my table (c1, c2, .cn)
SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"')
How can be retrieved only values From my table2( in the
linked server) that are NOT IN mytable ( or values that
are > max(values of my table )?
(The linked server is an Access, only openquery
statements are working)
Thanks for any suggestions.
DobbyThis is completely untested, but it may give you enough information to =get started...
SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn FROM "mytable2"') A
LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =3D B.PrimaryKeyColumn
WHERE B.PrimaryKeyColumn IS NULL
You could also try to write it using NOT EXISTS as well as NOT IN
-- Keith, SQL Server MVP
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message =news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
> Hi,
> > > INSERT INTO my table (c1, c2, .cn)
> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn > FROM "mytable2"')
> > How can be retrieved only values From my table2( in the > linked server) that are NOT IN mytable ( or values that > are > max(values of my table )?
> > (The linked server is an Access, only openquery > statements are working)
> > Thanks for any suggestions.
> > Dobby
> >|||Hi Keith,
It works fine!
Thank you very much!
Dobby
>--Original Message--
>This is completely untested, but it may give you enough
information to get started...
>SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"') A
>LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =B.PrimaryKeyColumn
>WHERE B.PrimaryKeyColumn IS NULL
>You could also try to write it using NOT EXISTS as well
as NOT IN
>--
>Keith, SQL Server MVP
>"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
>> Hi,
>>
>> INSERT INTO my table (c1, c2, .cn)
>> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
>> FROM "mytable2"')
>> How can be retrieved only values From my table2( in the
>> linked server) that are NOT IN mytable ( or values
that
>> are > max(values of my table )?
>> (The linked server is an Access, only openquery
>> statements are working)
>> Thanks for any suggestions.
>> Dobby
>>
>.
>

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:
>
>

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:
>
>

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.
>

Opening table in SQL Server 2005?

This ought to be easy. But I can't figure out how to do it.
Using Enterprise Manager in SQL Server 2000, I point to a table, select it,
right click and choose Open Table-> Return all rows. Now I have the table
open and I can edit to my heart's content.
How can I do the same thing in SQL Server 2005?
Hi
With Beta 2 of SQL Server 2005, that functionality is not included. It may
return in Beta 3.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David" <David@.blackdeck.com> wrote in message
news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> This ought to be easy. But I can't figure out how to do it.
> Using Enterprise Manager in SQL Server 2000, I point to a table, select
it,
> right click and choose Open Table-> Return all rows. Now I have the table
> open and I can edit to my heart's content.
> How can I do the same thing in SQL Server 2005?
|||You got to be kidding! I use that all the time! Will it definitely be in
the final release?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> With Beta 2 of SQL Server 2005, that functionality is not included. It may
> return in Beta 3.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "David" <David@.blackdeck.com> wrote in message
> news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> it,
>
>
sql

Opening Table in Management Studio is very slow

Dear Gurus

When I open a large table (say more than 1,000,000 Rows) in the SSMS by right clicking on the table name, it takes a very big time to fully open the table.More than 20 minutes for 1,000,000 records on a local instance.

SQL Server 2000 EM was extremely faster. Does any one knows a work around?

I need to be able to view and edit the data in SSMS.

Thanks in advance.

Parviz

I do not suggest ever opening a large table like this and editing the data through management studio. It is better to write queries to view the data as needed, and dml statements to update the data as needed.
Tim

Opening password protected Excel files

I have a stored procedure in which I am importing data from a password protected Excel file to a SQL table. The file comes from our client and the password cannot be removed before sending, per his company policy.

I would like to create a DTS package to open the file and import the data. Is there a way to pass the password to the DTS package?

Thanks!May refer to this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q211378).|||I can do this if I am using Excel, but I need to do it from a stored procedure. Is this possible using TSQL?

Thanks!|||Honestly I never tried, you may search under SQL DTS (http://www.sqldts.com) for further information.|||I got it!!! One of my coworkers (who was out when I needed the answer yesterday) had the solution!

dtsrun /Sservername /Uuid /Ppwd /Npackagename /AFileName:8= excelfilename /APassword:8=excelpwd

:-)|||Good keep it up, your reply help others.

Open-ended querying of DB (??)

I am working on a project with a couple of hundred tables and views. There is one primary table around which all other tables revolve with the primary table having somewhere around 42 to 45 million records. Some of the related tables can have many times this number of records. The data is static in that it only changes at specific intervals (once a month or so) when more records are added and some "old" records are removed.

My problem is that users need to query these tables without any major restrictions. I mean, the front end will allow users to create almost any selection criteria form the many columns in the DB. The selection of values will be controlled via drop-downs and lists from where the user will select the criteria. The other consideration is that the grouping can be for any value and up to ten levels deep. Our users are not sophisticated enough to create their own SQL-like queries so we have to guide them through the process of selecting the values…

I am trying to create stored procedures that will allow this behavior but I continue to run into the problem of how to properly join the tables when the combinations are almost endless. My conclusion is that although it seems this seems to be a case where D-SQL is a good choice, there are some draw-backs I am not sure we can afford, specifically the security issues. Furthermore, the creation of a D-SQL "code-generator" seems like almost impossible task and time is of the essence.

Has anyone here had any experience with a similar situation that can shed some light?I think without dynamic SQL, you're stuck with creating a view for every possible combination of joins - not really an option. I think you'll need to use it. Is it possible to provide the highest priority querying and slowly add more tables as you have a chance to build it? If a user isn't sophisticated enough to build SQL, will they be aware enough to even understand what joining tables does for them?

I've done 'build-your-own' query-generating stuff before, but never to that scope. That could be a tough one.|||A Friend of mine was reading an article in visualstudiomagazine.com that might be of some assistance to you. Check out the online version http://www.fawcette.com/vsm/2004_10/magazine/columns/databasedesign/

Hth,|||Scott, thanks for the heads-up. I will post my impressions on the article later on.|||PD, I have no choice but to look for a solution. Our users currently have a system that is based on a "flat-file" format where creating the queries to produce the reports is fairly easy. When we decided to re-write the system as a web based application, the decision was made to move to SQL Server and take advantage of its power. We normalized the files, added records for a wider time frame and have created a beautiful data structure. Unfortunately now we are confronted with the issues of querying the data in a multitude of ways.

We are considering de-normalizing the data a little, using Analysis Services, English Queries, and others. Given the confidential nature of the data, security is a must and using D-SQL may be a problem. We can live with a system where the user makes his selection from lists and drop-downs and we construct the query behind the scenes; we do not expect them to know SQL.|||Beleive it or not there are actual tools out there that exist to perform this task. Mind you some may seem expensive... but they work, and the amount of development time saved to get something that works and may be useful in other areas in your organization may warrant the costs.

The tools fall into the Business Intelligence area of software, and are typically called EII or something to that nature. EII stands for Enterprise Information Integration.

Another way to explain it, you need a tool, typically made to fetch data from multiple sources, apply english names to them, then you specify how the individual "views of data" could be related to another. Next the tool gives you a GUI interface and/or exposes an API allowing you to say "I want data from 'a' and 'b' filtered on 'c' and grouped on 'd'."

Nimble Technologies made such a tool. My company, Actuate Corporation, has acquired them to use their technology within our own software. I'm not sure if we did an outright takeover, or just bought largely into their company or what, but Nimble may or may not exist. In either case, they had competitors, so the tools do exists out there.

I would suggest looking up Business Intelligence tools, or publications that frequently discuss such software. Publications geared towards CIOs and IT management would be another area to look.

OPEN XML QUESTION

DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandlerINT
DECLARE@.QuestionName TABLE(
IdINT IDENTITY(1,1),
[metatag][Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT*
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation7700
quality7701
I expect like this
Evaluation7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
Deva
You want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>

OPEN XML QUESTION

DECLARE @.MetaTagXML XML
declare @.XmlDocumentHandler INT
DECLARE @.QuestionName TABLE (
Id INT IDENTITY(1,1),
[metatag] [Varchar](100))
SELECT @.MetaTagXML ='<Report ReportGUID
="CBFF8200-3B52-4F24-9D48-AC5458720B41">
<MetaTags>
<MetaTag>Awareness</MetaTag>
<MetaTag>advertising</MetaTag>
</MetaTags>
<Variables>
<Variable NodeID="7700">
<MetaTags>
<MetaTag>Evaluation</MetaTag>
<MetaTag>account management/primary source experience</MetaTag>
</MetaTags>
</Variable>
<Variable NodeID="7701">
<MetaTags>
<MetaTag>quality</MetaTag>
<MetaTag>quality of account management</MetaTag>
</MetaTags>
</Variable>
</Variables>
</Report>'
EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags',2)
WITH (
MetaTag varchar(100),
NodeID varchar(100) '../@.NodeID'
)
EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
i am getting the resultset like this:
Evaluation 7700
quality 7701
I expect like this
Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
account management/primary source experience 7700
CBFF8200-3B52-4F24-9D48-AC5458720B41
quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
1. how to get all records
2. how to reference reportguid
Thanks in advance
DevaYou want one row per MetaTag element, so your row pattern in OpenXML needs
to select that element.
And you need to add a row for the ReportGUID:
SELECT *
FROM OPENXML (@.XmlDocumentHandler,
'/Report/Variables/Variable/MetaTags/MetaTag',2)
WITH (
MetaTag varchar(100) '.',
NodeID varchar(100) '../../@.NodeID',
ReportID varchar(100) '../../../../@.ReportGUID'
)
Also note that since you are using OpenXML, using the XML datatype is not
necessarily beneficial, since sp_xml_preparedocument will serialize the XML
and reparse it. So using nvarchar(max) may be the better approach to pass
the XML data to the parser.
Best regards
Michael
"xmldev" <xmldev@.discussions.microsoft.com> wrote in message
news:8BD7E081-246C-47A8-8A53-C6FBDD157786@.microsoft.com...
> DECLARE @.MetaTagXML XML
> declare @.XmlDocumentHandler INT
> DECLARE @.QuestionName TABLE (
> Id INT IDENTITY(1,1),
> [metatag] [Varchar](100))
>
> SELECT @.MetaTagXML ='<Report ReportGUID
> ="CBFF8200-3B52-4F24-9D48-AC5458720B41">
> <MetaTags>
> <MetaTag>Awareness</MetaTag>
> <MetaTag>advertising</MetaTag>
> </MetaTags>
> <Variables>
> <Variable NodeID="7700">
> <MetaTags>
> <MetaTag>Evaluation</MetaTag>
> <MetaTag>account management/primary source experience</MetaTag>
> </MetaTags>
> </Variable>
> <Variable NodeID="7701">
> <MetaTags>
> <MetaTag>quality</MetaTag>
> <MetaTag>quality of account management</MetaTag>
> </MetaTags>
> </Variable>
> </Variables>
> </Report>'
> EXECUTE SP_XML_PREPAREDOCUMENT @.XmlDocumentHandler OUTPUT, @.MetaTagXML
> SELECT *
> FROM OPENXML (@.XmlDocumentHandler,
> '/Report/Variables/Variable/MetaTags',2)
> WITH (
> MetaTag varchar(100),
> NodeID varchar(100) '../@.NodeID'
> )
> EXECUTE SP_XML_REMOVEDOCUMENT @.XmlDocumentHandler
> i am getting the resultset like this:
> Evaluation 7700
> quality 7701
> I expect like this
>
> Evaluation 7700 CBFF8200-3B52-4F24-9D48-AC5458720B41
> account management/primary source experience 7700
> CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> quality of account management 7701 CBFF8200-3B52-4F24-9D48-AC5458720B41
> 1. how to get all records
> 2. how to reference reportguid
> Thanks in advance
> Deva
>

OPEN XML

I have to insert one table by using the concept OPENXML

But i want to insert a table lot of fields i require, in the open XML few fields fields are there , so i want to selet some fields from other table

can u guide me for this scenario to INSERT some fields from one table and some in OPENXML as a single insert statement

Awaiting for Reply PLease

Thanx

Consider using the openXml to create a temporary table first. Then join the two tables as the base for the insert.

Open URL in New Browser

My report has a table that when I click on a cell it jumps to URL and open
it in the same browser window. How to open the URL in a new browser instead
of current browser window?
Thanks.You didn't ask about Excel but this response of mine to another post will
answer you question:
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ME" <ME@.mail.com> wrote in message
news:OPWXUKT%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
> My report has a table that when I click on a cell it jumps to URL and open
> it in the same browser window. How to open the URL in a new browser
> instead of current browser window?
> Thanks.
>|||Bruce,
The report I am viewing is in SQL 2000. I replaced your example with my
server name and parameter, it didn't work.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e38AxLT%23GHA.4268@.TK2MSFTNGP02.phx.gbl...
> You didn't ask about Excel but this response of mine to another post will
> answer you question:
> Depending on how you design your reports you can do the following to
> export to Excel. Or, what I do sometimes is make a copy of the report and
> clean it up for data export and then hide it in list view. If you export
> from Report Manager it puts CSV data in unicode which Excel puts all in
> one column. If you export in ASCII then Excel does just as you want. To
> prevent a problem with cells (Excel will object to sorting the data) you
> need to remove any textboxes you have (for instance with a title, showing
> the parameters run etc) and instead add additional header rows, merge the
> cells and put your text in there instead. I add a link at the top of the
> report that says Export Data. With RS 2005 you will be able to configure
> it to use ASCII instead of Unicode.
> Here is an example of a Jump to URL link I use. This causes Excel to come
> up with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ME" <ME@.mail.com> wrote in message
> news:OPWXUKT%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
>> My report has a table that when I click on a cell it jumps to URL and
>> open it in the same browser window. How to open the URL in a new browser
>> instead of current browser window?
>> Thanks.
>|||Use exactly what I have. Do not put in your server name. Replace just the
folder, report and parameters.
Even better, do a test with a report that is without parameters so you know
that is not a problem.
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value','_blank'))"
One last point, you cannot test this in the development environment, you
have to deploy it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ME" <ME@.mail.com> wrote in message
news:O66gRCU%23GHA.3860@.TK2MSFTNGP02.phx.gbl...
> Bruce,
> The report I am viewing is in SQL 2000. I replaced your example with my
> server name and parameter, it didn't work.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e38AxLT%23GHA.4268@.TK2MSFTNGP02.phx.gbl...
>> You didn't ask about Excel but this response of mine to another post will
>> answer you question:
>> Depending on how you design your reports you can do the following to
>> export to Excel. Or, what I do sometimes is make a copy of the report and
>> clean it up for data export and then hide it in list view. If you export
>> from Report Manager it puts CSV data in unicode which Excel puts all in
>> one column. If you export in ASCII then Excel does just as you want. To
>> prevent a problem with cells (Excel will object to sorting the data) you
>> need to remove any textboxes you have (for instance with a title, showing
>> the parameters run etc) and instead add additional header rows, merge the
>> cells and put your text in there instead. I add a link at the top of the
>> report that says Export Data. With RS 2005 you will be able to configure
>> it to use ASCII instead of Unicode.
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up with the data in a separate window:
>>
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "ME" <ME@.mail.com> wrote in message
>> news:OPWXUKT%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
>> My report has a table that when I click on a cell it jumps to URL and
>> open it in the same browser window. How to open the URL in a new
>> browser instead of current browser window?
>> Thanks.
>>
>|||It worked finally. Thanks
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eXGqvGU%23GHA.3456@.TK2MSFTNGP02.phx.gbl...
> Use exactly what I have. Do not put in your server name. Replace just the
> folder, report and parameters.
> Even better, do a test with a report that is without parameters so you
> know that is not a problem.
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value','_blank'))"
> One last point, you cannot test this in the development environment, you
> have to deploy it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ME" <ME@.mail.com> wrote in message
> news:O66gRCU%23GHA.3860@.TK2MSFTNGP02.phx.gbl...
>> Bruce,
>> The report I am viewing is in SQL 2000. I replaced your example with my
>> server name and parameter, it didn't work.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e38AxLT%23GHA.4268@.TK2MSFTNGP02.phx.gbl...
>> You didn't ask about Excel but this response of mine to another post
>> will answer you question:
>> Depending on how you design your reports you can do the following to
>> export to Excel. Or, what I do sometimes is make a copy of the report
>> and clean it up for data export and then hide it in list view. If you
>> export from Report Manager it puts CSV data in unicode which Excel puts
>> all in one column. If you export in ASCII then Excel does just as you
>> want. To prevent a problem with cells (Excel will object to sorting the
>> data) you need to remove any textboxes you have (for instance with a
>> title, showing the parameters run etc) and instead add additional header
>> rows, merge the cells and put your text in there instead. I add a link
>> at the top of the report that says Export Data. With RS 2005 you will be
>> able to configure it to use ASCII instead of Unicode.
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>>
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "ME" <ME@.mail.com> wrote in message
>> news:OPWXUKT%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
>> My report has a table that when I click on a cell it jumps to URL and
>> open it in the same browser window. How to open the URL in a new
>> browser instead of current browser window?
>> Thanks.
>>
>>
>

Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help

Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help