EDB on WM. Do Do sort Orders generate index tables?

Hi,

I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger?
In the MSDN documentation I can not find anything on this.

Thanks,
GiulioHi, does anybody know the answer?
I am stuck with this.

thanks,
giulio|||

Hi Giulio2000

Moving to Sql Server compact edition forum where it has got better chances of being answered.

-Thanks,

|||

Yes, indexs do get generated. You can create a sort order and then specify the sort order you want to use while opening the database. EDB supports up to 16 different sort orders.

Manish Agnihotri

Program Manager

SQL Compact

sql

EDB - CeGetDBInformationByHandle

Hello,
I am tring to open some EDB databases... and to get the corect name using CeGetDBInformationByHandle.

My code looks like this:

*hSession = iCeCreateSession(pGuid);
if(*hSession==NULL)
return INVALID_HANDLE_VALUE;

if( (hwnDB=iCeOpenDatabaseInSession(*hSession,pGuid,poid,pwszName,pSort,dwFlags,pRequest)==INVALID_HANDLE_VALUE)
{
DisplayMessage(GetLastError());
return;
}

// DB open
BY_HANDLE_DB_INFORMATION lpDBInfo ;

lpDBInfo.wVersion = 2;// version for EDB
if(!CeGetDBInformationByHandle(hwnDB, &lpDBInfo))
{
DisplayMessage(GetLastError()); // HERE I get sometimes error code 87 (The parameter is incorrect.)
return;
}

if(lpDBInfo.infDatabase.dwFlags |= CEDB_VALIDNAME)
DisplayMessage(lpDBInfo.infDatabase.szDbaseName);

Could anyone tell me why this is happening ?
Best regards.

I tried this, it works fine for me. Do you have the steps to reproduce this?

EBCDIC to ASCII conversion in SSIS

I tried to setup a flat file data source that has code page 37 (EBCDIC)

Then I have a flat file destionation that is ASCII.

And inbetween I have tried several different data flow conversion tasks liked Data Conversion, and Derived Column. But I keep getting errors about different code pages.

I also tried to load the EBCDIC data into a SQL Server DB, and it complains about different code page.

Has anyone been able to do this with SSIS out of the box, without any extra components ?

Clarence

EBCDIC 037 is one of the EBCDIC defined in SQL Server you have to use the collation below to create your database, tables and columns and you have to use Nvarchar and SSIS datatype for Nvarchar. To export to ASCII just do convert to Varchar before the export. Some EBCDIC code pages are not defined in SQL Server the link below shows those covered. Hope this helps.

SQL_EBCDIC037_CP1_CS_AS

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

|||

Wow, that's great !! I'm able to import it into a DB table now, but when I do something like this

SELECT

CONVERT(varchar(2), Rec_Type) Rec_Type

FROM dbo.CCP_FAC_EBCDIC

it's giving me an error:

An error occurred while executing batch. Error message is: Object reference not set to an instance of an object.

any ideas ?

|||

I don't think your convert to varchar definition is correct because nvarchar is double bytes that is one nvarchar is two varchar so the question is what is the size of the data you are exporting to ASCII. You could avoid the error by using SELECT INTO with the convert to varchar if the varchar is not big enough to be destination for your nvarchar your SELECT INTO will fail. Hope this helps.

|||

Thank you so much for your help !! I just used Convert to nvarchar instead of varchar and it works fine !

You're a life saver !

|||

ClarenceC wrote:

Thank you so much for your help !! I just used Convert to nvarchar instead of varchar and it works fine !

You're a life saver !

I am glad I could help.

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Question: Handling Login Exceptions

I have some very simple code that connects to an SQL Server instance using the SMO Server object. I have everything working fine, but I would like to be able to know if the login failed and respond to the error. Does anyone have a snippet that will do this? Do have to catch an exception or handle an event or....

Thanks!

This is how I connect in SMO (VB.Net):

' Connect to the server

Dim srvMgmtServer As Server

srvMgmtServer = New Server("MyServer\MyInstance")

Dim srvConn As ServerConnection

srvConn = srvMgmtServer.ConnectionContext

srvConn.LoginSecure = True

Hope that helps.

|||Ok, that is fine, but what if "MyServer\MyInstance" doesn't exist, or the user supplies invalid credentials? I want to be able to tell the user the login failed and why. There must be an exception thrown somewhere, but I can’t find a simple way to handle an invalid login. Right now I am opening the connection to the server right after I create the instance, and then catching any exceptions, but this seems like a bit of a hack. There has to be a way to do something like this:

try
{

srvMgmtServer = New Server("MyServer\MyInstance")

}
catch(SomeException)
{

//Do Stuff Because The Login Failed

}
|||

Yes, I'm sorry. The method of handling the exception is exactly what you've shown. If you catch(ex), then you can scroll through the InnerException property until you get to the base level. When I just catch the error at the top level the Message property contains ""Failed to connect to server MyServer\MyInstance."

In VB.Net the code looks something like this:

Catch ex As Exception
Console.WriteLine("There has been a VB error. " + ex.Message)
Do While ex.InnerException IsNot (Nothing)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
End Try

Hope that helps.

Easy Question: Handling Login Exceptions

I have some very simple code that connects to an SQL Server instance using the SMO Server object. I have everything working fine, but I would like to be able to know if the login failed and respond to the error. Does anyone have a snippet that will do this? Do have to catch an exception or handle an event or....

Thanks!

This is how I connect in SMO (VB.Net):

' Connect to the server

Dim srvMgmtServer As Server

srvMgmtServer = New Server("MyServer\MyInstance")

Dim srvConn As ServerConnection

srvConn = srvMgmtServer.ConnectionContext

srvConn.LoginSecure = True

Hope that helps.

|||Ok, that is fine, but what if "MyServer\MyInstance" doesn't exist, or the user supplies invalid credentials? I want to be able to tell the user the login failed and why. There must be an exception thrown somewhere, but I can’t find a simple way to handle an invalid login. Right now I am opening the connection to the server right after I create the instance, and then catching any exceptions, but this seems like a bit of a hack. There has to be a way to do something like this:

try
{

srvMgmtServer = New Server("MyServer\MyInstance")

}
catch(SomeException)
{

//Do Stuff Because The Login Failed

}
|||

Yes, I'm sorry. The method of handling the exception is exactly what you've shown. If you catch(ex), then you can scroll through the InnerException property until you get to the base level. When I just catch the error at the top level the Message property contains ""Failed to connect to server MyServer\MyInstance."

In VB.Net the code looks something like this:

Catch ex As Exception
Console.WriteLine("There has been a VB error. " + ex.Message)
Do While ex.InnerException IsNot (Nothing)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
End Try

Hope that helps.

sql

Dynamicly setting events

I have the following code and I'm trying to set an event dynamicly in the foreach statement...

SqlConnection

conn =newSqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]);SqlCommand select =newSqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn);SqlDataAdapter da =newSqlDataAdapter(select);DataSet ds =newDataSet();

conn.Open();

da.Fill(ds);

conn.Close();

Table table =newTable();

foreach (DataRow drin ds.Tables[0].Rows)

{

TableRow tr =newTableRow();for (int i = 0; i < dr.ItemArray.Length; i++)

{

TableCell tc =newTableCell();

tc.Text = dr.ItemArray.GetValue(i).ToString();

tc.BackColor =

Color.White;if (dr.ItemArray.GetValue(8).ToString() =="COMPLETE")

{

tc.BackColor =

Color.BlueViolet;

}

tr.Cells.Add(tc);

}

table.Rows.Add(tr);

}

foreach (TableRow tablerowin table.Rows)

{

TableCell newtc =newTableCell();Button mybutton =newButton();

newtc.Controls.Add(mybutton);

mybutton.Text =

"details";

//set the event here

tablerow.Cells.Add(newtc);

}

Panel1.Controls.Add(table);

DataBind();

I am assuming you have added a button dynamically, and you want to create a handler for this button.

First you need to create a function to do what you want, e.g.

Sub myButtonClicked (sender, args)

....

End sub

Then you need to tell asp.net that this is the function to run when the button is clicked. In your code, before you add the button to the tablecell, write:

AddHandler myButton.click, AddressOg myButtonClicked

A little warning though - this may not work. This is because you have to create any dynamic controls everytime the page load. Have a read ofthis page which should help you.

|||

thanks for the response.

The solution that I have found is to add the eventhandler and specify what you want to do init. In this case redirect towww.google.com :

protected

void Page_Load(object sender,EventArgs e)

{

SqlConnection conn =newSqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]);SqlCommand select =newSqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn);SqlDataAdapter da =newSqlDataAdapter(select);DataSet ds =newDataSet();

conn.Open();

da.Fill(ds);

conn.Close();

Table table =newTable();

foreach (DataRow drin ds.Tables[0].Rows)

{

TableRow tr =newTableRow();for (int i = 0; i < dr.ItemArray.Length; i++)

{

TableCell tc =newTableCell();

tc.Text = dr.ItemArray.GetValue(i).ToString();

tc.BackColor =

Color.White;if (dr.ItemArray.GetValue(8).ToString() =="COMPLETE")

{

tc.BackColor =

Color.BlueViolet;

}

tr.Cells.Add(tc);

}

table.Rows.Add(tr);

}

foreach (TableRow tablerowin table.Rows)

{

TableCell newtc =newTableCell();Button mybutton =newButton();

newtc.Controls.Add(mybutton);

mybutton.Text =

"details";

mybutton.Click +=

newEventHandler(mybutton_Click);

tablerow.Cells.Add(newtc);

}

Panel1.Controls.Add(table);

DataBind();

}

void mybutton_Click(object sender,EventArgs e)

{

Response.Redirect(

"http://www.google.com");

}

Dynamically passing a Parameter Name to Custom Code

Hello,

I'm trying to create a custom code function for Reporting Services. I would like to have it user-friendly and give the user the ability to pass a report parameter name into the function (so it can be a generic function that can be used for many reports).

Is there a way to do this so inside the code I can have access to other properties of the object?

I envision something like:

Function Blah(ParameterName as String) as String

Dim MaxNum as Integer
Dim ParamValue as String

MaxNum = Reports.Parameters!ParameterName.Count - 1
ParamValue = Reports.Parameters!ParameterName.Value

etc...

Is this possible? I can't figure out how to do this. Using dynamic SQL you can do this very easily by concatenating string values together and then executing the string. Is there something similar to this in VB?

Help! Thanks

Below is an example for a custom code function that you can call in a textbox e.g. as

=Code.ShowParametersValues(Parameters!Country)

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

-- Robert

Dynamically Hide Table columns through code ?

How can I hide columns of table dynamically through code ? I am using Sql
Server Reporting Service 2005.
Best Regards,
LuqmanLuqman,
You should be able to click on the column heading and then right-click and
choose properties. In the properties window make sure it shows that you have
selected the entire column...should be something like "tablecolumn3". In the
Visibility property click on the Hidden property and set your expression
there. This will hide the column based on the condition you specify.
If you need the column visibility to toggle between hidden/visible after the
report is rendered, you will need to specify a "ToggleItem" under the
Visibility property. This will allow the end user to control the visibility
of the column.
Hope that helps
Luqman wrote:
>How can I hide columns of table dynamically through code ? I am using Sql
>Server Reporting Service 2005.
>Best Regards,
>Luqman
--
Message posted via http://www.sqlmonster.com|||On Sep 13, 10:46 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Luqman,
> You should be able to click on the column heading and then right-click and
> choose properties. In the properties window make sure it shows that you have
> selected the entire column...should be something like "tablecolumn3". In the
> Visibility property click on the Hidden property and set your expression
> there. This will hide the column based on the condition you specify.
> If you need the column visibility to toggle between hidden/visible after the
> report is rendered, you will need to specify a "ToggleItem" under the
> Visibility property. This will allow the end user to control the visibility
> of the column.
> Hope that helps
> Luqman wrote:
> >How can I hide columns of table dynamically through code ? I am using Sql
> >Server Reporting Service 2005.
> >Best Regards,
> >Luqman
> --
> Message posted viahttp://www.sqlmonster.com
Each column u can refer as textbox and it has a property called ->
Visibility -> Hidden' - U can provide expression here to Hide/Show

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...

> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.

> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.

> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.

> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.

> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:
Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegro ups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.
|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/is.../default.aspx.
|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...

> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.

> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.

> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.

> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.

> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?

>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.

>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:

> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.

>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...

> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)

> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.

> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.

> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.

> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Dynamically generate table name?

The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
--
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.

Dynamically controlling a cell in 2005 SRS

Is there a way to put code (i.e. an Expression) behind a cell in a
table that will control the background and foreground colors of the
cell itself?
For example (this is pseudocode to indicate what I want to happen)
If =Fields!policy_count.Value > 5 then
Set background and foreground to "Red"
else
Set background and foreground to "Green"
endif
Thanks!!Couple issues here:
First, to answer your main question, yes you can do this with
expressions but you're going to need to set up two sepearte
expressions; one for each of the cell properties you want to change,
BackgroundColor and Color. Color is the property for font color i.e.
Foreground.
Insert code something like =IIF(Fields!policy_count.Value >
5,"Red","Green") as the Expression for the property in question. That
should give you the appearance you desire.
Second thing and maybe I'm just reading too much in but you don't want
use the same expression for both properties otherwise you'll end up
with just all green or all red cells.
HTH
toolman
wardellcastles wrote:
> Is there a way to put code (i.e. an Expression) behind a cell in a
> table that will control the background and foreground colors of the
> cell itself?
> For example (this is pseudocode to indicate what I want to happen)
> If =Fields!policy_count.Value > 5 then
> Set background and foreground to "Red"
> else
> Set background and foreground to "Green"
> endif
> Thanks!!

Dynamic Update System

Hello folks!

I'm have some trouble with a dynamic update system! What I want to do:

1. I want to send in the code behind from any .aspx file values and parameters to a .vb class

2. Code (code behind in .aspx file):

1 gridupdate.updategrid("tblFeedbackA", e.CommandArgument,"locked=@.locked","@.locked","int","0") 'Call de .vb class
2 getgridfeedback()

3. Code (code in .vb class)

1Public Shared Function updategrid(ByVal tblnaamAs String,ByVal xAs Integer,ByVal sqlAs String,ByVal parametersAs String,ByVal sqltypeAs String,ByVal waardenAs String 'translation: values)2Dim dynstrAs String()3Dim dynstr2As String()4Dim reAs New Regex(",")5 dynstr = re.Split(parameters)6Dim iAs Integer7 dynstr2 = re.Split(sqltype)8Dim dynstr3As String()9 dynstr3 = re.Split(waarden)1011Dim sqlconnAs New SqlConnection(ConfigurationManager.ConnectionStrings("DataBase").ConnectionString)12Dim sql2As String ="UPDATE " & HttpContext.Current.Session("prefix").ToString & tblnaam &" set " & sql &" where id=" & x13Dim sqlcmdAs New SqlCommand(sql2, sqlconn)14 sqlconn.Open()1516For i = 0To dynstr.Length - 117 sqlcmd.Parameters.Add(dynstr(i),CType("sqldbtype." & dynstr2(i), System.Data.SqlDbType))18 sqlcmd.Parameters(dynstr(i)).Value = dynstr3(i)19Next20 sqlcmd.ExecuteNonQuery()21 sqlconn.Close()22Return Nothing23 End Function

All I want to do is to add the sqlparameters dynamically, but I don't find a way to do this :).

Can you help me out?
Thanks!

Hi Bert,

You are real close. You are trying to parse in a command string dynamically which usually wont work. A better way to do it, is to add paramaters to the sqlcommand method.

Rather than trying to parse a string together, do something like

sql2 = "UPDATE @.Parm1 SET @.Parm2 WHERE ID=@.Parm3"

sqlCmd.Paramaters.Addwithvalue("@.Parm1", HttpContext.Current.Session("prefix").ToString & tblnaam)

sqlCmd.Parmaters.addwithvalue("@.Parm2", sql)

sqlCmd.Parmaters.addwithvalue("@.parm3", x)

Sqlcmd.ExecuteNonQuery()

etc...

Give that a shot, see if it works for what you are trying to do.

Dynamic update of a column

Hi,
In the code below, I am trying to update a column to '0'. But, I dont know
which column to update until my variable @. Month fetches the column name.
DECLARE @.Month Varchar(20)
SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
WHERE MD.lMonthId = DTS.JobMonth
and DTS.JobId = 2
PRINT @.Month
update FTEActualForecast set @.Month = 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
Now, the problem is
1. The column I am trying to update to '0'is determined dynamically, so I
have used a variable name called @.Month
2. But in the UPDATE statement when I use the keyword SET it sets the
variable and not the column value to ‘zero’
Is there some workaround or better way of doing this ?
Please advise.
Cheers,
Hemil.Try
Exec ('update FTEActualForecast set ' + @.Month + '= 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
"Hemil" wrote:

> Hi,
> In the code below, I am trying to update a column to '0'. But, I dont kno
w
> which column to update until my variable @. Month fetches the column name.
> DECLARE @.Month Varchar(20)
> SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
> WHERE MD.lMonthId = DTS.JobMonth
> and DTS.JobId = 2
> PRINT @.Month
> update FTEActualForecast set @.Month = 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> Now, the problem is
> 1. The column I am trying to update to '0'is determined dynamically, so I
> have used a variable name called @.Month
> 2. But in the UPDATE statement when I use the keyword SET it sets the
> variable and not the column value to ‘zero’
>
> Is there some workaround or better way of doing this ?
> Please advise.
> Cheers,
> Hemil.
>|||Thanks a lot, Tom.
It does the job.
Thanks again,
Hemil.
"Tom" wrote:
> Try
> Exec ('update FTEActualForecast set ' + @.Month + '= 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
>
> "Hemil" wrote:
>|||Please include DDL with questions like this so that we don't have to guess
what your table looks like.
Why separate columns for each month? Instead, add a DATETIME column to
record the month then you can specify which row to update with a WHERE
clause.
David Portas
SQL Server MVP
--

Dynamic Tool Configuration

Newbie here wanting to get information, links, opinions on how to configure a simple data flow from code. Here's my example. I have a CSV input tool, a SORT tool, and a CSV output tool. I want to call this package from an application running C#. Can I access this package like DataPack("filename","sort","output filename") from the object model? Or does XML have to get involved? Can I dynamically swap out the input or output tools? Any links to the UML mockup of the object model? Just getting started here.

Thanks in advance,
Mitch

Start here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/60fe148b-a7c4-4289-ae3e-2e949fc1886c.htm

dynamic table name (was "Please help ! SQL")

Hi--

I am new to T-SQL i got this code from some website but its not working can anyone let me know why
@.cur_tab_name varchar(30) is not decleared while i have decleared
I want to use this for dynamic name of the table.

Thanks.


create table temp_tab
(
tab_name varchar(30),
no_of_rows INTEGER,
)

DECLARE
curREVIEW

CURSOR FOR
select name
from sysobjects
where xtype = 'U'

DECLARE @.cur_tab_name varchar(30)

OPEN curREVIEW

FETCH curREVIEW INTO @.cur_tab_name

WHILE (@.@.FETCH_STATUS =0)
BEGIN
DECLARE @.count integer

select @.count = count(*) from @.cur_tab_name
INSERT INTO temp_tab
(@.cur_tab_name, @.count)

FETCH curREVIEW INTO @.cur_tab_name

END

CLOSE curREVIEW

DEALLOCATE curREVIEWDynamic SQL must be passed as a string to EXEC:

EXEC ('select @.count = count(*) from ' + @.cur_tab_name)

...but this still will not give you what you want, because the code will EXECute in a different scope than your @.count variable.

Try this:

Declare @.SQLString varchar(500)
Set @.SQLString = 'INSERT INTO temp_tab select ''' + @.cur_tab_name + ''', count(*) from ' + @.cur_tab_name
EXEC (@.SQLString)