Solved

Error trying to run SQL view creation script

Posted on 2007-11-20
20
258 Views
Last Modified: 2010-04-21
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
Comment
Question by:Tunkster2
  • 10
  • 8
  • 2
20 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
SET @sProcText3 =  'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText3 + @sProcText )

add the @sproctext3
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
use this instead

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

EXEC ( @sProcText4 )
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
correction

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

EXEC ( @sProcText4 )
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
actually just use

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

don't have to declare another var
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
Ok  Imitchie - you are sooo close - now the only thing that isn't happening is it isn't dropping first...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
yes please. break it up and DON't use NVARCHAR. just varchar(8000)
0
 

Author Comment

by:Tunkster2
Comment Utility
Right on w/ the DROP fix !!
Just left w/ the 4000 issue :(
0
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
Neve mind - got it
GREAT JOB !!

THANK YOU !!!
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
my pleasure. i learnt something new today... forcing use @databasename.. that one took me a long while to find out
0
 

Author Closing Comment

by:Tunkster2
Comment Utility
Imitchie worked relentlessly - very patient!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now