I ran into the following error when calling a stored procedure from SSIS. The stored proedure dumps the SELECT query result into a table variable and I was doing a final SELECT from the table variable.
[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
The error was eliminated after putting SET NOCOUNT ON in the begining of the stored procedure
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Friday, February 3, 2012
Thursday, February 2, 2012
Script: To log the space of the database
As a DBA one of my primary job is to keep track of the growth of the database so that I can forecast the space required. I have a static table where I run the following script and collect the data in about in 4 hours and analyze the data collected in the table.
I have scehduled this script as a sql job that runs in every 4 hours. Every Friday I look at the historic data and see by how large our databases grew.
DECLARE
DECLARE
DECLARE @rowcnt INT @iterator INT @dbname VARCHAR(200)DECLARE @exec_sql VARCHAR(500)SET @rowcnt = 0SET
CREATE TABLE #db_file_info ([Database_Name] SYSNAME NOT NULL,[File_ID] SMALLINT NOT NULL,[File_Type] VARCHAR(10) NOT NULL,[File_Name] SYSNAME NOT NULL,[File_Path] VARCHAR(500) NOT NULL,[File_Size_in_MB] INT NOT NULL,[Space_Used_in_MB] INT NOT NULL,[Space_Left_in_MB] INT NOT NULL,[Time_collected] smalldatetime )
CREATE TABLE #db (dbid INT,name VARCHAR(200))INSERT INTO #dbSELECT dbid,nameFROM MASTER.dbo.sysdatabasesSET @rowcnt = @@ROWCOUNTWHILE@iterator <= @rowcntBEGINSELECT @dbname = '['+ name + ']'
Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,
[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)),
getdate()
from
dbo.sysfiles;
'FROM #dbWHERE dbid = @iteratorSET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
EXEC( @exec_sql)
SET @iterator = @iterator + 1END
INSERT INTO SQLAdmin_Monitor_db_file_infoSELECT *FROM #db_file_infoDROP TABLE #dbDROP
/*SQLAdmin_Monitor_db_file_info is the static table where I collect the results returned by the above script. */TABLE #db_file_info @iterator = 1
I have scehduled this script as a sql job that runs in every 4 hours. Every Friday I look at the historic data and see by how large our databases grew.
DECLARE
DECLARE
DECLARE @rowcnt INT @iterator INT @dbname VARCHAR(200)DECLARE @exec_sql VARCHAR(500)SET @rowcnt = 0SET
CREATE TABLE #db_file_info ([Database_Name] SYSNAME NOT NULL,[File_ID] SMALLINT NOT NULL,[File_Type] VARCHAR(10) NOT NULL,[File_Name] SYSNAME NOT NULL,[File_Path] VARCHAR(500) NOT NULL,[File_Size_in_MB] INT NOT NULL,[Space_Used_in_MB] INT NOT NULL,[Space_Left_in_MB] INT NOT NULL,[Time_collected] smalldatetime )
CREATE TABLE #db (dbid INT,name VARCHAR(200))INSERT INTO #dbSELECT dbid,nameFROM MASTER.dbo.sysdatabasesSET @rowcnt = @@ROWCOUNTWHILE@iterator <= @rowcntBEGINSELECT @dbname = '['+ name + ']'
Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,
[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)),
getdate()
from
dbo.sysfiles;
'FROM #dbWHERE dbid = @iteratorSET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
EXEC( @exec_sql)
SET @iterator = @iterator + 1END
INSERT INTO SQLAdmin_Monitor_db_file_infoSELECT *FROM #db_file_infoDROP TABLE #dbDROP
/*SQLAdmin_Monitor_db_file_info is the static table where I collect the results returned by the above script. */TABLE #db_file_info @iterator = 1
Script: Find the tables and thier sizes in a database
Use the following script to find out the size of different table in the database. If your database grows big suddenlt the following script comes very handy to find out what is causing the sudden growth.
use [yourdatabase]
GO
SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8 ) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO
use [yourdatabase]
GO
SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8 ) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO
SQL Agent: Script to disable and enable SQL agent jobs
The following script disables the job scedules in the agent
BEGIN TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
BEGIN TRY
exec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 0END TRY
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 0END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Disable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
--select @rcEnd
The following script enables the job scedules in the agent
BEGIN TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
BEGIN TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 1END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Enable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
--select @rcEnd exec @rc = msdb.dbo.sp_send_dbmailEND CATCHexec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 1END TRYEND CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 1END TRYexec @rc = msdb.dbo.sp_send_dbmail
END CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 0END TRY
BEGIN TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
BEGIN TRY
exec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 0END TRY
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 0END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Disable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
--select @rcEnd
The following script enables the job scedules in the agent
BEGIN TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
BEGIN TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 1END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Enable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
--select @rcEnd exec @rc = msdb.dbo.sp_send_dbmailEND CATCHexec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 1END TRYEND CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 1END TRYexec @rc = msdb.dbo.sp_send_dbmail
END CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 0END TRY
Wednesday, February 1, 2012
SQL Agent : Schedule DTS as a SQL Job
Call the DTS from an SQL agent job as
DTSRun /S Servername /E /N "DTSName"
Select type as Operating System(cmdExec) from the drop down
DTSName = Name of the DTS that you have created
Servername= Name of your server where the DTS is created
DTSRun /S Servername /E /N "DTSName"
Select type as Operating System(cmdExec) from the drop down
DTSName = Name of the DTS that you have created
Servername= Name of your server where the DTS is created
Tuesday, January 31, 2012
SSIS Error: Error: ForEach Variable Mapping number n to variable "User:: n1 " cannot be applied.
The exact error I got was
Error: ForEach Variable Mapping number 2 to variable "User::i_HospitalCode" cannot be applied.
I spent almost 2 hours trying to find out what the problem was. It was the problem in the data type. I was declaring the data type of variable User::i_HospitalCode as string where as the resultset ( returned by the stored procedure) was integer.
I changed the data type of the variable User::i_HospitalCode in the variable declaration window and it worked!
Error: ForEach Variable Mapping number 2 to variable "User::i_HospitalCode" cannot be applied.
I spent almost 2 hours trying to find out what the problem was. It was the problem in the data type. I was declaring the data type of variable User::i_HospitalCode as string where as the resultset ( returned by the stored procedure) was integer.
I changed the data type of the variable User::i_HospitalCode in the variable declaration window and it worked!
Friday, January 13, 2012
Restore database
use master
GO
Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE
GO RESTORE DATABASE INACTIVECLIENTS2009FROM DISK = 'E:\DatabaseArchivesNotRestored\INACTIVECLIENTS2009_20110112_1053am.bak'WITH REPLACEGO
Alter Database INACTIVECLIENTS2009SET MULTI_USER With ROLLBACK IMMEDIATEGO USE INACTIVECLIENTS2009;GO
EXEC sp_updatestats
I was getting error "database cannot be restored because it is in use" this was resolved when I set the database to single_user mode
Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE
Then after successfully restoring it I changed it to the multi user mode.
GO
GO
Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE
GO RESTORE DATABASE INACTIVECLIENTS2009FROM DISK = 'E:\DatabaseArchivesNotRestored\INACTIVECLIENTS2009_20110112_1053am.bak'WITH REPLACEGO
Alter Database INACTIVECLIENTS2009SET MULTI_USER With ROLLBACK IMMEDIATEGO USE INACTIVECLIENTS2009;GO
EXEC sp_updatestats
I was getting error "database cannot be restored because it is in use" this was resolved when I set the database to single_user mode
Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE
Then after successfully restoring it I changed it to the multi user mode.
GO
Subscribe to:
Posts (Atom)