problem when executing stored procedure
I am using SQL SERVER 2005 (DEV ED) as a backend for my websites and my accounting software.
When I try to execute a stored procIn query analyser the following error occurs. Can someone direct me where to look to fix this please?
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '\'.
Thanks aziz
Note that it works ok in sql server 2000!!!
Here is the proc txt
USE [master]
GO
/****** Object:StoredProcedure [dbo].[sp_ADM_Documentation]Script Date: 04/06/2007 17:35:33 ******/
IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ADM_Documentation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ADM_Documentation]
USE master
go
CREATEProc dbo.sp_ADM_Documentation (
@.dbname sysname = null,
@.JobStep sysname = null,
@.RefId numeric(4) = null,
@.Error int = 0 Output
)
With Encryption As
Begin
set nocount on
declare @.SrvName varchar(128),
@.StepIdint,
@.JobName varchar(128),
@.RefIdText varchar(18),
@.dbnameText varchar(128),
@.JobStepText varchar(128)
If( @.RefId is not null)
Begin
Select @.StepId = StepId, @.JobName = JobName, @.SrvName = SrvName From ADMinSRV..Referentiel Where Id=@.RefId
If( @.JobStep is null ) select @.JobStepText = ''
Else select @.JobStepText = @.JobStep
select @.RefIdText = CONVERT( varchar(18), @.RefId )
End
Else
Begin
Select @.JobName = 'Not executed by ADMinSRV', @.RefIdText = 'Not executed by ADMinSRV', @.SrvName = @.@.SERVERNAME
End
If( @.dbname is null ) select @.dbnameText = ''
Else select @.dbnameText = @.dbname
print '<ADM_DOCUMENTATION_RESULTS>'
print '<EXECUTION_PARAMETERS>'
print '<PARAMETER PARAMETER_NAME="@.dbname" PARAMETER_VALUE="'+@.dbnameText+'" />'
print '<PARAMETER PARAMETER_NAME="@.JobStep" PARAMETER_VALUE="'+@.JobStepText+'" />'
print '<PARAMETER PARAMETER_NAME="@.RefId" PARAMETER_VALUE="'+@.RefIdText+'" />'
print '</EXECUTION_PARAMETERS>'
-- Variables declaration
declare
@.Row_Count int,
@.Product_Version varchar,
@.ErrorLog nvarchar(255),
@.InstNamevarchar(128),
@.SQLCmd varchar(800),
@.RegData nvarchar (255),
@.Retour int
-- XP_MSVER PROCESSING -
-- Create temporary teble to recover xp_msver parameters
Create Table #Tmp_msver
(Index_rowsmallint,
Nomvarchar(50),
Internal_Valueint,
Character_Value varchar(1000))
-- Insert those parameters in the table
Insert #Tmp_msver
Exec master.dbo.xp_msver
Select @.Row_Count = count(*) From #Tmp_msver
-- print the item xp_msver
print '<XP_MSVER>'
-- Process the table and print information from "xp_msver" to the report
While( @.Row_Count > 0 )
Begin
declare
@.ParamName varchar(50),
@.Internal_Val varchar(20),
@.Character_Val varchar(1000)
-- Recover parameters
Select @.ParamName = Nom From #Tmp_msver Where Index_row = @.Row_Count
Select @.Internal_Val = CONVERT( varchar(20), Internal_Value) From #Tmp_msver Where Index_row = @.Row_Count
Select @.Character_Val = Character_Value From #Tmp_msver Where Index_row = @.Row_Count
-- print parameters in XML format
if( @.Character_Val is not null )
print '<MSVER_PARAM MSVER_PARAM_NAME ="'+ @.ParamName+ '" MSVER_PARAM_VALUE="'+ @.Character_Val+'"/>'
else if( @.Internal_Val is not null )
print '<MSVER_PARAM MSVER_PARAM_NAME ="'+ @.ParamName+ '" MSVER_PARAM_VALUE="'+ @.Internal_Val+'"/>'
-- Get ProductVerion for further use
if( @.ParamName = 'ProductVersion' )
Select @.Product_Version = @.Character_Val
-- decrement Rowcount
Select @.Row_Count = @.Row_Count - 1
End
-- Drop temporary table
Drop Table #Tmp_msver
-- print the enditem xp_msver
print '</XP_MSVER>'
-- XP_MSVER PROCESS END -
-- SP_CONFIGURE PROCESSING -
-- Create temporary teble to recover sp_configure parameters
Create Table #Tmp_configure
( Nomvarchar(50),
Miniint,
Maxiint,
Configint,
Usedint )
-- Insert those parameters in the table
Insert #Tmp_configure
Exec master.dbo.sp_configure
-- print the item sp_configure
print '<SP_CONFIGURE>'
-- Declare cursor
Declare config_cursor Cursor For
Select Nom, Mini, Maxi, Config, Used From #Tmp_configure for read only
Declare
@.Nom varchar(50),
@.Min int,
@.Max int,
@.Config int,
@.Used int
-- Open cursor
Open config_cursor
-- Process tables entries
Fetch NEXT From config_cursor Into @.Nom, @.Min, @.Max, @.Config, @.Used
While( @.@.FETCH_STATUS = 0 )
Begin
-- Print params in XML Format
print '<CONFIGURE_PARAM CONFIGURE_PARAM_NAME="'+ @.Nom +
'" CONFIGURE_PARAM_MIN="'+ CONVERT( varchar(20), @.Min) +
'" CONFIGURE_PARAM_MAX="'+ CONVERT( varchar(20), @.Max) +
'" CONFIGURE_PARAM_CONFIG="'+ CONVERT( varchar(20), @.Config) +
'" CONFIGURE_PARAM_USED="'+ CONVERT( varchar(20), @.Used) +'" />'
-- Print params sort order if version 7
if( @.Nom = 'default sortorder id' and @.Product_Version = '7' )
Begin
Declare @.sortorderused varchar(255),
@.sortorderconf varchar(255)
Select @.sortorderconf = description from master..syscharsets with(nolock) where id= @.Config
Select @.sortorderused = description from master..syscharsets with(nolock) where id= @.Used
print '<CONFIGURE_PARAM CONFIGURE_PARAM_NAME="default sortorder" CONFIGURE_PARAM_MIN="" CONFIGURE_PARAM_MAX="" CONFIGURE_PARAM_CONFIG="'+
@.sortorderconf + '" CONFIGURE_PARAM_USED="'+ @.sortorderused +'" />'
End
Fetch NEXT From config_cursor Into @.Nom, @.Min, @.Max, @.Config, @.Used
End
-- Close and deallocate cursor
Close config_cursor
Deallocate config_cursor
-- Drop temporary table
Drop Table #Tmp_configure
-- print the item sp_configure
print '</SP_CONFIGURE>'
-- SP_CONFIGURE PROCESS END -
-- VARIABLES RECOVERY -
-- print the item server_variables
print '<SERVER_VARIABLES>'
if( @.Product_Version = '7' )
Begin
print '<SERVERNAME>'+ @.@.SERVERNAME +'</SERVERNAME>'
print '<CONNECTIONS>'+ CONVERT( varchar(20),@.@.CONNECTIONS) +'</CONNECTIONS>'
print '<DATEFIRST>'+ CONVERT( varchar(20),@.@.DATEFIRST) +'</DATEFIRST>'
print '<DBTS>'+ CONVERT( varchar(20), CONVERT( int, @.@.DBTS)) +'</DBTS>'
print '<LANGUAGE>'+ @.@.LANGUAGE +'</LANGUAGE>'
print '<LANGID>'+ CONVERT( varchar(20),@.@.LANGID) +'</LANGID>'
print '<LOCK_TIMEOUT>'+ CONVERT( varchar(20),@.@.LOCK_TIMEOUT) +'</LOCK_TIMEOUT>'
print '<MAX_CONNECTIONS>'+ CONVERT( varchar(20),@.@.MAX_CONNECTIONS) +'</MAX_CONNECTIONS>'
print '<MAX_PRECISION>'+ CONVERT( varchar(20),@.@.MAX_PRECISION) +'</MAX_PRECISION>'
print '<NESTLEVEL>'+ CONVERT( varchar(20),@.@.NESTLEVEL) +'</NESTLEVEL>'
print '<REMSERVER>'+ @.@.REMSERVER +'</REMSERVER>'
print '<SERVICENAME>'+ @.@.SERVICENAME +'</SERVICENAME>'
print '<TEXTSIZE>'+ CONVERT( varchar(20),@.@.TEXTSIZE) + '</TEXTSIZE>'
print '<VERSION>'+ @.@.VERSION + '</VERSION>'
End
if( @.Product_Version = '8' )
Begin
print '<SERVERNAME>'+ @.@.SERVERNAME +'</SERVERNAME>'
print '<DATEFIRST>'+ CONVERT( varchar(20),@.@.DATEFIRST) +'</DATEFIRST>'
print '<LANGUAGE>'+ @.@.LANGUAGE +'</LANGUAGE>'
print '<LANGID>'+ CONVERT( varchar(20),@.@.LANGID) +'</LANGID>'
print '<LOCK_TIMEOUT>'+ CONVERT( varchar(20),@.@.LOCK_TIMEOUT) +'</LOCK_TIMEOUT>'
print '<MAX_CONNECTIONS>'+ CONVERT( varchar(20),@.@.MAX_CONNECTIONS) +'</MAX_CONNECTIONS>'
print '<MAX_PRECISION>'+ CONVERT( varchar(20),@.@.MAX_PRECISION) +'</MAX_PRECISION>'
print '<NESTLEVEL>'+ CONVERT( varchar(20),@.@.NESTLEVEL) +'</NESTLEVEL>'
print '<REMSERVER>'+ @.@.REMSERVER +'</REMSERVER>'
print '<SERVICENAME>'+ @.@.SERVICENAME +'</SERVICENAME>'
print '<TEXTSIZE>'+ CONVERT( varchar(20),@.@.TEXTSIZE) + '</TEXTSIZE>'
print '<VERSION>'+ @.@.VERSION + '</VERSION>'
Exec(' Declare @.InstName varchar(128)
Select @.InstName = CONVERT( nvarchar(128), SERVERPROPERTY(''InstanceName'') )
print ''<COLLATION>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''Collation''))+''</COLLATION>''
print ''<EDITION>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''Edition''))+''</EDITION>''
print ''<ENGINEEDITION>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''EngineEdition''))+''</ENGINEEDITION>''
print ''<INSTANCENAME>''+ @.InstName +''</INSTANCENAME>''
print ''<ISCLUSTERED>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''IsClustered''))+''</ISCLUSTERED>''
print ''<ISFULLTEXTINSTALLED>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''IsFullTextInstalled''))+''</ISFULLTEXTINSTALLED>''
print ''<ISINTEGRATEDSECURITYONLY>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''InIntegratedSecurityOnly''))+''</ISINTEGRATEDSECURITYONLY>''
print ''<ISSINGLEUSER>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''IsSingleUser''))+''</ISSINGLEUSER>''
print ''<ISSYNCWITHBACKUP>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''IsSyncWithBackup''))+''</ISSYNCWITHBACKUP>''
print ''<LICENSETYPE>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''LicenseType''))+''</LICENSETYPE>''
print ''<MACHINENAME>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''MachineName''))+''</MACHINENAME>''
print ''<NUMLICENSES>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''NumLicenses''))+''</NUMLICENSES>''
print ''<PROCESSID>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''ProcessId''))+''</PROCESSID>''
print ''<PRODUCTVERSION>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''ProductVersion''))+''</PRODUCTVERSION>''
print ''<PRODUCTLEVEL>''+ CONVERT(nvarchar(128), SERVERPROPERTY(''ProductLevel''))+''</PRODUCTLEVEL>''
')
End
-- print the item servervariables
print '</SERVER_VARIABLES>'
-- END OF VARIABLE RECOVERY -
-- DATAFILES LOCATION READ --
-- print the item datafileslocation
print '<FILES_LOCATION>'
declare
@.str varchar(1024)
Select @.str = [filename] from sysdatabases where name='master'
print '<DATAFILESLOCATION>'+ SUBSTRING( @.str, 1, LEN(@.str) - 10) +'</DATAFILESLOCATION>'
--
-- Recover ErroLog path
if @.InstName is null -- Pour une instance SQL2000 par dúfaut ou pour SQL7 (FBO)
begin
exec @.Retour = master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters',
'SQLArg1',
@.RegData OUTPUT
select @.Error = @.@.error
if (@.Error > 0) or (@.Retour <> 0) or (convert(char(2), @.RegData) <> '-e')
begin
select 'ErrorLogPath not found : ' + @.RegData
return 3
end
select @.ErrorLog = stuff(@.RegData, 1, 2, '')
end
else-- Instance nommúe SQL2000(FBO)
begin
create table #RetSQLDyn -- Codes retour SQL Dynamique
(typecode int,-- type code 1 = retour du xp_cmdshell ; 2 = @.@.error
errint)
create table #ErrLogLoc (Ligne varchar(500)) -- Emplacement du fichier d'errorlog
select @.SQLCmd = 'DECLARE @.Retour int,@.RegData nvarchar (255)'
select @.SQLCmd = @.SQLCmd + ' exec @.Retour = master.dbo.xp_regread '
select @.SQLCmd = @.SQLCmd + '''HKEY_LOCAL_MACHINE'','
select @.SQLCmd = @.SQLCmd + '''SOFTWARE\Microsoft\Microsoft SQL Server\'
select @.SQLCmd = @.SQLCmd + @.InstName
select @.SQLCmd = @.SQLCmd + '\MSSQLServer\Parameters'',''SQLArg1'','
select @.SQLCmd = @.SQLCmd + '@.RegData OUTPUT'
select @.SQLCmd = @.SQLCmd + ' INSERT #RetSQLDyn SELECT 2,@.@.Error'
select @.SQLCmd = @.SQLCmd + ' INSERT #RetSQLDyn SELECT 1,@.Retour'
select @.SQLCmd = @.SQLCmd + ' INSERT #ErrLogLoc SELECT @.RegData'
exec (@.SQLCmd)
select @.Error = err
from #RetSQLDyn
where typecode = 2
select @.Retour = err
from #RetSQLDyn
where typecode = 1
select @.RegData = Ligne
from #ErrLogLoc
if (@.Error > 0) or (@.Retour <> 0) or (convert(char(2), @.RegData) <> '-e')
begin
select 'ErrorLogPath not found : ' + @.RegData
return 3
end
select @.ErrorLog = stuff(@.RegData, 1, 2, '')
end
-- Print errorlog path
print '<TRANSACTIONSLOG>'+ @.ErrorLog +'</TRANSACTIONSLOG>'
-- print the item datafileslocation
print '</FILES_LOCATION>'
-- END OF DATAFILES LOCATION --
-- SERVICES ACCOUNTS READING --
-- print the item accounts
print '<SERVICES_ACCOUNTS>'
--
-- Recover Sql Server Service account
If( @.InstName is null ) -- Pour une instance SQL2000 par dúfaut ou pour SQL7 (FBO)
Begin
exec @.Retour = master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Services\MSSQLServer\',
'ObjectName',
@.RegData OUTPUT
select @.Error = @.@.error
if (@.Error > 0) or (@.Retour <> 0)
begin
select 'Account Path not found : ' + @.RegData
return 3
end
End
Else-- Instance nommúe SQL2000(FBO)
Begin
truncate table #RetSQLDyn -- Codes retour SQL Dynamique
truncate table #ErrLogLoc -- Emplacement du fichier d'errorlog
select @.SQLCmd = 'DECLARE @.Retour int,@.RegData nvarchar (255)'
select @.SQLCmd = @.SQLCmd + ' exec @.Retour = master.dbo.xp_regread '
select @.SQLCmd = @.SQLCmd + '''HKEY_LOCAL_MACHINE'','
select @.SQLCmd = @.SQLCmd + '''SYSTEM\CurrentControlSet\Services\MSSQL$'
select @.SQLCmd = @.SQLCmd + @.InstName
select @.SQLCmd = @.SQLCmd + '\'',''ObjectName'','
select @.SQLCmd = @.SQLCmd + '@.RegData OUTPUT'
select @.SQLCmd = @.SQLCmd + ' INSERT #RetSQLDyn SELECT 2,@.@.Error'
select @.SQLCmd = @.SQLCmd + ' INSERT #RetSQLDyn SELECT 1,@.Retour'
select @.SQLCmd = @.SQLCmd + ' INSERT #ErrLogLoc SELECT @.RegData'
exec (@.SQLCmd)
select @.Error = err
from #RetSQLDyn
where typecode = 2
select @.Retour = err
from #RetSQLDyn
where typecode = 1
select @.RegData = Ligne
from #ErrLogLoc
if (@.Error > 0) or (@.Retour <> 0)
begin
select 'Account Path not found : ' + @.RegData
return 3
end
End
-- End of FBO block
print '<SERVICEACCOUNT SERVICENAME="Sql Server" ACCOUNTNAME="'+ @.RegData +'"/>'
-
-- Recover Sql Server Agent Service account
if @.InstName is null -- Pour une instance SQL2000 par dúfaut ou pour SQL7 (FBO)
begin
exec @.Retour = master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Services\SQLServerAgent\',
'ObjectName',
@.RegData OUTPUT
select @.Error = @.@.error
if (@.Error > 0) or (@.Retour <> 0)
begin
select 'Account Path not found : ' + @.RegData
return 3
end
end
else-- Instance nommúe SQL2000(FBO)
begin
truncate table #RetSQLDyn -- Codes retour SQL Dynamique
truncate table #ErrLogLoc -- Emplacement du fichier d'errorlog
select @.SQLCmd = 'DECLARE @.Retour int,@.RegData nvarchar (255)'
select @.SQLCmd = @.SQLCmd + ' exec @.Retour = master.dbo.xp_regread '
select @.SQLCmd = @.SQLCmd + '''HKEY_LOCAL_MACHINE'','
select @.SQLCmd = @.SQLCmd + '''SYSTEM\CurrentControlSet\Services\SQLAgent$'
select @.SQLCmd = @.SQLCmd + @.InstName
select @.SQLCmd = @.SQLCmd + ''',''ObjectName'','
select @.SQLCmd = @.SQLCmd + '@.RegData OUTPUT'
select @.SQLCmd = @.SQLCmd + ' INSERT #RetSQLDyn SELECT 2,@.@.Error'
select @.SQLCmd = @.SQLCmd + ' INSERT #RetSQLDyn SELECT 1,@.Retour'
select @.SQLCmd = @.SQLCmd + ' INSERT #ErrLogLoc SELECT @.RegData'
exec (@.SQLCmd)
select @.Error = err
from #RetSQLDyn
where typecode = 2
select @.Retour = err
from #RetSQLDyn
where typecode = 1
select @.RegData = Ligne
from #ErrLogLoc
drop table #RetSQLDyn
drop table #ErrLogLoc
if (@.Error > 0) or (@.Retour <> 0)
begin
select 'Account not found : ' + @.RegData
return 3
end
end
-- End of FBO block
print '<SERVICEACCOUNT SERVICENAME="Sql Server Agent" ACCOUNTNAME="'+ @.RegData +'"/>'
-- print the item accounts
print '</SERVICES_ACCOUNTS>'
END OF SERVICES ACCOUNTS --
-- LOGINS ANALYSIS
Create Table #Tmp_logins
( nomvarchar(128),
dbvarchar(128),
sysbit,
securitybit,
setupbit,
[disk]bit,
serverbit,
processbit)
Insert #Tmp_logins
Select loginname, dbname, sysadmin, securityadmin, setupadmin, diskadmin, serveradmin, processadminfrom syslogins with(nolock)
-- XMl Element oipen
print '<LOGINS>'
-- Declare cursor
Declare logins_cursor Cursor For
Select nom, db, sys, security, setup, [disk], server, process From #Tmp_loginsfor read only
Declare
@.Rolevarchar(1024),
@.loginName varchar(128),
@.database varchar(128),
@.isSysAdminbit,
@.isSecurityAdminbit,
@.isSetupAdminbit,
@.isDiskAdminbit,
@.isServerAdminbit,
@.isProcessAdminbit
-- Open cursor
Open logins_cursor
Fetch NEXT From logins_cursor Into @.loginName, @.database, @.isSysAdmin, @.isSecurityAdmin, @.isSetupAdmin,
@.isDiskAdmin, @.isServerAdmin, @.isProcessAdmin
While( @.@.FETCH_STATUS = 0 )
Begin
Select @.Role = ''
if( @.isSysAdmin <> 0 )
Select @.Role = @.Role + 'SYS_ADMIN '
if( @.isSecurityAdmin <> 0 )
Select @.Role = @.Role + 'SECURITY_ADMIN '
if( @.isSetupAdmin <> 0 )
Select @.Role = @.Role + 'SETUP_ADMIN '
if( @.isDiskAdmin <> 0 )
Select @.Role = @.Role + 'DISK_ADMIN '
if( @.isServerAdmin <> 0 )
Select @.Role = @.Role + 'SERVER_ADMIN '
if( @.isProcessAdmin <> 0 )
Select @.Role = @.Role + 'PROCESS_ADMIN '
if( @.Role = '' ) Select @.Role = 'public'
print '<LOGIN LOGIN_NAME="'+ @.loginName+'" DATABASE="'+ @.database +'" ROLE="'+ @.Role +'"/>'
Fetch NEXT From logins_cursor Into @.loginName, @.database, @.isSysAdmin, @.isSecurityAdmin, @.isSetupAdmin,
@.isDiskAdmin, @.isServerAdmin, @.isProcessAdmin
End
-- Close and deallocate cursor
Close logins_cursor
Deallocate logins_cursor
-- Drop temporary table
Drop Table #Tmp_logins
-- print the item logins
print '</LOGINS>'
- END OF LOGINS ANALYSIS
-- MSDB TEMPDB ANALYSIS -
-- XML Element open
print '<SYSTEM_DATABASES>'
Declare
@.DatabaseNamevarchar(128),
@.Filenamevarchar(260),
@.Filesizeint
Set @.Filesize = 0
-- Declare cursor
Declare databases_cursor Cursor For
Select name, filename From master.dbo.sysdatabases with(nolock)
for read only
-- Open cursor
Open databases_cursor
Fetch NEXT From databases_cursor Into @.DatabaseName, @.Filename
While( @.@.FETCH_STATUS = 0 )
Begin
-- Change DB context
Exec( 'use '+ @.DatabaseName )
-- Declare cursor and temp table for files size
Create table #Tmp_files
( uNamevarchar(128),
fidsmallint,
filenamenchar(260),
filegroupvarchar(128),
sizenvarchar(18),
maxsizenvarchar(18),
growthnvarchar(18),
usagevarchar(9) )
Insert #Tmp_files
Exec sp_helpfile
Declare
@.sizevarchar(18)
Declare files_cursor Cursor For
Select size From #Tmp_files Where usage='data only' for read only
-- Open cursor
Open files_cursor
-- Process cursor
Fetch NEXT From files_cursor Into @.size
While( @.@.FETCH_STATUS = 0 )
Begin
Select @.Filesize = @.Filesize + CONVERT( int, SUBSTRING(@.size, 1, LEN(@.size)-2) )
Fetch NEXT From files_cursor Into @.size
End
-- Close cursor
Close files_cursor
-- Deallocate cursor
Deallocate files_cursor
-- delete temporary db
Drop Table #Tmp_files
-- Print XML item database
print '<DATABASE DATABASENAME="'+ @.DatabaseName+'" DATABASEFILENAME="'+ @.Filename +'" FILESIZE="'+ CONVERT( varchar(20), @.Filesize) +'">'
if( (@.DatabaseName <> 'msdb') or (@.DatabaseName <> 'tempdb') )
Begin
--Declare cursor for users
Declare
@.Usernamevarchar(128),
@.Userrolevarchar(2048)
Set @.Userrole = ''
Exec( 'Declare users_cursor Cursor For Select name From '+ @.DatabaseName +'..sysusers with(nolock) Where islogin = 1 AND status <> 0 for read only' )
-- Create temporary table
Create table #Tmp_roles
( uNamevarchar(128),
gNamevarchar(128),
loginNamevarchar(128),
dbNamevarchar(128),
uIdsmallint,
sIdsmallint )
-- Open user cursor
Open users_cursor
-- Process cursor
Fetch NEXT From users_cursor Into @.Username
While( @.@.FETCH_STATUS = 0 )
Begin
-- Declare cursor and temporary table for roles
Declare
@.tmpRolevarchar(128)
-- insert user data to temporary table
Exec( 'use '+@.DatabaseName +' Insert #Tmp_roles exec sp_helpuser '+ @.Username )
-- Declare cursor
Declare roles_cursor Cursor For
Select gName From #Tmp_roles Where uName = @.Username for read only
-- open cursor
Open roles_cursor
-- Process cursor
Fetch NEXT From roles_cursor Into @.tmpRole
While( @.@.FETCH_STATUS = 0 )
Begin
Select @.Userrole = @.Userrole + @.tmpRole
Fetch NEXT From roles_cursor Into @.tmpRole
End
-- Close and deallocate cursor
Close roles_cursor
Deallocate roles_cursor
-- Clear temporary table
Truncate Table #Tmp_roles
-- print the user item
print '<USER USERNAME="'+ @.Username +'" USERROLE="'+ @.Userrole +'"/>'
Fetch NEXT From users_cursor Into @.Username
End
-- Close and deallocate cursor
Close users_cursor
Deallocate users_cursor
-- Clear temporary table
Drop Table #Tmp_roles
End
-- print end item databases
print '</DATABASE>'
Fetch NEXT From databases_cursor Into @.DatabaseName, @.Filename
End
-- Close and deallocate cursor
Close databases_cursor
Deallocate databases_cursor
-- print the item sp_configure
print '</SYSTEM_DATABASES>'
DATABASES PROCESS END -
-- ALERTS PROCESSING
-- print the item sp_configure
print '<SERVER_ALERTS>'
-- Declare cursor
Declare alerts_cursor Cursor For
Select name, database_name, severity, message_id From msdb..sysalerts with(nolock) for read only
Declare
@.NomAlertevarchar(128),
@.dbAlerte varchar(128),
@.Sevint,
@.MessageId int
-- Open cursor
Open alerts_cursor
-- Process tables entries
Fetch NEXT From alerts_cursor Into @.NomAlerte, @.dbAlerte, @.Sev, @.MessageId
While( @.@.FETCH_STATUS = 0 )
Begin
-- manage public alerts
if( @.dbAlerte is null)
Set @.dbAlerte = 'none'
-- Print params in XMl Format
print '<ALERT ALERT_NAME="'+ @.NomAlerte +
'" ALERT_DATABASE="'+ @.dbAlerte +
'" ALERT_SEVERITY="'+ CONVERT( varchar(20), @.Sev) +
'" ALERT_MESSAGEID="'+ CONVERT( varchar(20), @.MessageId) +'" />'
Fetch NEXT From alerts_cursor Into @.NomAlerte, @.dbAlerte, @.Sev, @.MessageId
End
-- Close and deallocate cursor
Close alerts_cursor
Deallocate alerts_cursor
-- print the item sp_configure
print '</SERVER_ALERTS>'
-- ALERTS PROCESS END -
-- OPERATORS PROCESSING
-- print the item sp_configure
print '<SERVER_OPERATORS>'
-- Declare cursor
Declare operators_cursor Cursor For
Select name, email_address, pager_address, netsend_address From msdb..sysoperators with(nolock) for read only
Declare
@.NomOpvarchar(128),
@.email varchar(100),
@.pager varchar(100),
@.netsend varchar(100)
-- Open cursor
Open operators_cursor
-- Process tables entries
Fetch NEXT From operators_cursor Into @.NomOp, @.email, @.pager, @.netsend
While( @.@.FETCH_STATUS = 0 )
Begin
-- manage null addresses
if( @.email is null )
Set @.email = 'none'
if( @.pager is null )
Set @.pager = 'none'
if( @.netsend is null )
Set @.netsend = 'none'
-- Print params in XMl Format
print '<OPERATOR OPERATOR_NAME="'+ @.NomOp +
'" OPERATOR_EMAIL="'+ @.email +
'" OPERATOR_PAGER="'+ @.pager +
'" OPERATOR_NETSEND="'+ @.netsend +'" />'
Fetch NEXT From operators_cursor Into @.NomOp, @.email, @.pager, @.netsend
End
-- Close and deallocate cursor
Close operators_cursor
Deallocate operators_cursor
-- print the item sp_configure
print '</SERVER_OPERATORS>'
- OPERATORS PROCESS END
-- JOBS PROCESSING --
-- print the item sp_configure
print '<SERVER_JOBS>'
-- Declare cursor
Declare jobs_cursor Cursor For
Select job_id, name, enabled, owner_sid From msdb..sysjobs with(nolock) for read only
Declare
@.NomJobvarchar(128),
@.JobId uniqueidentifier,
@.Enabledbit,
@.ownervarbinary(85)
-- Open cursor
Open jobs_cursor
-- Process tables entries
Fetch NEXT From jobs_cursor Into @.JobId, @.NomJob, @.Enabled, @.owner
While( @.@.FETCH_STATUS = 0 )
Begin
-- Print params in XML Format
print '<JOB JOB_NAME="'+ @.NomJob +
'" JOBENABLED="'+CONVERT( varchar(10), @.Enabled) +
'" JOBOWNER="'+ SUSER_SNAME(@.owner) +'">'
-- Manage JobSteps
-- Declare cursor
Declare @.NomStepvarchar(128)
Declare steps_cursor Cursor For
Select step_name From msdb.dbo.sysjobsteps with(nolock) Where job_id = @.JobId Order by step_id asc for read only
-- Open cursor
Open steps_cursor
-- Process tables entries
Fetch NEXT From steps_cursor Into @.NomStep
While( @.@.FETCH_STATUS = 0 )
Begin
-- Print params in XMl Format
print '<STEP STEP_NAME="'+ @.NomStep +'"/>'
Fetch NEXT From steps_cursor Into @.NomStep
End
-- Close and deallocate cursor
Close steps_cursor
Deallocate steps_cursor
-- Manage JobSchedules
Declare @.NomSchedulevarchar(128)
-- Declare cursor
Declare schedules_cursor Cursor For
Select name From msdb..sysjobschedules with(nolock) Where job_id = @.JobId for read only
-- Open cursor
Open schedules_cursor
-- Process tables entries
Fetch NEXT From schedules_cursor Into @.NomSchedule
While( @.@.FETCH_STATUS = 0 )
Begin
-- Print params in XML Format
print '<SCHEDULE SCHEDULE_NAME="'+ @.NomSchedule +'"/>'
Fetch NEXT From schedules_cursor Into @.NomSchedule
End
-- Close and deallocate cursor
Close schedules_cursor
Deallocate schedules_cursor
-- terminate the XML Job Item
print '</JOB>'
Fetch NEXT From jobs_cursor Into @.JobId, @.NomJob, @.Enabled, @.owner
End
-- Close and deallocate cursor
Close jobs_cursor
Deallocate jobs_cursor
-- print the item sp_configure
print '</SERVER_JOBS>'
- JOBS PROCESS END
TARGET SERVERS PROCESSING -
-- print the item sp_configure
print '<LINKED_SERVERS>'
-- Create temporary table
Create Table #Tmp_linkedServers
( srvNamevarchar(128),
srvProviderNamevarchar(128),
srvProductvarchar(128),
srvDataSourcevarchar(2000),
srvProviderStringvarchar(2000),
srvLocationvarchar(2000),
srvCategoryvarchar(128) )
Insert #Tmp_linkedServers
Exec sp_linkedservers
-- Declare cursor
Declare linkedsrv_cursor Cursor For
Select srvName From #Tmp_linkedServers for read only
Declare
@.NomSrvvarchar(128)
-- Open cursor
Open linkedsrv_cursor
-- Process tables entries
Fetch NEXT From linkedsrv_cursor Into @.NomSrv
While( @.@.FETCH_STATUS = 0 )
Begin
-- Print params in XML Format
print '<LINKEDSERVER SERVER_NAME="'+ @.NomSrv +'" />'
Fetch NEXT From linkedsrv_cursor Into @.NomSrv
End
-- Close and deallocate cursor
Close linkedsrv_cursor
Deallocate linkedsrv_cursor
-- Drop temporary table
Drop Table #Tmp_linkedServers
-- print the item sp_configure
print '</LINKED_SERVERS>'
- TARGET SERVERS PROCESS END
print '</ADM_DOCUMENTATION_RESULTS>'
insert into ADMinSRV..ADM_Documentation_Out (SrvName, JobName, StepId, Language, ServiceName, Version, LastStart, LastStop) VALUES ( @.SrvName, @.JobName, @.StepId, @.@.LANGUAGE, @.@.SERVICENAME, @.@.VERSION, NULL, NULL )
if (@.Error <> 0)
return 1-- Error
else
return 0-- ok
End
As you are concatenating the query command together, you could just Print them out rather than executing them and check which command is actually have a missing quote in a string with a \ character. I guess this is where the error is based on.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Hi Jens
Firstly thank you for your answer.
I have just started my new job and it is not me which wrote the procedure, in more I begin in SQL, can you please help me to make the test which you recommend!!!
Think you
|||I have solved the pb
Thinks
No comments:
Post a Comment