March 23, 2015 at 9:15 pm
I have simple query which creates tables by passing database name as parameter from a parameter table .
SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .
Note** I am not passing any database of name '20' :angry:
Exception handler error :
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Error in SSIS
[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have sysadmin permission .
Can we have some discussion on this error on how to fix this .
March 23, 2015 at 9:46 pm
RantaSanta (3/23/2015)
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Quick thought, the above error message point towards the database name not being passed correctly after creating the database, don't think it is a coincidence that the database id and the name are the same in the message.
😎
March 23, 2015 at 10:16 pm
This is SP1.
CREATE PROCEDURE [dbo].[SP1]
AS
/*Create Table variable to hold the data that needs to be processed*/
DECLARE @databases TABLE (
id int IDENTITY(1, 1),
number varchar(100),
Status char)
/*Populate the table from fleets table which are enabled and not in system*/
INSERT INTO @databases
SELECT
number,
status
FROM
XYZ.dbo.abcTable
WHERE
Status = 'N'
AND number NOT IN (SELECT
name
FROM
sys.databases)
/*Variable for While loop*/
DECLARE @maxid int;
SELECT
@maxid = MAX(id)
FROM
DECLARE @id int;
SET @id = 1
DECLARE
@number varchar(50),
@dbname varchar(100)
/*Erro handling and mail variables */
DECLARE
@ErrorMessage nvarchar(4000),
@ErrorSeverity int,
@ErrorState int,
@ErrNo varchar(255),
@Body varchar(max),
@Subject varchar(255),
@ObjectName varchar(255)
SET @ObjectName = OBJECT_NAME(@@PROCID)
WHILE @id <= @maxid
BEGIN
/* Get one record (you can read the values into some variables) */
SELECT
@number = number
FROM
WHERE
id = @id
SET @dbname = @number
/*Declare location for Database paths*/
DECLARE @Query varchar(max)= ''
DECLARE @DbFilePath varchar(4000)
DECLARE @LogFilePath varchar(4000)
SET @DbFilePath = CONVERT(varchar(4000), (SELECT
SERVERPROPERTY('instancedefaultdatapath')))
SET @LogFilePath = CONVERT(varchar(4000), (SELECT
SERVERPROPERTY('instancedefaultlogpath')))
/*Build the Query here*/
--SET @Query = 'IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '+@dbname+') BEGIN '
SET @Query = @Query + 'CREATE DATABASE [' + @DbName + '] ON PRIMARY '
SET @Query = @Query + '( NAME = ''' + @DbName + ''', FILENAME = ''' + @DbFilePath + @DbName
+ '.mdf'' , SIZE = 500000KB , MAXSIZE = UNLIMITED , FILEGROWTH = 102400KB ) '
SET @Query = @Query + ' LOG ON '
SET @Query = @Query + '( NAME = ''' + @DbName + '_log'', FILENAME = ''' + @LogFilePath + @DbName
+ '_log.ldf'' , SIZE = 500000KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
ALTER DATABASE [' + @DbName + '] SET RECOVERY SIMPLE'
EXEC(@query)
/*Create table , this is the problem area */
EXEC SP2 @Dbname
/*Send mail of success of database(s) creation */
SET @Subject = 'Database ' + @dbname + ' is created in ' + @@servername + ''
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'xyz@xyz,com',
@subject = @subject, -- nvarchar(255)
@from_address = 'abc@abc.com' -- varchar(max)
SELECT
@id = @id + 1
END
March 23, 2015 at 10:52 pm
RantaSanta (3/23/2015)
/*Populate the table from fleets table which are enabled and not in system*/INSERT INTO @databases
SELECT
number,
status
FROM
XYZ.dbo.abcTable
WHERE
Status = 'N'
AND [font="Arial Black"]number [/font]NOT IN (SELECT
[font="Arial Black"]name[/font]
FROM
sys.databases)
Ask Eirikur pointed out in the error report, you don't think the bolded items above are a mismatch???
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2015 at 9:25 am
Jeff ,
I changed number to nvarchar to match data type of name column from sys.databases . It is still failing when I call from SSIS . :crazy:
SP2 is :
CREATE PROCEDURE [dbo].[SP2]
@Dbname AS nvarchar(100)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Query AS varchar(MAX)
Set @Query = 'USE [' + @dbname + ']
CREATE TABLE [dbo].[TableName](
[Col1] [decimal](18, 0) NOT NULL,
[col2] [decimal](18, 0) NOT NULL,
[col3] [varchar](3) NULL,
[col4] [varchar](80) NULL,
)'
EXEC (@Query)
March 24, 2015 at 9:27 am
Database ID and Name is always same in exception . What I am missing here for SSIS to run fine ..
---------------------------------------------------------------------------------------------------- ERROR :: 615 :: Could not find database ID 26, name '26'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 123 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 26, name '26'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
March 24, 2015 at 4:08 pm
I have figured the solution for this issue .
It was related to how table creation script was formatted . It is working fine now via SSIS
Correct Way
CREATE TABLE [dbo].[abc](
[col1] [decimal](18, 0) NOT NULL,
[col2] [decimal](18, 0) NOT NULL)
Wrong way
CREATE TABLE [dbo].[abc](
col1 decimal(18, 0) NOT NULL,
col2 decimal(18, 0) NOT NULL)
It was tricky to figure how SSIS and SSMS parse the queries differently .
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply