Solved

Error trying to run SQL view creation script

Posted on 2007-11-20
20
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 2
20 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 20322367
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
ID: 20322395
SET @sProcText3 =  'USE '+@sDatabaseName +char(13) + char(10) + 'GO'
EXEC (@sProcText3 + @sProcText )

add the @sproctext3
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20322510
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Tunkster2
ID: 20322580
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
ID: 20322868
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
ID: 20322902
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
ID: 20322922
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
ID: 20322946
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
ID: 20322971
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
ID: 20323058
Ok  Imitchie - you are sooo close - now the only thing that isn't happening is it isn't dropping first...
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20323134
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
ID: 20323219
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
ID: 20323313
yes please. break it up and DON't use NVARCHAR. just varchar(8000)
0
 

Author Comment

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

Author Comment

by:Tunkster2
ID: 20323389
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
ID: 20323400
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
ID: 20323441
Neve mind - got it
GREAT JOB !!

THANK YOU !!!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20323446
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
ID: 20323457
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
ID: 31410178
Imitchie worked relentlessly - very patient!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

749 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