Error trying to run SQL view creation script
Using SQL 2000, trying to have script loop through multiple databases, dropping/creating a view...
Getting following error:
Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'UPR00100'.
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'UPR00102'.
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'TW00820'.
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'dbo.TW70800'.
Getting following error:
Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'UPR00100'.
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'UPR00102'.
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'TW00820'.
Server: Msg 208, Level 16, State 1, Procedure VIEW_EMPLOYEE, Line 9
Invalid object name 'dbo.TW70800'.
-- ===========================================================
USE master
DROP TABLE tmpSQLDBS
DROP TABLE tmpActiveDBS
DROP TABLE tmpDARWINDBS
SELECT LTRIM(RTRIM(master.dbo.sysdatabases.name)) as SQLName
INTO master.dbo.tmpSQLDBS
FROM master.dbo.sysdatabases
ORDER BY master.dbo.sysdatabases.dbid
SELECT LTRIM(RTRIM(DYNAMICS.DBO.SY01500.INTERID)) as DarwinName
INTO master.dbo.tmpDARWINDBS
FROM DYNAMICS.DBO.SY01500
ORDER BY DYNAMICS.DBO.SY01500.INTERID
SELECT DarwinName, SQLName
INTO master.dbo.tmpActiveDBS
FROM master.dbo.tmpDARWINDBS LEFT OUTER JOIN master.dbo.tmpSQLDBS on master.dbo.tmpDARWINDBS.DarwinName = master.dbo.tmpSQLDBS.SqlName
DECLARE @sProcText varchar(8000),
@sProcText2 varchar(8000),
@sProcText3 varchar(8000),
@sCRLF char(2),
@sTAB char(1)
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
DECLARE dbnames_cursor CURSOR
FOR
SELECT dbo.tmpActiveDBS.DarwinName from dbo.tmpActiveDBS where dbo.tmpActiveDBS.SQLName is NOT Null
OPEN dbnames_cursor
DECLARE @sDatabaseName sysname
FETCH NEXT FROM dbnames_cursor INTO @sDatabaseName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @sDatabaseName = RTRIM(@sDatabaseName)
SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
PRINT('-------------------------------------------------------------')
PRINT('Creating ESC Reporting views for Database '+@sDatabaseName+'...')
--
-- Drop all the reporting views
--
SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE] ' + @sCRLF
SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText)
PRINT('Drop VIEW for Database '+@sDatabaseName+' View : VIEW_EMPLOYEE' )
--
-- Create all the reporting views
--
SET @sProcText = '
-----------------------------------------------------------------------------
-- Function : VIEW_EMPLOYEE
-----------------------------------------------------------------------------
-- Purpose:
-- Consolidated View of employee information
-----------------------------------------------------------------------------
CREATE VIEW VIEW_EMPLOYEE AS
SELECT
SUBSTRING(E.EMPLOYID, 3, 3) AS Client_Code,
dbo.FN_GET_EMP_CLIENT_NAME(E.EMPLOYID) AS Client_Name,
dbo.FN_GET_DEPARTMENT_CODE(E.DEPRTMNT) AS Department_Code,
dbo.FN_GET_DEPARTMENT(E.DEPRTMNT) AS Department,
E.EMPLOYID AS Employee_ID,
SUBSTRING(E.SOCSCNUM,1,3) + ''-'' + SUBSTRING(E.SOCSCNUM,4,2) + ''-'' + SUBSTRING(E.SOCSCNUM,6,4) AS SSN,
dbo.FN_GET_EMP_FULL_NAME(E.EMPLOYID) AS Employee_Name,
E.FRSTNAME AS FirstName,
E.LASTNAME AS LastName,
E.MIDLNAME AS Middle,
CASE E.GENDER
WHEN ''1'' THEN ''Male''
WHEN ''2'' THEN ''Female''
ELSE ''Gender not categorized''
END AS Gender,
CASE E.INACTIVE
WHEN ''0'' THEN ''Active''
WHEN ''1'' THEN ''Inactive''
ELSE ''Status not categorized''
END AS Employment_Status,
E.JOBTITLE AS Job_Title,
E.BRTHDATE AS Birth_Date,
E.STRTDATE AS Start_Date,
G.ThinkwareOriginHireDate AS OrigHire_Date,
dbo.FN_GET_EMP_ACCOUNT(E.EMPLOYID) AS Account#,
EG.ETHNIC_GROUP AS Ethnic_Group,
A.ADDRESS1 AS Address1,
ISNULL(H.TW_User_Def_Text_3,'''') AS TW_User_Def_Text_3,
ISNULL(H.TW_User_Def_Text_4,'''') AS TW_User_Def_Text_4,
ISNULL(H.TW_User_Def_Text_5,'''') AS TW_User_Def_Text_5
FROM
UPR00100 E
JOIN UPR00102 A ON E.EMPLOYID = A.EMPLOYID AND (A.ADRSCODE LIKE ''PR%'' OR A.ADRSCODE LIKE ''MA%'')
JOIN ETHNIC_GROUP EG ON E.ETHNORGN = EG.ETHNIC_GROUP_CODE
JOIN TW00820 G ON E.EMPLOYID = G.EMPLOYID
LEFT OUTER JOIN dbo.TW70800 H ON E.EMPLOYID = H.EMPLOYID
WHERE (CASE WHEN A.ADRSCODE = ''MAIN'' THEN CASE WHEN ADDRESS1 = '' '' THEN 0 ELSE 1 END ELSE 1 END) = 1'
SET @sProcText3 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText )
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE' )
END
FETCH NEXT FROM dbnames_cursor INTO @sDatabaseName
END
PRINT('SCRIPT EXECUTION COMPLETE.')
PRINT('-------------------------------------------------------------')
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
USE master
DROP TABLE tmpSQLDBS
DROP TABLE tmpActiveDBS
DROP TABLE tmpDARWINDBS
SET @sProcText3 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText3 + @sProcText )
add the @sproctext3
EXEC (@sProcText3 + @sProcText )
add the @sproctext3
I'm stumped.
"Create View" must be the first statement in a batch. So, you cannot put the "Use" before the "Create View" in the same batch. But, a dynamic SQL string runs as a single batch. So, your between a rock and hard spot.
The only workaround I can think is to hardcode the databases. In other words, like this:
Use database1
construct your dynamic SQL for database1
run it
Use database2
construct your dynamic SQL for database2
run it
and so forth.
I will be monitoring this thread to see if someone has a truely dynamic solution. You should consider deleting this question and rephrasing the question more to address the issue that I have identified.
"Create View" must be the first statement in a batch. So, you cannot put the "Use" before the "Create View" in the same batch. But, a dynamic SQL string runs as a single batch. So, your between a rock and hard spot.
The only workaround I can think is to hardcode the databases. In other words, like this:
Use database1
construct your dynamic SQL for database1
run it
Use database2
construct your dynamic SQL for database2
run it
and so forth.
I will be monitoring this thread to see if someone has a truely dynamic solution. You should consider deleting this question and rephrasing the question more to address the issue that I have identified.
ASKER
Nope, After changing to the above, I get this error now:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'GO'.
Server: Msg 111, Level 15, State 1, Line 9
'CREATE VIEW' must be the first statement in a query batch.
Could it be up at line 47 ?
I was successful running just lines 22-26, 55-114 - replacing the
'USE '+@sDatabaseName with one of the actual database names...
'USE SEL99' - but I need it to loop thru all - hence the other lines...
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'GO'.
Server: Msg 111, Level 15, State 1, Line 9
'CREATE VIEW' must be the first statement in a query batch.
Could it be up at line 47 ?
I was successful running just lines 22-26, 55-114 - replacing the
'USE '+@sDatabaseName with one of the actual database names...
'USE SEL99' - but I need it to loop thru all - hence the other lines...
use this instead
declare @sProcText4 varchar(8000)
SET @sProcText3 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText4 )
declare @sProcText4 varchar(8000)
SET @sProcText3 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText4 )
correction
declare @sProcText4 varchar(8000)
SET @sProcText4 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText4 )
declare @sProcText4 varchar(8000)
SET @sProcText4 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText4 )
actually just use
SET @sProcText3 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText3 )
don't have to declare another var
SET @sProcText3 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText3 )
don't have to declare another var
if you have dynamic sql longer than 8000 chars, you can use this pattern
(example for 3 blocks of 8000)
(example for 3 blocks of 8000)
declare @sDatabaseName varchar(100)
declare @sProcText1 varchar(8000), @sProcText1Replaced varchar(8000)
declare @sProcText2 varchar(8000), @sProcText2Replaced varchar(8000)
declare @sProcText3 varchar(8000), @sProcText3Replaced varchar(8000)
declare @sProcText4 varchar(8000)
--- put something into text1, text2, text3
set @sProcText1Replaced = replace(@sProcText1, '''', '''''')
set @sProcText2Replaced = replace(@sProcText2, '''', '''''')
set @sProcText3Replaced = replace(@sProcText3, '''', '''''')
declare @lastbit char(2) set @lastbit = ''')'
SET @sProcText4 = 'USE '+@sDatabaseName +';'+char(13) + char(10) + 'exec('''
exec (@sproctext4 + @sProcText1Replaced + @sProcText2Replaced + @sProcText3Replaced + @lastbit)ASKER
It always has worked ?? (as far as looping through each database)
How I got here (w/ a problem) was that I had views that exceeded the 4000 character limit (DECLARE @sProcText nvarchar(4000),)
I was told I could break the script into two diff. proctexts and concatenate...
I was also told to try varchar(8000) to give me more characters...
Did that and tested, successfully, with inserting one DBF - adding the ability to loop through is what got me here...
How I got here (w/ a problem) was that I had views that exceeded the 4000 character limit (DECLARE @sProcText nvarchar(4000),)
I was told I could break the script into two diff. proctexts and concatenate...
I was also told to try varchar(8000) to give me more characters...
Did that and tested, successfully, with inserting one DBF - adding the ability to loop through is what got me here...
ASKER
Ok Imitchie - you are sooo close - now the only thing that isn't happening is it isn't dropping first...
remove the first
SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
then replace
SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText)
with
SET @sProcText2 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText2 )
SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
then replace
SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText)
with
SET @sProcText2 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText2 )
ASKER
Besides the dropping snafu - when I add a script to create a view that is bigger then the nvarchar(4000) it doesn't work (I thought using varcar(8000) would give me 2x more room?)
If I take out a few lines it runs (except the drop part)
Am I back to where I started... my 4000 char. limit??
I see you have some code using "replace' - should I try that I presume?
If I take out a few lines it runs (except the drop part)
Am I back to where I started... my 4000 char. limit??
I see you have some code using "replace' - should I try that I presume?
yes please. break it up and DON't use NVARCHAR. just varchar(8000)
ASKER
Right on w/ the DROP fix !!
Just left w/ the 4000 issue :(
Just left w/ the 4000 issue :(
ASKER
Sorry that I'm a little hesitant/slow, but where in my code do these line go exactly... ?
Can you reference the line numbers, etc - I know you have worked EXTREMELY hard on this - and I am VERY appreciative...
Here's the code w/ the view that is too long...
Can you reference the line numbers, etc - I know you have worked EXTREMELY hard on this - and I am VERY appreciative...
Here's the code w/ the view that is too long...
USE master
DROP TABLE tmpSQLDBS
DROP TABLE tmpActiveDBS
DROP TABLE tmpDARWINDBS
SELECT LTRIM(RTRIM(master.dbo.sysdatabases.name)) as SQLName
INTO master.dbo.tmpSQLDBS
FROM master.dbo.sysdatabases
ORDER BY master.dbo.sysdatabases.dbid
SELECT LTRIM(RTRIM(DYNAMICS.DBO.SY01500.INTERID)) as DarwinName
INTO master.dbo.tmpDARWINDBS
FROM DYNAMICS.DBO.SY01500
ORDER BY DYNAMICS.DBO.SY01500.INTERID
SELECT DarwinName, SQLName
INTO master.dbo.tmpActiveDBS
FROM master.dbo.tmpDARWINDBS LEFT OUTER JOIN master.dbo.tmpSQLDBS on master.dbo.tmpDARWINDBS.DarwinName = master.dbo.tmpSQLDBS.SqlName
DECLARE @sProcText varchar(8000),
@sProcText2 varchar(8000),
@sProcText3 varchar(8000),
@sCRLF char(2),
@sTAB char(1)
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
DECLARE dbnames_cursor CURSOR
FOR
SELECT dbo.tmpActiveDBS.DarwinName from dbo.tmpActiveDBS where dbo.tmpActiveDBS.SQLName is NOT Null
OPEN dbnames_cursor
DECLARE @sDatabaseName sysname
FETCH NEXT FROM dbnames_cursor INTO @sDatabaseName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @sDatabaseName = RTRIM(@sDatabaseName)
--SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
PRINT('-------------------------------------------------------------')
PRINT('Creating ESC Reporting views for Database '+@sDatabaseName+'...')
--
-- Drop all the reporting views
--
SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText2 )
PRINT('Drop VIEW for Database '+@sDatabaseName+' View : VIEW_EMPLOYEE_CHECK_DEPT' )
SET @sProcText = '
-----------------------------------------------------------------------------
-- Function : VIEW_EMPLOYEE_CHECK_DEPT
-----------------------------------------------------------------------------
-- Purpose:
-- View of payroll check values broken out
-- for a client, department, check #, check date, employee
-----------------------------------------------------------------------------
CREATE VIEW VIEW_EMPLOYEE_CHECK_DEPT AS
SELECT W.EMPLOYID AS Employee_ID,
dbo.FN_GET_EMP_FULL_NAME(W.EMPLOYID) AS Employee_Name,
ISNULL(dbo.FN_GET_DEPARTMENT_CODE(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT_CODE(MAX(E.DEPRTMNT))) AS Department_Code,
ISNULL(dbo.FN_GET_DEPARTMENT(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT(MAX(E.DEPRTMNT))) AS Department,
dbo.FN_GET_EMP_ACCOUNT(W.EMPLOYID) AS Account#,
MAX(SUBSTRING(E.SOCSCNUM,1,3) + ''-'' + SUBSTRING(E.SOCSCNUM,4,2) + ''-'' + SUBSTRING(E.SOCSCNUM,6,4)) AS SSN,
W.CHEKNMBR AS Check_Number,
MAX(SUBSTRING(W.EMPLOYID, 3, 3)) AS Client_Code,
dbo.FN_GET_EMP_CLIENT_NAME(W.EMPLOYID) AS Client_Name,
MAX(W.CHEKDATE) AS Check_Date,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS REG_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''OVERTIME'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS OT_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''VACATION'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS VAC_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''SICK'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS SICK_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''PERSONAL HOURS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS PERS_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''HOLIDAY HOURS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS HOL_Hrs,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''VACATION EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Vacation,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''BONUS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Bonus,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''TIPS-R'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Tips,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''SICK EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Sick,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''HOLIDAY EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Holiday,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Regular,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''OVERTIME'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS OverTime,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''408P'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS P408,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''408P MATCH'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''3''),0) AS P408_Match,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN2'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan2,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K MATCH'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''3''),0) AS K401_Match,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''403B'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS B403,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''125'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS P125,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''DedU'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS UnReimbersed_Medical,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''DedP'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS Dependent_Care,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''Ded48'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS Parking,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''Ded49'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS Transit,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''DedR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''3''),0) AS Sect125Fee,
ISNULL(dbo.FN_GET_EMP_HOURS_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.CHEKNMBR),0) AS AllHours,
ISNULL(dbo.FN_EMP_CHECK_AMOUNT(W.EMPLOYID, ''TIPS-R'', W.CHEKDATE, W.CHEKNMBR,''1''),0) AS TIPSR,
ISNULL(dbo.FN_EMP_CHECK_AMOUNT(W.EMPLOYID, ''TIPS-C'', W.CHEKDATE, W.CHEKNMBR,''1''),0) AS TIPSC,
ISNULL(dbo.FN_GET_EMP_PAYTYPE_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS Gross
FROM UPR30300 W INNER JOIN UPR00100 E ON W.EMPLOYID = E.EMPLOYID
GROUP BY W.DEPRTMNT, W.EMPLOYID, W.CHEKDATE, W.CHEKNMBR'
SET @sProcText3 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText3 )
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE_CHECK_DEPT' )
END
FETCH NEXT FROM dbnames_cursor INTO @sDatabaseName
END
PRINT('SCRIPT EXECUTION COMPLETE.')
PRINT('-------------------------------------------------------------')
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
USE master
DROP TABLE tmpSQLDBS
DROP TABLE tmpActiveDBS
DROP TABLE tmpDARWINDBSASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neve mind - got it
GREAT JOB !!
THANK YOU !!!
GREAT JOB !!
THANK YOU !!!
search for all places with --++
USE master
DROP TABLE tmpSQLDBS
DROP TABLE tmpActiveDBS
DROP TABLE tmpDARWINDBS
SELECT LTRIM(RTRIM(master.dbo.sysdatabases.name)) as SQLName
INTO master.dbo.tmpSQLDBS
FROM master.dbo.sysdatabases
ORDER BY master.dbo.sysdatabases.dbid
SELECT LTRIM(RTRIM(DYNAMICS.DBO.SY01500.INTERID)) as DarwinName
INTO master.dbo.tmpDARWINDBS
FROM DYNAMICS.DBO.SY01500
ORDER BY DYNAMICS.DBO.SY01500.INTERID
SELECT DarwinName, SQLName
INTO master.dbo.tmpActiveDBS
FROM master.dbo.tmpDARWINDBS LEFT OUTER JOIN master.dbo.tmpSQLDBS on master.dbo.tmpDARWINDBS.DarwinName = master.dbo.tmpSQLDBS.SqlName
DECLARE @sProcText varchar(8000),
@sProcText2 varchar(8000),
@sProcText3 varchar(8000),
@sCRLF char(2),
@sTAB char(1)
declare @lastbit char(2) --++
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
DECLARE dbnames_cursor CURSOR
FOR
SELECT dbo.tmpActiveDBS.DarwinName from dbo.tmpActiveDBS where dbo.tmpActiveDBS.SQLName is NOT Null
OPEN dbnames_cursor
DECLARE @sDatabaseName sysname
FETCH NEXT FROM dbnames_cursor INTO @sDatabaseName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @sDatabaseName = RTRIM(@sDatabaseName)
--SET @sProcText2 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
PRINT('-------------------------------------------------------------')
PRINT('Creating ESC Reporting views for Database '+@sDatabaseName+'...')
--
-- Drop all the reporting views
--
SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE '+@sDatabaseName +';'+char(13) + char(10)
+ 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText2 )
PRINT('Drop VIEW for Database '+@sDatabaseName+' View : VIEW_EMPLOYEE_CHECK_DEPT' )
SET @sProcText = '
-----------------------------------------------------------------------------
-- Function : VIEW_EMPLOYEE_CHECK_DEPT
-----------------------------------------------------------------------------
-- Purpose:
-- View of payroll check values broken out
-- for a client, department, check #, check date, employee
-----------------------------------------------------------------------------
CREATE VIEW VIEW_EMPLOYEE_CHECK_DEPT AS
SELECT W.EMPLOYID AS Employee_ID,
dbo.FN_GET_EMP_FULL_NAME(W.EMPLOYID) AS Employee_Name,
ISNULL(dbo.FN_GET_DEPARTMENT_CODE(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT_CODE(MAX(E.DEPRTMNT))) AS Department_Code,
ISNULL(dbo.FN_GET_DEPARTMENT(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT(MAX(E.DEPRTMNT))) AS Department,
dbo.FN_GET_EMP_ACCOUNT(W.EMPLOYID) AS Account#,
MAX(SUBSTRING(E.SOCSCNUM,1,3) + ''-'' + SUBSTRING(E.SOCSCNUM,4,2) + ''-'' + SUBSTRING(E.SOCSCNUM,6,4)) AS SSN,
W.CHEKNMBR AS Check_Number,
MAX(SUBSTRING(W.EMPLOYID, 3, 3)) AS Client_Code,
dbo.FN_GET_EMP_CLIENT_NAME(W.EMPLOYID) AS Client_Name,
MAX(W.CHEKDATE) AS Check_Date,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS REG_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''OVERTIME'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS OT_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''VACATION'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS VAC_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''SICK'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS SICK_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''PERSONAL HOURS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS PERS_Hrs,
ISNULL(dbo.FN_GET_EMP_DEPT_CHECK_HOUR_TYPE(W.EMPLOYID, ''HOLIDAY HOURS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS HOL_Hrs,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''VACATION EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Vacation,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''BONUS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Bonus,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''TIPS-R'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Tips,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''SICK EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Sick,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''HOLIDAY EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Holiday,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Regular,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''OVERTIME'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS OverTime,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''408P'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS P408,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''408P MATCH'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''3''),0) AS P408_Match,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401,' --++
set @sProcText2 = '
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN2'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan2,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K MATCH'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''3''),0) AS K401_Match,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''403B'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS B403,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''125'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS P125,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''DedU'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS UnReimbersed_Medical,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''DedP'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS Dependent_Care,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''Ded48'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS Parking,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''Ded49'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS Transit,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''DedR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''3''),0) AS Sect125Fee,
ISNULL(dbo.FN_GET_EMP_HOURS_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.CHEKNMBR),0) AS AllHours,
ISNULL(dbo.FN_EMP_CHECK_AMOUNT(W.EMPLOYID, ''TIPS-R'', W.CHEKDATE, W.CHEKNMBR,''1''),0) AS TIPSR,
ISNULL(dbo.FN_EMP_CHECK_AMOUNT(W.EMPLOYID, ''TIPS-C'', W.CHEKDATE, W.CHEKNMBR,''1''),0) AS TIPSC,
ISNULL(dbo.FN_GET_EMP_PAYTYPE_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS Gross
FROM UPR30300 W INNER JOIN UPR00100 E ON W.EMPLOYID = E.EMPLOYID
GROUP BY W.DEPRTMNT, W.EMPLOYID, W.CHEKDATE, W.CHEKNMBR'
SET @sProcText3 = 'USE '+@sDatabaseName +';'+char(13) + char(10) + 'exec(''' --++
set @lastbit = ''')' --++
set @sProcText1 = replace(@sProcText1, '''', '''''') -- prepare for inner exec --++
set @sProcText2 = replace(@sProcText2, '''', '''''') -- prepare for inner exec --++
EXEC ( @sProcText3 + @sProcText1 + @sProcText2 + @lastbit ) --++
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE_CHECK_DEPT' )
END
FETCH NEXT FROM dbnames_cursor INTO @sDatabaseName
END
PRINT('SCRIPT EXECUTION COMPLETE.')
PRINT('-------------------------------------------------------------')
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
USE master
DROP TABLE tmpSQLDBS
DROP TABLE tmpActiveDBS
DROP TABLE tmpDARWINDBS
my pleasure. i learnt something new today... forcing use @databasename.. that one took me a long while to find out
ASKER
Imitchie worked relentlessly - very patient!
SET @sProcText3 = 'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText )
After constructing the USE statement to get you in the correct database, you do not execute it before trying to create the view in the next statement. As a result, the create view fails because the tables it references cannot be found. In other words, you are attempting to create the view in the wrong database.
Thought I'd point out the problem, but still working on a solution.