Moving code from Sybase to SQL Server 2005. I have two tables. One table (vINID) is reset and populated right before this code runs. The other table (vINPA) is a table in the database used by the Front End App. This is the error I get when I try and run on our SQL Server 2005.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
Here is the SQL code:
SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID
WHERE vINID.BLIV_ID = '071600007594'
Thank you for your help.
DavidIdeally, you would change the collation on the oddball table. Was there a reason to make one of these tables with a collation different from the database default collation?|||Just noticed one of the tables is actually on a remote server. Try this:
SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID COLLATE database_default
WHERE vINID.BLIV_ID = '071600007594'|||Code fix works. Thank you. Not sure what "Collation" is or how to change it or what the database default collations is? New to SQL Server 2005.sqlsql
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Thursday, March 22, 2012
collation conflict
I have a strange problem. When I run a script which drops and rebuilds an entire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?
Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Thanks all. It was indeed the collate on tempdbs.
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?
Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Thanks all. It was indeed the collate on tempdbs.
collation conflict
I have a strange problem. When I run a script which drops and rebuilds an entire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Thanks all. It was indeed the collate on tempdbs.
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Thanks all. It was indeed the collate on tempdbs.
collation conflict
I have a strange problem. When I run a script which drops and rebuilds an en
tire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a
function
in the SQL inside the procedure. That function also is in the same script an
d
it gets created successfully.
The script is self containing and does not have any collate statement. So, a
ll collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?Check if the two servers has different collations in the master database. If
they do, watch out for
temp tables (search BOL for database_default). And of course other reference
s outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.ne
t...
>I have a strange problem. When I run a script which drops and rebuilds an e
ntire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
a function
> in the SQL inside the procedure. That function also is in the same script
and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an
entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
a function
> in the SQL inside the procedure. That function also is in the same script
and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Thanks all. It was indeed the collate on tempdbs.
tire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a
function
in the SQL inside the procedure. That function also is in the same script an
d
it gets created successfully.
The script is self containing and does not have any collate statement. So, a
ll collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?Check if the two servers has different collations in the master database. If
they do, watch out for
temp tables (search BOL for database_default). And of course other reference
s outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.ne
t...
>I have a strange problem. When I run a script which drops and rebuilds an e
ntire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
a function
> in the SQL inside the procedure. That function also is in the same script
and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an
entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
a function
> in the SQL inside the procedure. That function also is in the same script
and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Thanks all. It was indeed the collate on tempdbs.
Sunday, March 11, 2012
Co-Existing with MSDE
Can SQL Express reside on the same server as MSDE and both run at the same time (different apps)?
I have one app that runs on MSDE and a new to be developed app by a vendor that needs SQL Express.
Thanks
JB
Yes, that is possible. When you start the SQL 2005 Express setup, it will detect the MSDE and ask if you wish to upgrade or install a new instance. Choose a new instance.
You may wish to read in Books Online about the topic: "Instance Naming Page"
Wednesday, March 7, 2012
CoCreate of DSO for BULKIMPORTSTREAM returned 0x8007045a
Hello,
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!
Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!
|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
>
|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it worked for me previously.
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>
|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
DSO
>
>.
>
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!
Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!
|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downloa...=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
>
|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it worked for me previously.
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>
|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b27101c4680e$032713e0$a401280a@.phx.gbl...
DSO
>
>.
>
CoCreate of DSO for BULKIMPORTSTREAM returned 0x8007045a
Hello,
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
>|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it wo
rked for me previously.
--
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
DSO[vbcol=seagreen]
>
>.
>
Every few hours my SQL Server runs a scheduled job that
performs a BULK INSERT. It was working fine, but today I
received this error
Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
Procedure sp_TACACSlog, Line 70
Could not create an instance of OLE DB
provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO
for BULKIMPORTSTREAM returned 0x8007045a].
I have checked KB article 302621, but that is related to
user permission and isn't relevant in this case (no user
permissions have changed).
Please help!Hi,
Seems some DLLS corruption. Can you try applysing the latest service pack
(sp3a)
and execute the bulk insert again.
Download the SP3a from below site:-
http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
Thanks
Hari
MCDBA
"steve" <anonymous@.discussions.microsoft.com> wrote in message
news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
> Hello,
> Every few hours my SQL Server runs a scheduled job that
> performs a BULK INSERT. It was working fine, but today I
> received this error
> Msg 7302, Level 16, State 1, Server DPGL-VS-SQL-02,
> Procedure sp_TACACSlog, Line 70
> Could not create an instance of OLE DB
> provider 'BULKIMPORTSTREAM'.
> OLE DB error trace [Non-interface error: CoCreate of DSO
> for BULKIMPORTSTREAM returned 0x8007045a].
> I have checked KB article 302621, but that is related to
> user permission and isn't relevant in this case (no user
> permissions have changed).
> Please help!|||Hi,
Before applying the service pack, confirm the current level by executing
below command from query analyzer:-
select serverproperty('edition') as version
go
select serverproperty('ProductLevel') as servicepack
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Seems some DLLS corruption. Can you try applysing the latest service pack
> (sp3a)
> and execute the bulk insert again.
> Download the SP3a from below site:-
> http://www.microsoft.com/sql/downlo...N=EN-US&gssnb=1
> Thanks
> Hari
> MCDBA
>
> "steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
>|||Try to register IMPPROV.DLL using REGSRV32 utility and see the result, it wo
rked for me previously.
--
--
Satya SKJ
"Hari Prasad" wrote:
> Hi,
> Before applying the service pack, confirm the current level by executing
> below command from query analyzer:-
> select serverproperty('edition') as version
> go
> select serverproperty('ProductLevel') as servicepack
> Thanks
> Hari
> MCDBA
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O5DXAABaEHA.2844@.TK2MSFTNGP12.phx.gbl...
>
>|||Hi,
I stopped and restarted the SQL Server, and the Bulk
Insert works fine again. Why on earth would this have
happened at all? Does this mean I have to reboot my SQL
Server regularly to ensure it behaves?
>--Original Message--
>Hi,
>Seems some DLLS corruption. Can you try applysing the
latest service pack
>(sp3a)
>and execute the bulk insert again.
>Download the SP3a from below site:-
>http://www.microsoft.com/sql/downloads/2000/sp3.asp?
SD=GN&LN=EN-US&gssnb=1
>Thanks
>Hari
>MCDBA
>
>"steve" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b27101c4680e$032713e0$a401280a@.phx
.gbl...
DSO[vbcol=seagreen]
>
>.
>
Saturday, February 25, 2012
CmdExec issue
Hello,
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
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|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
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|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> 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
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
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|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> 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
>
I have a batch file in the operating system, which runs an osql command that
create a stored procedure, a view and a function on a database. It is with
-E trust connect. It works when I just run it on windows.
However, if I run in as a SQL Job by using the CmdExec, it could not create
any stored procedure, view or function. And there is no error return
neither. The account to run the job is the same NT account with local admin
rights on the server.
Does anyone have some idea about this matter?
Many Thanks,
LianneAre you running the Job from a different server?
If so you need to use -U and -P providing a username and password. I
have experienced this problem before, but apparently linked servers do
not pass the user via windows authentication.|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I have a batch file in the operating system, which runs an osql command
> that create a stored procedure, a view and a function on a database. It
> is with -E trust connect. It works when I just run it on windows.
> However, if I run in as a SQL Job by using the CmdExec, it could not
> create any stored procedure, view or function. And there is no error
> return neither. The account to run the job is the same NT account with
> local admin rights on the server.
Did the job fail or succeed?
In case it failed, there is a checkbox in the upper right of the View
History dialog which says "Show job steps" or somesuch. The output from
the job step should have the real error.
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|||The job was running on the physical server. and the job return as
successful. but nothing was created on SQL Server side.
Thanks again,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Hi John,
I change the -E to -U sa login, but I got this error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.
Any idea?
Many Thanks,
Lianne
"Johnny D" wrote:
> Are you running the Job from a different server?
> If so you need to use -U and -P providing a username and password. I
> have experienced this problem before, but apparently linked servers do
> not pass the user via windows authentication.
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:605603
Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> I change the -E to -U sa login, but I got this error message:
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server connection.
> Any idea?
That would indicate that SQL authentication is not enabled on the server.
Well, changing the autentication should not matter, so consider that a
dead end.
As for why your job does not work as expected, I'm afraid that with the
information you have provided, all we can offer is wild guesses. What
is the exact command line you are using?
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|||Hi Eriand,
the command line is below:
for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U sa -P
zhongshan -n -d test -i "%%f" >> Createcp.txt
this is the command I put in .bat file. on the same level, I have three sql
files, which creates a view, a user defined function and a stored procedure.
Also, I had set SQL Server running on both (SQL and NT authentication)
If you meed more informations, let me know.
Many Thanks,
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> That would indicate that SQL authentication is not enabled on the server.
> Well, changing the autentication should not matter, so consider that a
> dead end.
> As for why your job does not work as expected, I'm afraid that with the
> information you have provided, all we can offer is wild guesses. What
> is the exact command line you are using?
>
> --
> 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
>|||Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> the command line is below:
> for %%f IN (*.sql) DO echo %%f >> Createcp.txt & osql -S LKWOCK-D1 -U
> sa -P zhongshan -n -d test -i "%%f" >> Createcp.txt
> this is the command I put in .bat file. on the same level, I have three
> sql files, which creates a view, a user defined function and a stored
> procedure.
Real stupid question: the BAT file does include a CD to the directory
where you have the SQL, doesn't it?
I composed this file on my machine:
F:
cd \moje dokumenty\WWW\dynsearch
for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale -i "
%%f" >> Createcp.txt
And the I created a job of which the command text was
C:\temp\test.bat
I deleted all stored procedure in Northgale, and then started the job.
When I had completed the job, all my procedures were back.
In job history I saw this for the job step:
Executed as user: KESMETS\sql. ...search
F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Createcp.tx
t & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.txt &
osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.txt
& osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.txt &
.. Process Exit Code 0. The step succeeded.
A way to get more output from the job step is to go the Advanced tab
for the job step, and there select a output file.
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|||Hi Erland,
Thank you very much for your suggection. I did not enclude the Cd line in
the .bat file since it work just fine when I run it through windows. My bad
.
I guess SQL is very piggy on directory specification then.
Thank you again. I appreciate your help very much!
Lianne
"Erland Sommarskog" wrote:
> Lianne Kwock (LianneKwock@.discussions.microsoft.com) writes:
> Real stupid question: the BAT file does include a CD to the directory
> where you have the SQL, doesn't it?
> I composed this file on my machine:
> F:
> cd \moje dokumenty\WWW\dynsearch
> for %%f IN (*.sp) DO echo %%f >> Createcp.txt & osql -E -n -d Northgale
-i "%%f" >> Createcp.txt
> And the I created a job of which the command text was
> C:\temp\test.bat
> I deleted all stored procedure in Northgale, and then started the job.
> When I had completed the job, all my procedures were back.
> In job history I saw this for the job step:
>
> Executed as user: KES?METS?\sql. ...search
> F:\Moje dokumenty\WWW\dynsearch>for %f IN (*.sp) DO echo %f 1>>Create
cp.txt & osql -E -n -d Northgale -i "%f" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_1.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_1.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_10.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_10.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_2.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_2.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3.sp 1>>Createcp.t
xt & osql -E -n -d Northgale -i "search_orders_3.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_3a.sp 1>>Createcp.
txt & osql -E -n -d Northgale -i "search_orders_3a.sp" 1>>Createcp.txt
> F:\Moje dokumenty\WWW\dynsearch>echo search_orders_4.sp 1>>Createcp.t
xt &... Process Exit Code 0. The step succeeded.
> A way to get more output from the job step is to go the Advanced tab
> for the job step, and there select a output file.
>
> --
> 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
>
CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog
Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me wha
t
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if th
e
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in t
he
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me wha
t
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if th
e
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in t
he
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog
Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me what
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if the
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in the
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me what
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if the
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in the
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Friday, February 10, 2012
Clustered index
I have the query below running against a table with no indexes. This is the
only query that ever runs against it so I was planning on creating a
clustered index for it to optimize performance. The table does rarely
receives inserts or updates but it is queried 10 or so times per second.
Right now the performance is pitiful. Also note, I do not have the authority
to change the query, only add an index.
SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID and
umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Line1
and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence asc,
DEX_ROW_ID asc
should I create the clustered index based predicate? Meaning index on
umBatchID, umDocumentNumber, umLineSequence
Or based make a convering index to include all the columns in the select
portion
or based on the 'order by' meaning umBatchID, umDocumentNumber,
umLineSequence, DEX_ROW_ID.
Any suggestions are appreciated...The index you suggest should speed that query considerably; only thing would
be to decide the order of umDocumentNumber and umLineSequence in the index
-- the one that will narrow the result set more (if at all) should come
before the other. Guessing from the field names you've probably got the righ
t
order.
A clustered index is effectivly a covering index because it is not seperate
from the table as a nonclustered index is - when a clustered index is used i
t
does not need a pointer back to the original record to do a bookmark lookup;
you can see this in the query plan.
"Dean" wrote:
> I have the query below running against a table with no indexes. This is th
e
> only query that ever runs against it so I was planning on creating a
> clustered index for it to optimize performance. The table does rarely
> receives inserts or updates but it is queried 10 or so times per second.
> Right now the performance is pitiful. Also note, I do not have the authori
ty
> to change the query, only add an index.
>
> SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
> umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID an
d
> umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Lin
e1
> and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence as
c,
> DEX_ROW_ID asc
> should I create the clustered index based predicate? Meaning index on
> umBatchID, umDocumentNumber, umLineSequence
> Or based make a convering index to include all the columns in the select
> portion
> or based on the 'order by' meaning umBatchID, umDocumentNumber,
> umLineSequence, DEX_ROW_ID.
> Any suggestions are appreciated...
>
only query that ever runs against it so I was planning on creating a
clustered index for it to optimize performance. The table does rarely
receives inserts or updates but it is queried 10 or so times per second.
Right now the performance is pitiful. Also note, I do not have the authority
to change the query, only add an index.
SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID and
umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Line1
and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence asc,
DEX_ROW_ID asc
should I create the clustered index based predicate? Meaning index on
umBatchID, umDocumentNumber, umLineSequence
Or based make a convering index to include all the columns in the select
portion
or based on the 'order by' meaning umBatchID, umDocumentNumber,
umLineSequence, DEX_ROW_ID.
Any suggestions are appreciated...The index you suggest should speed that query considerably; only thing would
be to decide the order of umDocumentNumber and umLineSequence in the index
-- the one that will narrow the result set more (if at all) should come
before the other. Guessing from the field names you've probably got the righ
t
order.
A clustered index is effectivly a covering index because it is not seperate
from the table as a nonclustered index is - when a clustered index is used i
t
does not need a pointer back to the original record to do a bookmark lookup;
you can see this in the query plan.
"Dean" wrote:
> I have the query below running against a table with no indexes. This is th
e
> only query that ever runs against it so I was planning on creating a
> clustered index for it to optimize performance. The table does rarely
> receives inserts or updates but it is queried 10 or so times per second.
> Right now the performance is pitiful. Also note, I do not have the authori
ty
> to change the query, only add an index.
>
> SELECT TOP 25 umBatchID, umDocumentNumber, umLineSequence, DSTINDX,
> umTransactionAmount, DEX_ROW_ID from UMPST04 where umBatchID = @.BatchID an
d
> umDocumentNumber = between @.Doc1 and @.Doc2 and umLineSequence between @.Lin
e1
> and @.Line2 order by umBatchID asc, umDocumentNumber asc, umLineSequence as
c,
> DEX_ROW_ID asc
> should I create the clustered index based predicate? Meaning index on
> umBatchID, umDocumentNumber, umLineSequence
> Or based make a convering index to include all the columns in the select
> portion
> or based on the 'order by' meaning umBatchID, umDocumentNumber,
> umLineSequence, DEX_ROW_ID.
> Any suggestions are appreciated...
>
Subscribe to:
Posts (Atom)