Problem scheduling a package

Hi,
I have a package saved in the msdb database. When I right click on it and execute, it works fine through the dts utility program.

When I schedule it, it always errors out with the message "The command line parameters are invalid".

The package is password protected. The command line in the dts utility reads: /DTS "\MSDB\<package name>" /SERVER aawork2/DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I have to enter my password to view this.

In the job step, if I click the command line, it reads: /DTS "/MSDB/<package name>" /SERVER aawork2/DECRYPT <password> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I suspect its something to do with the password protection and the fact the job runs it under the service account.

Thanks in advance for any help.

Asim.
No, the problem (most likely) is that the package path has forward slashes instead of backward slashes. Try changing them to back slashses. Also put a space between the server name and the /DECRYPT option. These were bugs that should be fixed by RTM.

Thanks,
Matt|||Matt, it works. Thanks! This was very helpful.

Asim.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);Nobody is doing this?|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

Nobody is doing this?
|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.
|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C#

I am having a problem running a SSIS Script from within a C# program. Script was running fine and then one day it stopped running and is giving me the following errors and nothing has changed.

2007-07-18 14:27:52,098 [1] ERROR reporting.Processor [(null)] - Problem with DTS Script

2007-07-18 14:27:52,895 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

The SSIS runs fine from the process on my machine, from SQL Server on my machine and from SQL Server on the server. But when I run it from the C# executable I get the above errors. This process also runs other SSIS scripts and they all work fine. I am using the following code to execute the script. Can anybody give me some ideas on how to troubleshoot this problem.

Package package = app.LoadFromSqlServer("\\Maintenance Plans\\SCRA2", "ppntt240", "load_abc", "bcp123", null);

DTSExecResult result = package.Execute();

Variables vars = package.Variables;
int rowcount = Convert.ToInt32(vars["count"].Value);
String rowcount2 = "0000000000" + rowcount.ToString();

string fullcnt = rowcount2.ToString().Substring(rowcount2.Length - 10, 10);

if (result == DTSExecResult.Failure)
{
Console.WriteLine("Task failed or abended");
log.Error("Problem with DTS Script");
log.Error(package.Errors);

}
else
{
Console.WriteLine("Task ran successfully");

Can you enable logging in the package so that you can get more detail about the error?|||How do I do this?|||Right-click on the package in the designer, choose Logging, add a log provider (Text is simple to set up, but database is good too), and check the checkbox beside the package name. In the details tab, make sure you check OnError and OnWarning,|||Logging is not an option if I right click. If I select run I see logging and it ask for log provider e.g.(text file, event log or SQL Server) and Configuration String. Is this where I turn it on? What are the best options? Thanks.
|||If you open the package in BIDS, go to the control flow for the package, and right-click in an empty area (one not occupied by a task), you should see the logging option.|||Is there anything unique to the server that would cause the following errors?

PackageStart,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:21 PM,7/19/2007 12:35:21 PM,0,0x,Beginning of package execution.

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

OnTaskFailed,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,0,0x,(null)
OnWarning,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-2147381246,0x,The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

PackageEnd,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,1,0x,End of package execution.

|||

agentf1 wrote:

Is there anything unique to the server that would cause the following errors?

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

Looks like you have problem with you source data. Since you get out-of-range problem, my bet is that the source date format is messed up. Probably server is expecting mm/dd/yyyy and your source date is dd/mm/yyyy

Thanks.

|||Data is fine. Script runs on server outside of C#.|||Are you absolutely positive that the package is connecting to the same data sources when you run it from C#? I have a hard time believing that an error message on a data conversion error isn't caused by the data |||As John said, Check for the connections in the data flow task (Direct_Prod) when you run from the C# and in Server.

Thanks|||It is the same. It was running fine and then one day just started bombing. It runs from the server ok and runs from the C# ok when it is on my desktop. Strangest thing I have ever seen.

I recently added the logging so I am sure I have the same version.
|||I figured out my problem. The date comes in like this in most cases 07/09/2007 but comes in like this when run via C# on the server 7/9/2007. I am doing this when I build my query in the string + HolidayEndDt.ToString.Substring(0, 10) +

It is also apparently giving me problems in another spot where I compare month to see if it is EOM and it fails because it is comparing 07 to 7/ since I am using substring for that as well.

What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.
|||

agentf1 wrote:

HolidayEndDt.ToString.Substring(0, 10)
What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.

Assuming you HolidayEndDt is datetime type,

HolidayEndDt.ToString("yyyyMMdd") should solve your problem, and as far as i know thats one of the best way to deal with dates.

Thanks

|||

Yes, that is what I did. Thanks.

Actually I did HolidayEndDt.ToString("MM/dd/yyyy") but I guess there is 6 in one and half dozen in the other. More or less the same thing.

Problem reattaching database

I am writing a program in vb6 to move a database and its log file from one
directory to another using sp_attach_db and sp_detach_db.
I used 'sp_detach_db mydb' to detach the database, moved it to the new
directory location and then tried to attach the database using the command:
'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
it; however I get the following error message.
"
Run-time error '-2147217900 (80040e14)':
The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
files. A database can have only one primary file.
"
Am I missing a parameter in sp_attach_db?
Is there another step that I am missing that needs to be done before I
attach it again?> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
I would guess that you have a bug in your code and accidentally copied the
primary data file (mdf) to both the mdf and ldf file names.
Hope this helps.
Dan Guzman
SQL Server MVP
"iScanTeam@.community.nospam"
<iScanTeamcommunitynospam@.discussions.microsoft.com> wrote in message
news:164CA596-73DD-46F5-9590-6EED155B5AF9@.microsoft.com...
>I am writing a program in vb6 to move a database and its log file from one
> directory to another using sp_attach_db and sp_detach_db.
> I used 'sp_detach_db mydb' to detach the database, moved it to the new
> directory location and then tried to attach the database using the
> command:
> 'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
> it; however I get the following error message.
> "
> Run-time error '-2147217900 (80040e14)':
> The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
> files. A database can have only one primary file.
> "
> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
>|||My man! Check to make sure your specified log and data files are indeed in
that directory. Otherwise, try something like this.
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'c:\path\mydb_log.ldf',
@.filename2 = N'c:\path\mydb.mdf'
"iScanTeam@.community.nospam" wrote:

> I am writing a program in vb6 to move a database and its log file from one
> directory to another using sp_attach_db and sp_detach_db.
> I used 'sp_detach_db mydb' to detach the database, moved it to the new
> directory location and then tried to attach the database using the command
:
> 'sp_attach_db mydb, 'c:\path\mydb.mdf', c:\path\mydb_log.ldf' to reattach
> it; however I get the following error message.
> "
> Run-time error '-2147217900 (80040e14)':
> The file 'c:\path\mydb.mdf' and ' c:\path\mydb_log.ldf' are both primary
> files. A database can have only one primary file.
> "
> Am I missing a parameter in sp_attach_db?
> Is there another step that I am missing that needs to be done before I
> attach it again?
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GO
Do not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:

> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity()
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccurent,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================
> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================
> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
============================= CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GODo not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:
> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity()
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccurent,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem on insert using stored procedure

Here is my problem on SQL Server SP3a.
I have a table that reach more than 2 millions rows. My primary key is an
identity column.
From a Java program I'm calling a strored procedure to insert a new row in
that table and to get back the id of the new row using the scope_identity()
function.
I log the id returned by the first stored procedure and to ensure the line
has been added I call a second stored procedure to look if my line exists in
the table. That request returns a line and when I call a request from SQL
Server enterprise manager on my id I get no line in my table.
I really don't understand what can be my problem because it's not reccurent,
most of the time it's work fine. It seems that the first transaction is
sometimes rolled back by the systems.
If someone has an idea...
Stored procedure 1 to INSERT:
=============================
CREATE procedure SetIndFpsLogWeb
@.F_WobNum varchar(40)='' ,
@.codeuser varchar(30)='' ,
@.datedebutetat varchar(20)='',
@.datefinetat varchar(20)='',
@.datereception varchar(20)='',
@.datecreationdossier varchar(20)='',
@.numeroenregistrement varchar(16)='',
@.activites varchar(20)='' ,
@.produit varchar(4)='' ,
@.do varchar(4)='' ,
@.idclient varchar(12)='',
@.etatdossier varchar(2)='',
@.mediaentree varchar(4)='',
@.mediasortie varchar(4)='',
@.datefindossier varchar(20)='',
@.segmentclient varchar(12)='',
@.actions varchar(50)='' ,
@.etatexportdata varchar(1)='',
@.numeroenregistrementpli varchar(20)='' ,
@.taches varchar(50)='' ,
@.languecontactentrant varchar(4)='' ,
@.languecommunication varchar(4)='',
@.pays varchar(4)='' ,
@.datefinprevisionnelle varchar(20)='' ,
@.datelimitetraitement varchar(20)=''
AS
begin transaction
IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
BEGIN
SET @.numeroenregistrementpli = @.numeroenregistrement
END
IF (@.actions<> '' )
BEGIN
if( SUBSTRING(@.actions, 1,1)=';')
BEGIN
SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
END
END
IF (@.taches<> '' )
BEGIN
if( SUBSTRING(@.taches, 1,1)=';')
BEGIN
SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
END
END
IF (@.activites<> '' )
BEGIN
if( SUBSTRING(@.activites, 1,1)=';')
BEGIN
SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
END
END
INSERT INTO ind_fps (
F_WobNum,
codeuser,
datedebutetat,
datefinetat,
datereception,
datecreationdossier,
numeroenregistrement,
activites,
produit,
do,
idclient,
etatdossier,
mediaentree,
mediasortie,
datefindossier,
segmentclient,
actions,
etatexportdata,
numeroenregistrementpli,
taches,
languecontactentrant,
languecommunication,
pays,
datefinprevisionnelle,
datelimitetraitement)
VALUES (
@.F_WobNum,
@.codeuser,
@.datedebutetat,
@.datefinetat,
@.datereception,
@.datecreationdossier,
@.numeroenregistrement,
@.activites,
@.produit,
@.do,
@.idclient,
@.etatdossier,
@.mediaentree,
@.mediasortie,
@.datefindossier,
@.segmentclient,
@.actions,
@.etatexportdata,
@.numeroenregistrementpli,
@.taches,
@.languecontactentrant,
@.languecommunication,
@.pays,
@.datefinprevisionnelle,
@.datelimitetraitement)
declare @.return varchar(500)
if @.@.error <> 0
begin
set @.return = 'ERROR : ' + cast(@.@.error as varchar)
rollback tran
end
else
begin
set @.return = scope_identity()
commit tran
end
select @.return
GO
Stored procedure 2 to GET:
==========================
CREATE procedure dbo.GetIndFpsInfosById
@.indfps_id varchar(20) = ''
as
begin transaction
SELECT *
FROM [ind_fps]
WHERE id = @.indfps_id
commit transaction
GODo not use a transaction in the second sp and use "set nocount on" in both
sps, as the first statement.
AMB
"edemasi" wrote:

> Here is my problem on SQL Server SP3a.
> I have a table that reach more than 2 millions rows. My primary key is an
> identity column.
> From a Java program I'm calling a strored procedure to insert a new row in
> that table and to get back the id of the new row using the scope_identity(
)
> function.
> I log the id returned by the first stored procedure and to ensure the line
> has been added I call a second stored procedure to look if my line exists
in
> the table. That request returns a line and when I call a request from SQL
> Server enterprise manager on my id I get no line in my table.
> I really don't understand what can be my problem because it's not reccuren
t,
> most of the time it's work fine. It seems that the first transaction is
> sometimes rolled back by the systems.
> If someone has an idea...
> Stored procedure 1 to INSERT:
> =============================
> CREATE procedure SetIndFpsLogWeb
> @.F_WobNum varchar(40)='' ,
> @.codeuser varchar(30)='' ,
> @.datedebutetat varchar(20)='',
> @.datefinetat varchar(20)='',
> @.datereception varchar(20)='',
> @.datecreationdossier varchar(20)='',
> @.numeroenregistrement varchar(16)='',
> @.activites varchar(20)='' ,
> @.produit varchar(4)='' ,
> @.do varchar(4)='' ,
> @.idclient varchar(12)='',
> @.etatdossier varchar(2)='',
> @.mediaentree varchar(4)='',
> @.mediasortie varchar(4)='',
> @.datefindossier varchar(20)='',
> @.segmentclient varchar(12)='',
> @.actions varchar(50)='' ,
> @.etatexportdata varchar(1)='',
> @.numeroenregistrementpli varchar(20)='' ,
> @.taches varchar(50)='' ,
> @.languecontactentrant varchar(4)='' ,
> @.languecommunication varchar(4)='',
> @.pays varchar(4)='' ,
> @.datefinprevisionnelle varchar(20)='' ,
> @.datelimitetraitement varchar(20)=''
> AS
>
> begin transaction
> IF (@.numeroenregistrement <> '' AND @.numeroenregistrementpli ='' )
> BEGIN
> SET @.numeroenregistrementpli = @.numeroenregistrement
> END
> IF (@.actions<> '' )
> BEGIN
> if( SUBSTRING(@.actions, 1,1)=';')
> BEGIN
> SET @.actions = SUBSTRING(@.actions, 2, LEN(@.actions))
> END
> END
> IF (@.taches<> '' )
> BEGIN
> if( SUBSTRING(@.taches, 1,1)=';')
> BEGIN
> SET @.taches = SUBSTRING(@.taches, 2, LEN(@.taches))
> END
> END
> IF (@.activites<> '' )
> BEGIN
> if( SUBSTRING(@.activites, 1,1)=';')
> BEGIN
> SET @.activites= SUBSTRING(@.activites, 2, LEN(@.activites))
> END
> END
> INSERT INTO ind_fps (
> F_WobNum,
> codeuser,
> datedebutetat,
> datefinetat,
> datereception,
> datecreationdossier,
> numeroenregistrement,
> activites,
> produit,
> do,
> idclient,
> etatdossier,
> mediaentree,
> mediasortie,
> datefindossier,
> segmentclient,
> actions,
> etatexportdata,
> numeroenregistrementpli,
> taches,
> languecontactentrant,
> languecommunication,
> pays,
> datefinprevisionnelle,
> datelimitetraitement)
> VALUES (
> @.F_WobNum,
> @.codeuser,
> @.datedebutetat,
> @.datefinetat,
> @.datereception,
> @.datecreationdossier,
> @.numeroenregistrement,
> @.activites,
> @.produit,
> @.do,
> @.idclient,
> @.etatdossier,
> @.mediaentree,
> @.mediasortie,
> @.datefindossier,
> @.segmentclient,
> @.actions,
> @.etatexportdata,
> @.numeroenregistrementpli,
> @.taches,
> @.languecontactentrant,
> @.languecommunication,
> @.pays,
> @.datefinprevisionnelle,
> @.datelimitetraitement)
> declare @.return varchar(500)
> if @.@.error <> 0
> begin
> set @.return = 'ERROR : ' + cast(@.@.error as varchar)
> rollback tran
> end
> else
> begin
> set @.return = scope_identity()
> commit tran
> end
> select @.return
> GO
> Stored procedure 2 to GET:
> ==========================
> CREATE procedure dbo.GetIndFpsInfosById
> @.indfps_id varchar(20) = ''
> as
> begin transaction
> SELECT *
> FROM [ind_fps]
> WHERE id = @.indfps_id
> commit transaction
> GO
>

Problem On Font....

i Have A List Of Data In Sql All Are In 7 Bit Font.....
Does Any One Have Any Idea To Display That Particular Font By Calling In Asp Program...please Help....if am reading this right, you're trying to display the data in a different font? i don't think it's a good use of your sql-server resources. try doing that on the front-end of your application (vbscript, javascript, css)|||

Quote:

Originally Posted by ck9663

if am reading this right, you're trying to display the data in a different font? i don't think it's a good use of your sql-server resources. try doing that on the front-end of your application (vbscript, javascript, css)

thanks ck9663

but those data are stored in oracle which is install in unixware 1.0. i transfer the oracle data to sql but those all are in 7 bit local regional font...so now the problem is i develop a new software to access those data but fail...|||

Quote:

Originally Posted by madhurjya

thanks ck9663

but those data are stored in oracle which is install in unixware 1.0. i transfer the oracle data to sql but those all are in 7 bit local regional font...so now the problem is i develop a new software to access those data but fail...

append <font> tag to the data column you retrieve... like

select '<font ....>' + col_name + '</font>' as col_alias from table_name

i am not sure wether this is a right approach or not. hope will help you.

Problem of MS SQL -ntwdblib.lib NOT found

Dear all,

I want to use C program to connect MS SQL server.
I copied some sample file to install on my computer.
When I run the program, an error occurred, which is about ntwdblib.dll
not find in some paths.
But when I check the paths again, they have ntwdblib.dll.
When I run my C program in another computer, it works...

so , I feel very frustrated.

In my C program, I include the following libraries.
#defineDBNTWIN32
#include "windows.h"
#include <sqlfront.h>
#include <sqldb.h>
#include <stdio.h
Also, I don't know the meaning of "#defineDBNTWIN32".
I haven't installed SQL Client on my computer. Is it the main
problem??

Pls help!!

Cheers,
Alanalan (cheung_yuklun@.i-cable.com) writes:
> I want to use C program to connect MS SQL server.
> I copied some sample file to install on my computer.
> When I run the program, an error occurred, which is about ntwdblib.dll
> not find in some paths.
> But when I check the paths again, they have ntwdblib.dll.
> When I run my C program in another computer, it works...
> so , I feel very frustrated.
> In my C program, I include the following libraries.
> #define DBNTWIN32
> #include "windows.h"
> #include <sqlfront.h>
> #include <sqldb.h>
> #include <stdio.h>
> Also, I don't know the meaning of "#define DBNTWIN32".
> I haven't installed SQL Client on my computer. Is it the main
> problem??

Yes, that is the problem. NTWDBLIB.DLL is the DLL for DB-Library, which
is not part of MDAC, so SQL connectivity is about the only way to install
it.

DB-Library is a very good client library, but unfortunately Microsoft
does not agree, so they stopped developmet of DB-Lib many years ago.
As a consequence, you have restricted support for new features in
SQL Server with DB-Library.

Unless you have some legacy software you are working with, I'd recommend
you to use ODBC or OLE DB instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Problem istalling SQL2000 RS

I am trying to install SQL 2000 RS on my Vista box. I have installed VS
2003. But installation program says I don't have IIS running as well as ASP
1.1 framework not being registered or installed. Can someone please help me?
ThanksOn May 13, 6:21 pm, "Mark Goldin" <markgoldin_2...@.yahoo.com> wrote:
> I am trying to install SQL 2000 RS on my Vista box. I have installed VS
> 2003. But installation program says I don't have IIS running as well as ASP
> 1.1 framework not being registered or installed. Can someone please help me?
> Thanks
I'm not completely sure, but I don't believe that SSRS 2000 can run on
Vista. I believe that you need SQL Server/SSRS 2005 SP2 to run it on
Vista.
Regards,
Enrique Martinez
Sr. Software Consultant

Problem installing SQL 2000 on SBS2003

Hi,
I am getting an error right after it asks if the server is local or remote.
It says that a previous program installation created pending file operations
on the installation machine. And that i must restart the computer. I've
restarted...doesn't make a difference. The server has MSDE for sbs
installed. Not sure what to do.
Thanks,
Andrew OuellettePlease try the following:
1. Run Registry Editor
2. Go to HKEY_LOCAL_MACHINE folder
3. Go to SYSTEM folder
4. Go to CurrentControlSet folder
5. Go to Control folder
6. Go to Session Manager folder
7. Double-click on the PendingFileRenameOperations key
and remove everything from the Value Data section
This ought to do it, I hope!
Thanks,
Dee
>--Original Message--
>Hi,
>I am getting an error right after it asks if the server
is local or remote.
>It says that a previous program installation created
pending file operations
>on the installation machine. And that i must restart the
computer. I've
>restarted...doesn't make a difference. The server has
MSDE for sbs
>installed. Not sure what to do.
>Thanks,
>Andrew Ouellette
>.
>

problem installing sp4 on msde 2000 release A

Hello,
When I try to run the setup with the parameters I need (I have a default
instance installed already) to upgrade MSDE 2000 the setup program runs with
a blue screen on background and a modal window appears showing me the
parameters (like /i /p /v etc.). It seems to me that the setup is somehow
messed (I wonder if has something to do with the Windows Installer). Can
anyone help?
Thanks!
hi Leonardo,
Leonardo wrote:
> Hello,
> When I try to run the setup with the parameters I need (I have a
> default instance installed already) to upgrade MSDE 2000 the setup
> program runs with a blue screen on background and a modal window
> appears showing me the parameters (like /i /p /v etc.). It seems to
> me that the setup is somehow messed (I wonder if has something to do
> with the Windows Installer). Can anyone help?
have you tried re-downloading the sp4 package? perhaps it' corrupted..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Problem installing MSDE

Hi, are days that i'm trying to install MSDE on my computer (windows XP service pack 1).
The problem is that when I run the setup program from console using SAPWD="(Some password)" SecurityMode=SQL, it fails with that error log:

--- LOG START
2003-08-15 17:56:13.12 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 1)

