Asp.net / sql help: type

Showing posts with label type. Show all posts

Thursday, March 22, 2012

ASYNC_NETWORK_IO wait type

We had an issue yesterday where a mess of async_network_io wait types started
showing up in activity monitor (sql 2005). It turns out the optimizer was
picking a bad execution plan for a certain report stored procedure and it was
taking much longer than normal. We've fixed that issue, but my question was
more of why we were seeing that wait type. My understanding of
async_network_io is it indicates that SQL Server is waiting on sending data
back to the client. It looks like stored procedure was taking long enough for
the client app to time out. Eventually when the procedure finished, the
connection just hangs with a wait type of async_network_io until I explicitly
issue a Kill. Is this behavior by design? It used up quite a few connections.
Thanks in advance.
Perhaps try adding WITH RECOMPILE to the sproc to ensure it gets appropriate
plan for all parameter combinations. Perhaps update statistics too, and
check indexes for fragmentation.
TheSQLGuru
President
Indicium Resources, Inc.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:352EC2C2-67DD-4C14-A9AB-58C4BE18FC80@.microsoft.com...
> We had an issue yesterday where a mess of async_network_io wait types
> started
> showing up in activity monitor (sql 2005). It turns out the optimizer was
> picking a bad execution plan for a certain report stored procedure and it
> was
> taking much longer than normal. We've fixed that issue, but my question
> was
> more of why we were seeing that wait type. My understanding of
> async_network_io is it indicates that SQL Server is waiting on sending
> data
> back to the client. It looks like stored procedure was taking long enough
> for
> the client app to time out. Eventually when the procedure finished, the
> connection just hangs with a wait type of async_network_io until I
> explicitly
> issue a Kill. Is this behavior by design? It used up quite a few
> connections.
> Thanks in advance.

ASYNC_NETWORK_IO wait type

We had an issue yesterday where a mess of async_network_io wait types starte
d
showing up in activity monitor (sql 2005). It turns out the optimizer was
picking a bad execution plan for a certain report stored procedure and it wa
s
taking much longer than normal. We've fixed that issue, but my question was
more of why we were seeing that wait type. My understanding of
async_network_io is it indicates that SQL Server is waiting on sending data
back to the client. It looks like stored procedure was taking long enough fo
r
the client app to time out. Eventually when the procedure finished, the
connection just hangs with a wait type of async_network_io until I explicitl
y
issue a Kill. Is this behavior by design? It used up quite a few connections
.
Thanks in advance.Perhaps try adding WITH RECOMPILE to the sproc to ensure it gets appropriate
plan for all parameter combinations. Perhaps update statistics too, and
check indexes for fragmentation.
TheSQLGuru
President
Indicium Resources, Inc.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:352EC2C2-67DD-4C14-A9AB-58C4BE18FC80@.microsoft.com...
> We had an issue yesterday where a mess of async_network_io wait types
> started
> showing up in activity monitor (sql 2005). It turns out the optimizer was
> picking a bad execution plan for a certain report stored procedure and it
> was
> taking much longer than normal. We've fixed that issue, but my question
> was
> more of why we were seeing that wait type. My understanding of
> async_network_io is it indicates that SQL Server is waiting on sending
> data
> back to the client. It looks like stored procedure was taking long enough
> for
> the client app to time out. Eventually when the procedure finished, the
> connection just hangs with a wait type of async_network_io until I
> explicitly
> issue a Kill. Is this behavior by design? It used up quite a few
> connections.
> Thanks in advance.

ASYNC_NETWORK_IO wait type

