Create Database Problem

P

Paul Say

I hope this is the right newsgroup. I have written a program for a database
install. I have a file that contains the T-SQL code to create the database
(I generated the file from SQL2000 Enterprise manager). In the program I
break the file up based on the GO statments within the T-SQL file and
execute each SQL Command seperatly. The program creates most of the objects,
bet it jams up and crashes when try ing to create selected stored
procedures. Below is listed one of the stroed procedures it wont create as
well as part of the code used to break up and execute the T-SQL File.

Are ther limitations on the size of the commandtext string
Or could it be the double quotes in the file that causes the problem if so
how should I handle them

thank you in advance

Paul



Stored Procedure
--------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE RPF_CashFlowRpt_GetAll

@SourceType nvarchar(15),

@ReportType nvarchar(1),

@StartYear Int,

@StartMonth Int,

@ValueColumn nvarchar(25)

as

Declare @Month1 int

Declare @Month2 int

Declare @Month3 int

Declare @Month4 int

Declare @Month5 int

Declare @Month6 int

Declare @Year1 int

Declare @Year2 int

Declare @Year3 int

Declare @Year4 int

Declare @Year5 int

Declare @Year6 int



create table #CashTran2 (

SourceType Nvarchar(15),

SourceTransNo int,

Value1 Float,

Value2 Float,

Value3 Float,

Value4 Float,

Value5 Float,

Value6 Float

)

if @ReportType ='Y'

Begin

Set @Year1 = @StartYear

Set @Year2 = @StartYear + 1

Set @Year3 = @StartYear + 2

Set @Year4 = @StartYear + 3

Set @Year5 = @StartYear + 4

Set @Year6 = @StartYear + 5

if @ValueColumn = "Revenue"

insert into #CashTran2 SELECT sourcetype,sourceTransNo,

SUM(CASE CFYear WHEN @Year1 THEN Revenue ELSE 0 END) AS Value1,

SUM(CASE CFYear WHEN @Year2 THEN Revenue ELSE 0 END) AS Value2,

SUM(CASE CFYear WHEN @Year3 THEN Revenue ELSE 0 END) AS Value3,

SUM(CASE CFYear WHEN @Year4 THEN Revenue ELSE 0 END) AS Value4,

SUM(CASE CFYear WHEN @Year5 THEN Revenue ELSE 0 END) AS Value5,

SUM(CASE CFYear WHEN @Year6 THEN Revenue ELSE 0 END) AS Value6

FROM CashFlowYear

Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType

GROUP BY SourceType,SourceTransNo

else if @ValueColumn = "Expenses"

insert into #CashTran2 SELECT sourcetype,sourceTransNo,

SUM(CASE CFYear WHEN @Year1 THEN Expenses ELSE 0 END) AS Value1,

SUM(CASE CFYear WHEN @Year2 THEN Expenses ELSE 0 END) AS Value2,

SUM(CASE CFYear WHEN @Year3 THEN Expenses ELSE 0 END) AS Value3,

SUM(CASE CFYear WHEN @Year4 THEN Expenses ELSE 0 END) AS Value4,

SUM(CASE CFYear WHEN @Year5 THEN Expenses ELSE 0 END) AS Value5,

SUM(CASE CFYear WHEN @Year6 THEN Expenses ELSE 0 END) AS Value6

FROM CashFlowYear

Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType

GROUP BY SourceType,SourceTransNo

else if @ValueColumn = "Net Profit"

insert into #CashTran2 SELECT sourcetype,sourceTransNo,

SUM(CASE CFYear WHEN @Year1 THEN (Revenue - Expenses) ELSE 0 END) AS Value1,

SUM(CASE CFYear WHEN @Year2 THEN (Revenue - Expenses) ELSE 0 END) AS Value2,

SUM(CASE CFYear WHEN @Year3 THEN (Revenue - Expenses) ELSE 0 END) AS Value3,

SUM(CASE CFYear WHEN @Year4 THEN (Revenue - Expenses) ELSE 0 END) AS Value4,

SUM(CASE CFYear WHEN @Year5 THEN (Revenue - Expenses) ELSE 0 END) AS Value5,

SUM(CASE CFYear WHEN @Year6 THEN (Revenue - Expenses) ELSE 0 END) AS Value6

FROM CashFlowYear

Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType

GROUP BY SourceType,SourceTransNo

