Create Table SQL 2000->2005

I have some simple sql that I use to create tables in a database in 2000. Have done it many times, many sql 2000 instances are fresh installs. Now on a 2005 fresh install it doesn't work<br>

USE MyDB
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE "care"."MyObjects"
(
"DId" integer NULL ,
"MyObjectID" integer NOT NULL ,
"idType" integer NULL ,
"name" varchar(64) NULL ,
"description" varchar(1024) NULL ,
CONSTRAINT "pk_MyObject" PRIMARY KEY NONCLUSTERED ("MyObjectID"),
)

go

I get "The specified schema name "care" either does not exist or you do
not have permission to use it."

I have used the db owner, sa both to try and create the tables using 90/Tools/Binn/OSQL.exe but no go. What am I missing ?

Thanks

The obvious answer is that the schema care does not exist. Schemas are now fully supported in SQL Server 2005, and no longer reflect the user in the pseudo implementation for SQL 2000. You may wish to read the relevant topics in Books Online and ensure you methods and usage of them is still appropriate.

Instead of using osql you may also wish to try using sqlcmd as this is the replacement going forwards.

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create Difference Query

I have a table that has participant scores for each performance and I want
to select all those instances where the score changed by 3.0 or more (up or
down) points in a 2 w period. A participant may be scored twice on any
given day. To try to clarify, the table includes the following basic
information:
Unit Name
Performance Date
Score
What I want to get is:
"Unit A1", 3/12/2005, 57.7
"Unit A1", 3/13/2005, 62.4
"Unit A2", 3/5/2005, 62.2
"Unit A2", 3/5/2005, 63.5
"Unit A2", 3/12/2005, 58.8
etc.
I cannot get my head around this? Should I look at an SP instead of just a
query?
Any help is much appreciated.
WayneBTW, here is what I am trying right now:
Select Distinct y.Unit, y.Class, y.Contest, y.Date, y.[Gross Score] From YTD
y
Inner Join YTD y2 on y.Unit = y2.Unit
Where (ABS(y.[Gross Score]-y2.[Gross Score]) > 3)
AND y.Class = y2.Class
AND DATEDIFF(day, y.[Date], y2.[date]) < 14
Order By y.Class, y.Unit, y.[Date]
But the result set is including cases where the score difference is less
than 3?
Wayne
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:uqCNGC$JFHA.2212@.TK2MSFTNGP12.phx.gbl...
> I have a table that has participant scores for each performance and I want
> to select all those instances where the score changed by 3.0 or more (up
or
> down) points in a 2 w period. A participant may be scored twice on any
> given day. To try to clarify, the table includes the following basic
> information:
> Unit Name
> Performance Date
> Score
> What I want to get is:
> "Unit A1", 3/12/2005, 57.7
> "Unit A1", 3/13/2005, 62.4
> "Unit A2", 3/5/2005, 62.2
> "Unit A2", 3/5/2005, 63.5
> "Unit A2", 3/12/2005, 58.8
> etc.
> I cannot get my head around this? Should I look at an SP instead of just a
> query?
> Any help is much appreciated.
> Wayne
>|||On Sun, 13 Mar 2005 11:24:41 -0700, Wayne Wengert wrote:

>BTW, here is what I am trying right now:
>Select Distinct y.Unit, y.Class, y.Contest, y.Date, y.[Gross Score] From YT
D
>y
>Inner Join YTD y2 on y.Unit = y2.Unit
>Where (ABS(y.[Gross Score]-y2.[Gross Score]) > 3)
>AND y.Class = y2.Class
>AND DATEDIFF(day, y.[Date], y2.[date]) < 14
>Order By y.Class, y.Unit, y.[Date]
>But the result set is including cases where the score difference is less
>than 3?
Hi Wayne,
Am I correct that you didn't get a reply yet? Or did my newsreader miss
it?
Anyway, my first guess would be to add
AND y2.[date] > y.[date]
somewhere in the query.
If that doesn't fix it, then I need to be able to reproduce it. For
that, I need a CREATE TABLE statement (including all constraints and
properties, but excluding irrelevant columns), some chosen rows of
sample data (as INSERT statements - and make sure to include both rows
that should and rows that should not be returned), and the expected
output. Check out www.aspfaq.com/5006 as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the reply Hugo. Yours is the only one I got. I ended up breaking
it into two queries and added the results to a temp table. Kludgy but it got
the job done.
Maybe later I'll come back to see if I can figure out the right way to do
it.
Wayne
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7poe31l1icu89gd1q7a8lsj1lnclum2eb2@.
4ax.com...
> On Sun, 13 Mar 2005 11:24:41 -0700, Wayne Wengert wrote:
>
YTD
> Hi Wayne,
> Am I correct that you didn't get a reply yet? Or did my newsreader miss
> it?
> Anyway, my first guess would be to add
> AND y2.[date] > y.[date]
> somewhere in the query.
> If that doesn't fix it, then I need to be able to reproduce it. For
> that, I need a CREATE TABLE statement (including all constraints and
> properties, but excluding irrelevant columns), some chosen rows of
> sample data (as INSERT statements - and make sure to include both rows
> that should and rows that should not be returned), and the expected
> output. Check out www.aspfaq.com/5006 as well.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)