Dynamic SQL and Datatable

[Jason MacKenzie]

I have a question about populating a datatable from a stored proc. I have a
dynamic Sql statement that I'm building that I execute using the exec
command to populate a temporary table. I then do a select from that table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has any rows in
it (no exception is thrown). I'm thinking it must be because it stops after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason

 

[William Ryan eMVP]

Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.

 

[Jason MacKenzie]

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is going to be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.

 

[William Ryan eMVP]

It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

 

[Jason MacKenzie]

The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm going to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is going to be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


I
have rows

 

[William Ryan eMVP]

Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.

The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm going to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is going

to
be

a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored proc. I
have
a
dynamic Sql statement that I'm building that I execute using the exec
command to populate a temporary table. I then do a select from that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has any rows
in
it (no exception is thrown). I'm thinking it must be because it stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason

 

[Jason MacKenzie]

Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.

Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.

The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm going to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the

dataset
and

won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is

going

to proc.
I

 

[William Ryan eMVP]

What next line?

Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.

Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one may
be rejected. If that's in fact the problem, we can work around it but let's
verify it first.

The problem is that I have set up the application to use datatables instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called all
over the place.

So before I change them all to datasets for this one situation I'm

going
to

try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it. You
can set the reference to your existing table to table[1] in the dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match, however,
just to test, go ahead and make a dataset, call fill on it and check the
table count. That can be done with just one new line of code and one
change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying definitely
could be true. Maybe I do need to use a dataset. Which if so is

going

to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored proc.
I
have
a
dynamic Sql statement that I'm building that I execute using the
exec
command to populate a temporary table. I then do a select from that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has any
rows
in
it (no exception is thrown). I'm thinking it must be because it
stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason

 

[Jason MacKenzie]

Sorry - that was really vague. I have an object that I call the methods from

dim dsDataSet as dataset = objCommon.returnDataSet(strSQL)

dim dtDataTable as datatable = objCommon.returnDataTable(strSQL)

dsDataSet has 1 table with 20 Rows
dtDataTable has no rows

These two methods work fine - I've used them for a long time with no issues.
But that's what I meant by "next line"

I appreciate all the time you're taking.

What next line?

Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.

Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second

one
may

be rejected. If that's in fact the problem, we can work around it but let's
verify it first.
The problem is that I have set up the application to use datatables
instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is

called
all

over the place.

So before I change them all to datasets for this one situation I'm going
to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it.
You
can set the reference to your existing table to table[1] in the dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match,
however,
just to test, go ahead and make a dataset, call fill on it and

check
the

table count. That can be done with just one new line of code and one
change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying
definitely
could be true. Maybe I do need to use a dataset. Which if so is going
to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right direction.


Jason, check the table count of your dataset, I'll be you have two
datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored proc.
I
have
a
dynamic Sql statement that I'm building that I execute using the
exec
command to populate a temporary table. I then do a select from
that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never

has
any

rows
in
it (no exception is thrown). I'm thinking it must be because it
stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason

 

[Jason MacKenzie]

I made a hacky change to get around the issue right now. I'm just going to
check to see if my datatable has any rows and return it if it does. If not
I'll create a dataset and return the first table that has any rows. It
seems to be working so far although I'd really like to know why this is
happening in the first place.

Sorry - that was really vague. I have an object that I call the methods from

dim dsDataSet as dataset = objCommon.returnDataSet(strSQL)

dim dtDataTable as datatable = objCommon.returnDataTable(strSQL)

dsDataSet has 1 table with 20 Rows
dtDataTable has no rows

These two methods work fine - I've used them for a long time with no issues.
But that's what I meant by "next line"

I appreciate all the time you're taking.

What next line?

Interesting: I populated a dataset. Table count is 1 and the rowcount for
that table is 20 which is correct. The next line populates a datatable with
the same sql statement and the row count is 0.

I have no idea what to make of that.



Right, but we need to verify what the problem is, and with Batch Queries,
usually two or more tables will be filled. In one table, the second one
may
be rejected. If that's in fact the problem, we can work around it but
let's
verify it first.
The problem is that I have set up the application to use datatables
instead
of datasets as up until this issue cropped up a dataset seemed like
overkill. I have one function that returns a datatable that is called
all
over the place.

So before I change them all to datasets for this one situation I'm going
to
try to come up with a better approach.

It shouldn't be a pain at all b/c you'll just have two tables in it.
You
can set the reference to your existing table to table[1] in the
dataset
and
won't have to change any other code.

I think that's your problem b/c the schemas probably don't match,
however,
just to test, go ahead and make a dataset, call fill on it and check
the
table count. That can be done with just one new line of code

and
one

change
to the line on the call to fill.

If that's the problem, it's really easy to fix, I promise.

Bill

www.devbuzz.com
www.knowdotnet.com

I'm populating a datatable actually. But what you're saying
definitely
could be true. Maybe I do need to use a dataset. Which if so is
going
to
be
a real pain!

Thanks for the tip. I think you've pointed me in the right
direction.


Jason, check the table count of your dataset, I'll be you

have
two

datatables. The second one should have some values in it.
I have a question about populating a datatable from a stored
proc.
I
have
a
dynamic Sql statement that I'm building that I execute

using
the

exec
command to populate a temporary table. I then do a select from
that
table.
Here is a simple example of what I'm talking about

SET @SQL ='SELECT * INTO dbo.TempTable FROM Table;
EXEC (@SQL)

SELECT * FROM dbo.TempTable

GO

In QA everything seems to work fine but my datatable never has
any
rows
in
it (no exception is thrown). I'm thinking it must be

because
it

stops
after
the EXEC command.

I've noticed a similar phenomenon when using PRINT for debugging
purposes
and forgetting to comment it out.

Is this behaviour by design? Any advice is appreciated,

Jason