else if @ValueColumn = "To Receive"

insert into #CashTran2 SELECT sourcetype,sourceTransNo,

SUM(CASE CFYear WHEN @Year1 THEN ToReceive ELSE 0 END) AS Value1,

SUM(CASE CFYear WHEN @Year2 THEN ToReceive ELSE 0 END) AS Value2,

SUM(CASE CFYear WHEN @Year3 THEN ToReceive ELSE 0 END) AS Value3,

SUM(CASE CFYear WHEN @Year4 THEN ToReceive ELSE 0 END) AS Value4,

SUM(CASE CFYear WHEN @Year5 THEN ToReceive ELSE 0 END) AS Value5,

SUM(CASE CFYear WHEN @Year6 THEN ToReceive ELSE 0 END) AS Value6

FROM CashFlowYear

Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType

GROUP BY SourceType,SourceTransNo

else if @ValueColumn = "To Pay"

insert into #CashTran2 SELECT sourcetype,sourceTransNo,

SUM(CASE CFYear WHEN @Year1 THEN ToPay ELSE 0 END) AS Value1,

SUM(CASE CFYear WHEN @Year2 THEN ToPay ELSE 0 END) AS Value2,

SUM(CASE CFYear WHEN @Year3 THEN ToPay ELSE 0 END) AS Value3,

SUM(CASE CFYear WHEN @Year4 THEN ToPay ELSE 0 END) AS Value4,

SUM(CASE CFYear WHEN @Year5 THEN ToPay ELSE 0 END) AS Value5,

SUM(CASE CFYear WHEN @Year6 THEN ToPay ELSE 0 END) AS Value6

FROM CashFlowYear

Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType

GROUP BY SourceType,SourceTransNo

else if @ValueColumn = "Net Cash"

insert into #CashTran2 SELECT sourcetype,sourceTransNo,

SUM(CASE CFYear WHEN @Year1 THEN (ToReceive-ToPay) ELSE 0 END) AS Value1,

SUM(CASE CFYear WHEN @Year2 THEN (ToReceive-ToPay) ELSE 0 END) AS Value2,

SUM(CASE CFYear WHEN @Year3 THEN (ToReceive-ToPay) ELSE 0 END) AS Value3,

SUM(CASE CFYear WHEN @Year4 THEN (ToReceive-ToPay) ELSE 0 END) AS Value4,

SUM(CASE CFYear WHEN @Year5 THEN (ToReceive-ToPay) ELSE 0 END) AS Value5,

SUM(CASE CFYear WHEN @Year6 THEN (ToReceive-ToPay) ELSE 0 END) AS Value6

FROM CashFlowYear

Where (CFYear >= @Year1 AND CFYear <= @Year6) and SourceType = @SourceType

GROUP BY SourceType,SourceTransNo

End

Else If @ReportType = 'M'

Begin

Set @Month1 = @StartMonth

Set @Year1 = @StartYear

Set @Month2 = @Month1 + 1

Set @Year2 = @Year1

if @Month2 > 12

Begin

Set @Month2 = @Month2 - 12

Set @Year2 = @Year1 + 1

end

Set @Month3 = @Month2 + 1

Set @Year3 = @Year2

if @Month3 > 12

Begin

Set @Month3 = @Month3 - 12

Set @Year3 = @Year2 + 1

end

Set @Month4 = @Month3 + 1

Set @Year4 = @Year3

if @Month4 > 12

Begin

Set @Month4 = @Month4 - 12

Set @Year4 = @Year3 + 1

end



Set @Month5 = @Month4 + 1

Set @Year5 = @Year4

if @Month5 > 12

Begin

Set @Month5 = @Month5 - 12

Set @Year5 = @Year4 + 1

end



Set @Month6 = @Month5 + 1

Set @Year6 = @Year5

if @Month6 > 12

Begin

Set @Month6 = @Month6 - 12

Set @Year6 = @Year5 + 1

end



if @ValueColumn = "Revenue"

insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,

SUM(CASE CFMonth WHEN @Month1 THEN m.Revenue ELSE 0 END) AS Value1,

SUM(CASE CFMonth WHEN @Month2 THEN m.Revenue ELSE 0 END) AS Value2,

SUM(CASE CFMonth WHEN @Month3 THEN m.Revenue ELSE 0 END) AS Value3,

