looping through results of a OSQL query

Hello,

I have the following problem.

I wrote a batch file that runs a sql script on SQLServer 2000. This
script must be executed on several databases. This works fine.

The problem is that in my database is a table that holds a databases i
have to update with this script. What i want is run a query with osql
in my batchfile to retrieve this these records so I can loop through
them an run the script for those databases.

I managed to execute the query that return the records and write them
to a textfile.

What i want is store the results in some kind of a resultset so i can
loop through them in my batchfile.

Anyone have the solution

Thanks in advance

Patrickpatrick (pveechoud@.xs4all.nl) writes:
> I have the following problem.
> I wrote a batch file that runs a sql script on SQLServer 2000. This
> script must be executed on several databases. This works fine.
> The problem is that in my database is a table that holds a databases i
> have to update with this script. What i want is run a query with osql
> in my batchfile to retrieve this these records so I can loop through
> them an run the script for those databases.
> I managed to execute the query that return the records and write them
> to a textfile.
> What i want is store the results in some kind of a resultset so i can
> loop through them in my batchfile.

A simple-minded approach is to write the query so that it generates
a complete OSQL command and you write that to the file. Then you
execute the file.

But it would probably better to write this in some script language
like VBscript or Perl from which you can submit queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Loopback linked servers

I want to set a server up to be able to reference itself
via an alias in a 4-part name. Let us call the server
SQLSERVER and its alias BILLING. The motive for this is
to be able to, move the so-referenced databases to another
server when I want to and reference that truely remote
server by the BILLING alias.
I have the alias set-up correctly and the server linked
via that alias with the proper security contexts.
When I query:
select * from [billing].master.dbo.sysdatabases
I get the resultset I want.
When I query:
begin tran
select * from [billing].master.dbo.sysdatabases
rollback tran
I get the following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
If I use BILLING to alias another server (a truely remote
server), the transactioned query works without error.
I've came to understand this as a loopback linked server limitation
in that loopback linked servers cannot enlist in a distributed transaction.
But, I made a table, x, on sqlserver.Master with one int column, x
I made a stored proc, spx, on sqlserver.Master that takes one parameter
and inserts it's value into table x
On Sql Server I did the following
Use Master
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 1
select @.@.Trancount TranCount
if @.@.trancount != 0 commit tran
select * from [BILLING].Master.dbo.x
-- Saw 1 and no errors so transaction worked.
go
if @.@.Trancount = 0 begin tran
exec [BILLING].Master.dbo.spx 2
select @.@.Trancount TranCount
if @.@.trancount != 0 rollback tran
select * from [BILLING].Master.dbo.x
-- Saw 2 and no errors so transaction worked.
go
begin tran
select * from [BILLING].Master.dbo.x
-- Saw same error as cited above as expected.
if @.@.trancount != 0 rollback tran
What is going on? Transactions should have failed in stored proc usage
as well as a direct query if this were a loopback failure to enlist a
distributed transaction, shouldn't they?Hi
There are problems with loopbacks within implicit, explicit or distributed
transactions see http://tinyurl.com/5dlnq
John
"de_corum" <de_corum@.hotmail.com> wrote in message
news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> I want to set a server up to be able to reference itself
> via an alias in a 4-part name. Let us call the server
> SQLSERVER and its alias BILLING. The motive for this is
> to be able to, move the so-referenced databases to another
> server when I want to and reference that truely remote
> server by the BILLING alias.
> I have the alias set-up correctly and the server linked
> via that alias with the proper security contexts.
> When I query:
> select * from [billing].master.dbo.sysdatabases
> I get the resultset I want.
> When I query:
> begin tran
> select * from [billing].master.dbo.sysdatabases
> rollback tran
> I get the following error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> If I use BILLING to alias another server (a truely remote
> server), the transactioned query works without error.
> I've came to understand this as a loopback linked server limitation
> in that loopback linked servers cannot enlist in a distributed
transaction.
> But, I made a table, x, on sqlserver.Master with one int column, x
> I made a stored proc, spx, on sqlserver.Master that takes one parameter
> and inserts it's value into table x
> On Sql Server I did the following
> Use Master
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 1
> select @.@.Trancount TranCount
> if @.@.trancount != 0 commit tran
> select * from [BILLING].Master.dbo.x
> -- Saw 1 and no errors so transaction worked.
> go
> if @.@.Trancount = 0 begin tran
> exec [BILLING].Master.dbo.spx 2
> select @.@.Trancount TranCount
> if @.@.trancount != 0 rollback tran
> select * from [BILLING].Master.dbo.x
> -- Saw 2 and no errors so transaction worked.
> go
> begin tran
> select * from [BILLING].Master.dbo.x
> -- Saw same error as cited above as expected.
> if @.@.trancount != 0 rollback tran
> What is going on? Transactions should have failed in stored proc usage
> as well as a direct query if this were a loopback failure to enlist a
> distributed transaction, shouldn't they?
>
>|||Ok, but why does it work when I am executing stored procs against the
loopback server and not when I am directly querying the tables?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ue4HnKHiEHA.3476@.tk2msftngp13.phx.gbl...
> Hi
> There are problems with loopbacks within implicit, explicit or distributed
> transactions see http://tinyurl.com/5dlnq
> John
> "de_corum" <de_corum@.hotmail.com> wrote in message
> news:unv3GUGiEHA.2880@.TK2MSFTNGP10.phx.gbl...
> > I want to set a server up to be able to reference itself
> > via an alias in a 4-part name. Let us call the server
> > SQLSERVER and its alias BILLING. The motive for this is
> > to be able to, move the so-referenced databases to another
> > server when I want to and reference that truely remote
> > server by the BILLING alias.
> >
> > I have the alias set-up correctly and the server linked
> > via that alias with the proper security contexts.
> >
> > When I query:
> >
> > select * from [billing].master.dbo.sysdatabases
> >
> > I get the resultset I want.
> >
> > When I query:
> >
> > begin tran
> > select * from [billing].master.dbo.sysdatabases
> > rollback tran
> >
> > I get the following error:
> >
> > Server: Msg 7391, Level 16, State 1, Line 2
> > The operation could not be performed because the OLE DB
> > provider 'SQLOLEDB' was unable to begin a distributed
> > transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> >
> > If I use BILLING to alias another server (a truely remote
> > server), the transactioned query works without error.
> >
> > I've came to understand this as a loopback linked server limitation
> > in that loopback linked servers cannot enlist in a distributed
> transaction.
> >
> > But, I made a table, x, on sqlserver.Master with one int column, x
> > I made a stored proc, spx, on sqlserver.Master that takes one parameter
> > and inserts it's value into table x
> >
> > On Sql Server I did the following
> >
> > Use Master
> > go
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 1
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 commit tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 1 and no errors so transaction worked.
> > go
> >
> > if @.@.Trancount = 0 begin tran
> > exec [BILLING].Master.dbo.spx 2
> > select @.@.Trancount TranCount
> > if @.@.trancount != 0 rollback tran
> >
> > select * from [BILLING].Master.dbo.x
> > -- Saw 2 and no errors so transaction worked.
> > go
> >
> > begin tran
> > select * from [BILLING].Master.dbo.x
> > -- Saw same error as cited above as expected.
> > if @.@.trancount != 0 rollback tran
> >
> > What is going on? Transactions should have failed in stored proc usage
> > as well as a direct query if this were a loopback failure to enlist a
> > distributed transaction, shouldn't they?
> >
> >
> >
> >
>

looking to tune query...

If anyone is able to provide advice for tuning the below query in sql
server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.

select a.id, isnull(b.advisement_satisfaction_yes, 0) as
advisement_satisfaction_yes,
isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,
case
when isnull(b.advisement_satisfaction_yes, 0) >
isnull(c.advisement_satisfaction_no, 0) then 'YES'
when isnull(b.advisement_satisfaction_yes, 0) <
isnull(c.advisement_satisfaction_no, 0) then 'NO'
when isnull(b.advisement_satisfaction_yes, 0) =
isnull(c.advisement_satisfaction_no, 0) then 'TIE'
end as Satisfied_With_Advisement

from a
left Join

(select id, count(answer_text) as Advisement_Satisfaction_yes from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'yes'
GROUP BY id) b

on a.id = b.id

left join

(select id, count(answer_text) as Advisement_Satisfaction_NO from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'NO'
GROUP BY id) c

on a.id = b.id

where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:

SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x

Razvan|||AMAZING...THANK YOU SO MUCH!

Razvan Socol wrote:

Quote:

Originally Posted by

The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
>
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
>
Razvan

Looking to copy from hosted server to sqlserver express

We have a small sqlserver express database that is hosted. It seems
to make more sense to take care of it on our own machine, so I'm
looking to copy a database.
I suppose one option would be to get a copy of the physical files from
the hosting company. I haven't looked into that yet.
I have an SS2000 database and tried to use DTS, but it wouldn' t work
with Express.
Is there a simple way to do this on my own, or will I be calling the
hosting company?
Hi
Your hosting company should be able to backup the database and allow you to
FTP it, if this is possible it would be the quickest solution for even a
modest size database! You can detach/attach the database if you have access
to the web admin tools and ftp http://support.microsoft.com/kb/314546 should
help you.
John
"cowznofsky" <jhcorey@.yahoo.com> wrote in message
news:953cf302-8373-4f4c-9e3e-6d81eedb82ec@.s12g2000prg.googlegroups.com...
> We have a small sqlserver express database that is hosted. It seems
> to make more sense to take care of it on our own machine, so I'm
> looking to copy a database.
> I suppose one option would be to get a copy of the physical files from
> the hosting company. I haven't looked into that yet.
> I have an SS2000 database and tried to use DTS, but it wouldn' t work
> with Express.
> Is there a simple way to do this on my own, or will I be calling the
> hosting company?

Looking to copy from hosted server to sqlserver express

We have a small sqlserver express database that is hosted. It seems
to make more sense to take care of it on our own machine, so I'm
looking to copy a database.
I suppose one option would be to get a copy of the physical files from
the hosting company. I haven't looked into that yet.
I have an SS2000 database and tried to use DTS, but it wouldn' t work
with Express.
Is there a simple way to do this on my own, or will I be calling the
hosting company?Hi
Your hosting company should be able to backup the database and allow you to
FTP it, if this is possible it would be the quickest solution for even a
modest size database! You can detach/attach the database if you have access
to the web admin tools and ftp http://support.microsoft.com/kb/314546 should
help you.
John
"cowznofsky" <jhcorey@.yahoo.com> wrote in message
news:953cf302-8373-4f4c-9e3e-6d81eedb82ec@.s12g2000prg.googlegroups.com...
> We have a small sqlserver express database that is hosted. It seems
> to make more sense to take care of it on our own machine, so I'm
> looking to copy a database.
> I suppose one option would be to get a copy of the physical files from
> the hosting company. I haven't looked into that yet.
> I have an SS2000 database and tried to use DTS, but it wouldn' t work
> with Express.
> Is there a simple way to do this on my own, or will I be calling the
> hosting company?

Looking to copy from hosted server to sqlserver express

We have a small sqlserver express database that is hosted. It seems
to make more sense to take care of it on our own machine, so I'm
looking to copy a database.
I suppose one option would be to get a copy of the physical files from
the hosting company. I haven't looked into that yet.
I have an SS2000 database and tried to use DTS, but it wouldn' t work
with Express.
Is there a simple way to do this on my own, or will I be calling the
hosting company?Hi
Your hosting company should be able to backup the database and allow you to
FTP it, if this is possible it would be the quickest solution for even a
modest size database! You can detach/attach the database if you have access
to the web admin tools and ftp http://support.microsoft.com/kb/314546 should
help you.
John
"cowznofsky" <jhcorey@.yahoo.com> wrote in message
news:953cf302-8373-4f4c-9e3e-6d81eedb82ec@.s12g2000prg.googlegroups.com...
> We have a small sqlserver express database that is hosted. It seems
> to make more sense to take care of it on our own machine, so I'm
> looking to copy a database.
> I suppose one option would be to get a copy of the physical files from
> the hosting company. I haven't looked into that yet.
> I have an SS2000 database and tried to use DTS, but it wouldn' t work
> with Express.
> Is there a simple way to do this on my own, or will I be calling the
> hosting company?

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.
> Jake
>

Looking for help

Hello,
I am looking for some type of example where we have a mailbox called
"sqlserver" When a user sends an email to that box with a specific title we
would like to do specific actions. For example is a user sends an xml
attachment with the subject line "sales report" we would like the data to be
imported into our sql2k box. How can we have sql monitor the email box, read
the emails, and process based upon the subject line. Any examples or
suggestions would be greatly appreciated.
Jake
"Jake" <rondican@.hotmail.com> wrote in message
news:eqohc7KqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am looking for some type of example where we have a mailbox called
> "sqlserver" When a user sends an email to that box with a specific title
we
> would like to do specific actions. For example is a user sends an xml
> attachment with the subject line "sales report" we would like the data to
be
> imported into our sql2k box. How can we have sql monitor the email box,
read
> the emails, and process based upon the subject line. Any examples or
> suggestions would be greatly appreciated.
Setup a scheduled task using task manager to run say every 5 minutes and
have it execute xp_readmail.
However, I'd highly recommend you give serious thought to your security if
you do this.

> Jake
>

Looking for good Migration tool which can convert Access db to SQL

I'm looking for migration tool which can convert Access database to SQL
Server. Any one which works 100%. Any suggestions would be greatly
appreciated.
Regards,
RaoRao
Linked Servers?
---
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Rao" <Rao@.discussions.microsoft.com> wrote in message
news:7215F579-1C4C-4F6D-AF5B-6D0D12117772@.microsoft.com...
> I'm looking for migration tool which can convert Access database to SQL
> Server. Any one which works 100%. Any suggestions would be greatly
> appreciated.
> Regards,
> Rao
>|||You can import the database using DTS. Do mean that you want to also carry
over all the queries, etc. and have the SQL properly translated as well?
"Rao" <Rao@.discussions.microsoft.com> wrote in message
news:7215F579-1C4C-4F6D-AF5B-6D0D12117772@.microsoft.com...
> I'm looking for migration tool which can convert Access database to SQL
> Server. Any one which works 100%. Any suggestions would be greatly
> appreciated.
> Regards,
> Rao
>