2003-08-15 17:56:13.12 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-08-15 17:56:13.12 server All rights reserved.
2003-08-15 17:56:13.12 server Server Process ID is 3076.
2003-08-15 17:56:13.12 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2003-08-15 17:56:13.14 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2003-08-15 17:56:13.18 server SQL Server configured for thread mode processing.
2003-08-15 17:56:13.21 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2003-08-15 17:56:13.29 spid3 Warning ******************
2003-08-15 17:56:13.29 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2003-08-15 17:56:13.87 spid3 Starting up database 'master'.
2003-08-15 17:56:15.76 server Using 'SSNETLIB.DLL' version '8.0.766'.
2003-08-15 17:56:15.76 spid5 Starting up database 'model'.
2003-08-15 17:56:15.87 spid3 Server name is 'ALEX'.
2003-08-15 17:56:15.87 spid3 Skipping startup of clean database id 5
2003-08-15 17:56:15.87 spid3 Skipping startup of clean database id 6
2003-08-15 17:56:15.87 spid3 Starting up database 'msdb'.
2003-08-15 17:56:17.18 spid5 Clearing tempdb database.
2003-08-15 17:56:18.70 server SQL server listening on Shared Memory.
2003-08-15 17:56:18.70 server SQL Server is ready for client connections
2003-08-15 17:56:21.43 spid5 Starting up database 'tempdb'.
2003-08-15 17:56:22.56 spid3 Recovery complete.
2003-08-15 17:56:22.56 spid3 SQL global counter collection task is created.
2003-08-15 17:56:22.93 spid3 Warning: override, autoexec procedures skipped.
2003-08-15 17:56:36.48 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.
--- LOG END

if anybody can help, I would really appreciate!
Alex.http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q185/8/06.asp&NoWebContent=1

you should have googled that last line.

This article was previously published under Q185806
BUG #: Windows NT: 17812 (6.50)
SYMPTOMS
A SQL Server computer that is enabled for clustering with Microsoft Cluster Server (MSCS), may experience sudden stops of the SQL Server service and subsequent restarts of the service. The last error log terminates with the following message:

SQL Server terminating due to 'stop' request from Service Control Manager
CAUSE
The Resource DLL for the SQL Server service exports two functions used by the MSCS Cluster Manager to check for availability of the SQL Server resource at predefined intervals. There is a simple check, LooksAlive, that queries the service status through the Windows NT Service Control Manager, and a more stringent check, IsAlive, that connects to SQL Server as user "probe" and performs a simple query to the system catalog. By default, LooksAlive is fired every 5 seconds and IsAlive is fired every 60 seconds.

IsAlive uses a fixed login time-out of 15 seconds to connect to SQL Server. In situations where the server is very busy, SQL Server may fail to respond to the IsAlive login request within this interval. Thus IsAlive returns FALSE to the Cluster Manager, which issues a Terminate request and a subsequent Online request to SQL Server and the SQL Executive Resource DLL which causes both services to be stopped and restarted.
WORKAROUND
Increase the polling interval for SQL Server's IsAlive test in MSCS Cluster Administrator to decrease the chance for this to happen.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server 6.5. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:
http://support.microsoft.com/highlights/sql.asp

For more information, contact your primary support provider.
MORE INFORMATION
If you experience sudden SQL Server restarts in a cluster environment and are unsure of the cause, enable MSCS logging by restarting the MSCS service with the system environment variable "Clusterlog=<path>". MSCS will now log all activity in the specified log file. There you will find, among others, all calls to LooksAlive and IsAlive and their outcome. In a situation where the login fails you'll find the following message in the log:

[sql65res] CheckQueryProcessorAlive: dbopen failed|||I just upgraded to XP pro, was able to get IIS running and figured out how to browse to the server on my home network from another computer.

The goal is simply to follow the instructions when setting up the ASP CSK.

I downloaded and installed MSDE receiving the same error log as shown above.

How do I...

Increase the polling interval for SQL Server's IsAlive test in MSCS Cluster Administrator to decrease the chance for this to happen.

if the only thing I have is MSDE through the ASP.net link? And according to the error log, it isn't installed, because it was 'stop'ped.

Thanks,
Chris

Problem install SQL Server Desktop Engine

I'm in the process of attempting to install Microsoft SQL Server Desktop Engine on my computer in support of an email program call Mailloop6. I've tried going through their tech support but they haven't been able to figure it out.

I'm running Windows XP with SP1 upgrade on a DSL connection.

When I installed the program the first time, I got the following error:

"Setup failed to configure the server. Refer to the server error logs and
setup error logs for more information."

The program obviously wouldn't install. I contacted the vendor and they sent a version with a batch file. Same result. I removed all the appropriate registry entries between installs and removed all the data files as well.

I've tried installing the program using the free MDSE download from the MS site with the following result:

"A Strong SA password is required for security reasons. Please use SAPWD switch to supply the same. Refer to readme for more details. Setup will now exit."

Here's the error log that I got after attempting to install:

2004-02-25 19:31:42.55 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 1)

2004-02-25 19:31:42.55 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-02-25 19:31:42.55 server All rights reserved.
2004-02-25 19:31:42.55 server Server Process ID is 3404.
2004-02-25 19:31:42.55 server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL$MAILLOOP6LOGERRORLOG'.
2004-02-25 19:31:42.56 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-02-25 19:31:42.60 server SQL Server configured for thread mode processing.
2004-02-25 19:31:42.60 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-02-25 19:31:42.63 spid3 Warning ******************
2004-02-25 19:31:42.63 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-02-25 19:31:42.63 spid3 Starting up database 'master'.
2004-02-25 19:31:42.91 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-02-25 19:31:42.94 spid5 Starting up database 'model'.
2004-02-25 19:31:43.24 server SQL server listening on 152.163.0.0: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 172.16.1.33: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 127.0.0.1: 4113.
2004-02-25 19:31:43.25 spid3 Server name is 'MONSTERMAILLOOP6'.
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 5
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 6
2004-02-25 19:31:43.25 spid3 Starting up database 'msdb'.
2004-02-25 19:31:43.56 spid5 Clearing tempdb database.
2004-02-25 19:31:44.13 spid5 Starting up database 'tempdb'.
2004-02-25 19:31:44.17 spid3 Recovery complete.
2004-02-25 19:31:44.17 spid3 SQL global counter collection task is created.
2004-02-25 19:31:44.19 spid3 Warning: override, autoexec procedures skipped.
2004-02-25 19:31:47.75 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-02-25 19:31:47.75 server SQL Server is ready for client connections
2004-02-25 19:31:57.97 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

Any thoughts on this would be very much appreciated!Originally posted by betteru
I'm in the process of attempting to install Microsoft SQL Server Desktop Engine on my computer in support of an email program call Mailloop6. I've tried going through their tech support but they haven't been able to figure it out.

I'm running Windows XP with SP1 upgrade on a DSL connection.

When I installed the program the first time, I got the following error:

"Setup failed to configure the server. Refer to the server error logs and
setup error logs for more information."

The program obviously wouldn't install. I contacted the vendor and they sent a version with a batch file. Same result. I removed all the appropriate registry entries between installs and removed all the data files as well.

I've tried installing the program using the free MDSE download from the MS site with the following result:

"A Strong SA password is required for security reasons. Please use SAPWD switch to supply the same. Refer to readme for more details. Setup will now exit."

Here's the error log that I got after attempting to install:

2004-02-25 19:31:42.55 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 1)

2004-02-25 19:31:42.55 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-02-25 19:31:42.55 server All rights reserved.
2004-02-25 19:31:42.55 server Server Process ID is 3404.
2004-02-25 19:31:42.55 server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL$MAILLOOP6LOGERRORLOG'.
2004-02-25 19:31:42.56 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-02-25 19:31:42.60 server SQL Server configured for thread mode processing.
2004-02-25 19:31:42.60 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-02-25 19:31:42.63 spid3 Warning ******************
2004-02-25 19:31:42.63 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
2004-02-25 19:31:42.63 spid3 Starting up database 'master'.
2004-02-25 19:31:42.91 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-02-25 19:31:42.94 spid5 Starting up database 'model'.
2004-02-25 19:31:43.24 server SQL server listening on 152.163.0.0: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 172.16.1.33: 4113.
2004-02-25 19:31:43.24 server SQL server listening on 127.0.0.1: 4113.
2004-02-25 19:31:43.25 spid3 Server name is 'MONSTERMAILLOOP6'.
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 5
2004-02-25 19:31:43.25 spid3 Skipping startup of clean database id 6
2004-02-25 19:31:43.25 spid3 Starting up database 'msdb'.
2004-02-25 19:31:43.56 spid5 Clearing tempdb database.
2004-02-25 19:31:44.13 spid5 Starting up database 'tempdb'.
2004-02-25 19:31:44.17 spid3 Recovery complete.
2004-02-25 19:31:44.17 spid3 SQL global counter collection task is created.
2004-02-25 19:31:44.19 spid3 Warning: override, autoexec procedures skipped.
2004-02-25 19:31:47.75 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-02-25 19:31:47.75 server SQL Server is ready for client connections
2004-02-25 19:31:57.97 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.

Any thoughts on this would be very much appreciated!

with the desktop edition the setup does not work in windows! you must exit to dos and on the directory where you've installed msde you must write:
setup.exe /qb+ instancename= something sapwd=your password|||I did not know that - the tech support for the software didn't mention a single thing about exiting to DOS. I will definitely give that a try.

Thanks!

problem inserting data to a database using ADODB

Hi! I have a vb.net program that writes data to an SQL Server database using ADODB. The problem is if I put the code that writes data to the database in between BeginTrans() and CommitTrans(), the database is not updated. Here is the flow of my program:

Dim connection as ADODB.Connection
'setup the connection to the SQL Server database here

connection.BeginTrans()

InsertData() ' --> data is written to the database
' this function works properly

connection.CommitTrans()

If I comment out the BeginTrans() and CommitTrans() functions, the data is properly inserted into the database.

Does an SQL Server database requires special settings to support transactions?

Please help.
And thank you in advance.My guess is that your BEGIN TRAN and COMMIT TRAN counts are out of sequence, meaning that you've probably got at least one more BEGIN TRAN than you've got COMMIT operations, which leaves the INSERT in a pending state until you close the connection, after which the INSERT rolls back because there is still a pending transaction.

Try selecting the value of @.@.TRANCOUNT to see where your transactions stand at different points in the process.

-PatP|||thanks.

I'm sorry but I am not familiar with the SQL Server environment. Where can I look at the value of @.@.TRANCOUNT??|||Open a recordset, or use the connection's execute method to see the contents of @.@.trancount.

-PatP

Problem inserting data from IDictionary into Sql2005

Hi,

I'm not sure if this should be in the Sql or VB forums.

Using VS2005 I have a graphics program which holds data in a Dictionary Object.

The data consists of a Key and a Structure called Arrows. This structure holds a startpoint and an endpoint, both of which consist of x and y co-ordinates. I need to insert these four co-ordinates into X,Y,X1 and Y1 columns of a table,

The following Insert statement will insert the correct IdKey and Textbox data, but will not compile the co-ordinates.

I have initialised the Points of the structure, but get the error message that Type is not defined.

With dbCommand

Dim _mstartPoint As Point

Dim _mendPoint As Point

Dim strSql As String

For Each kvp As KeyValuePair(Of String, Arrow) In Arrows

strSql = "INSERT INTO Drawing_Arrows (ArrowId,FlashingCode,X1,Y1,X2,Y2) Values ('" & kvp.Key & "', '" & txt_Title.Text & "','" & CType(Arrows(kvp.Key), _mstartPoint).x.ToString & "', '" & CType(Arrows(kvp.Key), _mstartPoint).y.ToString & "''" & CType(Arrows(kvp.Key), _mendPoint).x.ToString & "', '" & CType(Arrows(kvp.Key), _mendPoint).y.ToString & "')"

.CommandText = strSql

.CommandType = Data.CommandType.Text

.Connection = dbConnection

.ExecuteNonQuery()

Next

End With

My problem is how to extract the _mstartPoint x and _mstartPoint y as seperate items for insertion into X and Y in the table.

Any suggestions welcome.

Tailor

Hi,

Finally figured out the way to do answer this question. use IDictionaryEnumerator. Code as shown, works.

Dim arrowEnumerator As IDictionaryEnumerator = Arrows.GetEnumerator

Do While arrowEnumerator.MoveNext

strSql = "INSERT INTO Drawing_Arrows (ArrowId,FlashingCode,X1,Y1,X2,Y2) Values ('" & _uniqueID & "','" & txt_Title.Text & "','" & CType(arrowEnumerator.Value, Arrow).StartPoint.X.ToString & "', '" & CType(arrowEnumerator.Value, Arrow).StartPoint.Y.ToString & "', '" & CType(arrowEnumerator.Value, Arrow).EndPoint.X.ToString & "', '" & CType(arrowEnumerator.Value, Arrow).EndPoint.Y.ToString & "')"

.CommandText = strSql

.CommandType = Data.CommandType.Text

.Connection = dbConnection

.ExecuteNonQuery()

Loop

Probably no use to anyone else, but posted it in case.

Tailor