SUM(CASE CFMonth WHEN @Month4 THEN m.Revenue ELSE 0 END) AS Value4,

SUM(CASE CFMonth WHEN @Month5 THEN m.Revenue ELSE 0 END) AS Value5,

SUM(CASE CFMonth WHEN @Month6 THEN m.Revenue ELSE 0 END) AS Value6

FROM CashFlowMonth m

JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo

Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType

GROUP BY y.SourceType,y.SourceTransNo

else if @ValueColumn = "Expenses"

insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,

SUM(CASE CFMonth WHEN @Month1 THEN m.Expenses ELSE 0 END) AS Value1,

SUM(CASE CFMonth WHEN @Month2 THEN m.Expenses ELSE 0 END) AS Value2,

SUM(CASE CFMonth WHEN @Month3 THEN m.Expenses ELSE 0 END) AS Value3,

SUM(CASE CFMonth WHEN @Month4 THEN m.Expenses ELSE 0 END) AS Value4,

SUM(CASE CFMonth WHEN @Month5 THEN m.Expenses ELSE 0 END) AS Value5,

SUM(CASE CFMonth WHEN @Month6 THEN m.Expenses ELSE 0 END) AS Value6

FROM CashFlowMonth m

JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo

Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType

GROUP BY y.SourceType,y.SourceTransNo

else if @ValueColumn = "To Pay"

insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,

SUM(CASE CFMonth WHEN @Month1 THEN m.ToPay ELSE 0 END) AS Value1,

SUM(CASE CFMonth WHEN @Month2 THEN m.ToPay ELSE 0 END) AS Value2,

SUM(CASE CFMonth WHEN @Month3 THEN m.ToPay ELSE 0 END) AS Value3,

SUM(CASE CFMonth WHEN @Month4 THEN m.ToPay ELSE 0 END) AS Value4,

SUM(CASE CFMonth WHEN @Month5 THEN m.ToPay ELSE 0 END) AS Value5,

SUM(CASE CFMonth WHEN @Month6 THEN m.ToPay ELSE 0 END) AS Value6

FROM CashFlowMonth m

JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo

Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType

GROUP BY y.SourceType,y.SourceTransNo

else if @ValueColumn = "To Receive"

insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,

SUM(CASE CFMonth WHEN @Month1 THEN m.ToReceive ELSE 0 END) AS Value1,

SUM(CASE CFMonth WHEN @Month2 THEN m.ToReceive ELSE 0 END) AS Value2,

SUM(CASE CFMonth WHEN @Month3 THEN m.ToReceive ELSE 0 END) AS Value3,

SUM(CASE CFMonth WHEN @Month4 THEN m.ToReceive ELSE 0 END) AS Value4,

SUM(CASE CFMonth WHEN @Month5 THEN m.ToReceive ELSE 0 END) AS Value5,

SUM(CASE CFMonth WHEN @Month6 THEN m.ToReceive ELSE 0 END) AS Value6

FROM CashFlowMonth m

JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo

Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType

GROUP BY y.SourceType,y.SourceTransNo



else if @ValueColumn = "Net Profit"

insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,

SUM(CASE CFMonth WHEN @Month1 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value1,

SUM(CASE CFMonth WHEN @Month2 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value2,

SUM(CASE CFMonth WHEN @Month3 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value3,

SUM(CASE CFMonth WHEN @Month4 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value4,

SUM(CASE CFMonth WHEN @Month5 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value5,

SUM(CASE CFMonth WHEN @Month6 THEN (m.Revenue - m.Expenses) ELSE 0 END) AS
Value6

FROM CashFlowMonth m

JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo

Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType

GROUP BY y.SourceType,y.SourceTransNo



else if @ValueColumn = "Net Cash"

insert into #CashTran2 SELECT y.sourcetype,y.sourceTransNo,

SUM(CASE CFMonth WHEN @Month1 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value1,

SUM(CASE CFMonth WHEN @Month2 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value2,

SUM(CASE CFMonth WHEN @Month3 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value3,

SUM(CASE CFMonth WHEN @Month4 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value4,

SUM(CASE CFMonth WHEN @Month5 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value5,

SUM(CASE CFMonth WHEN @Month6 THEN (m.ToReceive - m.ToPay) ELSE 0 END) AS
Value6

FROM CashFlowMonth m

JOIN CashFlowYear y ON m.CFYTransNo = y.TransNo

Where ((y.CFYear = @Year1 AND m.CFMonth = @Month1) OR (y.CFYear = @Year2 AND
m.CFMonth = @Month2) OR (y.CFYear = @Year3 AND m.CFMonth = @Month3) OR
(y.CFYear = @Year4 AND m.CFMonth = @Month4) OR (y.CFYear = @Year5 AND
m.CFMonth = @Month5) OR (y.CFYear = @Year6 AND m.CFMonth = @Month6) ) and
y.SourceType = @SourceType

GROUP BY y.SourceType,y.SourceTransNo

End

Select ct.*,st.SourceId, st.[Description] From

#CashTran2 ct Join

(

select 'Job' as SourceType, Transno as SourceTransNo, Job as SourceId,
[Description] From Job

Union

select 'Opportunity' as SourceType, Transno as SourceTransNo, OpportunityId,
[Description] From Opportunity

Union

select 'Sundry' as SourceType, Transno as SourceTransNo, GLAccount,
[Description] From GLAccount

) st

on ct.SourceType = st.SourceType and ct.SourceTransNo = st.SourceTransNo



SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO



CODE SEGMENT

-------------------------------------------------------------------------------------------------

Protected Sub ExecuteScript(ByVal strDBName As String, ByVal ScriptFile As
String)

Dim scriptString As StreamReader = GetSqlScript(ScriptFile)

Dim strCommand As String = String.Empty

Dim strLine As String = String.Empty

strLine = scriptString.ReadLine()

While Not strLine Is Nothing

If Trim(strLine) = "GO" Then

ExecuteSql(strDBName, strCommand)

strCommand = ""

Else

If Len(Trim(strLine)) > 1 Then

If Len(strCommand) > 1 Then

strCommand = strCommand & vbCrLf

End If

strCommand = strCommand

End If

End If

strLine = scriptString.ReadLine()

End While

scriptString.Close()

End Sub

Private Function GetSqlScript(ByVal Name As String) As StreamReader

Try

' Gets the current assembly.

Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()

' Resources are named using a fully qualified name.

Dim strm As Stream = Asm.GetManifestResourceStream(Asm.GetName().Name + "."
+ Name)

' Reads the contents of the embedded file.

Dim reader As StreamReader = New StreamReader(strm)

Return reader

Catch ex As Exception

MsgBox("In GetSQL: " & ex.Message)

Throw ex

End Try

End Function



Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)

Dim Command As New SqlClient.SqlCommand(Sql, sqlConnection1)

Command.Connection.Open()

Command.Connection.ChangeDatabase(DatabaseName)

Try

Command.ExecuteNonQuery()

Finally

' Finally, blocks are a great way to ensure that the connection

' is always closed.

Command.Connection.Close()

End Try

End Sub
 
M

Miha Markic [MVP C#]

What does error say?
How are you passing quoted text to commandtext?
 
P

Paul Say

The errors vary and are inconsistant usually say incorrect syntax near XXXXX

I have found that doubble quotes within the file cause errors also any lines
in the text file that contain only bracket are read as empty strings why
would this happen and is there a workaround.

i.e

Insert Sometable
( this line is skiped
UserName,
UserId
) values (
'bill',
'billyboy'
) this line is skiped


Paul


Miha Markic said:
What does error say?
How are you passing quoted text to commandtext?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Paul Say said:
I hope this is the right newsgroup. I have written a program for a
database install. I have a file that contains the T-SQL code to create
the database (I generated the file from SQL2000 Enterprise manager). In
the program I break the file up based on the GO statments within the T-SQL
file and execute each SQL Command seperatly. The program creates most of
the objects, bet it jams up and crashes when try ing to create selected
stored procedures. Below is listed one of the stroed procedures it wont
create as well as part of the code used to break up and execute the T-SQL
File.

Are ther limitations on the size of the commandtext string
Or could it be the double quotes in the file that causes the problem if
so how should I handle them

thank you in advance
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help Using Calculated Field or Item in Pivot Table 1
Transforming access data in rows to columns 1
Summing for a report 2
VLookup: Return Multiple Columns? 6
Finding records that have 12 consecutive values. 1
Maintaining cell 'variable' values even after the the macro has en 1
Manual Trend lines 1
Nested if returning error 1004 2

Top