Error in SQL Server Enterprise Manager

Hi Frndz,

I am using SQL Server 7.0, I will regularly take backup daily using sql

server enterprise manager. During backup running, i had cancelled

abnormally, when next time i take backup it shows the following error
"Microsoft SQL-DMO(ODBC SQLState:42000)"

"Backup,

CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as

CREATE FILE) operations on A database must be serialized. Reissue the

statement after the current backup. CHECKALLOC or file manipulation

operation is completed.
Backup or restore operation terminating abnormally"


How to solve this problem? send reply in detail.

With Regards,
Arul

Wrong forum. Try the disaster recovery forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=744&SiteID=1sql

Error in SQL 2005 Maintenance Plan

I'm trying to setup a nightly backup to a remote computer using an SQL
Server 2005 Maintenance Plan. It keeps giving an error. I've narrowed
the cause down to an execute command...
EXECUTE master.dbo.xp_create_subdir
N'\\\\10.2.32.15\\Backup_SQL2005\\\\IOC'
which is giving the error below...
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 123, 'The filename, directory name,
or volume label syntax is incorrect.'
I am signed on as administrator on both computers. I'm able to map a
drive though the Windows Explorer and create a subdirectory on the
remote machine.
I have also tried...
EXECUTE master.dbo.xp_create_subdir N'\\10.2.32.15\Backup_SQL2005\IOC'
EXECUTE master.dbo.xp_create_subdir N'\\GOMB-BACKUP\Backup_SQL2005\IOC'
EXECUTE master.dbo.xp_create_subdir N'G:\IOC' (after mapping a drive)
This works...
EXECUTE master.dbo.xp_create_subdir N'C:\IOC'
...but of course it's on the local computer.
Any ideas why this doesn't work?
Thanks,
CThe stuff you do in Explorer uses the Windows account *you* have logged in w
ith. The stuff that SQL
Server does uses the Service account for the SQL Server service. Verify that
the service account has
proper permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"The Cornjerker" <addoty@.gmail.com> wrote in message
news:1140218704.513963.300930@.g44g2000cwa.googlegroups.com...
> I'm trying to setup a nightly backup to a remote computer using an SQL
> Server 2005 Maintenance Plan. It keeps giving an error. I've narrowed
> the cause down to an execute command...
> EXECUTE master.dbo.xp_create_subdir
> N'\\\\10.2.32.15\\Backup_SQL2005\\\\IOC'
> which is giving the error below...
> Msg 22048, Level 16, State 1, Line 0
> xp_create_subdir() returned error 123, 'The filename, directory name,
> or volume label syntax is incorrect.'
> I am signed on as administrator on both computers. I'm able to map a
> drive though the Windows Explorer and create a subdirectory on the
> remote machine.
> I have also tried...
> EXECUTE master.dbo.xp_create_subdir N'\\10.2.32.15\Backup_SQL2005\IOC'
> EXECUTE master.dbo.xp_create_subdir N'\\GOMB-BACKUP\Backup_SQL2005\IOC'
> EXECUTE master.dbo.xp_create_subdir N'G:\IOC' (after mapping a drive)
> This works...
> EXECUTE master.dbo.xp_create_subdir N'C:\IOC'
> ...but of course it's on the local computer.
> Any ideas why this doesn't work?
> Thanks,
> C
>|||Tibor, thanks for the replay.
How do I give the Service account access to create remote
subdirectories?
C|||The Cornjerker (addoty@.gmail.com) writes:
> Tibor, thanks for the replay.
> How do I give the Service account access to create remote
> subdirectories?
As you would to for any other Windows user. (Which if you don't know is
a question for a Windows newsgroup. I hardly know it myself.)
Note howver, that you cannot do this if SQL Server is running under
Local System. In this case you need to change which account under which
SQL Server is running. To do this, right-click My Computer, select Manager,
find Services, and there find the SQL Server service. Double-click, and
log-on information is on the Log On tab.
If you are using SQL 2005, you should use the SQL Computer Manager instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thanks for the replay. Are there any divantages to running the
MSSQLSERVER service under the Administrator account?
C

error in image field when using CASE statement

I've this Stored procedure on a SQLserver 2000 SP3:

SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
--------
OK
--------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??So, are you saying that "sometimes" it works and sometimes it doesn't?

This should work "always":

SELECT *,CASE WHEN immagine IS NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra

But exclude the image field from the SELECT list, unless you really intend to use it.|||i must use it!!!

Error in DataList test query - sqlserver

Error in DataList test query - sqlserver
I found some vs 2005 asp.net walkthroughs.
The first I tried required Northwind.
But sqlserver 2005 came with AdventureWorks, so I used that instead.

I followed the walkthrough instructions, dragging a DataList to Design, and clicking the smart tag to configure my datasource.

sqldatasource=SqlDataSource1
connection string - Data Source=YOUR-02910F1DF1;Initial Catalog=AdventureWorks;Integrated Security=True
I tried the test query.
There was an error executing the query....
Invalid name '[Employee}
I tried the query builder, and then the second line is my own sql statement. Both produced the error above.

SELECT [EmployeeID], [Title] FROM [Employee]
SELECT [EmployeeID], [Title] FROM Employee

I tried this with several different tables, all producing the same error.

When I do with with an access database with an access datasource, it works fine. But since I really want to put something on the web, I need sqlserver 2005.

I confirmed that sqlserver.exe was running.

Can somebody help me?

dennist685

I'd really appreciate an answer to this question. I can't go very far in web applications if I can't attach a datasource to a DataList.

dennist685|||

I got an answer from of all places, the 2003 newsgroups.

Hi Dennist685,

Welcome to ASPNET newsgroup.
From your description, when you try creating a data-driven page in ASP.NET
2.0/VS.NET 2005, you always get error on test query, yes?

Based on my experience, the VS.NET 2005 will use configuration wizard to
help create Sql query statements for our SqlDataSource control. Are you
following the wizard to configure the datasoruce? We can first confirm that
the SqlDataSource is configured correctly. We can open the aspx after
configured the sqldatasource control and check the sql query statement
embeded (test in SQLServer's query console/analyzer to see whether it
works....).

After the SqlDataSource is configured OK, we can then attach it to the
DataList control so as to displaying data.

BTW, for Northwind and Pubs databases, we can manually copy their mdf file
from the original SQLServer 2000 and manually attach them into SQLServer
2005. Also, VS.NET 2005/ .net 2.0 support sqlserver 7.0 2005 (also
include a SQLExpress edition which replace the original MSDE). So please
feel free to use either of them for your data store.

If there're anything unclear or need any further help, please feel free to
post here.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Thank you Steven,

I never got as far as configuring the datasource correctly.

There was an error executing the (test) query. Please check the syntax of the command and if present, the types and values of the parameters to be sure they are correct.

Invalid name 'Employee'

There is no stack trace. I was in the configuration stage where the wizard asks you to perform a test query.

I went ahead and built default.aspx successfully,

But when I tried to view it in a browser, I got the stack trace you wanted.

Server Error in '/FormatDataList' Application.
--

Invalid object name 'Employee'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'Employee'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Invalid object name 'Employee'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
System.Web.UI.WebControls.BaseDataList.GetData() +53
System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +57
System.Web.UI.WebControls.BaseDataList.DataBind() +62
System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55
System.Web.UI.WebControls.BaseDataList.CreateChildControls() +62
System.Web.UI.Control.EnsureChildControls() +97
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

--
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

dennist685

hank you for the detailed response Dennis,

After tested through your further description, I've reproduce the problem

you encountered. Yes, when using the SqlDataSource's configuration wizard

to generate select statement by "specify columns from a table or view", the

generated SQL statement always cause Error.

And based on my further research, I think this should be a problem of the

SqlDataSource control's configuration wizard. Because for SQL 2005 tables,

they could be grouped by a new unit called "Schema", so when we reference a

table in our SQLStatement , we can not simply use their tablename, but need

to add the schema name together . for example, in our problem, the

Adventureworks database's Employee table are in a schema named

"HumanResources", then, our sql statement should be

"SELECT HumanResources.Employee.* FROM HumanResources.Employee"

or "SELECT * FROM HumanResources.Employee"

rather than "Select * from Employee", "HumanResources" is the schema name.

And seems the SqlDataSource control's

"specify columns from a table or view" wizard dosn't support this

correctly. I'll report this issue to our product team. Curently, I suggest

you try the following means to workaround this problem when dealing with

the SQL2005 database tables:

1. We can manually adjust the sql statement in web page (aspx )'s template

by adding the Schema name before the table name.

2. Instead of using the "specify columns from a table or view" in the

wizard, we can choose another option

"specify a custom SQL statement or stored procedure"

in this wizard, we can use another SQL statement generating tool (Query

Builder ) to auto generate the sql statement (which will correctly add the

Schema name for SQL 2005 data tables based on my tests). Or we can manually

enter sqlstatement also.

Anyway, since this is a problem of our product , I'm sorry for the

inconvenience it brings you. Thanks for your understanding.

Steven Cheng

Microsoft Online Support

Get Secure! www.microsoft.com/security

(This posting is provided "AS IS", with no warranties, and confers no

rights.)

Steven,

thank you every so much for the time you spent tracing down the bug. Frankly, I'm very disappointed in Visual Studio for this and other problems, especially in asp.net 2.0.

Also thank you for solving the problems.

There are also problems I found with other asp.net walkthroughs, but those I found easy ways around.

I find it interesting that the people in Microsoft asp.net forums and Microsoft technical forums couldn't answer the question, and you people, still concentrating of vs 2003, did.

dennist685

|||Thank you Dennis for posting the answer you got; the same problem has been driving me crazy for couple of days. At some point I thought it might be the naming issue but when I got the same error in testing it from the query builder (which I assumed is doing its job right) I did not pursue that direction further.
I don't understand why the common logic was not used - i.e., as long as the name is unque you don't have to qualify it.

Arie|||There's still a problem. The query builder doesn't support WHERE and other clauses. So I guess we have to use the table name and type in the schema qualifier.

It's really disappointing. I don't see how the visual studio team overlooked something so glaring. I imagine in the advanced areas there are bugs galore.

I'd bet SP1 is coming soon.

dennist685

Error in DataList test query - sqlserver

Error in DataList test query - sqlserver
I found some vs 2005 asp.net walkthroughs.
The first I tried required Northwind.
But sqlserver 2005 came with AdventureWorks, so I used that instead.

I followed the walkthrough instructions, dragging a DataList to Design, and clicking the smart tag to configure my datasource.

sqldatasource=SqlDataSource1
connection string - Data Source=YOUR-02910F1DF1;Initial Catalog=AdventureWorks;Integrated Security=True
I tried the test query.
There was an error executing the query....
Invalid name '[Employee}
I tried the query builder, and then the second line is my own sql statement. Both produced the error above.

SELECT [EmployeeID], [Title] FROM [Employee]
SELECT [EmployeeID], [Title] FROM Employee

I tried this with several different tables, all producing the same error.

When I do with with an access database with an access datasource, it works fine. But since I really want to put something on the web, I need sqlserver 2005.

I confirmed that sqlserver.exe was running.

Can somebody help me?

dennist685

I'd really appreciate an answer to this question. I can't go very far in web applications if I can't attach a datasource to a DataList.

dennist685|||

I got an answer from of all places, the 2003 newsgroups.

Hi Dennist685,

Welcome to ASPNET newsgroup.
From your description, when you try creating a data-driven page in ASP.NET
2.0/VS.NET 2005, you always get error on test query, yes?

Based on my experience, the VS.NET 2005 will use configuration wizard to
help create Sql query statements for our SqlDataSource control. Are you
following the wizard to configure the datasoruce? We can first confirm that
the SqlDataSource is configured correctly. We can open the aspx after
configured the sqldatasource control and check the sql query statement
embeded (test in SQLServer's query console/analyzer to see whether it
works....).

After the SqlDataSource is configured OK, we can then attach it to the
DataList control so as to displaying data.

BTW, for Northwind and Pubs databases, we can manually copy their mdf file
from the original SQLServer 2000 and manually attach them into SQLServer
2005. Also, VS.NET 2005/ .net 2.0 support sqlserver 7.0 2005 (also
include a SQLExpress edition which replace the original MSDE). So please
feel free to use either of them for your data store.

If there're anything unclear or need any further help, please feel free to
post here.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Thank you Steven,

I never got as far as configuring the datasource correctly.

There was an error executing the (test) query. Please check the syntax of the command and if present, the types and values of the parameters to be sure they are correct.

Invalid name 'Employee'

There is no stack trace. I was in the configuration stage where the wizard asks you to perform a test query.

I went ahead and built default.aspx successfully,

But when I tried to view it in a browser, I got the stack trace you wanted.

Server Error in '/FormatDataList' Application.
--

Invalid object name 'Employee'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'Employee'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Invalid object name 'Employee'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
System.Web.UI.WebControls.BaseDataList.GetData() +53
System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +57
System.Web.UI.WebControls.BaseDataList.DataBind() +62
System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55
System.Web.UI.WebControls.BaseDataList.CreateChildControls() +62
System.Web.UI.Control.EnsureChildControls() +97
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

--
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

dennist685

hank you for the detailed response Dennis,

After tested through your further description, I've reproduce the problem

you encountered. Yes, when using the SqlDataSource's configuration wizard

to generate select statement by "specify columns from a table or view", the

generated SQL statement always cause Error.

And based on my further research, I think this should be a problem of the

SqlDataSource control's configuration wizard. Because for SQL 2005 tables,

they could be grouped by a new unit called "Schema", so when we reference a

table in our SQLStatement , we can not simply use their tablename, but need

to add the schema name together . for example, in our problem, the

Adventureworks database's Employee table are in a schema named

"HumanResources", then, our sql statement should be

"SELECT HumanResources.Employee.* FROM HumanResources.Employee"

or "SELECT * FROM HumanResources.Employee"

rather than "Select * from Employee", "HumanResources" is the schema name.

And seems the SqlDataSource control's

"specify columns from a table or view" wizard dosn't support this

correctly. I'll report this issue to our product team. Curently, I suggest

you try the following means to workaround this problem when dealing with

the SQL2005 database tables:

1. We can manually adjust the sql statement in web page (aspx )'s template

by adding the Schema name before the table name.

2. Instead of using the "specify columns from a table or view" in the

wizard, we can choose another option

"specify a custom SQL statement or stored procedure"

in this wizard, we can use another SQL statement generating tool (Query

Builder ) to auto generate the sql statement (which will correctly add the

Schema name for SQL 2005 data tables based on my tests). Or we can manually

enter sqlstatement also.

Anyway, since this is a problem of our product , I'm sorry for the

inconvenience it brings you. Thanks for your understanding.

Steven Cheng

Microsoft Online Support

Get Secure! www.microsoft.com/security

(This posting is provided "AS IS", with no warranties, and confers no

rights.)

Steven,

thank you every so much for the time you spent tracing down the bug. Frankly, I'm very disappointed in Visual Studio for this and other problems, especially in asp.net 2.0.

Also thank you for solving the problems.

There are also problems I found with other asp.net walkthroughs, but those I found easy ways around.

I find it interesting that the people in Microsoft asp.net forums and Microsoft technical forums couldn't answer the question, and you people, still concentrating of vs 2003, did.

dennist685

|||Thank you Dennis for posting the answer you got; the same problem has been driving me crazy for couple of days. At some point I thought it might be the naming issue but when I got the same error in testing it from the query builder (which I assumed is doing its job right) I did not pursue that direction further.
I don't understand why the common logic was not used - i.e., as long as the name is unque you don't have to qualify it.

Arie|||There's still a problem. The query builder doesn't support WHERE and other clauses. So I guess we have to use the table name and type in the schema qualifier.

It's really disappointing. I don't see how the visual studio team overlooked something so glaring. I imagine in the advanced areas there are bugs galore.

I'd bet SP1 is coming soon.

dennist685

error handling vbscript via sqlserver

Hi,

I wrote a small vbscript that sends information from a local computer to SQL server.
The method I use for connecting to the SQL server is via ODBC DSN.
This scripts runs on a DMZ located machine, while the SQL server is inside our LAN.

All works fine, but when the network connection between DMZ machine and SQL server lost
for a min. I receive an error.

I monitored the error and tried to error handle it.

after network is back the script is still on, but the error still there.

this is the part where I get the error:

-----------------------
Public function reso(byval res)

'error 3705 occurs here

objConn.Open strConn

Set objrs = objconn.Execute("exec cmp_creation "& Computer &"," & res)


objConn.Close

end function
-----------------------

Help would be much appreciatedWhy not use a DSN-less connection?

Also, if the server is in your DMZ and the SQL Server is behind a firewall (assumed), I would be sure to use the IP address instead of the network name.

Regards,

hmscott|||I'm not sure about the DSN-Less connection.... what would it change regarding my problem? also, what port do I have to open for a DSN-Less connection?|||I don't think that there's anything really wrong with a DSN connection and I'm not positive it would rectify your particular issue. However, DSN connections leave "footprints" on the server in your DMZ (in the registry) which are potentially vulnerable.

DSN and DSN-less connections use the same ports to connect to their respective data sources. Thus, a DSN connection to SQL Server would use port 1433 as would a DSN-less connection.

In general (from what I understand), DSN-less connections are "lighter" on the server, incurring less memory overhead. They are not, however, necessarily faster.

In your script, do you check for the state of the connection? Is it just opened once and left open? Or is it opened and closed as necessary?

Regards,

hmscott

Originally posted by eransp
I'm not sure about the DSN-Less connection.... what would it change regarding my problem? also, what port do I have to open for a DSN-Less connection?

Error handling in nested stored procedures

I have a problem with my error handling in nested stored procedures (SQL
Server 2000).
Below is an example of stored procedures that are causing this problem. If
you install the scripts and run sp1, you'll get 2 errors:
(1) Divide by zero error encountered. -- This is expected due to : select 1/
0
(2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
This 2nd error is the problem and I'm not sure what is it.
If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')",
where Col1 is an int, I get : Syntax error converting the varchar value
'hello' to a column of data type int. BUT I don't get that 2nd error about
the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
error, my error handling works perfectly.
Does anyone know what is causing this problem, and how I should change my
error handling to avoid it.
Thanks,
Craig
CREATE proc sp2
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
select 1/0
select @.err=@.@.error if (@.err<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SP
GO
CREATE proc sp1
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
exec @.retvalue = sp2
select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount<>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SPIn SQL Server, you get this error if you, for instance, begin a transaction
in one procedure and end
it in another procedure. In short, if you exist a procedure with a different
@.@.TRANCOUNT compared to
when you entered the procedure, you get this error. The reason why it seems
to work when you assign
'hello' to an int is that SQL Server will stop execution in such case. In sh
ort, have the
transaction handling in the outermost procedure. Or, you can in the inner pr
ocedure check
@.@.TRANCOUNT when you enter and SAVE TRAN if it was > 0 and in such case do t
he rollback to the
savepoint, but you still have to communicate to the caller that you didn't p
erform the work so the
caller can ROLLBACK its work. Check out the error handling articles at www.sommark
og.se for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:8F9E1557-B00E-48BB-AA9F-1C7436C1B547@.microsoft.com...
>I have a problem with my error handling in nested stored procedures (SQL
> Server 2000).
> Below is an example of stored procedures that are causing this problem. If
> you install the scripts and run sp1, you'll get 2 errors:
> (1) Divide by zero error encountered. -- This is expected due to : select
1/0
> (2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> This 2nd error is the problem and I'm not sure what is it.
> If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')"
,
> where Col1 is an int, I get : Syntax error converting the varchar value
> 'hello' to a column of data type int. BUT I don't get that 2nd error about
> the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
> error, my error handling works perfectly.
> Does anyone know what is causing this problem, and how I should change my
> error handling to avoid it.
> Thanks,
> Craig
>
> CREATE proc sp2
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> select 1/0
> select @.err=@.@.error if (@.err<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
> GO
> CREATE proc sp1
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> exec @.retvalue = sp2
> select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount<>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
>