• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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'.
-- ===========================================================
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

Open in new window

0
Tunkster2
Asked:
Tunkster2
  • 10
  • 8
  • 2
1 Solution
 
dqmqCommented:
I'm pretty sure your problem is here (line 112 of your snippet):

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.


 
0
 
imitchieCommented:
SET @sProcText3 =  'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText3 + @sProcText )

add the @sproctext3
0
 
dqmqCommented:
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.









0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Tunkster2Author Commented:
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...
0
 
imitchieCommented:
use this instead

declare @sProcText4 varchar(8000)
SET @sProcText3 =  'USE '+@sDatabaseName +';'+char(13) + char(10)
 + 'exec(''' + replace(@sProcText, '''', '''''') + ''')'

EXEC ( @sProcText4 )
0
 
imitchieCommented:
correction

declare @sProcText4 varchar(8000)
SET @sProcText4 =  'USE '+@sDatabaseName +';'+char(13) + char(10)
 + 'exec(''' + replace(@sProcText, '''', '''''') + ''')'

EXEC ( @sProcText4 )
0
 
imitchieCommented:
actually just use

SET @sProcText3 =  'USE '+@sDatabaseName +';'+char(13) + char(10)
 + 'exec(''' + replace(@sProcText, '''', '''''') + ''')'
EXEC ( @sProcText3 )

don't have to declare another var
0
 
imitchieCommented:
if you have dynamic sql longer than 8000 chars, you can use this pattern
(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)

Open in new window

0
 
Tunkster2Author Commented:
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...
0
 
Tunkster2Author Commented:
Ok  Imitchie - you are sooo close - now the only thing that isn't happening is it isn't dropping first...
0
 
imitchieCommented:
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 )
0
 
Tunkster2Author Commented:
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?
0
 
imitchieCommented:
yes please. break it up and DON't use NVARCHAR. just varchar(8000)
0
 
Tunkster2Author Commented:
Right on w/ the DROP fix !!
Just left w/ the 4000 issue :(
0
 
Tunkster2Author Commented:
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...
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 tmpDARWINDBS

Open in new window

0
 
imitchieCommented:
if you break a create view into 4 parts due to the 8000 limit and use this

exec ( @part1 + @part2 + @part3 + @part4 )

you would instead use. make sure that @part 1 to 4 are around 7000 or so, because "replace" will increase the size

declare @part1x varchar(8000) set @part1x = replace(@part1, '''', '''''')
declare @part2x varchar(8000) set @part2x = replace(@part2, '''', '''''')
declare @part3x varchar(8000) set @part3x = replace(@part3, '''', '''''')
declare @part4x varchar(8000) set @part4x = replace(@part4, '''', '''''')
declare @lastbit char(2) set @lastbit = ''')'
SET @firstbit =  'USE '+@sDatabaseName +';'+char(13) + char(10) + 'exec('''
 
exec (@firstbit + @part1x + @part2x + @part3x + @part4x + @lastbit)
0
 
Tunkster2Author Commented:
Neve mind - got it
GREAT JOB !!

THANK YOU !!!
0
 
imitchieCommented:
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

Open in new window

0
 
imitchieCommented:
my pleasure. i learnt something new today... forcing use @databasename.. that one took me a long while to find out
0
 
Tunkster2Author Commented:
Imitchie worked relentlessly - very patient!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now