Error processing BACKUP DATABASE statement

Hello all!
I do database backup using SQL-script (BACKUP DATABASE to ...). This scripts
runs by osql utility.
How i can know - was backup successful or not.
I'm trying to use @.@.error variable, but it doesn't return error if device is
full or doesn't exists.
How can i do such error processing is SQL-script ?
Thanks, Slava
If works, you get errors - example
-- I don't have disk n:
backup database northwind to disk='n:\notexisting.bak'
select @.@.error As 'Error No.'
-- Output in QA
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'n:\notexisting.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Error No.
3013
Check your script. Also, if you are using a scheduled job, you can define
workflow - what to do is a task fails, for example send a mail to a
operator.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Slava" <Slava@.discussions.microsoft.com> wrote in message
news:22375259-06DC-4C80-A66C-4136A1A897FB@.microsoft.com...
> Hello all!
> I do database backup using SQL-script (BACKUP DATABASE to ...). This
scripts
> runs by osql utility.
> How i can know - was backup successful or not.
> I'm trying to use @.@.error variable, but it doesn't return error if device
is
> full or doesn't exists.
> How can i do such error processing is SQL-script ?
> Thanks, Slava
sql

Error processing BACKUP DATABASE statement

Hello all!
I do database backup using SQL-script (BACKUP DATABASE to ...). This scripts
runs by osql utility.
How i can know - was backup successful or not.
I'm trying to use @.@.error variable, but it doesn't return error if device is
full or doesn't exists.
How can i do such error processing is SQL-script ?
Thanks, SlavaIf works, you get errors - example
-- I don't have disk n:
backup database northwind to disk='n:\notexisting.bak'
select @.@.error As 'Error No.'
-- Output in QA
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'n:\notexisting.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Error No.
--
3013
Check your script. Also, if you are using a scheduled job, you can define
workflow - what to do is a task fails, for example send a mail to a
operator.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Slava" <Slava@.discussions.microsoft.com> wrote in message
news:22375259-06DC-4C80-A66C-4136A1A897FB@.microsoft.com...
> Hello all!
> I do database backup using SQL-script (BACKUP DATABASE to ...). This
scripts
> runs by osql utility.
> How i can know - was backup successful or not.
> I'm trying to use @.@.error variable, but it doesn't return error if device
is
> full or doesn't exists.
> How can i do such error processing is SQL-script ?
> Thanks, Slava

Error processing BACKUP DATABASE statement

Hello all!
I do database backup using SQL-script (BACKUP DATABASE to ...). This scripts
runs by osql utility.
How i can know - was backup successful or not.
I'm trying to use @.@.error variable, but it doesn't return error if device is
full or doesn't exists.
How can i do such error processing is SQL-script ?
Thanks, SlavaIf works, you get errors - example
-- I don't have disk n:
backup database northwind to disk='n:\notexisting.bak'
select @.@.error As 'Error No.'
-- Output in QA
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'n:\notexisting.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Error No.
--
3013
Check your script. Also, if you are using a scheduled job, you can define
workflow - what to do is a task fails, for example send a mail to a
operator.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Slava" <Slava@.discussions.microsoft.com> wrote in message
news:22375259-06DC-4C80-A66C-4136A1A897FB@.microsoft.com...
> Hello all!
> I do database backup using SQL-script (BACKUP DATABASE to ...). This
scripts
> runs by osql utility.
> How i can know - was backup successful or not.
> I'm trying to use @.@.error variable, but it doesn't return error if device
is
> full or doesn't exists.
> How can i do such error processing is SQL-script ?
> Thanks, Slava

Error on Stored Procedure

I need to perform a select statement across servers... this statement works in query analyzer but not as a procedure. It tells me I need to enable the ANSI_NULLS and ANSI_WARNINGS options when put into a procedure (which it looks like i did!) I think it has something to do with this GO as the stored procedure isn't recognizing it. I tried even building it as a string and executing. Any ideas as to enable this in a procedure?

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
select * from #tableTake out GO statements after SET and re-run the SP.|||Originally posted by Satya
Take out GO statements after SET and re-run the SP.

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer

I get the same error telling me to enable ansi_nulls and ansi_warnings...|||Aren't the ANSI_NULL and ANSI_WARNING options to has to be set at connection level?

i belive (thought not so sure) that you cannot use it inside a stored procedure, you just have to call it BEFORE you call your stored procedure.|||that is a GOOD CALL!!!!!!!!!!!

You wouldn't know how about to set those at connection level would you? I tried doing it in a job scheduled in EM and it didn't work too well

First transact-sql statement in job:
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

Second transact-sql statement in job
exec procedure1

also tried combining
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO|||mmm...do have already tried to remove the "GO" statement?

to set an option at connection level you just have to call it BEFORE anything else you want to execute with that options activated.

now i cannot try, but i'll make a test as soon as i get a SQL Server console under my hand :-)sql

Error on Select Statement when Using IIF

What's wrong with this query? I get an error at ">" sign.
SELECT MyCol1, MyCol2, MyCol3Balance,
IIf(MyCol3Balance > 1, "Past", "No") as MyBalance
from MyTableHi,
There is no IIF in SQL Server. Instead use CASE statement.
Eg:-
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
Thanks
Hari
SQL Server MVP
"Shariq" <Shariq@.discussions.microsoft.com> wrote in message
news:88174876-91BB-48E7-BD1B-3481D6B32FDE@.microsoft.com...
> What's wrong with this query? I get an error at ">" sign.
> SELECT MyCol1, MyCol2, MyCol3Balance,
> IIf(MyCol3Balance > 1, "Past", "No") as MyBalance
> from MyTable
>

error on building up where clause statement

how can I resolve this error message coming from the line of code used to
build up a whereclause ?
error message
--
syntax error converting the varchar value ' and IDUser >= 1 ' to a column of
data type int
sproc
--
...
IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser = '
+ @.ClientID END
IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >= 1
' END
...SQL Server thinks that you want to mathematical add two numbers together
because there is a INT in the addition. To Keep sure to concat the two
strings together you have to cast the INT value to a character type like
varchar(10), so the whole expression should be:

> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + CAST(@.ClientID AS VARCHAR(50)) END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
> how can I resolve this error message coming from the line of code used to
> build up a whereclause ?
> error message
> --
> syntax error converting the varchar value ' and IDUser >= 1 ' to a column
> of data type int
> sproc
> --
> ...
> IF @.ClientID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser =
> ' + @.ClientID END
> IF @.ClientID = -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDuser >=
> 1 ' END
> ...
>|||thanks for replying, I tried your suggestion but I still get the same error
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
> SQL Server thinks that you want to mathematical add two numbers together
> because there is a INT in the addition. To Keep sure to concat the two
> strings together you have to cast the INT value to a character type like
> varchar(10), so the whole expression should be:
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "TJS" <nospam@.here.com> schrieb im Newsbeitrag
> news:e9a6rJuaFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||Can you send in he whole statements and the exact error to see where the
Parser throws the error ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:OKz0PDvaFHA.2768@.tk2msftngp13.phx.gbl...
> thanks for replying, I tried your suggestion but I still get the same
> error
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:e5hlJguaFHA.3572@.TK2MSFTNGP12.phx.gbl...
>|||Could it be that @.WhereClause is declared as integer?
Ral Moloye|||here is current version, the purpose of the sproc is to return a list if
matching record id's
based on filter parameters provided.
========================================
============================
Alter Procedure "ListID"
(
@.admin nvarchar(10),
@.YearID int,
@.ClientID int,
@.ProjectID int
)
As
DECLARE @.List varchar(100)
DECLARE @.WhereClause nvarchar (1000)
DECLARE @.CastString1 varchar (50)
DECLARE @.CastString2 varchar (50)
SET @.WhereClause=''
SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
-- Define the selection criteria for client depending on whether or not
user is an admin
-- ----
---
IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString1 END
IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
@.WhereClause + @.CastString2 END
IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
@.CastString2 END
-- Define the selection criteria for selections of project and year
-- ----
---
IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and IDProject
= ' + @.ProjectID END
IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
year(issueDate) = ' + @.YearID END
-- retrieve selection
-- ----
---
SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
FROM invoices, Projects
WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
-- return results
-- ----
---
SELECT @.List|||Everywhere where a INt takes place (doesntmatter if you declared it in your
procedure or you get it from the database) and you want to add it to the
string, SQL Server will try to do a methematical addition to the strings
which wont work, so you will have to replace that in your whole code (e.g.
For YearID, projectId ...)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"TJS" <nospam@.here.com> schrieb im Newsbeitrag
news:uVOaKl4aFHA.3840@.tk2msftngp13.phx.gbl...
> here is current version, the purpose of the sproc is to return a list if
> matching record id's
> based on filter parameters provided.
> ========================================
============================
> Alter Procedure "ListID"
> (
> @.admin nvarchar(10),
> @.YearID int,
> @.ClientID int,
> @.ProjectID int
> )
> As
> DECLARE @.List varchar(100)
> DECLARE @.WhereClause nvarchar (1000)
> DECLARE @.CastString1 varchar (50)
> DECLARE @.CastString2 varchar (50)
> SET @.WhereClause=''
> SET @.CastString1 = ' and IDuser >= ' + CAST(@.ClientID AS VARCHAR(50))
> SET @.CastString2 = ' and IDuser = ' + CAST(@.ClientID AS VARCHAR(50))
> -- Define the selection criteria for client depending on whether or not
> user is an admin
> -- ----
---
> IF @.admin = 'true' and @.ClientID = -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString1 END
> IF @.admin = 'true' and @.ClientID <> -1 BEGIN SET @.WhereClause =
> @.WhereClause + @.CastString2 END
> IF @.admin = 'false' BEGIN SET @.WhereClause = @.WhereClause +
> @.CastString2 END
> -- Define the selection criteria for selections of project and year
> -- ----
---
> IF @.ProjectID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> IDProject = ' + @.ProjectID END
> IF @.YearID <> -1 BEGIN SET @.WhereClause = @.WhereClause + ' and
> year(issueDate) = ' + @.YearID END
> -- retrieve selection
> -- ----
---
> SELECT @.List = COALESCE(@.List + ', ', '') + CAST(InvoiceID AS varchar(5))
> FROM invoices, Projects
> WHERE Projects.ProjectID = Invoices.IDProject + @.WhereClause
> -- return results
> -- ----
---
> SELECT @.List
>

error Multi-part identifier

After OK parse, I try to execute this statement in a trigger:
if inserted.col1 = 10 --col1 type int
--do instruction
But I have this message:
The multi-part identifier "inserted.col1" could not be bound.
What is this problem'
How can I resolve it?
Thanks!
Good night!Inserted is a "table" that can have many rows in it (1 for each row inserted
or updated in the statement that fired the trigger). As such, you could do
something like this:
SELECT @.variable = col1 FROM Inserted
IF @.Variable = 10 ...
However, this will only get the col1 value from 1 of the rows in the
inserted table.
Show us the rest of the code in your trigger and we can likely help you
write it using a set based solution that will handle multiple rows in the
inserted table.
"bubixx" <bubixx@.discussions.microsoft.com> wrote in message
news:7BBC3219-4A58-4561-8DBA-95CF54E46489@.microsoft.com...
> After OK parse, I try to execute this statement in a trigger:
> if inserted.col1 = 10 --col1 type int
> --do instruction
> But I have this message:
> The multi-part identifier "inserted.col1" could not be bound.
> What is this problem'
> How can I resolve it?
> Thanks!
> Good night!|||You can't do a direct comparison on a column in a table in an IF statement.
It's pretty tough to tell you what the appropriate syntax should be without
knowing more about what you are trying to do. In any case you should always
code triggers to account for multiple rows being affected. What you are
trying to do infers only one row is ever affected which will ultimately get
you in trouble.
Andrew J. Kelly SQL MVP
"bubixx" <bubixx@.discussions.microsoft.com> wrote in message
news:7BBC3219-4A58-4561-8DBA-95CF54E46489@.microsoft.com...
> After OK parse, I try to execute this statement in a trigger:
> if inserted.col1 = 10 --col1 type int
> --do instruction
> But I have this message:
> The multi-part identifier "inserted.col1" could not be bound.
> What is this problem'
> How can I resolve it?
> Thanks!
> Good night!

Error Msg 7391 in distributed transaction

I have a procedure which is trying to insert data into a table on Server A.
This insert is done based ona return value from a select statement from
Server B . A linked server has been set up for Server B on server A. The
error when populating table by inserting data is
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Is there any other way to get thsi work. TIA
Message posted via http://www.webservertalk.comYou want to start with this kb for your troubleshooting.
http://support.microsoft.com/kb/306212
-oj
"ishaan99 via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:e4a371938b9f454d851da07f5c672039@.SQ
webservertalk.com...
>I have a procedure which is trying to insert data into a table on Server A.
> This insert is done based ona return value from a select statement from
> Server B . A linked server has been set up for Server B on server A. The
> error when populating table by inserting data is
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Is there any other way to get thsi work. TIA
> --
> Message posted via http://www.webservertalk.com

Error Msg

String or binary data would be truncated.
The statement has been terminated.

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: String or binary data would be truncated.
The statement has been terminated.

Source Error:

Line 32: Line 33: Dim add As New SqlCommand(change, conn2)Line 34: add.ExecuteNonQuery()Line 35: conn2.Close()Line 36: Response.Redirect("ThankUReg.aspx")

hi there,

I have this error msg when i try to execute my register page..

What do they mean by string or binary data will be truncated? i've set my database value type to varchar(MAX).

still this error appears.. Looking forward for ur reply.

Yini

well it Does in fact mean that you are trying to insert data into a column that is not big enough for the data. Without knowing your db structure or seeing the SQL your actually using to insert i can't offer much more help. debug the process and maybe copy and paste your values into excel or something so you can run a LEN Function on them and see which one is too big.

hth,
mcm

|||

yinibabe:

i've set my database value type to varchar(MAX).

Have you set the data type to be varchar (max) for every string type column ? My advice is to again check all the columns for their data types, I think you are missing one column which may not allow the amount of data you are trying to insert for that column.

Hope this will help.

Error message:

One of our production box is running really slow right now.. and I issue the following statement: DBCC MEMUSAGE and I got back the following error message:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:204858) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:206393) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:206392) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
The comment doesn't even finish. I thought there were some corruption on our databases, but I schedule a job and ran it through all our databases with DBCC checkdb. None of them seems to have any integrity or data corruption problem.
Any idea where else I should look for any possible problems? I free the buffer and procedure cache using
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
already!
help!
What sql server build are you on? You might consider going through
826433 PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
http://support.microsoft.com/?id=826433 and also considre opening a case
with PSS Support for this.
In the meantime, I'd suggest you check your Application and Sys event logs
for hardware errors. Also check your backups. Do you see the same error
message after restoring them?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error message:

One of our production box is running really slow right now.. and I issue the
following statement: DBCC MEMUSAGE and I got back the following error mess
age:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:204858) with latch type SH. VerifyPageId fa
iled.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:206393) with latch type SH. VerifyPageId fa
iled.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:206392) with latch type SH. VerifyPageId fa
iled.
Server: Msg 8966, Level 16, State 1, Line 1
The comment doesn't even finish. I thought there were some corruption on ou
r databases, but I schedule a job and ran it through all our databases with
DBCC checkdb. None of them seems to have any integrity or data corruption p
roblem.
Any idea where else I should look for any possible problems? I free the buf
fer and procedure cache using
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
already!
help!What sql server build are you on? You might consider going through
826433 PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
http://support.microsoft.com/?id=826433 and also considre opening a case
with PSS Support for this.
In the meantime, I'd suggest you check your Application and Sys event logs
for hardware errors. Also check your backups. Do you see the same error
message after restoring them?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error message:

One of our production box is running really slow right now.. and I issue the following statement: DBCC MEMUSAGE and I got back the following error message:
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (1:204858) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:206393) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:206392) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
The comment doesn't even finish. I thought there were some corruption on our databases, but I schedule a job and ran it through all our databases with DBCC checkdb. None of them seems to have any integrity or data corruption problem.
Any idea where else I should look for any possible problems? I free the buffer and procedure cache using
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
already!
help!What sql server build are you on? You might consider going through
826433 PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
http://support.microsoft.com/?id=826433 and also considre opening a case
with PSS Support for this.
In the meantime, I'd suggest you check your Application and Sys event logs
for hardware errors. Also check your backups. Do you see the same error
message after restoring them?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error Message.....

I am using SQL 2K.
I am calling the below SQL statement as one of the SQL task in one of the
step.
EXEC SP_CYCLE_ERRORLOG
I am getting the attached message.
How to solve it?
Thanks,
Alok
Below is the error message.
"EXCUTE permission denied on object 'sp_cycle_errolog', database
'master',owner 'dbo'"
Alok Choudhary wrote:
> I am using SQL 2K.
> I am calling the below SQL statement as one of the SQL task in one of
> the step.
> EXEC SP_CYCLE_ERRORLOG
> I am getting the attached message.
> How to solve it?
> Thanks,
> Alok
> Below is the error message.
> "EXCUTE permission denied on object 'sp_cycle_errolog', database
> 'master',owner 'dbo'"
Your user id does not have rights to execute that procedure because you
are probably not a system administrator. Admin rights are required.
David Gugick
Imceda Software
www.imceda.com
|||What server roles are you a member of? sp_cycle_errorlog is sysadmin only.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alok Choudhary" <AC_Ind@.hotmail.com> wrote in message
news:eCsUogNCFHA.1396@.TK2MSFTNGP14.phx.gbl...
> I am using SQL 2K.
> I am calling the below SQL statement as one of the SQL task in one of the
> step.
> EXEC SP_CYCLE_ERRORLOG
> I am getting the attached message.
> How to solve it?
> Thanks,
> Alok
> Below is the error message.
> "EXCUTE permission denied on object 'sp_cycle_errolog', database
> 'master',owner 'dbo'"
>
>

Error Message.....

I am using SQL 2K.
I am calling the below SQL statement as one of the SQL task in one of the
step.
EXEC SP_CYCLE_ERRORLOG
I am getting the attached message.
How to solve it?
Thanks,
Alok
Below is the error message.
"EXCUTE permission denied on object 'sp_cycle_errolog', database
'master',owner 'dbo'"Alok Choudhary wrote:
> I am using SQL 2K.
> I am calling the below SQL statement as one of the SQL task in one of
> the step.
> EXEC SP_CYCLE_ERRORLOG
> I am getting the attached message.
> How to solve it?
> Thanks,
> Alok
> Below is the error message.
> "EXCUTE permission denied on object 'sp_cycle_errolog', database
> 'master',owner 'dbo'"
Your user id does not have rights to execute that procedure because you
are probably not a system administrator. Admin rights are required.
David Gugick
Imceda Software
www.imceda.com|||What server roles are you a member of? sp_cycle_errorlog is sysadmin only.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alok Choudhary" <AC_Ind@.hotmail.com> wrote in message
news:eCsUogNCFHA.1396@.TK2MSFTNGP14.phx.gbl...
> I am using SQL 2K.
> I am calling the below SQL statement as one of the SQL task in one of the
> step.
> EXEC SP_CYCLE_ERRORLOG
> I am getting the attached message.
> How to solve it?
> Thanks,
> Alok
> Below is the error message.
> "EXCUTE permission denied on object 'sp_cycle_errolog', database
> 'master',owner 'dbo'"
>
>

Error Message.....

I am using SQL 2K.
I am calling the below SQL statement as one of the SQL task in one of the
step.
EXEC SP_CYCLE_ERRORLOG
I am getting the attached message.
How to solve it?
Thanks,
Alok
Below is the error message.
"EXCUTE permission denied on object 'sp_cycle_errolog', database
'master',owner 'dbo'"Alok Choudhary wrote:
> I am using SQL 2K.
> I am calling the below SQL statement as one of the SQL task in one of
> the step.
> EXEC SP_CYCLE_ERRORLOG
> I am getting the attached message.
> How to solve it?
> Thanks,
> Alok
> Below is the error message.
> "EXCUTE permission denied on object 'sp_cycle_errolog', database
> 'master',owner 'dbo'"
Your user id does not have rights to execute that procedure because you
are probably not a system administrator. Admin rights are required.
--
David Gugick
Imceda Software
www.imceda.com|||What server roles are you a member of? sp_cycle_errorlog is sysadmin only.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alok Choudhary" <AC_Ind@.hotmail.com> wrote in message
news:eCsUogNCFHA.1396@.TK2MSFTNGP14.phx.gbl...
> I am using SQL 2K.
> I am calling the below SQL statement as one of the SQL task in one of the
> step.
> EXEC SP_CYCLE_ERRORLOG
> I am getting the attached message.
> How to solve it?
> Thanks,
> Alok
> Below is the error message.
> "EXCUTE permission denied on object 'sp_cycle_errolog', database
> 'master',owner 'dbo'"
>
>

error message....

hi

I have the followin code but it isnot working giving the following error message

String or binary data would be truncated.
The statement has been terminated.

string sqlStrInsert = "insert into tbl_izinTalep (b_date, e_date, info, cc, comment) values('" + dateTimePicker1.Value.ToString() + "' , '" + dateTimePicker2.Value.ToString() + "','" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "')";

SqlCommand command = newSqlCommand(sqlStrInsert, conn);

command.ExecuteNonQuery();

thanks

if this forum is out of my topic, please write the related forum's name

You really, really, really should not do that becasue this leaves a security hole for SQL injection, imagine you user input (which should be treaten as suspious), enter in the textbox2 the following text:

'',NULL'); DROP DATABASE model; --

THis would cause the model database to be dropped, I am sure you don′t want to do this. :-) Always use parametrized queries for the access, that can′t be comnposed as easy as in the sample code of yours.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

but I dont understand the security hole reason, how I overcome it please explain with a sample

thank you

|||

hi,

Jens already provide the example..

you "load" a dynamic SQL statement into tbl_izinTalep table that must be evenutally executed...

if your loaded statement has been "tampered" with malicious code, like Jens said, you will be very sad

your code is

string sqlStrInsert = "insert into tbl_izinTalep (b_date, e_date, info, cc, comment) values('" + dateTimePicker1.Value.ToString() + "' , '" + dateTimePicker2.Value.ToString() + "','" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "')";

but if someone adds "; DROP DATABASE model" to your statement, it will be executed as well..

have a look at http://www.sommarskog.se/dynamic_sql.html for futher info about dynamic SQL..

regards

error message....

hi

I have the followin code but it isnot working giving the following error message

String or binary data would be truncated.
The statement has been terminated.

string sqlStrInsert = "insert into tbl_izinTalep (b_date, e_date, info, cc, comment) values('" + dateTimePicker1.Value.ToString() + "' , '" + dateTimePicker2.Value.ToString() + "','" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "')";

SqlCommand command = new SqlCommand(sqlStrInsert, conn);

command.ExecuteNonQuery();

thanks

if this forum is out of my topic, please write the related forum's name

You really, really, really should not do that becasue this leaves a security hole for SQL injection, imagine you user input (which should be treaten as suspious), enter in the textbox2 the following text:

'',NULL'); DROP DATABASE model; --

THis would cause the model database to be dropped, I am sure you don′t want to do this. :-) Always use parametrized queries for the access, that can′t be comnposed as easy as in the sample code of yours.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

but I dont understand the security hole reason, how I overcome it please explain with a sample

thank you

|||

hi,

Jens already provide the example..

you "load" a dynamic SQL statement into tbl_izinTalep table that must be evenutally executed...

if your loaded statement has been "tampered" with malicious code, like Jens said, you will be very sad

your code is

string sqlStrInsert = "insert into tbl_izinTalep (b_date, e_date, info, cc, comment) values('" + dateTimePicker1.Value.ToString() + "' , '" + dateTimePicker2.Value.ToString() + "','" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "')";

but if someone adds "; DROP DATABASE model" to your statement, it will be executed as well..

have a look at http://www.sommarskog.se/dynamic_sql.html for futher info about dynamic SQL..

regards

Error message

Hello,
I want to insert data into a temp Table using an Insert Exec statement such
as
insert #Mytable(Col1,...)
exec mysstoreProcedure @.var1
And I have the following message :
Server: Msg 8164, Level 16, State 1, Procedure ps_hasThisSolution, Line 25
An INSERT EXEC statement cannot be nested.
What is the problem ?This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C37912.1B9AAE00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi ! Following you can seee the 2 procedures :
CREATE Procedure ps_hasThisSolution(
@.p_Client varchar(32) =3D NULL,
@.p_Produit varchar(32) =3D NULL,
@.hasSol int output
)
AS
create table #temp_sw_2 (
CODCLI varchar(10),
CODLOG varchar(10),
VERLOG float,
TYPE varchar(1),
CREATION smalldatetime,
DTEACHAT smalldatetime,
DTEEXPIR smalldatetime,
TYPE_ORDINATEUR varchar(30)
)
SET NOCOUNT ON
declare @.tmp varchar(50)
insert #temp_sw_2(CODCLI, =CODLOG,VERLOG,TYPE,CREATION,DTEACHAT,DTEEXPIR,TYPE_ORDINATEUR)
exec ps_getProductBySolution @.p_Client
if exists (SELECT *
FROM produit_version INNER JOIN
#temp_sw_2 ON produit_version.code_version =3D #temp_sw_2.CODLOG
WHERE #temp_sw_2.CODLOG =3D @.p_Produit )
select @.hasSol =3D 1
else
select @.hasSol =3D 0
return
And the 2nd procedure :
CREATE Procedure ps_getProductBySolution(
@.p_Client varchar(10) =3D Null
)
AS
declare @.p_solution varchar(10), @.p_verlog float, @.p_creation =smalldatetime, @.p_dteachat smalldatetime, @.dteexpir =smalldatetime,@.dteexpir_min smalldatetime
declare @.type_ordinateur varchar(30),@.type_produit varchar(10)
exec ps_getSolution @.p_Client, @.p_solution output, @.p_verlog output, =@.p_creation output, @.p_dteachat output, @.dteexpir output, @.dteexpir_min =output,@.type_ordinateur output,@.type_produit output
if @.p_solution is NULL begin
SELECT WCM.CODCLI, produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur
FROM produit_version INNER JOIN
WCM ON produit_version.code_version =3D WCM.CODLOG INNER JOIN produit ON
produit_version.code_produit=3Dproduit.code_produit AND =produit.type_produit=3D'LG'
WHERE (WCM.CODCLI =3D @.p_Client) end
else
begin
SELECT WCM.CODCLI,produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur
FROM produit_version INNER JOIN
WCM ON produit_version.code_version =3D WCM.CODLOG INNER JOIN
produit ON produit.code_produit =3D WCM.CODLOG AND produit.type_produit ==3D'LG'
WHERE (WCM.CODCLI =3D @.p_Client) AND produit_version.code_produit NOT IN
(SELECT produit_plus.code_produit
FROM produit_plus
WHERE produit_version.code_produit =3D produit_plus.code_produit
AND (produit_plus.type =3D 'SL' OR
produit_plus.type =3D 'SP') AND produit_plus.produit_type =3D 'LG' AND produit_plus.Code_plus ==3D @.p_solution)
UNION
SELECT @.p_Client, @.p_solution, @.p_verlog, null ,@.p_creation, =@.p_dteachat, @.dteexpir,@.type_ordinateur
end
return
"jeff" <jeff@.hotmail.com> wrote in message =news:02ec01c37888$83c24c20$a101280a@.phx.gbl...
> Perheps the mysstoreProcedure procedure too uses Insert > into ... exec <proc> to insert data in some table, is it > true?
> > Can you post the entire procedure code?
> > >--Original Message--
> >Hello,
> >I want to insert data into a temp Table using an Insert > Exec statement such
> >as
> >insert #Mytable(Col1,...)
> >exec mysstoreProcedure @.var1
> >
> >And I have the following message :
> >Server: Msg 8164, Level 16, State 1, Procedure > ps_hasThisSolution, Line 25
> >An INSERT EXEC statement cannot be nested.
> >
> >What is the problem ?
> >
> >
> >.
> >
--=_NextPart_000_0012_01C37912.1B9AAE00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi !
Following you can seee the 2 procedures =:
CREATE Procedure ps_hasThisSolution(@.p_Client varchar(32) =3D NULL,@.p_Produit varchar(32) =3D =NULL,@.hasSol int output)AS
create table #temp_sw_2 (CODCLI varchar(10),CODLOG varc=har(10),VERLOG float,TYPE  =;varchar(1),CREATION smalldatetime,DTEACHAT  =;smalldatetime,DTEEXPIR smalldatetime,TYPE_ORDINATEUR&=nbsp;varchar(30))SET NOCOUNT ONdeclare @.tmp varchar(50)
insert #temp_sw_2(CODCLI, CODLOG,VERLOG,TYPE,CREATION,DTEACHAT,DTEEXPIR,TYPE_ORDINATEUR)exec ps_getProductBySolution @.p_Client
if exists (SELECT * FROM produit_version INNER JOIN #temp_sw_2 ON produit_version.code_versio=n =3D #temp_sw_2.CODLOG WHERE =#temp_sw_2.CODLOG =3D @.p_Produit ) select @.hasSol =3D 1 else select @.hasSol =3D 0return
And the 2nd procedure :
CREATE Procedure ps_getProductBySolution(@.p_Client varchar(10) =3D Null)AS declare @.p_solution varchar(10), @.p_verlog =float, @.p_creation smalldatetime, @.p_dteachat smalldatetime, @.dteexpir smalldatetime,@.dteexpir_min smalldatetime declare =@.type_ordinateur varchar(30),@.type_produit varchar(10)
exec ps_getSolution =@.p_Client, @.p_solution output, @.p_verlog output, @.p_creation output, @.p_dteachat =output, @.dteexpir output, @.dteexpir_min output,@.type_ordinateur =output,@.type_produit output
if @.p_solution is NULL begin SELECT WCM.CODCLI, =produit_version.code_produit, WCM.VERLOG, WCM.TYPE, WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur FROM produit_version INNER JOIN WCM ON = produit_version.code_version =3D WCM.CODLOG INNER JOIN produit =ON produit_version.code_produit=3Dproduit.code_produit AND produit.type_produit=3D'LG' WHERE (WCM.CODCLI =3D @.p_Client) end else begin
SELECT WCM.CODCLI,produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur FROM =produit_version INNER JOIN WCM ON produit_version.code_version =3D WCM.CODLOG =INNER JOIN produit ON = produit.code_produit =3D WCM.CODLOG AND =produit.type_produit =3D'LG' WHERE (WCM.CODCLI =3D @.p_Client) AND produit_version.code_produit NOT IN (SELECT produit_plus.code_produit FROM produit_plus WHERE produit_version.code_produit =3D produit_plus.code_produit &n=bsp; AND (produit_plus.type =3D 'SL' OR produit_plus.type =3D 'SP') AND produit_plus.produit_type =3D 'LG' AND produit_plus.Code_plus =3D @.p_solution) UNION SELECT @.p_Client, =@.p_solution, @.p_verlog, null ,@.p_creation, @.p_dteachat, @.dteexpir,@.type_ordinateur end return
"jeff" =wrote in message news:02ec01c37888$83c24c20$a101280a@.phx.gbl...> Perheps the mysstoreProcedure procedure too uses Insert > into ... exec = to insert data in some table, is it > true?> > Can =you post the entire procedure code?> > >--Original Message--> >Hello,> >I want to insert data into a =temp Table using an Insert > Exec statement such> =>as> >insert #Mytable(Col1,...)> >exec mysstoreProcedure =@.var1> >> >And I have the following message :> >Server: =Msg 8164, Level 16, State 1, Procedure > ps_hasThisSolution, Line =25> >An INSERT EXEC statement cannot be nested.> >> =>What is the problem ?> >> >> >.> >

---

--=_NextPart_000_0012_01C37912.1B9AAE00--|||<snip>
There is a third procedure involved - ps_getsolution. You must check the
entire execution chain.