Data source with a type of SSIS

I know that you can do this, because I have read about it in MSDN, but I'm not suer about the syntax.

I am creating a report that needs an SSIS type data source. I have deleted the comments in the relevant config files both on my local machine (development) and the reporting services server, and can see SSIS as data type. Now for the tricky bit!

I go to create a new data source. Give it a name, choose SSIS as a data type, and now for the connection string. I "think" I know what I would do if it were a file system SSIS package, but it is stored in the MSDB. What connection string do I use to connect to this data source? There are no DTSX files on the server. Somehow, I presume, I have to talk directly to the MSDB..... Any ideas anyone?

OK, so I gave up on trying to get data out of the MSDB! I capitulated to the MSDN way of doing things and saved my SSIS package as a file system object and attached in that way.

Works like a dream - surprisingly fast. I wonder how it will perform (if at all) when using report snapshots!

If anyone has been successful in reporting over an SSIS, MSDB data source, I would appreciate it if you could post the solution here.

Data script

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Picture of type image so the
insert is wrong in this way. How do i correct it?

/*****MarinLIf TAble ********************/
DECLARE @.ID INT
DECLARE @.typeID INT
DECLARE @.NAME NVARCHAR(50)
Declare @.scName nvarchar(50)
Declare @.distribution nvarchar(600)
Declare @.maxSize nvarchar(200)
Declare @.env nvarchar(200)
Declare @.climate nvarchar(200)
Declare @.country nvarchar(2000)
Declare @.desc nvarchar(4000)
Declare @.pic image

DECLARE CURS CURSOR STATIC FOR
SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic
FROM MarinLif
OPEN CURS
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
PRINT 'SET IDENTITY_INSERT MarinLif ON'
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic)
VALUES (' + convert(varchar,@.ID) + ','
+ convert(varchar,@.typeID) + ','
+ '''' + @.NAME + '''' +
+ '''' + @.scName + '''' + ','
+ '''' + @.distribution + '''' + ','
+ '''' + @.maxSize + '''' + ','
+ '''' + @.env + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.country + '''' + ','
+ '''' + @.desc + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.pic + '''' + ')'
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
END
PRINT 'SET IDENTITY_INSERT MarinLif OFF'
CLOSE CURS
DEALLOCATE CURS

I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!MC (anonymous@.discussions.microsoft.com) writes:
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>...
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?

You can't assign to image variables, so this approach is not going
to work.

You are probably better off using BCP, a command-line which is designed
for importing and exporting data. In this case you could try:

bcp yourdb..MarinLif out MarinLif.bcp -N -T -S source_server
bcp yourotherdb..MarinLif in MarinLif.bcp -N -T -S target_server

-N here means that you are using native datatypes with Unicode. -T is for
trusted connection. -S specifies the server.

For -N to work, the tables must be identical, including column order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erland's response, if both databases are on the same server, you
can use the INSERT INTO... SELECT syntax:

INSERT INTO MyOtherDatabase.dbo.MarinLif
(
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
)
SELECT
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
FROM MyDatabase.dbo.MarinLif

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <anonymous@.discussions.microsoft.com> wrote in message
news:40c4c158$0$165$c397aba@.news.newsgroups.ws...
>
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>
> /*****MarinLIf TAble ********************/
> DECLARE @.ID INT
> DECLARE @.typeID INT
> DECLARE @.NAME NVARCHAR(50)
> Declare @.scName nvarchar(50)
> Declare @.distribution nvarchar(600)
> Declare @.maxSize nvarchar(200)
> Declare @.env nvarchar(200)
> Declare @.climate nvarchar(200)
> Declare @.country nvarchar(2000)
> Declare @.desc nvarchar(4000)
> Declare @.pic image
> DECLARE CURS CURSOR STATIC FOR
> SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic
> FROM MarinLif
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> PRINT 'SET IDENTITY_INSERT MarinLif ON'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic)
> VALUES (' + convert(varchar,@.ID) + ','
> + convert(varchar,@.typeID) + ','
> + '''' + @.NAME + '''' +
> + '''' + @.scName + '''' + ','
> + '''' + @.distribution + '''' + ','
> + '''' + @.maxSize + '''' + ','
> + '''' + @.env + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.country + '''' + ','
> + '''' + @.desc + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.pic + '''' + ')'
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> END
> PRINT 'SET IDENTITY_INSERT MarinLif OFF'
> CLOSE CURS
> DEALLOCATE CURS
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Data Retrieval From Online Database - Paid Consultant Required

Hi,

I wonder if anyone can help with the following on a fee paying basis
for the the design or development of some type of script or utility or
piece of code to do the following work.

I wish to retrieve some data from an online database that is in the
public domain. The online database has a search facility that matches
the entry (name) in a search box then returns a screen stating that a
match has been found or not found.

If a match is found there is a button to click that proceeds to the
screen containing the data which is simply two names. It is these two
names that I wish to retrieve and store them in something like a text
file where they are associated with the original entry (name) used in
the search box.

I have a list of the entries for the search box that can be supplied
in sample format for testing as a columnar text file or comma
delimited file or a spreadsheet.. I would need the procedure to
process the list of search entries, retrieve the data then move on to
the next one in the list. Obviously, if a match was not found then the
process would need to move on the the next entry in the list with
perhaps a message saying "No Match" until the entire list was
processed.

The PC I hope to run the process on is a Windows XP machine and if I
need to purchase any particular software that is necessary for the job
then I am quite willing to do so. Also, as I pointed out above I would
pay for the work to be done.

I hope that I have explained the above OK and that I have posted it to
the correct newsgroup(s). If it is not in the correct newsgroup I
would be grateful if anyone could point me in the right direction.

Regards
Dave GibsonReplied in microsoft.public.sqlserver.programming.

--
David Portas
SQL Server MVP
--sql

Data Overflow

Hi,

I would like to know if we can define an unsigned integer data type in SQL Server 2005. We have a situation where one of the integer columns will reach its 2 billion limit. I want to know if there's any way in which I can extend this to say 4 billion by making the data type unsigned or any other way which doesn't require me to change the data type to bigint.

Any help is highly appreciated.

TIA

Ritesh

Unfortunately, there isn't. If you are able to figure out a way to use the negative numbers then you may be OK, otherwise, it might be time for a bigint.

Data of type SQLBIT always 1?

Hello all,

Apologies if this is posted in the wrong forum, however I wasn't exactly sure as to which one would be the best for my query...

I have recently inherited the job of maintaining a database of internet logs and I am using the bcp utility to bulk insert rows into the database. The problem is that for the fields which are classified as SQLBIT in the .fmt file (and the field in the database is bit) are always inserted as '1' even if the data is actually a '0'. Any value will be inserted into the database as a 1 except for NULL which is inserted as NULL.

I tested changing the type to SQLCHAR in the .fmt file, and this allows the insert of a 0 into the database.

Does anyone have any ideas as to why this is happening? The database is SQL Server Standard Edition running on NT 4.0.

Any help/suggestions would be greatly appreciated.

Regards,
Daniel

Is your data file native/binary or character? It appears it is character, so you would use SQLCHAR and the characters '0' or '1' to represent the bit value. If your file is native (binary), then there would be a byte in it with a binary zero, 0x00, to represent a zero bit, or anything not zero to represent a 1 bit, and you'd use SQLBIT. If you use SQLBIT with a character file, since both “0” and “1” are not binary zeros, BCP treats them both as 1s. In order for SQLBIT format file work correctly, the corresponding field in the data file should contain binary 0 instead of character ‘0’.

Hope that explains it for you,

Don|||Hi Don,

yes, that makes perfect sense...thanks very much for your help! I'll insert them as a type SQLCHAR.

Cheers,
Daniel

Data not displayed in Preview Tab, but is displayed in Data Tab

This has happened on more than one occasion. I have defined a stored
procedure type datasource with 12 parameters. When I execute the SP in the
Data tab, the resulting rows are correctly displayed. When I use the same
parameters when previewing the report, no data is displayed. I have found
that empty string parameters need to be <blank> on the data tab, but need to
be '' on the preview tab, so I know about this little gotcha.
Has anyone else had this problem and found a solution. I have check the rdl
file to make sure that the datasource and fields are correct. I have a table
and a pie chart on the report and both are empty when I preview it.
===================== Thanks!
Keith
p.s. Get off the couch and exercise!I have answered my own question.
This might come in handy for a few people. If one of your parameters is a
string and the SP allows an empty string, then make sure to check the
Parameter definition box "Allow Blank Value". otherwise if you try to send
it a blank or '' (empty string), then SQL profiler shows the parameter as
N'''. This solved my problem and I am now getting data.
=====================Thanks!
Keith
p.s. Get off the couch and exercise!
"Keith" wrote:
> This has happened on more than one occasion. I have defined a stored
> procedure type datasource with 12 parameters. When I execute the SP in the
> Data tab, the resulting rows are correctly displayed. When I use the same
> parameters when previewing the report, no data is displayed. I have found
> that empty string parameters need to be <blank> on the data tab, but need to
> be '' on the preview tab, so I know about this little gotcha.
> Has anyone else had this problem and found a solution. I have check the rdl
> file to make sure that the datasource and fields are correct. I have a table
> and a pie chart on the report and both are empty when I preview it.
>
> =====================> Thanks!
> Keith
> p.s. Get off the couch and exercise!

Data Modeling: Type + SubTypes

Suppose I have a database that includes 5 different "types" of people, and
each person could simultaneously be of multiple types (the types don't
really matter... so pretend it has doctors, lawyers, janitors, students, and
customers).
My Question: In general, is it perfectly acceptable in a scenario like this
to have one People table for common attributes and then 5 additional "person
type-specific" tables (e.g., a Doctors table, Lawyers, Janitors, Students,
and Customers tables) that each hold type-specific attributes?
Or is there a *general* recommendation to go with 5 tables total and
duplicate the common attributes amongst the tables.
Please understand my objective is to come up with a solid relational
database design - NOT simply to emulate the object-oriented programming
concept of inheritance in my database; and yes, I do understand the issues
related to choice of primary key (my plan is to use an IDENTITY property to
glue the People table with the various "person type" tables, and then have a
UNIQUE constraint on the "real" natural keys per "person type").
Thanks!"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>
Your first suggestion is better. The following article explains how and why
tables should not duplicate each other (more precisely, their predicates
should not overlap):
http://www.dbdebunk.com/page/page/622331.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Why do I find that amusing :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23CQjX2eUGHA.1564@.TK2MSFTNGP11.phx.gbl...
> http://www.dbdebunk.com|||Think about it this way and it might be clearer:
People are people and have attributes that describe them as a person (Name,
DOB, etc)
People can play different roles such as Doctor, Lawyer, etc
So, you would likely have a table named Person to store those attributes
that are about the person
Then, seperate tables for each role the person may play
"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>

Data Migration Error - SQL TYPE Variant data

I am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?

Thanks,

Jay

If you explain how you are trying to do the migration and what the error message is then maybe someone will be able to help.

-Jamie

Data Migration

I have 2 Tables

Table 1 has

Id

Type

Table 2 has

Id

Type

PartNumber

I need to migrate data from Table 1 to Table 2 based on Type.

Type can be 1 or 2.

Example of Source.

Table 1 has

Id Type

1 1

2 1

3 1

3 2

Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different.

Example of Destination.

Id Type PartNumber

1 1 10

1 2 10

2 1 11

2 2 11

3 1 12

3 2 13

================================

As you see above

1. I need to duplicate records which had just one type in the Source table.

1 1 10

1 2 10

I will have 2 (1 and 2) types now, but everything else the same for the same id.

In my case I will have Part Numbers as guid. I would create them on my own. They will be the same here.

2. I need to have 2 records as it was before in Source table for id's with 2 types, but with different Part Numbers.

3 1 12

3 2 13

In my case I will have Part Numbers as guid. I would create them on my own.They will be different here.

How do I create 2 records for each Id and certain rules for them? How do I find if there is one or there are two records in Source and how do I apply my rules in looping through records? What control in SSIS tool can I use?

Thanks.

This looks like a job for T-SQL, not SSIS. If you can elaborate on the "Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different" statement, I may be able to help more. The logic you describe here isn't precise enough to implement (or perhaps I'm just not following it correctly) but this looks like a simple INSERT .. SELECT to me.|||

Can you give me more details? I think you understand correctly.

Look at my examples. It describes what I need.

Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.

In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.

Please let me know if you have more questions.

|||

Vita wrote:

Can you give me more details? I think you understand correctly.

Look at my examples. It describes what I need.

Table 2 should have 2 records for each Id. Part Number will be the same if Source has just one type = 1. Part Number will be different if the Source has type 1 and 2.

In my case Part Number will be guid (uniqueidentifier) which I need to create on the fly. If Part Number will be the same for 2 records - I would need to use the same guid.

Please let me know if you have more questions.

"Part Number will be different" is what was unclear to me. Different in what way? In each case, what should it be?

I think your most recent post provides the needed clarity - let me play with it for a while...

|||

Ok... that was more annoying than I figured - it was the NEWID() function that was giving me trouble, so I needed to use a temp table to get the desired results. Here's the deal:

Code Snippet

USE tempdb

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))

DROP TABLE [dbo].[Table1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table2]') AND type in (N'U'))

DROP TABLE [dbo].[Table2]

GO

CREATE TABLE Table1

(

Id INT NOT NULL,

Type INT NOT NULL

)

go

CREATE TABLE Table2

(

Id INT NOT NULL,

Type INT NOT NULL,

PartNumber UNIQUEIDENTIFIER NULL

)

GO

INSERT INTO Table1 VALUES (1, 1)

INSERT INTO Table1 VALUES (2, 1)

INSERT INTO Table1 VALUES (3, 1)

INSERT INTO Table1 VALUES (3, 2)

GO

WITH IdExistsOnceCTE

AS

(

SELECT Id

,MIN (Type) AS Type

,NEWID() AS PartNumber

FROM Table1

GROUP BY Id

HAVING COUNT (Type) = 1

)

SELECT IdExistsOnceCTE.*

INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!

FROM IdExistsOnceCTE;

WITH IdExistsTwiceCTE

AS

(

SELECT Id

,Type

,NEWID() AS PartNumber

FROM Table1

WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )

),

DummyDuplicationCTE

AS

(

SELECT 1 AS Dup

UNION ALL

SELECT 2

)

INSERT INTO Table2

SELECT #ExistsOnce.*

FROM #ExistsOnce

CROSS JOIN DummyDuplicationCTE

UNION ALL

SELECT *

FROM IdExistsTwiceCTE

ORDER BY 1, 2;

SELECT *

FROM Table2;

The final SELECT statement is a verification of the contents of Table2, and based on the inputs you provided, produces this output:

Code Snippet

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

Does this give you what you need?

|||

Thank you for your hard work.

The only thing I see wrong is the type.

You got

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

and I need

Id Type PartNumber

-- --

1 1 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

1 2 8A26BF6E-21D6-4977-AFAE-BA8CB0B22C6D

2 1 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

2 2 C1DCDCCE-4FCD-4B30-98BA-1D003F3B23F1

3 1 38070FA6-0805-45C3-8503-58F8A9EDF473

3 2 26AE5281-149C-47C2-9AE5-4E1F42228EB

Type always needs to be 1 and 2.

Everything else looks great.

|||

Here you go:

Code Snippet

WITH IdExistsOnceCTE

AS

(

SELECT Id

,MIN (Type) AS Type

,NEWID() AS PartNumber

FROM Table1

GROUP BY Id

HAVING COUNT (Type) = 1

)

SELECT IdExistsOnceCTE.*

INTO #ExistsOnce -- This will fail if run more than once in a row, as the temp table will already exist on subsequent executions!

FROM IdExistsOnceCTE;

WITH IdExistsTwiceCTE

AS

(

SELECT Id

,Type

,NEWID() AS PartNumber

FROM Table1

WHERE Id NOT IN ( SELECT Id FROM #ExistsOnce )

),

DummyDuplicationCTE

AS

(

SELECT 1 AS Dup

UNION ALL

SELECT 2

)

INSERT INTO Table2

SELECT Id

,Dup

,PartNumber

FROM #ExistsOnce

CROSS JOIN DummyDuplicationCTE

UNION ALL

SELECT *

FROM IdExistsTwiceCTE

ORDER BY 1, 2;

SELECT *

FROM Table2;

This yields:

Code Snippet

Id Type PartNumber

-- --

1 1 7578AB32-6F38-4D23-887B-D81DD9D71DBC

1 2 7578AB32-6F38-4D23-887B-D81DD9D71DBC

2 1 F554FFA0-B0DB-4C53-9D3F-181678617A9B

2 2 F554FFA0-B0DB-4C53-9D3F-181678617A9B

3 1 165DEB39-D6DA-4DD3-B577-DD295BAF7455

3 2 55389A91-EAF6-4360-BFAA-10032EA1AB67

|||Thank you very much!

Data manipulation with One-to-many-to-one relationship

Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.

Thanks

Hi,

Try providing more information about what you are trying to do. Do you have tables set up in a DB already? What columns do you have in those tables, and what are the references that make a given row of data in each table unique (primary keys)?

If you don't have a schema yet, provide (in your own words), what facts you are trying to convey - such as 'A customer places an order. Order has line items. Each product has a product number.' etc... This will give viewers here a better understanding of what you need.

Good luck, in any event. BRN..

|||

Let's say for example:

Table 1 has Table1ID (PK) and Table1Data

Table 2 has Table2ID (PK), Table1ID (FK for Table1.Table1ID), Table3ID (FK for Table3.Table3ID)

Table 3 has Table3ID (PK), and Table3Data

Relationship is one-to-many for Table1.Table1ID to Table2.Table1ID and one-to-many for Table3.Tat.ble3ID to Table2.Table3ID, creating a many to many relationship for Table1 to Table 3 using Table 2 as an intersection table. I do not have any cascading.

I am able to query the database and gather the information, but need to be able to add, edit, and delete in a C# VS2005 project. Starting off small with something like this would enable me to conquer a bigger project that I would like to create. This is the first time I have had to work with data that required this type of relationship.

I'd prefer to see the SQL.

Is this enough information?

Many thanks

|||OK, let take these 3 tables for example:

create table Employee (EmplNumber int not null primary key,EmplName nvarchar(20) not null)

create table Department (DeptNumber int not null primary key,DeptName nvarchar(50) not null)

-- relationship between Employee and Department

create table DeptEmpl (DeptNumber int not null foreign key references Department(DeptNumber)
ON DELETE CASCADE ON UPDATE CASCADE,
EmplNumber int not null foreign key references Employee(EmplNumber)
ON DELETE CASCADE ON UPDATE CASCADE)

insert into Employee values (1, 'John')
insert into Employee values (2, 'Terry')
insert into Employee values (3, 'Andrew')

insert into Department values (1, 'Marketing')
insert into Department values (2, 'Sales')
insert into Department values (3, 'Accounting')

insert into DeptEmpl values (1, 1)
insert into DeptEmpl values (1, 3)
insert into DeptEmpl values (2, 1)
insert into DeptEmpl values (2, 2)
insert into DeptEmpl values (2, 3)
insert into DeptEmpl values (3, 1)

?
There should be no problem when you try to insert data-- the PK/FK contraint will maintain the reference integrity for you. The key point here is theON DELETE/UPDATE CASCADE, they will help to maintain reference integrity during DELETE/UPDATE, so no extra work need to be done manually. If you have other concerns, feel free to post them|||

Thanks Iori_Jay

It is completely clear to me what you are doing in this example. How does one handle user input variables from a web application?

Let's say the user wants to insert a new Employee 'Edgar' that belongs to Department 'Sales'.

|||OK, typical steps for such INSERT should be:

1. Insert the information for the new employee 'Edgar' toEmployee table, and get theEmplNumber for the new inserted employee
2. Check theDepartment table to see whether there is a 'Sales' department, if not then add the 'Sales' department.
3. Insert a row toDeptEmpl table to represent the relationship between 'Edgar' and 'Sales'.

So a sample stored procedure looks like:

CREATE PROCEDURE usp_InsertNewEmployee @.EmpName nvarchar(20), @.DeptName nvarchar(50)
AS
IF((LEN(@.EmpName)=0) OR (LEN(@.DeptName)=0))
RAISERROR('Please input both Employee name and the department name which he(she) belongs to',16,1)
DECLARE @.newEmpNumber INT,@.DeptNumber INT
SELECT @.newEmpNumber=max(EmplNumber)+1 FROM Employee
SELECT @.DeptNumber=DeptNumber FROM Department WHERE DeptName=@.DeptName

INSERT INTO Employee SELECT @.newEmpNumber, @.EmpName
IF (@.DeptNumber IS NULL)
BEGIN
SELECT @.DeptNumber=max(DeptNumber)+1 FROM Department
INSERT INTO Department SELECT @.DeptNumber,@.DeptName
END
INSERT INTO DeptEmpl SELECT @.newEmpNumber,@.DeptNumber
go

EXEC usp_InsertNewEmployee 'Edgar','Sales'|||

Iori_jay,

Thanks so much for your help. I've learned many things from this post. I appreciate you sharing your talents.

ICE|8

|||It's my pleasure to help with this issue Wish you a happy festival!

Data Manipulation

HI everyone,

I have a column STATUS of type bit(1,0) , i would like to manipulate it(without editing the database) in the Store Procedure in such a way that

if
STATUS =1 // return result STATUS= 'Active '
Else if
STATUS = 0 // return result STATUS = 'Inactive'
End if

My Stored Procedure Is As Follow

SELECT
STATUS + '--' + NAME AS STATUSNAME
FROM M_USER
END

Desired Result When Populated Into a Listbox Should Be As Follow

Active--Tester
Inactive--Tester1

Thanks In Advance for the Help Provided!

SELECT
 CASE When Status = 1 Then 'Active'
 ELSE 'Inactive'
 END As STATUSNAME
FROM M_USER

:)

JB|||Thanks JBelthoff for the reply,

I follow ur example but its still not working , the code are as follow,

IF (@.MASTER = 'NRICSEARCH')
BEGIN
SELECT
CASE WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS ACTIVE,
USER_ID,
NRIC + '--' + NAME+ '(' + CONVERT(VARCHAR, ACTIVE) + ')' AS NRICNAME
FROM M_USER
ORDER BY NRIC

--
Column ACTIVE is of type bit.
NRICNAME is used in the listbox as datatextfield

TheCurrent Output from the current code in the listbox is as follow,

123--Ben(0)
124--Andy(1)

TheDesired Output in the listbox is as follow,

123--Ben(ACTIVE)
124--Andy(INACTIVE)

Apparantly, The CASE is not working.|||To get a 1 column result set in your desired output.......

SELECT CAST(USER_ID As Varchar) + '--' + NAME + '(' +
CASE
WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END
+ ')' As NRICNAME
FROM M_USER

:-D

Enjoy!

JB|||Hi JB,

Its working now, thanks a lot.

Data leaked a buffer with ID 1 of type 1

We are using a datareader component to retrieve data from a Pervasive 8.6 database. We have four separate datareader components in various packages retrieving data into our datawarehouse in SQL2005. One of the components has started to fail regularly with the following error.

Date 6/8/2007 3:05:00 AM
Log Job History (LoadMAXDailyBookings)

Step ID 1
Server US-CO-DEN-101
Job Name LoadMAXDailyBookings
Step Name Load Bookings Step
Duration 00:00:37
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Destination Write Bookings Detail" (121)" wrote 0 rows.
End Info
Log:
Name: PipelineBufferLeak
Computer: US-CO-DEN-101
Message: component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
End Log
Log:
Name: OnTaskFailed
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnPostExecute
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnWarning
Computer: US-CO-DEN-101
Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

End Log
Warning: 2007-06-08 03:05:36.92
Code: 0x80019002
Source: LoadMAXDailyBookings
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution...

The other components run without any problems as did this one up until we installed service pack 2. We then started getting these occasional failures. Any thoughts on what is happening here?

Thanks,

Phil

Do you have package logging turned on? The PipelineBufferLeak, I believe, is a warning, not an error, and hence would not stop execution.

Turning on package logging might help get a more clear error message....|||

I do have logging turned on and the error in the log is more vague than within the job history. I'm copying in the first rows indicating the error from the log. The execution is halted due to whatever error is happening.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookings,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

|||Make a copy of that package and strip everything out of the data flow except for the data reader source. Then hook it into a row count transformation. Re-run the package. What happens?|||The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.|||

pkdenver wrote:

The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.

The only reason I suggested this is because the error message returned is a DataReader failure message, so this takes out all processing that might unduly influence things. Can you use something other than a DataReader Source? OLE DB Source, for instance?|||

I see your point regarding isolating the datareader. As recommended last week, I deployed a package which performs the query and dumps to a rowcounter. We get the same failure as the regular job. Both jobs failed on one day (Sat). It ran fine on Sun and Mon. Here are the error rows from the logger: I'm wondering if we are getting a timeout on this query as it is a more complex query than the other three jobs running against the DB.

Regarding an alternate driver, the Pervasive OLE DB driver is very unstable in the SSIS environment and so it can't be used at all. We are looking at the timeout value to see if the Pervasive ODBC driver can be tweaked.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016268,0x,Execute phase is beginning.

OnPipelinePrePrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,PrimeOutput will be called on a component. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

OnPipelinePostPrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,A component has returned from its PrimeOutput call. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.

OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,50,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Post Execute
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.

OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.

OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,50,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Cleanup
PipelineBufferLeak,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
OnTaskFailed,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnPostExecute,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnWarning,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

OnPostExecute,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
PackageEnd,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1,0x,End of package execution.

Data Getting truncated as I insert i.e. instead of inserting Hello it inserts H

m inserting some data in big-sized fieldand small-sized fields, data of varchar type, int's, dateTime, andothers... i am using something called a stored procedure to add datainto a table.. now when i execute the stored procedure my data getstruncated (although i made the sizes for my fields ridiculously biglike 1000 just in case) for example if i want to enter Jasmine it onlyinserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I amguessing it's a problem of using single vs. double quotes and stufflike that within my insert statement...
the following is my stored procedure:
CREATE procedure addLabor
@.lName varchar,
@.fName varchar,
@.mName varchar,
@.title varchar,
@.craft varchar,
@.lastFour varchar,
@.SSN varchar,
@.dateOfHire varchar,
@.currentProj varchar,
@.status tinyInt,
@.project_id int,
@.updateBy varchar,
@.updateDate varchar,
@.address varchar,
@.email varchar,
@.phone varchar,
@.zip varchar,
@.myfeedBack varchar,
@.ethnicity varchar,
@.userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @.newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,lastFour, SSN, dateOfHire, currentProj, status, project_id,updateBy,
updateDate, address, email, phone, zip, ethnicity)
VALUES
(@.lName , @.fName , @.mName , @.title , @.craft , @.lastFour , @.SSN ,@.dateOfHire , @.currentProj ,
@.status, @.project_id , @.UpdateBy, @.UpdateDate , @.address , @.email , @.phone , @.zip , @.ethnicity )
SELECT @.newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@.newid ,+'
+@.myfeedBack + ',
+@.userID ,
@.project_id )
SET NOCOUNT ON
END
GO

When i use 2 single quotes it insert the following string: "@.lName" not the actual value of the variable @.lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman','1234','keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==','6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/20052:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345','123-12-1234', 'African American', '3'
I appreciate any help or hints
thanks to all
All of your parameters are declared like this:
@.lName varchar,
Should be like this:

@.lName varchar(1000),
a type of varchar is like varchar(1), meaning a variable length string with a max of 1 character. The number used should likely match the underlying length of the field in the table