We had an issue yesterday where a mess of async_network_io wait types started
showing up in activity monitor (sql 2005). It turns out the optimizer was
picking a bad execution plan for a certain report stored procedure and it was
taking much longer than normal. We've fixed that issue, but my question was
more of why we were seeing that wait type. My understanding of
async_network_io is it indicates that SQL Server is waiting on sending data
back to the client. It looks like stored procedure was taking long enough for
the client app to time out. Eventually when the procedure finished, the
connection just hangs with a wait type of async_network_io until I explicitly
issue a Kill. Is this behavior by design? It used up quite a few connections.
Thanks in advance.Perhaps try adding WITH RECOMPILE to the sproc to ensure it gets appropriate
plan for all parameter combinations. Perhaps update statistics too, and
check indexes for fragmentation.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:352EC2C2-67DD-4C14-A9AB-58C4BE18FC80@.microsoft.com...
> We had an issue yesterday where a mess of async_network_io wait types
> started
> showing up in activity monitor (sql 2005). It turns out the optimizer was
> picking a bad execution plan for a certain report stored procedure and it
> was
> taking much longer than normal. We've fixed that issue, but my question
> was
> more of why we were seeing that wait type. My understanding of
> async_network_io is it indicates that SQL Server is waiting on sending
> data
> back to the client. It looks like stored procedure was taking long enough
> for
> the client app to time out. Eventually when the procedure finished, the
> connection just hangs with a wait type of async_network_io until I
> explicitly
> issue a Kill. Is this behavior by design? It used up quite a few
> connections.
> Thanks in advance.

Tuesday, March 20, 2012

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-Eric
The fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) but the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use â'Localhostâ' rather than the DNS name
(thinking that it might have to ask the DNS server what itâ's name is) but the
process will still get the same error.
Iâ'm confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:
> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use â'Localhostâ' rather than the DNS name
> (thinking that it might have to ask the DNS server what itâ's name is) but the
> process will still get the same error.
> Iâ'm confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>sql

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_I
O
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a proces
s
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but
the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among al
l
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, yo
u
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network
_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID
5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes fr
om
> one table and inserts/updates another. This is the one that starts a proc
ess
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) bu
t the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

Monday, March 19, 2012

Assistance with error message

I am trying to run this script in Query Analyser and it seems to have a
problem in line 3:
-
select ID, 9, '2004', (select id from type where type = 'MF') from Dept
where dept in ('Internal Medicine', 'Surgery')
and ID + (select id from type where type = 'MF') +
convert(varchar(2), 9) +
convert(varchar(4), '2004')
not in
(select m.deptID + m.typeid + convert(varchar(2), m.dtmonth) +
convert(varchar(4), m.dtyear) from monthdept m, dept d, type t
where m.DeptID *= d.ID
and m.TypeID *= t.Type
)
The error message is: Invalid operator for data type. Operator equals add,
type equals uniqueidentifier.
I have tried playing with CAST/CONVERT without success. I am trying to look
for records on a key that spans 4 fields. Any suggestions/thoughts?
Schoo
PS: SQL Server 2000 running on W2K Server
Hi Schoo,
From your descriptions, I understood that the following codes raise an
error in QA, correct me if I was wrong, however, when I copied codes you
provided to my QA and then Parse Query (Ctrl + F5), it passed. To make
further research on this issue, I would appreciated if you could provide me
detailed DDL of your database and some sample record for me to reporduce it
on my machine. One more question, have you upgraded to the latest SQL
Server Service Pack?
Here is the document on how to get DDL from Enterprise Manager
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

assigning xml output

I have a small question.
I am recently started using 2005 and I want to assign results of following
query in to a variable of any type.
select top 1 * from dbo.authors for xml auto
Please let me know how do I achieve this.
Kishortry using for xml path()
--
"kishor" wrote:

> I have a small question.
> I am recently started using 2005 and I want to assign results of following
> query in to a variable of any type.
> select top 1 * from dbo.authors for xml auto
>
> Please let me know how do I achieve this.
> Kishor|||and use it this way. Hope this helps.
declare @.a varchar(8000)
set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
select @.a|||Hi
I got error.
Line 2: Incorrect syntax near 'xml'.
Kishor
"Omnibuzz" wrote:

> and use it this way. Hope this helps.
> declare @.a varchar(8000)
> set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
> select @.a
>|||This works only is SQL Server 2005. You are using SQL 2005 right?

Assigning XML data to variables

Hi Guys...

Need some assistance here again.
What data type should I use for the variable that I receive from a resultset?

I tried setting it to object, but I encountered some problem later in the stage during the data flow because the XML source adapter cannot get the variable.

I tried setting it to String but get some error during runtime because of invalid datatype.

The main purpose here is actually to get the data from an XML column in a table and populate it into the XML Source Adapter so that I can do some transformations.

Any ideas?

Thanks.Hi,
Yes, this is definitely an issue. In the next release (post- CTP15) you will be able to put the XML result set into a string variable that can then be consumed by the XML Source Adapter. For now, you might be able to work around it with a script component that creates a new string variable and populates it with the data from the object variable. I'm sorry I don't have a better answer for you.

Thanks,
Mark

Sunday, March 11, 2012

assigning sequence numbers to records

hey all,
i have a table with about 1300 rows
the records are grouped by a type and i want to
assign each type a numbering sequence. what's the best way to do this?
For instance,
Rec1 Type1 Seq1
Rec2 Type1 Seq2
Rec3 Type2 Seq1
Rec4 Type2 Seq2
Rec5 Type2 Seq3
The seq columns is what i'm trying to incorporate.
thanks,
rodcharI assume from your post that the records are already sequenced globally...
Then you can do this for output only with a query
Select RecSeq, Type,
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq) TypeSeq
From TableName T
Or if you have another column in the table that you want to actually
populate with the value
Then
Update T Set TypeSeq =
(Select Count(*) From TableName
Where Type = T.Type
And RecSeq <= T.RecSeq)
From TableName T
"rodchar" wrote:

> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||We need to know if there is another column we can using to uniquely identity
a row in a group.
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"rodchar" wrote:

> hey all,
> i have a table with about 1300 rows
> the records are grouped by a type and i want to
> assign each type a numbering sequence. what's the best way to do this?
> For instance,
> Rec1 Type1 Seq1
> Rec2 Type1 Seq2
> Rec3 Type2 Seq1
> Rec4 Type2 Seq2
> Rec5 Type2 Seq3
> The seq columns is what i'm trying to incorporate.
> thanks,
> rodchar
>
>|||I don't think I'm doing something right here cause it's not working.
Please let me explain a different way to make sure:
Rec1 Type Seq#
-- -- --
1 A
3 B
2 A
4 A
5 B
After should look like the following:
Rec1 Type Seq#
-- -- --
1 A 1
2 A 2
4 A 3
3 B 1
5 B 2
The Seq# field is a new field that I need numbered sequentially by Types
So you're saying that the update statement in the above reply should do this
?
thanks,
rodchar
"CBretana" wrote:
> I assume from your post that the records are already sequenced globally...
> Then you can do this for output only with a query
> Select RecSeq, Type,
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq) TypeSeq
> From TableName T
> Or if you have another column in the table that you want to actually
> populate with the value
> Then
> Update T Set TypeSeq =
> (Select Count(*) From TableName
> Where Type = T.Type
> And RecSeq <= T.RecSeq)
> From TableName T
>
> "rodchar" wrote:
>|||The "new" field that you want populated must already exist in the Table
first. Have you added it? If you have named it "SeqNo" then
Update T Set SeqNo=
(Select Count(*) From TableName
Where Type = T.Type
And Rec1 <= T.Rec1)
From TableName T
What error are you getting ?
"rodchar" wrote:
> I don't think I'm doing something right here cause it's not working.
> Please let me explain a different way to make sure:
> Rec1 Type Seq#
> -- -- --
> 1 A
> 3 B
> 2 A
> 4 A
> 5 B
> After should look like the following:
> Rec1 Type Seq#
> -- -- --
> 1 A 1
> 2 A 2
> 4 A 3
> 3 B 1
> 5 B 2
> The Seq# field is a new field that I need numbered sequentially by Types
> So you're saying that the update statement in the above reply should do th
is?
> thanks,
> rodchar
> "CBretana" wrote:
>|||I'm not getting an error message however the value for the new field contain
s
that maximum number (1372) for all the records:
Here's my actual statement:
UPDATE Records
SET SeqNo =
(SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department AND
RecID <= Records.RecID)
FROM Records
So, here we have
RecID (like Rec1 is just an autonumber field)
Department is the type
"CBretana" wrote:
> The "new" field that you want populated must already exist in the Table
> first. Have you added it? If you have named it "SeqNo" then
> Update T Set SeqNo=
> (Select Count(*) From TableName
> Where Type = T.Type
> And Rec1 <= T.Rec1)
> From TableName T
>
> What error are you getting ?
>
> "rodchar" wrote:
>|||On Tue, 5 Apr 2005 11:55:03 -0700, rodchar wrote:

>I'm not getting an error message however the value for the new field contai
ns
>that maximum number (1372) for all the records:
>Here's my actual statement:
>UPDATE Records
>SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
>RecID <= Records.RecID)
>FROM Records
(snip)
You omitted the table alias from CBretana's solution. Try this one
instead (not exactly the same as CBretana's suggestion - I changed it to
use ANSI-standard instead of proprietary Transact-SQL syntax):
UPDATE Records
SET SeqNo = (SELECT COUNT(*)
FROM Records AS R
WHERE R.Department = Records.Department
AND R.RecID <= Records.RecID)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Because you're not using an alias for the tablename in the outer query... Th
e
way you wrote it,
UPDATE Records SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = Records.Department
AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
FROM Records
The token <Records> in the Last part of the subquery's where clause refers
to the Records table IN the subquery, so you're saying Where RecID in The
Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s to
ask
" Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That's
why you need to use Table ALias
UPDATE R SET
SeqNo = (SELECT COUNT(*)
FROM Records
WHERE Department = R.Department
AND RecID <= R.RecID)
FROM Records As R
"rodchar" wrote:
> I'm not getting an error message however the value for the new field conta
ins
> that maximum number (1372) for all the records:
> Here's my actual statement:
> UPDATE Records
> SET SeqNo =
> (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department AND
> RecID <= Records.RecID)
> FROM Records
> So, here we have
> RecID (like Rec1 is just an autonumber field)
> Department is the type
>
> "CBretana" wrote:
>|||thank you all, I give a try and let you know.
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>|||That's awesome, hey any recommendations on a good book or how to get up to
speed in knowing how to write statements like these?
thanks so much for everyone's help here,
rodchar
"CBretana" wrote:
> Because you're not using an alias for the tablename in the outer query...
The
> way you wrote it,
> UPDATE Records SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = Records.Department
> AND RecID <= Records.RecID) -- THIS IS SAME AS X <=X
> FROM Records
> The token <Records> in the Last part of the subquery's where clause refer
s
> to the Records table IN the subquery, so you're saying Where RecID in The
> Subquery Record <= Same RecID in Sam eSubqueryRecord... This part need s t
o
> ask
> " Where RecID in SubQuery Record <= RecID in teh OUTER Query Record. That'
s
> why you need to use Table ALias
> UPDATE R SET
> SeqNo = (SELECT COUNT(*)
> FROM Records
> WHERE Department = R.Department
> AND RecID <= R.RecID)
> FROM Records As R
> "rodchar" wrote:
>

Thursday, March 8, 2012

assign the Null value to a variable that is not a Variant data type.

Hello Everyone,

I trying to upgrade our Alpha 4v4 Dos Database to MS SQL 2000 with Access XP front end and I have four tables that won't let import my data into them. I keep recieveing a message that says "You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)"
What can I do to get rid of this stupid error, is it a problem with Access XP or SQL 2000.Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

I tried that and I get a MS Jet Database Engine Error when I tried the import function, I know that in Access you can append data in, can you do that in SQL. What I mean is could I just link my Alpha tables into the SQL Database and then try to Append/Insert into my SQL tables. SQL is all new to me and I'm still in the process of learning it and it is alot different then Alphav4 and Access 97 functionality that I'm used to.|||Originally posted by Paul Young
Sounds like an Access problem to me. Can you bypass Access and load the data directly into the server?

Paul,

I can't import, append or insert. But I can copy and paste my old records into the new tables with a few error messages and only 65000 at a time. I guess that something is better than nothing. If anyone has a better way, I'm always open to try something new.|||Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.|||Originally posted by Paul Young
Yes, you can append data in SQL.

There would be many ways to do this. The very first one that comes to mind would be to use DTS if you are using SQL 7/2k. DTS can connect to a variety of data sources and allows you to transform the data on the fly.

Microsoft's Books Online has some good information on this.
I've been using the DTS function and that is what I first tried to do my import with or even append and I still get an error message. If I import the whole table it will only bring in the structure, no data i get an error on that. THen if I try to append the data I get a different error. I'll look online and see if I can findout what I'm doing wrong. Or I'll just cut and paste since that seems to work.|||What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.|||Originally posted by Paul Young
What were your errors?

DTS can be used to import a structure and/or data. Often I have found it easyier to created a db, and then use DTS to suck strucutre and all. Once the data is in I can make modifications and move the data to it's ultimate home.

If I import my data from Alpha into Access 97 and then into SQL I get an Insert error on 4 of my tables for certain Date fields that says Data Over flow invaild character value for cast specification. If I try to go directly into Alpha 4v4 which are Dbase 5 tables it just won't do it. I get a ms jet vb error and it will import nothing at all. Atleast in access it will import 32 of 36 tables.|||It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?|||Originally posted by Paul Young
It seems odd that you get a jet error. Are you using the dBase 5 driver or the ODBC driver?

Ok I changed my data source to dbase III and I was able to import my data directly form Alpha 4v4, plus I'm starting to usnderstand this DTS function. I was wondering if I can use it to just append the tables, not create them everytime. I noticed that everytime I use the wizard it wants to create the table then import the data. I want it to just Append the data now that I have the tables in SQL so that I can refreash the old data with the new until I'm ready to run everything in SQL. Can i physically change the SQL startments for that DTS function and is that possible.|||You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.|||Originally posted by Paul Young
You should be able to just append data, I don't have dBase 5 to test with but when I load a CSV file into an existing table I can select Transformations and choose Append rows to destination table. That should do it for you.

Ok I can't find what your talking about "Select transformations and choose append rows?", could you do me a favor and type out the steps that you use to see if I'm going in the right direction. Appending my Bbase file and your CSV file shouldn't be that different as far as the steps go. Thank you for your help.|||ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..|||Originally posted by aaron_kempf
ADP is the answer to all your problems.

It only deals with SQL Server-- so its much simpler than what you're talkin about..

in access 2002, you can even create a linked server-- just like how you can link to a different db in a mdb..

of course, i think that you'll need to put drivers on the SQL Server-- but thats not that big of a deal..

ADP, could you tell me more about it, I typed it into the help file and nothing came up.|||1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.|||Originally posted by Paul Young
1. Fire up DTS
2. Fill in the data source
3. fill in the data destination click "NEXT >"
4. On the "Select Source Tables and Views" panel click on the elips "..." under Transformations.
5. Click on the "Append rows to destination table" radio button and then click on "OK"
6. Back on the "Select Source Tables and Views" panel click on "Next >"
7. On the "Save, schecule , and replicate package" panel click on "Next >".
8. You should be able to take it from here.

Step four was the one that I wasn't finidng, thank you so much Paul, I did get it to work and now I just need to adjust it so that I won't get any errors when I try to append a date field. Thanks

Wednesday, March 7, 2012

assign numeric data type to user variable

how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

there is no numeric data type in variable..

if you click the drop down box in variable data type, you can only see the below data type:

Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char

Do some searching on this forum. It has been discussed in just the last week, along with several other occasions. You'll likely have to read it in as a string and then later convert it to an integer using variable expressions.|||thank you

Assign Null value to Variable

I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro

The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Am i getting this error becasue the column has NULL value?. how can I resove this probelm?

Populate your source with 0s instead of NULLs in your source query, if applicable. (Or some arbitrary number)|||

can i do thsi in Expression builder using NULL function?

if so, can you show me some examples?

|||Anywhere in an expression builder, you can do:

ISNULL(ColumnOrVariable) ? 0 : ColumnOrVariable

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun