Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to
access data on another Server running 2000. I get the following error, whe
n I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable the
se options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analy
zer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
JimReview the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure t
o access data on another Server running 2000. I get the following error, wh
en I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to b
e set for the connection. This ensures consistent query semantics. Enable th
ese options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Anal
yzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.sql

Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options

Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
Jim
Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".
|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:

>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim
|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>
|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.

Error: Could not get a list of SSIS packages from the project.

Hello,

I have a SSIS package and I am trying to build it using devenv.com from command line. It gives me this error:

Error: Could not get a list of SSIS packages from the project.

However if I open the project in Visual Studio it builds properly. In project property I have set CreateDeploymentUtility=true.

This is what I do from command line:

devenv.com MySSIS.sln /Build "Development|Default"

Any idea what I should do ?

Thanks

Deval

Hello,

There is no solution to it as far as I know if we set CreateDeploymentUtility=true. If you uncheck it then it will work fine.

Thanks

Deval

Error: Command not properly ended

Hi there,
Would appreciate if someone can have a glance at this code, and tell me
what SQL Talk doesnt like about it?
SELECT X, Y, Z, A, B FROM TSECURITY_LEVEL INTO :x, :y, :z, :a, :b
WHERE 1=1 ORDER BY A;
^
Error: Command not properly ended
Thanks All,
SamSam
SELECT <columns list> INTO #Test FROM MyTable WHERE blabalabala
"Sam" <sammA11111@.hotmail.com> wrote in message
news:1132738877.588734.57500@.g49g2000cwa.googlegroups.com...
> Hi there,
> Would appreciate if someone can have a glance at this code, and tell me
> what SQL Talk doesnt like about it?
> SELECT X, Y, Z, A, B FROM TSECURITY_LEVEL INTO :x, :y, :z, :a, :b
> WHERE 1=1 ORDER BY A;
> ^
> Error: Command not properly ended
> Thanks All,
> Sam
>|||Thanks for the reply Uri.
rookie mistake, and the problem is sorted ;)
sam

Error: Command not properly ended

Hi there,
Would appreciate if someone can have a glance at this code, and tell me
what SQL Talk doesnt like about it?
SELECT X, Y, Z, A, B FROM TSECURITY_LEVEL INTO :x, :y, :z, :a, :b
WHERE 1=1 ORDER BY A;
^
Error: Command not properly ended
Thanks All,
SamSam
SELECT <columns list> INTO #Test FROM MyTable WHERE blabalabala
"Sam" <sammA11111@.hotmail.com> wrote in message
news:1132738877.588734.57500@.g49g2000cwa.googlegroups.com...
> Hi there,
> Would appreciate if someone can have a glance at this code, and tell me
> what SQL Talk doesnt like about it?
> SELECT X, Y, Z, A, B FROM TSECURITY_LEVEL INTO :x, :y, :z, :a, :b
> WHERE 1=1 ORDER BY A;
> ^
> Error: Command not properly ended
> Thanks All,
> Sam
>|||Thanks for the reply Uri.
rookie mistake, and the problem is sorted ;)
samsql

Error: Command not properly ended

Hi there,
Would appreciate if someone can have a glance at this code, and tell me
what SQL Talk doesnt like about it?
SELECT X, Y, Z, A, B FROM TSECURITY_LEVEL INTO :x, :y, :z, :a, :b
WHERE 1=1 ORDER BY A;
^
Error: Command not properly ended
Thanks All,
Sam
Sam
SELECT <columns list> INTO #Test FROM MyTable WHERE blabalabala
"Sam" <sammA11111@.hotmail.com> wrote in message
news:1132738877.588734.57500@.g49g2000cwa.googlegro ups.com...
> Hi there,
> Would appreciate if someone can have a glance at this code, and tell me
> what SQL Talk doesnt like about it?
> SELECT X, Y, Z, A, B FROM TSECURITY_LEVEL INTO :x, :y, :z, :a, :b
> WHERE 1=1 ORDER BY A;
> ^
> Error: Command not properly ended
> Thanks All,
> Sam
>
|||Thanks for the reply Uri.
rookie mistake, and the problem is sorted ;)
sam

Error: Backup Log terminating abnormally

hi
i am using sql server 2000. when i try to backup log file it throws out the following error and hence the second command that is dbcc shrinkfile never gets executed. if i restart sql server and re-run these commands ... no error pops up ... both the comma
nds get completed sccessfully and log file gets shrunk too.
these are the commands that i run
BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
this is the error that i get
Server: Msg 3023, Level 16, State 3, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I am quite sure that no other command is active at the time when i issue these commands, still i get error. any help will be appreciated.
regards
yashrah.
Hi
Can you check your SQL Error log for the message when this command failed,
also can you please provide the SQL Server 2000 Service pack level.
you can get the Service pack level by running select @.@.version from query
analyzer.
Also, do you have replication set up, if yes, what kind of replication?
3023 means, It thinks there is some concurrent database operation going on
that is blocking the ALTER DATABASE, because it is unable to acquire the
database lock.
Regards
Sadeesh
This posting is provided AS IS with no warranties, and confers no rights.
"Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in message
news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
> hi
> i am using sql server 2000. when i try to backup log file it throws out
> the following error and hence the second command that is dbcc shrinkfile
> never gets executed. if i restart sql server and re-run these commands ...
> no error pops up ... both the commands get completed sccessfully and log
> file gets shrunk too.
> these are the commands that i run
> BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
> DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
> this is the error that i get
> Server: Msg 3023, Level 16, State 3, Line 1
> Backup and file manipulation operations (such as ALTER DATABASE ADD FILE)
> on a database must be serialized. Reissue the statement after the current
> backup or file manipulation operation is completed.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> I am quite sure that no other command is active at the time when i issue
> these commands, still i get error. any help will be appreciated.
> regards
> yashrah.
|||thanx for ur concern...
SQL Error Log says
2004-07-22 02:40:23.10 backup BACKUP failed to complete the command exec shrink_bamprimaryimport_logfile
infact "shrink_bamprimaryimport_logfile" is my stored procedure that contains the two commands (backup log and dbcc shrinkfile) written below.
servie pack 3a is installed and no replication set up is there.
"Sadeesh[MSFT]" wrote:

> Hi
> Can you check your SQL Error log for the message when this command failed,
> also can you please provide the SQL Server 2000 Service pack level.
> you can get the Service pack level by running select @.@.version from query
> analyzer.
> Also, do you have replication set up, if yes, what kind of replication?
> 3023 means, It thinks there is some concurrent database operation going on
> that is blocking the ALTER DATABASE, because it is unable to acquire the
> database lock.
> Regards
> Sadeesh
> --
> This posting is provided AS IS with no warranties, and confers no rights.
> "Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in message
> news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
>
>

Error: Backup Log terminating abnormally

hi
i am using sql server 2000. when i try to backup log file it throws out the
following error and hence the second command that is dbcc shrinkfile never g
ets executed. if i restart sql server and re-run these commands ... no error
pops up ... both the comma
nds get completed sccessfully and log file gets shrunk too.
these are the commands that i run
BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
this is the error that i get
Server: Msg 3023, Level 16, State 3, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on
a database must be serialized. Reissue the statement after the current back
up or file manipulation operation is completed.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
I am quite sure that no other command is active at the time when i issue the
se commands, still i get error. any help will be appreciated.
regards
yashrah.Hi
Can you check your SQL Error log for the message when this command failed,
also can you please provide the SQL Server 2000 Service pack level.
you can get the Service pack level by running select @.@.version from query
analyzer.
Also, do you have replication set up, if yes, what kind of replication?
3023 means, It thinks there is some concurrent database operation going on
that is blocking the ALTER DATABASE, because it is unable to acquire the
database lock.
Regards
Sadeesh
--
This posting is provided AS IS with no warranties, and confers no rights.
"Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in mess
age
news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
> hi
> i am using sql server 2000. when i try to backup log file it throws out
> the following error and hence the second command that is dbcc shrinkfile
> never gets executed. if i restart sql server and re-run these commands ...
> no error pops up ... both the commands get completed sccessfully and log
> file gets shrunk too.
> these are the commands that i run
> BACKUP LOG BAMPrimaryImport WITH TRUNCATE_ONLY
> DBCC SHRINKFILE ( BAMPrimaryImport_log, 500 )
> this is the error that i get
> Server: Msg 3023, Level 16, State 3, Line 1
> Backup and file manipulation operations (such as ALTER DATABASE ADD FILE)
> on a database must be serialized. Reissue the statement after the current
> backup or file manipulation operation is completed.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
> I am quite sure that no other command is active at the time when i issue
> these commands, still i get error. any help will be appreciated.
> regards
> yashrah.|||thanx for ur concern...
SQL Error Log says
2004-07-22 02:40:23.10 backup BACKUP failed to complete the command exec
shrink_bamprimaryimport_logfile
infact "shrink_bamprimaryimport_logfile" is my stored procedure that contain
s the two commands (backup log and dbcc shrinkfile) written below.
servie pack 3a is installed and no replication set up is there.
"Sadeesh[MSFT]" wrote:

> Hi
> Can you check your SQL Error log for the message when this command failed,
> also can you please provide the SQL Server 2000 Service pack level.
> you can get the Service pack level by running select @.@.version from query
> analyzer.
> Also, do you have replication set up, if yes, what kind of replication?
> 3023 means, It thinks there is some concurrent database operation going on
> that is blocking the ALTER DATABASE, because it is unable to acquire the
> database lock.
> Regards
> Sadeesh
> --
> This posting is provided AS IS with no warranties, and confers no rights.
> "Yashrah [Xavor]" <YashrahXavor@.discussions.microsoft.com> wrote in me
ssage
> news:73E811CE-FCBF-4D29-9538-6D22F842A8EC@.microsoft.com...
>
>sql

Error: 18456, Severity: 14, State: 16

Hi All,
I am getting the following error in the ERRORLOG File when I am trying
to connect to SQL Server 2005 Express Edition from the command line.
2007-03-23 07:30:05.54 Logon Error: 18456, Severity: 14, State:
16.
2007-03-23 07:30:05.54 Logon Login failed for user 'sa'.
[CLIENT: xx.xx.xx.xx]
I have a fresh install of SQL Server on my local machine, which is
probably the source of my angst, but I am trying to create a database
from scratch through using a batch file that is run from the command
line, the batch file looks like this:
<snip>
SQLCMD -U %USR% -P %PWD% -S %SVR% -d %DBF% -i CreateDatabase.sql
<snip>
The values of %USR% and %PWD% are correct as I frequently log into
Management Studio with the details, %SVR% is "<ComputerName>
\SQLEXPRESS,1433" and %DBF% is <DatabaseName>. The contents of
CreateDatabase.sql when run in Management Studio successfully creates
the database that I require so I know the error doesn't lie there.
The command line error that is showing reads as the following:
Msg 4060, Level 11, State 1, Server <ComputerName>\SQLEXPRESS, Line 1
Cannot open database "<DatabaseName>" requested by the login. The
login failed.
Msg 18456, Level 14, State 1, Server <ComputerName>\SQLEXPRESS, Line 1
Login failed for user 'sa'.
I have a feeling that it is related to 'sa' not having the correct
permissions with regards to logging into or creating a database from
scratch, but I find it odd that I can login as 'sa' in Management
Studio and create the database from there, but when I try it from the
command-line (despite having TCP/IP enabled) it won't let me connect
to create a database.
Is there something I am missing here? I have been banging my head off
of walls for days over this and am unsure of the next steps to take.
If any of this screams "why the hell are you doing this?" then please
let me know as at the moment I am punching in the dark with this
problem, I have searched Usenet, Google Groups, the Web and although I
have found potential solutions to this particular problem I am having,
nothing I have done so far has actually managed to fix the issue.
Related articles I have followed are:
Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx
Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
Upgrading MSDE 2000 to SQL Server 2005 Express [Enabling network
protocols section]
http://www.microsoft.com/technet/pr...sqlexpress.mspx
NOTE: I just listed these articles to show what path I am currently
looking at with regards to this problem, but again I stress I have
found nothing yet to get round this problem, and any further articles
or direction would be much appreciated.
Yours Distressingly
AlastairDoes the password have any blank spaces or double quotes ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Alastair" <alastair_anderson@.hotmail.com> wrote in message
news:1174642161.471601.110030@.y80g2000hsf.googlegroups.com...
> Hi All,
> I am getting the following error in the ERRORLOG File when I am trying
> to connect to SQL Server 2005 Express Edition from the command line.
> 2007-03-23 07:30:05.54 Logon Error: 18456, Severity: 14, State:
> 16.
> 2007-03-23 07:30:05.54 Logon Login failed for user 'sa'.
> [CLIENT: xx.xx.xx.xx]
> I have a fresh install of SQL Server on my local machine, which is
> probably the source of my angst, but I am trying to create a database
> from scratch through using a batch file that is run from the command
> line, the batch file looks like this:
> <snip>
> SQLCMD -U %USR% -P %PWD% -S %SVR% -d %DBF% -i CreateDatabase.sql
> <snip>
> The values of %USR% and %PWD% are correct as I frequently log into
> Management Studio with the details, %SVR% is "<ComputerName>
> \SQLEXPRESS,1433" and %DBF% is <DatabaseName>. The contents of
> CreateDatabase.sql when run in Management Studio successfully creates
> the database that I require so I know the error doesn't lie there.
> The command line error that is showing reads as the following:
> Msg 4060, Level 11, State 1, Server <ComputerName>\SQLEXPRESS, Line 1
> Cannot open database "<DatabaseName>" requested by the login. The
> login failed.
> Msg 18456, Level 14, State 1, Server <ComputerName>\SQLEXPRESS, Line 1
> Login failed for user 'sa'.
> I have a feeling that it is related to 'sa' not having the correct
> permissions with regards to logging into or creating a database from
> scratch, but I find it odd that I can login as 'sa' in Management
> Studio and create the database from there, but when I try it from the
> command-line (despite having TCP/IP enabled) it won't let me connect
> to create a database.
> Is there something I am missing here? I have been banging my head off
> of walls for days over this and am unsure of the next steps to take.
> If any of this screams "why the hell are you doing this?" then please
> let me know as at the moment I am punching in the dark with this
> problem, I have searched Usenet, Google Groups, the Web and although I
> have found potential solutions to this particular problem I am having,
> nothing I have done so far has actually managed to fix the issue.
> Related articles I have followed are:
> Login failed for user 'x'
> http://msdn2.microsoft.com/en-us/library/ms366351.aspx
> Change Server Authentication Mode
> http://msdn2.microsoft.com/en-us/library/ms188670.aspx
> Upgrading MSDE 2000 to SQL Server 2005 Express [Enabling network
> protocols section]
> http://www.microsoft.com/technet/pr...sqlexpress.mspx
> NOTE: I just listed these articles to show what path I am currently
> looking at with regards to this problem, but again I stress I have
> found nothing yet to get round this problem, and any further articles
> or direction would be much appreciated.
> Yours Distressingly
> Alastair
>|||On 23 Mar, 13:00, "Jens K. Suessmeyer" <J...@.NoSpamhere-
sqlserver2005.de> wrote:
> Does the password have any blank spaces or double quotes ?
> Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --
> "Alastair" <alastair_ander...@.hotmail.com> wrote in message
> news:1174642161.471601.110030@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
The only double quotes that it contains are those that surround the
password in the .bat file.
Should these be single quotes?|||Normally this should work, did you try conencting with the same syntax that
you are using in the batch file via an interactive commandprompt ? Did you
try to leave out the quotes ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Alastair" <alastair_anderson@.hotmail.com> wrote in message
news:1174655306.297982.325260@.b75g2000hsg.googlegroups.com...
> On 23 Mar, 13:00, "Jens K. Suessmeyer" <J...@.NoSpamhere-
> sqlserver2005.de> wrote:
> The only double quotes that it contains are those that surround the
> password in the .bat file.
> Should these be single quotes?
>|||On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@.NoSpamhere-
sqlserver2005.de> wrote:
> Normally this should work, did you try conencting with the same syntax tha
t
> you are using in the batch file via an interactive commandprompt ? Did you
> try to leave out the quotes ?
> Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --
> "Alastair" <alastair_ander...@.hotmail.com> wrote in message
> news:1174655306.297982.325260@.b75g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hi Jens,
Thanks for the input on this it is very much appreciated,
I tried running the batch file without quotations around the strings
and I still get the same error, I also tried single quotes, but the
command-line processor was interpreting them as part of the strings.
I took your advice and tried running it straight from the command-line
without using the .bat file, and still the same error came screaming
back at me.
Alastair|||On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@.NoSpamhere-
sqlserver2005.de> wrote:
> Normally this should work, did you try conencting with the same syntax tha
t
> you are using in the batch file via an interactive commandprompt ? Did you
> try to leave out the quotes ?
> Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --
> "Alastair" <alastair_ander...@.hotmail.com> wrote in message
> news:1174655306.297982.325260@.b75g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
I've done some further hunting around for solutions, could it
potentially be related to "sa" not having any permissions in the
SYS.DATABASE_PERMISSIONS table?|||No, sa has by default access to everything, including the internal data.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Alastair" <alastair_anderson@.hotmail.com> wrote in message
news:1174779869.212689.303860@.o5g2000hsb.googlegroups.com...
> On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@.NoSpamhere-
> sqlserver2005.de> wrote:
> I've done some further hunting around for solutions, could it
> potentially be related to "sa" not having any permissions in the
> SYS.DATABASE_PERMISSIONS table?
>

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

error with sqlmaint.exe

Hi All
I'm using McAfee ePO here which uses an MSDE database. I am trying to run
the recommended maintenance command on the DB but keep getting an error. It
seems to be related to the -U and -P account I'm using but I've tried every
variation of user and password it could be including domain admin + passwd.
Is there some way of adding a user to the DB? My only way of interacting
with it is through osql commands as far as I know.
The command I'm trying to run is:
sqlmaint -S SERVERNAME -U "domain\user" -P "password" -D
ePO_database_data -RebldIdx 15 -RmUnusedSpace 110 15 -UpdOptiStats 15
and the error that it's returning:
error 18452 Login failed for user, 'domain\user' reason: Not associated with
a trusted SQL Server connection.
I've tried several variations for the user and password but always get the
same result. I've read a bit about changing the authentication type for the
DB but A. I'm not sure if thats the right way to go and B. am afraid if I do
change something that ePO will not then be able to access the DB.
Thanks for the reply and if you can provide any assistance I'd really
appreciate it!
gerryR
hi,
gerryR wrote:
> Hi All
> I'm using McAfee ePO here which uses an MSDE database. I am trying to
> run the recommended maintenance command on the DB but keep getting an
> error. It seems to be related to the -U and -P account I'm using but
> I've tried every variation of user and password it could be including
> domain admin + passwd.
> Is there some way of adding a user to the DB? My only way of
> interacting with it is through osql commands as far as I know.
> The command I'm trying to run is:
> sqlmaint -S SERVERNAME -U "domain\user" -P "password" -D
> ePO_database_data -RebldIdx 15 -RmUnusedSpace 110 15 -UpdOptiStats 15
> and the error that it's returning:
> error 18452 Login failed for user, 'domain\user' reason: Not
> associated with a trusted SQL Server connection.
> I've tried several variations for the user and password but always
> get the same result. I've read a bit about changing the
> authentication type for the DB but A. I'm not sure if thats the right
> way to go and B. am afraid if I do change something that ePO will not
> then be able to access the DB.
this exception,
> error 18452 Login failed for user, 'domain\user' reason: Not
> associated with a trusted SQL Server connection.
usually is relative to a SQL Server instance only allowing trusted WinNT
authenticated connections (default setting for MSDE, you can actually modify
as reported in
http://support.microsoft.com/default...b;en-us;285097 ...
anyway, you do not have to provide SQL Server credentials for a standard SQL
Server authenticated connection in the form -U "domain\user" -P "domain
password" as SQL Server is unaware of your domain controller... for this
type of login you have to provide credentials regarding a registered SQL
Server login... ie: "sa" and it's password..
http://msdn.microsoft.com/library/de...maint_19ix.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Thanks for your reply and apologies for not getting back sooner!
I changed the regiatry to allow mixed authentication and have got a bit
further. I am now getting a new error and was wondering if you know what
might be causing it (google has not returned much info on this!)
Using the same command as before but with "-U administrator -P password" I
now get the following error:
Logged on to SQL Server 'servername'
as 'administrator' <non-trusted>
error 21776: the name 'data_baseName' was not found in the database
collection. If the name is a qualified name use [] to seperate various
parts of the name and try again.
I've tried enclosing the database with the [ ] but to no avail. My database
name looks like
ePO_SERVER_data.mdf
Ive tried [ePO_SERVER_data] and [ePO_SERVER_data.mdf] but it doesn't do
anything.
I'd appreciate any help you can give
cheers
gerry
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:4691c0Fa2i54U1@.individual.net...
> hi,
> gerryR wrote:
> this exception,
> usually is relative to a SQL Server instance only allowing trusted WinNT
> authenticated connections (default setting for MSDE, you can actually
> modify as reported in
> http://support.microsoft.com/default...b;en-us;285097 ...
> anyway, you do not have to provide SQL Server credentials for a standard
> SQL Server authenticated connection in the form -U "domain\user" -P
> "domain password" as SQL Server is unaware of your domain controller...
> for this type of login you have to provide credentials regarding a
> registered SQL Server login... ie: "sa" and it's password..
> http://msdn.microsoft.com/library/de...maint_19ix.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Gerry,
gerryR wrote:
> error 21776: the name 'data_baseName' was not found in the database
> collection. If the name is a qualified name use [] to seperate
> various parts of the name and try again.
> I've tried enclosing the database with the [ ] but to no avail. My
> database name looks like
> ePO_SERVER_data.mdf
> Ive tried [ePO_SERVER_data] and [ePO_SERVER_data.mdf] but it doesn't
> do anything.
you have to indicate the "logical" database name..
ePO_SERVER_data.mdf is the physical name of the primary data file..
try a
SELECT db.name
FROM master.dbo.sysdatabases db
WHERE db.filename LIKE '%ePO_SERVER_data.mdf'
to identify the correct logical database name you are interested with...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea, thanks a million for that I didn't realise it had to be the
logical name and not the file name. The logical name is ePO_servername and
the command ran without error!! Thanks a million for your help!
One thing I did notice is the file size stayed the same. Is this normal? I
would have thought the "RmUnusedSpace" part would have freed up some space
and thus reduced the file size?
any way thanks again for all your help in solving my problem.
all the best
gerryR
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:46j2qqFbc3ebU1@.individual.net...
> hi Gerry,
> gerryR wrote:
> you have to indicate the "logical" database name..
> ePO_SERVER_data.mdf is the physical name of the primary data file..
> try a
> SELECT db.name
> FROM master.dbo.sysdatabases db
> WHERE db.filename LIKE '%ePO_SERVER_data.mdf'
> to identify the correct logical database name you are interested with...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||The file won't get smaller unless you explicitly tell it to get smaller with
the shrink command. IMHO, shrink should only be used if you are desperate
for disk space and you know that your database isn't going to grow anymore.
Growing a database is reasonably expensive - as is shrinking it so
continuously growing and shrinking your database files is a waste of
resources. To me it's like when your mother comes to visit you add another
bedroom to your house and as soon as she leaves you tear it down. This only
makes sense if you know for sure you're never having visitors again. If
your mom is coming again next Christmas, you would probably be better off
leaving the spare room where it is - unless you really need the space for
the new hot-tub.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gerryR" <gerryrminus@.thisgerryr.com> wrote in message
news:%230$BsIHPGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi Andrea, thanks a million for that I didn't realise it had to be the
> logical name and not the file name. The logical name is ePO_servername
> and the command ran without error!! Thanks a million for your help!
> One thing I did notice is the file size stayed the same. Is this normal?
> I would have thought the "RmUnusedSpace" part would have freed up some
> space and thus reduced the file size?
> any way thanks again for all your help in solving my problem.
> all the best
> gerryR
>
>
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:46j2qqFbc3ebU1@.individual.net...
>
|||Thaks for the info Roger, I'll leave it the way it is so.
good analogy btw!
gerryR
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uq51f4HPGHA.3064@.TK2MSFTNGP10.phx.gbl...
> The file won't get smaller unless you explicitly tell it to get smaller
> with the shrink command. IMHO, shrink should only be used if you are
> desperate for disk space and you know that your database isn't going to
> grow anymore. Growing a database is reasonably expensive - as is shrinking
> it so continuously growing and shrinking your database files is a waste of
> resources. To me it's like when your mother comes to visit you add
> another bedroom to your house and as soon as she leaves you tear it down.
> This only makes sense if you know for sure you're never having visitors
> again. If your mom is coming again next Christmas, you would probably be
> better off leaving the spare room where it is - unless you really need the
> space for the new hot-tub.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "gerryR" <gerryrminus@.thisgerryr.com> wrote in message
> news:%230$BsIHPGHA.312@.TK2MSFTNGP12.phx.gbl...
>

error with SQL statement

hi, i try the below sql code, however when i try to execute the command, it always give me the error 'Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.'

If i remove the "(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)" under the group by clause, it will work

do I need to change something in the sql statement?

Thanks in advance

SELECT TAG_FACE_CON.REQUEST_ID,
(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)AS status
FROM (TAG_FACE_CON RIGHT JOIN tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID)
GROUP BY TAG_FACE_CON.REQUEST_ID,(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)

You are going to have to remove the select staement from the group by. Also in that SQl Staement there is no from clause... It does not know where to get the batch completed information.

I would look into create a temp table to create the first section as raw data, then when you need to do the group by run it as a query from the temp table.|||I think restructuring your query like this will solve your problem:

SELECT
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END AS status
FROM
 TAG_FACE_CON
RIGHT JOIN
 tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID
GROUP BY
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END

Terri

error with rs.exe utility

I'm getting some errors when I run rs.exe from command line with regard to Parameters, and was hoping I might get some help here. I'm somewhat new to RS. I'm running SQL 2000 and RS 2000 on a Windows 2003 Server.

Here is my script:
***********************************************************
' File: RunReport.rss
Dim format as string = "Excel"
Dim fileName as String = "C:\Export2.xls"
Dim reportPath as String = "/APSAgentCourseCompletionByUserGroup"

Public Sub Main()

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "ms-excel"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Report Parameters
Dim parameters(5) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "User Group"
parameters(0).Value = "66179312-f341-4df7-a855-79c5b8abaf9f"
parameters(1) = New ParameterValue()
parameters(1).Name = "Start Date"
parameters(1).Value = "12/22/2006"
parameters(2) = New ParameterValue()
parameters(2).Name = "End Date"
parameters(2).Value = "2/17/2007"
parameters(3) = New ParameterValue()
parameters(3).Name = "Course Name"
parameters(3).Value = "AA2B1AA0-E78A-4F84-834F-8EAAB75BEBD3"
parameters(4) = New ParameterValue()
parameters(4).Name = "Status To Include"
parameters(4).Value = "0"
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub

'End of script
***********************************************************

As you can see, right now I've got all 5 report parameters hard-coded.

Here is the command line that I'm using as well as the error that I receive:

rs.exe -i c:\RunReport.rss -s http://localhost/reportserver -t
Unhandled exception: The value of parameter 'Parameters' is not valid. Check the documentation for information about valid values.

Any insight that anyone can provide is greatly appreciated.

Thanks!

Raywhat...? no response to this yet? i'm desperately seeking samples for
how to use this rs.exe utility, and there's just not enough out there.

a comprehensive tutorial would be nice too actually; but thats just a dream at this point.
|||Yeah, i was really hoping that someone would respond to it as well, but luckily i stumbled upon my own solution. The error that I was receiving was because the array I initialized was incremented by too much. Instead of

Dim parameters(5) As ParameterValue

it should have been:

Dim parameters(4) As ParameterValue

Once I made that change the script was executing just fine!

Hope this helps you somewhat. Are you receiving other errors?

error with rs.exe utility

I'm getting some errors when I run rs.exe from command line with regard to Parameters, and was hoping I might get some help here. I'm somewhat new to RS. I'm running SQL 2000 and RS 2000 on a Windows 2003 Server.

Here is my script:
***********************************************************
' File: RunReport.rss
Dim format as string = "Excel"
Dim fileName as String = "C:\Export2.xls"
Dim reportPath as String = "/APSAgentCourseCompletionByUserGroup"

Public Sub Main()

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "ms-excel"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Report Parameters
Dim parameters(5) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "User Group"
parameters(0).Value = "66179312-f341-4df7-a855-79c5b8abaf9f"
parameters(1) = New ParameterValue()
parameters(1).Name = "Start Date"
parameters(1).Value = "12/22/2006"
parameters(2) = New ParameterValue()
parameters(2).Name = "End Date"
parameters(2).Value = "2/17/2007"
parameters(3) = New ParameterValue()
parameters(3).Name = "Course Name"
parameters(3).Value = "AA2B1AA0-E78A-4F84-834F-8EAAB75BEBD3"
parameters(4) = New ParameterValue()
parameters(4).Name = "Status To Include"
parameters(4).Value = "0"
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub

'End of script
***********************************************************

As you can see, right now I've got all 5 report parameters hard-coded.

Here is the command line that I'm using as well as the error that I receive:

rs.exe -i c:\RunReport.rss -s http://localhost/reportserver -t
Unhandled exception: The value of parameter 'Parameters' is not valid. Check the documentation for information about valid values.

Any insight that anyone can provide is greatly appreciated.

Thanks!

Ray

what...? no response to this yet? i'm desperately seeking samples for
how to use this rs.exe utility, and there's just not enough out there.

a comprehensive tutorial would be nice too actually; but thats just a dream at this point.
|||Yeah, i was really hoping that someone would respond to it as well, but luckily i stumbled upon my own solution. The error that I was receiving was because the array I initialized was incremented by too much. Instead of

Dim parameters(5) As ParameterValue

it should have been:

Dim parameters(4) As ParameterValue

Once I made that change the script was executing just fine!

Hope this helps you somewhat. Are you receiving other errors?

Error with NULL using EXECUTE SCALAR

I keep getting an error message when I try to place the result of an execute scalar command into a text box. The msg is:"conversion from 'DBNull' to type 'String' is not valid"

The code I am using is:

Dim con as OLEDBConnection

con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:\caps.mdb")

Dim cmd As OLEDBCommand

cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue

texBox1.Text = cmd.ExecuteScalar

The code works fine as long as there is a value for the Product. However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this?

Chas28

You need to first check the returning value of the query that if it is null then you don't need to set the text box text because its text property require an empty or non empty string not the Null value.

Try this:

if not cmd.ExecuteScalar =DBNull.Value then

texBox1.Text = cmd.ExecuteScalar

End If

Don't forget to click "Mark as Answer" on the post that helped you.
This earns you a point.

Zeeshan Malik
http://zeemalik.wordpress.com

|||

Sorry, but "if not cmd.ExecuteScalar =DBNull.Value" does not work. I get the error message :

'= is not defined for system.data.OLEDbCommand' and 'system.DBNull''

but thanks for trying.

Chas28

|||

ok try this:

Dim returnValue as String

returnValue = cmd.ExecuteScalar

if not ( returnValue =DBNull.Value OR returnValue =Nothing)then

texBox1.Text = returnValue

End If

Don't forget to click "Mark as Answer" on the post that helped you.
This earns you a point.

Zeeshan Malik
http://zeemalik.wordpress.com

Error with Jump to Javascript statement

I have the following statement being used in the Jump to command.
= "javascript:void(window.open('" &
http://domainname/ReferralForm.aspx?ReferralID= "& Fields!REF_ID.Value &
','_blank'))"
When I try to preview the report, I get the following error in the task list
The hyperlink expression for the textbox "REF_ID" contains a colon or a line
terminator. Colons and line terminators are not valid in expresssions.This should work:
="javascript:void(window.open('http://domainname/ReferralForm.aspx?ReferralID="
& Fields!REF_ID.Value & "','_blank'))"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:D6B0AAD2-44FB-48F4-9DD2-F598FFD7D52E@.microsoft.com...
>I have the following statement being used in the Jump to command.
> = "javascript:void(window.open('" &
> http://domainname/ReferralForm.aspx?ReferralID= "& Fields!REF_ID.Value &
> ','_blank'))"
>
> When I try to preview the report, I get the following error in the task
> list
> The hyperlink expression for the textbox "REF_ID" contains a colon or a
> line
> terminator. Colons and line terminators are not valid in expresssions.

error while working with dynamic query in report

Hi all,
I am trying to add report parameter to my report.I am getting "cannot
set the command text for dataset ''dataset_name " this kind of error. I am
working with sql server 2005.I found this tutorial in Books Online->sql
server tutorials - >Reporting Services Tutorials - >Using a dynamic query in
a report.
Can u plz tell me why i am getting this kind of error.
Thnx.YOu should repost this in the Reporting Services Group. But also include the
actual Query you are using...
ie
=" Select * From titles"
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"supriya" wrote:

> Hi all,
> I am trying to add report parameter to my report.I am getting "cann
ot
> set the command text for dataset ''dataset_name " this kind of error. I am
> working with sql server 2005.I found this tutorial in Books Online->sql
> server tutorials - >Reporting Services Tutorials - >Using a dynamic query
in
> a report.
> Can u plz tell me why i am getting this kind of error.
> Thnx.
>
>

Error while shrinking transaction log

Hi all,

while I shrinked transaction log using command "dbcc shrinkfile (logfile)" in query analyzer, the error message " cannot find logfile in sysfiles" was shown up.

The I list out content of sysfiles, and found that sysfiles doesn't contain logfile.

Can anybody tell me how to eliminate error in order to shrink transaction log?

Thanks in advances.

PaulYou need to enter the correct filename for your tempdb database. Default is templog. Right click on tempdb to find your correct filename.
Try:
USE tempdb
go
DBCC SHRINKFILE (TEMPLOG)|||Martsar,

Thanks for your input.

I checked and found that my syntax and log file name were correct, but preceding error message was still there. But I did successfully to shrink TEMPLOG.|||to check the filenames of db and log execute from your database:

use yourDB
go
sp_helpfile

The syntax to shrink db and log is:
DBCC SHRINKDATABASE (N'dbName/or logName', 0)

Also, try to shrink log in EM.