Help! What Is The Risk For Data On Insufficient Hardware?

Jul 20, 2005

I am sertainly no SQL expert so I am looking for a bit of help
(actually quite desperate).

The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2:
1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduled
to go live tomorrow with a MS SQL Server web app. Will potentially
insufficient hardware resources endanger the data of other apps
running on the SQL Server or will the system simply be slow for
everyone?

Basically, I need to know whether SQl Server bogs down gracefully
(under 2x-3x an acceptable load) or will we be corrupting the database
with a crash.

Thanks a million,
Bert

View 3 Replies


ADVERTISEMENT

Insufficient Log Data

Apr 14, 2006

The mirror database, "UOP_PIMB", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)



I've taken a backup while the database is online and applied it to my "other" server. What do I need to do to have both in sync to get mirroring to work. My way around this is to restore the backup to my primary.


thanks.

View 17 Replies View Related

IUSER Security Risk??

Jun 29, 2006

We have an old ASP application that authenticates to a SQL Server 2000 database using the IUSER account.

We are exploring the possibility of moving it to a DMZ so users can access from home. The IUSER account is being used for all database activity for this application. Is there any reason to be concerned about using IUSER as opposed to sql authentication for this tool, or any other security risks I should be wary of. Mind you there may be some sensitive information contained in the database. Thanks in advance.

View 1 Replies View Related

Select With Nolock Has Calculation Risk??

Apr 8, 2008

Hi all,

I just read this article. The kind of select is called dirty read.
So select with nolock might have inaccurate result...? PLEASE COMMENT ON THIS. I am using it to count some huge tables, and has problem on the result..

http://www.4guysfromrolla.com/webtech/101100-1.2.shtml

NOLOCK
Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.

For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique.

View 9 Replies View Related

A Rewrite Of The Sp_help_revlogin Procedure (use At Own Risk)

Mar 21, 2006

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @logintype char(1)

DECLARE @logindisabled int

DECLARE @binpwd varbinary (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@logintype = 'G' OR @logintype = 'U')

BEGIN -- NT authenticated account/group

IF @logindisabled = 1

BEGIN -- NT login is denied access

SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- NT login has access

SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

PRINT @tmpstr

END

END

ELSE IF (@logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @SID_varbinary

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

SET @tmpstr = 'SET @pwd = CONVERT (nvarchar(128), ' + @txtpwd + ')'

PRINT @tmpstr

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = @pwd, @sid = ' + @SID_string + ', @encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = NULL, @sid = ' + @SID_string

END

PRINT @tmpstr

END

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

View 4 Replies View Related

SQL 2012 :: Security Risk Of Guest User

Sep 24, 2015

I've read a bunch of articles saying you should always remove the guest user from the user databases and model. It seems to me that if a user only has public access then the user can't do anything on the database. If the guest user only has public access to a user database how is it a security threat? I must be missing something.

View 0 Replies View Related

DB Engine :: Risk Of Enabling Change Tracking

Oct 6, 2015

We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an

ALTER
DATABASE [db_name] SET
CHANGE_TRACKING =
ON (CHANGE_RETENTION
= 2 DAYS,
AUTO_CLEANUP =
ON)

We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?

View 5 Replies View Related

SSIS Execution Error: The Product Level Is Insufficient For Component Data Reader Source

Jun 5, 2007

Hi all, while executing my ssis package Im getting this error

" the product level is insufficient for component "data reader source" "



but through the bussiness intelligence while debugging it was executing successfully,

but only after deploying the package on to the IS server, while executing getting this error

I checked with the server version also, its was with service pack 2



can I get any help



thanks in advance



View 1 Replies View Related

Insufficient Memory

May 25, 2001

Hi,
I get the following error Error: 17803, Severity: 17, State: 17
Insufficient memory available.Source ODS. When I have lot of scheduled jobs are running during the night. Does anyone know why this happens and how it can be fixed. Let me know.

Thanks

View 1 Replies View Related

Insufficient Memory

Apr 14, 2000

About once a week I'm receiving this message in the Sqlserver Log from ODS

Error 17803,Severity: 17, State: 14
Insufficient Memory Available

The machine has 1 gig of RAM and is dedicated to sqlserver7 with sp1. Any ideas of what might be causing this problem? Any help is greatly appreciated.

Chris

View 5 Replies View Related

Insufficient Memory

Apr 26, 2001

I have a dedicated SQL 2000 on Windows 2K with over 7GB memory, SQL memory configuration is dynamic. This is a new server and doesn't have much processes yet. This morning, SQL logs recorded the error 'insufficient memory available, error 17803, severity 20, state 17'. Does anyone have any clue what could be the cause?
Thanks in advance.

View 1 Replies View Related

Insufficient Memory

Nov 8, 2006

Hi All,
Greetings,

Sql Server 7, SP 3
OS: Win NT

Every day i am facing problem with Memory.
I get the below in SQL server Logs

"Insufficient memory available..
Error: 17803, Severity: 17, State: 17"


Please suggest How to solve it.


Thanks in Advance
Adil

View 2 Replies View Related

The Buffer Is Insufficient

Aug 22, 2007

Hi all,

I have a strange problem that I need to solve as soon as possible.
I have created two CLR UDTs called point and point_list. Each record of a point_list consists of a list of points. I created a CLR stored procedure which reads some raw data and updates the point_list records.
When I execute the stored procedure the following error appears :

System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.
System.Data.SqlTypes.SqlTypeException:
at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BinaryWriter.Write(Char ch) etc ...


Does anybody know what should I do ?
Thanks!

View 4 Replies View Related

Insufficient Privileges

May 25, 2008

After installing Express, I tried running the QuickStart utility and received an error that I have insufficient privileges to create. I am the administrative user on my laptop and don't understand why I am unable to run the utility.

I did have an instance previously and had no problems with it until it was corrupted somehow.

I am running on Vista.

Any help will be greatly appreciated.

View 12 Replies View Related

Insufficient Memory To Continue

Nov 27, 2001

I am running a .sql file containing a large number of delete and insert statements, using isql from the command line. After 2 minutes I get a message "Insufficient memory to continue", same statements if I cut and paste in SQL server query analyzer I do not get this message. On looking at the
task manager, it shows a lot of available memory.

Any clues

Thanks in advance

View 1 Replies View Related

Insufficient Key Column...error. Why??

Oct 17, 2001

Hiya!
I have a static, optimistic ADO recordset in VB6 selecting rows from a view definition which selects fields (includes all primary keys!) from two tables using a right join on the table whose fields I absolutely need. My update affects only one table (the right one), yet I am getting an error "Insufficient key column info for updating or refreshing", NativeError is 1007, which does not match any SQL error in BOL, and source gives me Microsoft Cursor Engine, although my cursor type is fine for updating. Any clues?

Thanks,
Sarah

View 1 Replies View Related

Insufficient Disk Space

Feb 19, 2001

I'm trying to save a dts package and it keeps coming back with insufficient disk space. I noiticed that db MSDB was full so I manually increased the size. It was set to manually grow at 1 mb increments. But for some reason it didn't look like it was doing that so I manually increased it. Right now this is about 355 MB free so that should be plenty to save a package. But its still coming back with the same error insufficient disk space to complete operation. Any ideas on why or why it didn't grow on its own? Please help I can't seem to save any packages.

View 1 Replies View Related

SQL Insufficient Memory Errors

Nov 3, 1998

Here's the deal.....

I was converting a lot of MS Access records to SQL Server (almost 500,000)
and about midway through I got the old MSACCESS.EXE Not Responding. A few
more times through this and SQL Server informed me that I was running out
of locks. I upped the locks from 5000 to 20000, and retried it. I am now
getting errors like "There is insufficient system memory to run this
query". I receive this when I attempt to look at tables, devices, etc. In
SQL Enterprise Manager. I am also not able to run the sp_configure command
or choose configure by right-clicking on the server in Ent. Mgr. I am
running SQL Server 6.5, SP4, on a dual P200 with 256MB RAM. Any help would be
incredibly great.

Rob

View 2 Replies View Related

Insufficient Memory Error

Oct 25, 2005

My company has a database that is throwing a weird error. We've tried reinstalling both the OS and the SQL instance, and the error still persists. We think this error might have to do with some .NET code we've written to run on the box, but I cannot find anything out on the internet about it. Here is the Enterprise Manager Error Log:


Insufficient memory available..
Error: 17803, Severity: 20, State: 4
Query Memory Manager: Grants=0 Waiting=0 Maximum=97638 Available=97638
Global Memory Objects: Resource=912 Locks=42
SQLCache=67 Replication=2
LockBytes=2 ServerGlobal=20
Xact=12
Dynamic Memory Manager: Stolen=2138 OS Reserved=1048
OS Committed=1026
OS In Use=1022
Query Plan=1777 Optimizer=0
General=1066
Utilities=12 Connection=262
Procedure Cache: TotalProcs=488 TotalPages=1787 InUsePages=542
Buffer Counts: Commited=5168 Target=131072 Hashed=1917
InternalReservation=191 ExternalReservation=0 Min Free=128 Visible= 131072
Buffer Distribution: Stolen=351 Free=1113 Procedures=1787
Inram=0 Dirty=599 Kept=0
I/O=0, Latched=23, Other=1295
WARNING: Failed to reserve contiguous memory of Size= 65536.


I can find information if I do a Google search on "Error: 17803, Severity: 20" But as soon as I add "State: 4" to the query I get no results. Also, the articles that I have seen that give the same error messages (but different states) tend to deal with servers that have more than 4GB of memory. This server has ONLY 4GB of memory and in order to try and resolve this issue, we have limited the server to 1GB of physical memory to no avail.

Any help would be appreciated. Thanks!

View 3 Replies View Related

Insufficient Memory Error

Feb 20, 2004

When I execute the very long query(in the attached), I got an insufficient memory error, Please help me check. Thanks in advance.

View 1 Replies View Related

Insufficient Buffer Zone

Jun 12, 2008

hello again,

This is another pending issue.

It is another package accessing the same database of the same system ("Sage" for commercial and accounting operations).

When run, it gives an error message, as following (also translated from french):
Simba ODBC Driver[CBase]: Very Small/Insufficient Buffer Zone. Data is truncated.

it seems there is a bug in the database, as when we run the same package on another database, it runs successfully.

It may be possible that the database needs a maintenance. Is it possible to advise how to do it, if it is so?
May you help in helping resolve this issue?
thank you in advance.

Leïla
P.S: How is it possible to attach a file?

View 1 Replies View Related

Insufficient System Memory

Feb 13, 2007

We are hitting a crippling 701 "insufficient System Memory" error intermittently in out production environment. I haven’t gotten anywhere with PSS in two weeks. The error has occurred 4 times over the past two weeks, crippling our SQL server and application each time. When the error occurs it lasts for 5 to 20 minutes, causing the app to time out, refusing new connections, and a massive slow-down of anything that is running. SQL has recovered on its own two of these times. It recovered following a Kill of hundreds of threads reporting “SEMAPHORE WAIT�. The most recent occurrence nailed all 16 processors at 100%. We were forced to issue shutdown with nowait. I have been monitoring Perfmon very closely; there are no symptoms that precede the error. Each occurrence captures a different query. Any of the queries, when run from Management Studio, complete in under a second. DBCC MEMORY STATUS reports all memory as being in an unstressed state. The first time the error occurred there were 10 GB still available on the server.

Has anyone else experienced this problem or anything similar? We don’t use linked servers or table valued functions (there are known memory bugs related to each of these items)

The following server and configuration has been running in production for 6 weeks with no issues:

.SQL 2005 EE SP1 Post SP1 Hotfix kb918222
.Win 2003 SP1 (dedicated box)
.Quad Dual Core 3GHz
.32 GB memory
.AWE enabled
.No memory related flags in boot.ini
."Lock Pages in memory" set for SQL Startup account
.1 Instance (default)
.1,994 OLTP databases avg less than 100MB each
.1,200 active user threads on average (from connection pool of avg 4,000 concurent users)

Any comments would be appraciated

View 7 Replies View Related

SQL Error 701 Insufficient Memory

Jan 18, 2008

We have an application running on a SQL cluster (Win 2003) and SQL 2005 SP2 within it's own instance - 12 total databases and about 100G of data total. The node this instance is on has 64G of RAM with 16 allocated to this instance (only 8G allocated to other instances currently).

Now to the problem there is one process that when running we get the error below and we cannot figure how to correct this - the process runs 8 times a day and has been running great and then all of a sudden stopped running with the memory error. I am in search of any tips to diagnose or correct this issue

[298] SQLServer Error: 701, There is insufficient system memory to run this query. [SQLSTATE 42000]

Thanks in Advance

View 2 Replies View Related

Insufficient Memory To Run This Query

Jul 23, 2005

We seem to have developed a memory leak in our sql server applicationand are getting the above error on occasion. Also, over several hoursof hard usage the memory consumed by the sql server ramps up and isnever released. The only thing we have found to remedy the problem isto stop/start sql server.My question to the group is, how can I debug this problem? Are theresystem stored procedures that would be useful in indentifying any temptables, cursors, etc, not getting cleaned up?Thanks,John

View 2 Replies View Related

Key Column Information Is Insufficient ....II

Jul 20, 2005

This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 5 Replies View Related

Key Column Information Is Insufficient ....

Jul 20, 2005

This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Insufficient System Memory

Nov 28, 2006

Just wondering if anyone else has seen any memory issues with cross instance communication. Just this last week we moved from single instance to cross instance communication. I started up a process that will move 3,000,000 messages through service broker, but after a couple of hours the server tanks out and we get insufficient system memory errors in the sql error log. If no one else has seen or experienced this then I'll probably get a ticket opened to see what it may be. It's just that the change in our service broker network was the only change I was aware of on this instance.

Regards.

View 4 Replies View Related

Product Level Is Insufficient

Feb 26, 2007

I am receiving the following error when I try to execute an SSIS package that is saved to a structured file. I am using DTExec to execute the package.

Description: The task "Initialize Variables" cannot run on this edition of Integration Services. It requires a higher level edition.

After some research, I was able to find a supporting article in SQL Server 2005 BOL at http://msdn2.microsoft.com/en-us/library/aa337371.aspx

The issue was resolved by installing the Enterprise Edition of SQL Server Integration Services (Windows Service) on the local server (server where the package is saved and executed from). However, this goes against my original anticipation that the SSIS Windows Service would only need to be installed on a machine where I needed to store packages and/or monitor package execution. Not if I wanted to execute packages.

Unfortunately, my package is using advanced options (as identified in the BOL article previously referenced).

Am I missing something??

Sincerely,

Sean Fitzgerald

View 3 Replies View Related

Product Level Insufficient

Mar 13, 2006

I am trying to run dtexec on some DTSC packages I created using SSIS. I keep getting the error "The product level is insufficient for the component". Anybody know what this means?

Thanks,

Michael D. Fox

View 4 Replies View Related

17803 Insufficient Memory Available Why?

Aug 31, 2007

This morning the server became unresponsive and we had to stop and restart the services. The log showed the dreaded 17803 "Insufficient memory available" error. Server has 4gig of O/S memory and 4 processors. Searching on this suggests this is really an internal error and not something we can do much about. Is that true? What should be my next step?

Thanks!

2007-08-20 21:04:47.89 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
.
.
.
2007-08-31 10:48:09.51 spid2 LazyWriter: warning, no free buffers found.
2007-08-31 10:48:09.51 spid2 Buffer Distribution: Stolen=243524 Free=0 Procedures=6
Inram=0 Dirty=60 Kept=0
I/O=0, Latched=98, Other=0
2007-08-31 10:48:09.51 spid2 Buffer Counts: Commited=243688 Target=243688 Hashed=158
InternalReservation=254 ExternalReservation=42 Min Free=512 Visible= 243688
2007-08-31 10:48:09.51 spid2 Procedure Cache: TotalProcs=4 TotalPages=6 InUsePages=6
2007-08-31 10:48:09.51 spid2 Dynamic Memory Manager: Stolen=243474 OS Reserved=1112
OS Committed=1090
OS In Use=1042
Query Plan=524 Optimizer=240444
General=2750
Utilities=143 Connection=489
2007-08-31 10:48:09.51 spid2 Global Memory Objects: Resource=1217 Locks=94
SQLCache=44 Replication=2
LockBytes=2 ServerGlobal=25
Xact=48
2007-08-31 10:48:09.51 spid2 Query Memory Manager: Grants=1 Waiting=0 Maximum=9793 Available=9695
2007-08-31 10:48:27.41 spid64 Error: 17803, Severity: 20, State: 8
2007-08-31 10:48:27.41 spid64 Insufficient memory available..
2007-08-31 10:48:39.51 spid2 LazyWriter: warning, no free buffers found.
2007-08-31 10:48:39.52 spid2 Buffer Distribution: Stolen=243522 Free=0 Procedures=6
Inram=0 Dirty=60 Kept=0
I/O=0, Latched=100, Other=0

View 3 Replies View Related

There Is Insufficient System Memory To Run This Query

Jan 28, 2004

Help,

Getting this error msg when running a bulk insert.
Bulk insert was working fine before and all of a sudden getting this.

Any Ideas?

View 8 Replies View Related

Insufficient Memory To Continue The Execution

Nov 24, 2007

I have scheduled a SSIS package to run repeatedly by creating a scheduled SQL job that runs every minute . After every hour the packgae fails with the following error

Description: System.OutOfMemoryException: Insufficient memory to continue the execution of the program. at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSCustomProperty90.get_Value() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent() End Erro

The strange thing is that if i right click on SQL JOb and run it...it works fine.


Did anyone encounter similar problem or know the solution of the above problem ?

Thanks....

View 1 Replies View Related

SPROC Won't Execute - Insufficient Permissions

Jun 30, 2006

I'm trying to use the SPROC below (courtesy of Erland!) to capture theerror message but it fails owing to insufficient permissions (I can'treproduce it just now, but I think it's because it can't get access tothe DBCC OUTPUTBUFFER).How do I give the SPROC permission to execute?Many thanksEdwardCREATE PROCEDURE stpShowErrorMessage @errmsg nvarchar(500) OUTPUT ASDECLARE @dbccrow nchar(77),@msglen int,@lenstr nchar(2),@sql nvarchar(2000),@s tinyint-- Catch the output buffer.CREATE TABLE #DBCCOUT (col1 nchar(77) NOT NULL)INSERT INTO #DBCCOUTEXEC ('DBCC OUTPUTBUFFER(@@spid)')-- Set up a cursor over the table. We skip the first-- row, because there is nothing of interest.DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FORSELECT col1FROM #DBCCOUTWHERE left(col1, 8) <> replicate('0', 8)ORDER BY col1-- Init variable, and open cursor.SELECT @errmsg = ''OPEN error_cursorFETCH NEXT FROM error_cursor INTO @dbccrow-- On this first row we find the length.SELECT @lenstr = substring(@dbccrow, 15, 2)-- Convert hexstring to intSELECT @sql = 'SELECT @int = convert(int, 0x00' + @lenstr + ')'EXEC sp_executesql @sql, N'@int int OUTPUT', @msglen OUTPUT-- @s is where the text part of the buffer starts.SELECT @s = 62-- Now assemble rest of string.WHILE @@FETCH_STATUS = 0 AND datalength(@errmsg) - 1 < 2 * @msglenBEGINSELECT @errmsg = @errmsg + substring(@dbccrow, @s + 1, 1) +substring(@dbccrow, @s + 3, 1) +substring(@dbccrow, @s + 5, 1) +substring(@dbccrow, @s + 7, 1) +substring(@dbccrow, @s + 9, 1) +substring(@dbccrow, @s + 11, 1) +substring(@dbccrow, @s + 13, 1) +substring(@dbccrow, @s + 15, 1)FETCH NEXT FROM error_cursor INTO @dbccrowENDCLOSE error_cursorDEALLOCATE error_cursor-- Now chop first character which is the length, and cut after end.SELECT @errmsg = substring(@errmsg, 2, @msglen)GO

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved