Creating table using dynamic SQL

I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

IF @.Param_leadage = '91+'
SET @.TopRange = 91
ELSE
Set @.TopRange = RTRIM(LEFT(REPLACE(@.Param_leadage,'-',''),2))

IF @.Param_leadage = '91+'
SET @.BottomRange = 4000
ELSE
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.Param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@.TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@.BottomRange)

IF @.Param_status = 'Online Churn'
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = ' + @.Param_idstatus

EXEC(@.SQL)

If the Refresh Fields toolbar button enabled on the Data tab is enabled, try clicking it to get the fields. Or, plug in only the SELECT statement and hit Refresh Fields to get the fields.Then, change it to the original SQL. Finally, you can create the dataset fields manually.

|||

Hi Teo,

This isnt working, when i try to select a data field from the table textbox properties value dropdown box, it says 'Dataset1 has no fields'

When I enter it in manually it gives an error which is obviously to do with the fields not being in the scope.

In the Data screen within VSS 2005 the sql executes fine, when i refresh it prompts for parameters off which i enter and the data is returned, it is not recognising any fields when i try to access any fields from any controls..

it has something to do with the exec(sql) command and dynamic data i would suspect

I also tried doing the SQL first off which the table fields are recognised but as soon as i add the other portions such as the single quotes etc it gives the same error

Can someone please help

thanks

|||

Try putting this code in a stored proc and changing the command type to be stored proc.

The other option is to not use dynamic SQL, instead use an expression based query and construct the SQL using expression syntax e.g.

= Iif(Parameters!x.value = "91+", "Select...", "select ...")

Again, in this case I follow the previous advice of just putting in the SQL query first, hitting refresh so RS auto-generates the Filed definitions and then swapping in the expression

|||

@. is interpreted as a query parameter in the Query Designer. You can make the whole darn thing expression-based as Adam suggested or move this query to a stored procedure.

|||

thanks guys, i put it in a stored proc and it is working now

Creating new table from passed variabe in MSSQL Stored procedure

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS
BEGIN

create table @.TableName ([id] int identity(1,1),[diller] int)

end
GO

THIS CANT BE TRUE !!!!!!!!You need to do the following to get it working

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS

BEGIN
Declare @.SQLStatement nvarchar(4000)

SET @.SQLStatement = 'create table ' + @.TableName + '([id] int identity(1,1),[diller] int)'

Exec (@.SQLStatement )

end
GO

That should do it.

Let me know if you have any questions.|||If it aint to much i would very much like to check for the tables existens before i create it... so basicly i would need to check if the variable exists as a table before i insert it something like

if not exists(Select * From @.TableName)
begin

Stuff...

End

And by the way thk you for the quick reply...|||You can have it as
if not exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = @.TableName)
begin

Stuff...

End

Recently, I wrote article on how to check the objects in database. You can check it at www.aspalliance.com/349 to understand the information_schema views.

Let me know if the above does not work for you.

AP|||Thk you very much been pulling my hair over this... and good article very helpfull... i just startet programming in SQL well my vision is to create websites structured from SQL so that changing stuff around the page is going to be an ease for the user..

thx again...

Jakob

Creating index on table at subscriber

I have a database for a 3rd party application that I need to report from.
Since I can't do anything to the 3rd party database, I am replicating the
relevant tables to my own database and running the queries there.
One of the tables (Action table) has two fields (Datestamped, Timestamped),
which are critical to some complicated queries I'm running. However neither
of these fields have indexes in the 3rd party database (since they obviously
aren't important to the 3rd party application).
I want to create indexes at the subscriber, but am worried about the
following.
1) Can I do it without breaking the replication or the databases?
2) What will the maintenance be like for this? Will I need to recreate the
indexes every time I need to rebuild the replication?
Thanks in advance
Derek
sp_addscriptexec is only good for UNC type snapshots, ie it will not work
for snapshots delivered by FTP.
The recommendation to deploy this index via a post snapshot command by Paul
is good one.
You could also deliver a custom object script using sp_addarticle, where the
custom script would have the index in it; however you may find that this
will slow down your snapshot delivery.
Here is an example of this:
http://groups.google.com/groups?selm...&output=gplain
The downside of having additional indexes on the subscriber tables is that
it will slow down update activity on your subscriber. In general minimze
your indexes on your subscriber tables. You probably have no option in your
case.
For index maintenance, auto update stats always helps (again this may lead
to some performance degradation with heavy updates), so you might want to
consider nightly dbcc dbreindex or indexdefrag.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
> Derek,
> the easiest way to do this is to have a post-snapshot
> script to run which adds the indexes. If initialization
> has already taken place, you can add the index manually
> or using sp_addscriptexec (transactional and merge)
> without breaking the replication setup.
> HTH,
> Paul Ibison
|||I haven't been able to get this working yet, but haven't yet had the time to
investigate fully and am now knocking off for the day (in Australia).
However I'm having trouble keeping the replication going once I have changed
indexes. Today I have gotten this error:
The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a clustering
key at the same time.
Is this related?
"Hilary Cotter" wrote:

> sp_addscriptexec is only good for UNC type snapshots, ie it will not work
> for snapshots delivered by FTP.
> The recommendation to deploy this index via a post snapshot command by Paul
> is good one.
> You could also deliver a custom object script using sp_addarticle, where the
> custom script would have the index in it; however you may find that this
> will slow down your snapshot delivery.
> Here is an example of this:
> http://groups.google.com/groups?selm...&output=gplain
> The downside of having additional indexes on the subscriber tables is that
> it will slow down update activity on your subscriber. In general minimze
> your indexes on your subscriber tables. You probably have no option in your
> case.
> For index maintenance, auto update stats always helps (again this may lead
> to some performance degradation with heavy updates), so you might want to
> consider nightly dbcc dbreindex or indexdefrag.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
>
>
|||Derek,
I found this whch is relevant for your situation:
http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
From BOL for Update it states "If an update query could alter more than one
row while updating both the clustering key and one or more text, image, or
Unicode columns, the update operation fails and SQL Server returns an error
message."
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That seems to have worked.
What I have ended up doing is instead of deleting all the indexes and
creating my own, I have simply added some that I needed. Not quite as good,
but should do the job.
Derek
"Paul Ibison" wrote:

> Derek,
> I found this whch is relevant for your situation:
> http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
> From BOL for Update it states "If an update query could alter more than one
> row while updating both the clustering key and one or more text, image, or
> Unicode columns, the update operation fails and SQL Server returns an error
> message."
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>