Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

sql

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

create table test

hi,
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
--
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > hi,
> >
> > What is the syntax for creating a new table as that of
> > existing one with data..
> >
> > create table test1 as select * from test is not working.
> >
> >
> > Regards
> > Krish
>|||That's right. However, IDENTITY property will be copied to new table.
--
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > SELECT * INTO NewTable FROM OldTable
> >
> > --
> > Rohtash Kapoor
> > http://www.sqlmantra.com
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > hi,
> > >
> > > What is the syntax for creating a new table as that of
> > > existing one with data..
> > >
> > > create table test1 as select * from test is not working.
> > >
> > >
> > > Regards
> > > Krish
> >
> >
>|||Hi,
Yes, That is correct.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > To add on, this command just copies the table structure and data.
Indexes
> ,
> > Constraints and Identity property
> > needs to be created manually.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> > news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > > SELECT * INTO NewTable FROM OldTable
> > >
> > > --
> > > Rohtash Kapoor
> > > http://www.sqlmantra.com
> > >
> > >
> > >
> > > <anonymous@.discussions.microsoft.com> wrote in message
> > > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > > hi,
> > > >
> > > > What is the syntax for creating a new table as that of
> > > > existing one with data..
> > > >
> > > > create table test1 as select * from test is not working.
> > > >
> > > >
> > > > Regards
> > > > Krish
> > >
> > >
> >
> >
>

Create Table Syntax

Hi Guys
Really need your help I donno what I am doing wrong in here
Want to create a table with another existing table
Here is the syntax I am using

create table pctemp1
As
(SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date]
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null )

and it says Syntax error with AS clause, tried removing AS clause but no go , can anybody help me please ...

thanksselect * into NEW TABLE NAME from OLD TABLE NAME--
THIS IS THE SYTAX..YOU CAN USE THIS FOR UR NEED|||SELECT distinct a.Promo,b.Ban,b.[Ban Status],
b.[BAn Statys Reson Code],b.[Last Ban Status Date] INTO NEW_TABLE_NAME
FROM PC_FUSION_070424 a
LEFT OUTER JOIN ARCL05_070423 b
ON a.BAN = b.BAN
WHERE b.BAN is not null

TRY THIS...

Create Table

Can i create a table using the structure of an existing table, instead of defining the columns one by one?

e.g.
I already have table1(col1 int,col2 char(3))

I want to create another table with the same structure as table 1 without doing the following:
create table table2(col1 int,col2 char(3))

Is there a command of doing create table2 as table1 let say?Depending on the size of the table you could do the following:

select * into newtable from oldtable|||if you don't want to include data in newtable, you should modify the query as below:

select * into newtable from oldtable where 0=1|||Just what I was going to add:

If you want table structure and data then

select * into newtable from oldtable

else

select * into newtable from oldtable where (statement is false)

Create SQL sever 2005 clustering in existing non cluster SQL 2005

Hi (I send one before but looklike it did not pass through)
I have questions as below.
1) On production server win 2003 64 bits, I can not see Cluster by
ClusterAdmin, Quarum is the folder in C:\Drive, SAN is only shared drive, SQL
logon by local system account, Sql connect as (local) or ComputerName from
Management Studio. Get info from my boss, it was SQL server 2005 Ent
clustering on this server. What else should I check?
2) Get info that I need to add just only SQL Virtual server name and IP
address to make it clustering. If item#1 is true, How? Any modification?
In the case I found single instance, I will detach databases, uninstall
SQL server, make cluster, reinstall and attached databasesto shared disk but
this scenario is beyond my knowledge.
This is new to me, please help.
Thanks
Please rephrase the question. Are you asking what to do if the cluster fails
and can't be found in cluster administrator or are you asking how to
reinstall sql.
It sounds like a little of both.
"James" wrote:

> Hi (I send one before but looklike it did not pass through)
> I have questions as below.
> 1) On production server win 2003 64 bits, I can not see Cluster by
> ClusterAdmin, Quarum is the folder in C:\Drive, SAN is only shared drive, SQL
> logon by local system account, Sql connect as (local) or ComputerName from
> Management Studio. Get info from my boss, it was SQL server 2005 Ent
> clustering on this server. What else should I check?
> 2) Get info that I need to add just only SQL Virtual server name and IP
> address to make it clustering. If item#1 is true, How? Any modification?
> In the case I found single instance, I will detach databases, uninstall
> SQL server, make cluster, reinstall and attached databasesto shared disk but
> this scenario is beyond my knowledge.
> This is new to me, please help.
> Thanks
|||Thank Burt,
I am sorry for confusion.
First: In my scenario, I open ClusterAdmin but can not found any cluster in
that server, can not found quorum disk and SQL server start up logon as Local
account.
My boss told me that this is SQL server 2005 Clustering on wins 2003 64
bits. I do not beleive this is true.
--> I want to ask, How can I confirm it was true as my boss said?
Do I have enough fact as above to tell there have no clustering in this
server?
Second: If my boss correct, he said I need to create SQL virtual name and IP
address to make it clustering no need to reinstall SQL server 2005.
-->I want to ask, What should I do?
If my boss misunderstand what is the other fact that I need to support my
idea?
Thanks you so much.
"burt_king" wrote:
[vbcol=seagreen]
> Please rephrase the question. Are you asking what to do if the cluster fails
> and can't be found in cluster administrator or are you asking how to
> reinstall sql.
> It sounds like a little of both.
>
> --
> "James" wrote:
|||The simplest thing to do is ask him what the virtual name of the server is.
Then go to a command prompt on the server and enter "hostname" as the
command. The return value should be different than the virtual server name.
If you're using cluster administrator correctly (note that you can look at
local as well as remote clusters) then it sounds like it's not a cluster.
--DatabaseAdmins.com, LLC
Remote DBA Services for SQL and Oracle.
"James" wrote:
[vbcol=seagreen]
> Thank Burt,
> I am sorry for confusion.
> First: In my scenario, I open ClusterAdmin but can not found any cluster in
> that server, can not found quorum disk and SQL server start up logon as Local
> account.
> My boss told me that this is SQL server 2005 Clustering on wins 2003 64
> bits. I do not beleive this is true.
> --> I want to ask, How can I confirm it was true as my boss said?
> Do I have enough fact as above to tell there have no clustering in this
> server?
> Second: If my boss correct, he said I need to create SQL virtual name and IP
> address to make it clustering no need to reinstall SQL server 2005.
> -->I want to ask, What should I do?
> If my boss misunderstand what is the other fact that I need to support my
> idea?
> Thanks you so much.
> "burt_king" wrote:
|||Thank ypu again Burt,
I ask her and she reply that we just delete the clustering out but the SQL
that create on clustering still run. (I have no idea, but it is working so I
think it is not install in clustering)
They will create new windows clustering again. The hard part is
"they do not allow me to reinstall or create name instance but want me to
only add SQL virtual name and IP address and make it clustering".
--> last question: Is it possible to make SQL server 2005 local(I believe it
is local) to be SQL server 2005 clustering by add SQL virtual name and IP
address without reinstall (or new install) after network guy create windows
clustering? If yes,How?
Thank you so much.
"burt_king" wrote:
[vbcol=seagreen]
> The simplest thing to do is ask him what the virtual name of the server is.
> Then go to a command prompt on the server and enter "hostname" as the
> command. The return value should be different than the virtual server name.
> If you're using cluster administrator correctly (note that you can look at
> local as well as remote clusters) then it sounds like it's not a cluster.
> --
> --DatabaseAdmins.com, LLC
> Remote DBA Services for SQL and Oracle.
>
> "James" wrote:
|||Did you run the command at the command line and see what was returned? It's
hard to give good advice here because it seems like we're not getting the
whole picture.
Additionally, it sounds like your manager is asking if an existing instance
that is not clustered can be made a cluster without reinstalling: to my
knowledge no.
HTH
"James" wrote:
[vbcol=seagreen]
> Thank ypu again Burt,
> I ask her and she reply that we just delete the clustering out but the SQL
> that create on clustering still run. (I have no idea, but it is working so I
> think it is not install in clustering)
> They will create new windows clustering again. The hard part is
> "they do not allow me to reinstall or create name instance but want me to
> only add SQL virtual name and IP address and make it clustering".
> --> last question: Is it possible to make SQL server 2005 local(I believe it
> is local) to be SQL server 2005 clustering by add SQL virtual name and IP
> address without reinstall (or new install) after network guy create windows
> clustering? If yes,How?
>
> Thank you so much.
> "burt_king" wrote:
|||Thanks
As they said they delete the windows cluster out, and do not have SQL
virtual name. the hostname command show the server name when run that command.
Thank you for your answer.
Just coming up in my head, If you install SQL server 2005 over windows
clustering and you delete windows clustering out. Can SQL server still
survive or running? I never delete windows clustering before.
Thank you so much.
James
"oracleSQLdba" wrote:
[vbcol=seagreen]
> Did you run the command at the command line and see what was returned? It's
> hard to give good advice here because it seems like we're not getting the
> whole picture.
> Additionally, it sounds like your manager is asking if an existing instance
> that is not clustered can be made a cluster without reinstalling: to my
> knowledge no.
> HTH
>
> "James" wrote:
|||Sorry, what to you mean, "delete the windows cluster out"?
If you mean, can you uninstall windows cluster services, the answer is yes.
But if you're at the virtual server and run the hostname command and receive
that same name back then you're not likely to be on a cluster.
I think you may be best served by getting a consultant in there for a day to
look at what you've got and help you.
"James" wrote:
[vbcol=seagreen]
> Thanks
> As they said they delete the windows cluster out, and do not have SQL
> virtual name. the hostname command show the server name when run that command.
> Thank you for your answer.
> Just coming up in my head, If you install SQL server 2005 over windows
> clustering and you delete windows clustering out. Can SQL server still
> survive or running? I never delete windows clustering before.
> Thank you so much.
> James
> "oracleSQLdba" wrote:

create new user on MS SQL 2005 Professional edition problem

Hi Everyone,

Can you please kindly tell me how to create a new user on the MS SQL 2005 Professional edition for accessing the existing database called VCalendar?

thanks for your time and help!

Jiimmy

1. Create a login: Open Management Studio->MySQLInstance->Security->Logins->New Login, type the name for the new login, and select a default database for the new login.

2. In the 'User Mapping' panel, check the 'Map' checkbox for the databases (VCalendar) that you want the login to access. Make sure the new login has user mapping to its default database.

3. Click 'OK' to save the new login. You can press F1 for help in the process.

|||

Iori_Jay:

1. Create a login: Open Management Studio->MySQLInstance->Security->Logins->New Login, type the name for the new login, and select a default database for the new login.

2. In the 'User Mapping' panel, check the 'Map' checkbox for the databases (VCalendar) that you want the login to access. Make sure the new login has user mapping to its default database.

3. Click 'OK' to save the new login. You can press F1 for help in the process.

Thank you very much for you help! I will try it tonight.

Jimmy

|||

Iori_Jay:

1. Create a login: Open Management Studio->MySQLInstance->Security->Logins->New Login, type the name for the new login, and select a default database for the new login.

2. In the 'User Mapping' panel, check the 'Map' checkbox for the databases (VCalendar) that you want the login to access. Make sure the new login has user mapping to its default database.

3. Click 'OK' to save the new login. You can press F1 for help in the process.

No it does not work. I am using MS SQL Server 2005 Professional edition. Thanks,

Jimmy

|||

Go to the new security section inside VCalendar database and create the User but that just creates the database login, then go to the Management section of Management Studio and then create the server login for the User. The reason is there are two permissions in SQL Server database permissions in the new security section within the database and the Server permissions in the Security section in the management section of Management Studio. Another option is to use System Stored Procssp_adduser and sp_addlogin, try the links below for details. Hope this helps.

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

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

|||

Caddre:

Go to the new security section inside VCalendar database and create the User but that just creates the database login, then go to the Management section of Management Studio and then create the server login for the User. The reason is there are two permissions in SQL Server database permissions in the new security section within the database and the Server permissions in the Security section in the management section of Management Studio. Another option is to use System Stored Procssp_adduser and sp_addlogin, try the links below for details. Hope this helps.

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

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

First, thanks for your time and help! I try two ways and both of the ways don't work.

1st way I try:

Right click on Security of VCalendar -> select New -> select User (Database User - New window open) Now I enter jimmy_user into the User name: box and I enter jimmy_login into the Login name: box. And I got an error message such as: "Create failed for User 'jimmy_user'. .... 'jimmy_login' is not a valid login or you do not have permission."

2nd way I try:

expand SBTOR101EV\SQLEXPRESS -> expand Security -> Right click on Logins -> Choose New Login (Login - New window open) Now enter jimmy_login into the Login name: box -> And then select Windows authenticaion -> click on OK. And I got an error message such as:

"Create failed for Login 'jimmy_login'. .... 'jimmy_login is not a valid Windows NT name. Give the complete name:
<domain\username>."

Please just let me know if you find a way to solve me problem. Thanks again!

I am not 100% understand the following two links so that I didn't try it.

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

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

Jimmy

|||

MayLam:

2nd way I try:

expand SBTOR101EV\SQLEXPRESS -> expand Security -> Right click on Logins -> Choose New Login (Login - New window open) Now enter jimmy_login into the Login name: box -> And then select Windows authenticaion -> click on OK. And I got an error message such as:

"Create failed for Login 'jimmy_login'. .... 'jimmy_login is not a valid Windows NT name. Give the complete name:
<domain\username>."

Look at the error message, which indicates that you choose Window Authentiaction (should be something like domain\account) as the login type and that's why 'jimmy_login' is no valid. So please select SQL Server Authentication for the new login 'jimmy_login', and do remember to add necessary database mapping for the new login. For more information about Authentication Modes, you can take a look at this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

|||

I think the error means SQL Server wants the login created before the database permissions. So you can use the gui or modify the code in the links below for your server and database. Hope this helps.

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

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

|||

Iori_Jay:

MayLam:

2nd way I try:

expand SBTOR101EV\SQLEXPRESS -> expand Security -> Right click on Logins -> Choose New Login (Login - New window open) Now enter jimmy_login into the Login name: box -> And then select Windows authenticaion -> click on OK. And I got an error message such as:

"Create failed for Login 'jimmy_login'. .... 'jimmy_login is not a valid Windows NT name. Give the complete name:
<domain\username>."

Look at the error message, which indicates that you choose Window Authentiaction (should be something like domain\account) as the login type and that's why 'jimmy_login' is no valid. So please select SQL Server Authentication for the new login 'jimmy_login', and do remember to add necessary database mapping for the new login. For more information about Authentication Modes, you can take a look at this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

No this way does now work. Thanks for letting me know.

Jimmy

|||

Caddre:

I think the error means SQL Server wants the login created before the database permissions. So you can use the gui or modify the code in the links below for your server and database. Hope this helps.

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

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

No this way does not work. Thanks for letting me know.

Jimmy

|||

How about I uninstall MS SQL Server 2005 and then re-install MS SQL Server 2005. And I hope that it will ask me to choose a username and choose a password during the installation. Will it ask me choosing a username and choosing a password during the installation?

Thanks,

Jimmy

|||

MayLam:

Iori_Jay:

MayLam:

2nd way I try:

expand SBTOR101EV\SQLEXPRESS -> expand Security -> Right click on Logins -> Choose New Login (Login - New window open) Now enter jimmy_login into the Login name: box -> And then select Windows authenticaion -> click on OK. And I got an error message such as:

"Create failed for Login 'jimmy_login'. .... 'jimmy_login is not a valid Windows NT name. Give the complete name:
<domain\username>."

Look at the error message, which indicates that you choose Window Authentiaction (should be something like domain\account) as the login type and that's why 'jimmy_login' is no valid. So please select SQL Server Authentication for the new login 'jimmy_login', and do remember to add necessary database mapping for the new login. For more information about Authentication Modes, you can take a look at this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

No this way does now work. Thanks for letting me know.

Jimmy

Sorry I mean this way does not work. Thanks.

Jimmy

|||

How about I uninstall MS SQL Server 2005 and then re-install MS SQL Server 2005. And I hope that it will ask me to choose a username and choose a password during the installation. Will it ask me choosing a username and choosing a password during the installation?

Thanks,

Jimmy

|||

MayLam:

How about I uninstall MS SQL Server 2005 and then re-install MS SQL Server 2005. And I hope that it will ask me to choose a username and choose a password during the installation. Will it ask me choosing a username and choosing a password during the installation?

Thanks,

Jimmy

What is did is that

- uninstall the MS SQL Server 2005 Professional edition

- and then install the MS SQL Server 2005 Express edition.

And it works.

Thanks for all your time and all your help!

Jimmy

Create new Table from existing table

Hi

I'm trying to Create a new Table from existing table in Q/Analyzer. I figured it would be something like this:

CREATE TABLE newTable AS
(SELECT * FROM OldTable);

but i keep getting

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

also.. is there another method of doing this, something like

INSERT INTO newTable
(SELECT * FROM OldTable);

and it creates the table ( newTable ) for u if it doenst already exist??

Cheers!!!

im using sql2000

You can try this one:

SELECT

* INTO newTableFROM OldTable|||

The easiest method to copy a table with data is :

SELECT * INTO MyNewTable FROM MyTable

Note: this method does not copy constraints and indexes.

|||

SELECT * INTO will create the table and also transfer the data. If you just want the table structure you can do

SELECT * INTO newTable FROM OldTable WHERE 1=0

You would need to add any constraints/indexes manually.

|||

Thanks for ya help fellas

all helpfull answers

Cheers!!!

create new table from base table

Hi,
I am trying to create a new table from an existing base table. The base
table isn't normalised in any way. The table was given to me and that's
what I have to work with. The problem is that parent ids can have
multiple sectors and the second level records only have 1 sector. I
want to iteratate to the parent record and take all the sector
available and put them in the new table together with the alfacode.
base table:
id, name, parent, alfacode, level, sector
eg.
1, test, 0, 100, 0, 122
1, test, 0, 100, 0, 123
2, test1, 1, 101, 1, 122
3, test2, 1, 102, 1, 122
destination table:
alfacode, sector
The destination table with the data above would become:
100, 122
100, 123
101, 122
101, 123
102, 122
102, 123
Hope I made myself clear.
Any help would be greatly appreciated.
PezkelHi Pezkel
Try something like:
SELECT a.alfacode, a.sector
FROM #structure a
WHERE a.level = 0
UNION
SELECT b.alfacode, b.sector
FROM #structure a
JOIN #structure b ON A.id = b.parent
UNION
SELECT b.alfacode, a.sector
FROM #structure a
JOIN #structure b ON A.id = b.parent
John
"barcode@.dds.nl" wrote:

> Hi,
> I am trying to create a new table from an existing base table. The base
> table isn't normalised in any way. The table was given to me and that's
> what I have to work with. The problem is that parent ids can have
> multiple sectors and the second level records only have 1 sector. I
> want to iteratate to the parent record and take all the sector
> available and put them in the new table together with the alfacode.
> base table:
> id, name, parent, alfacode, level, sector
> eg.
> 1, test, 0, 100, 0, 122
> 1, test, 0, 100, 0, 123
> 2, test1, 1, 101, 1, 122
> 3, test2, 1, 102, 1, 122
> destination table:
> alfacode, sector
> The destination table with the data above would become:
> 100, 122
> 100, 123
> 101, 122
> 101, 123
> 102, 122
> 102, 123
> Hope I made myself clear.
> Any help would be greatly appreciated.
> Pezkel
>|||Hi John,
Thanks. I will give it a spin tomorrow.|||Hi John,
Just ran the query and it works like a charm. Very much appreciated.

Create Maintenance Plans on SQL2K and SQL2005 w/SQL Mgmt. Studio?

I don't see any way to run the Maintenance Plan wizard against my existing SQL2K or SQL2005 DBs using the MS SQL Maintenance Studio. I read in another thread about having to install SSIS, and I think I have that on the SQL2005, but how is it done against the SQL2K DBs? I have dug around in the Mgmt Studio to no avail. If this thing is not fully backward compatible, then in addition to being slow as an old dog on a 2.5Ghz computer, then I really think MS has not demonstrated how cool this .NET stuff can be (or have they). What a sink for CPU cycles.

Do you have SQL 2000 server tools installed as a seperate instance, if so why not schedule with this server instance.

True that you have to use SSIS in order to run the maintenance plan and schedule that a seperate job, refer to the books online for more information in this regard.

|||

Yes, I do have the SQL 2000 Enterprise Manager installed, but I was trying to transition to a single set of tools to manage all my servers. One would think that MS would support fully the next most recent version.

What would be the way to verify if the SSIS is installed on a specific server?

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:

> Hello,
> I've this problem with a database user that was restored from a backup. If i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else.
> Is it possible to work around it.
> THX!
>
>
|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
If i[vbcol=seagreen]
else.[vbcol=seagreen]
|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:

> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.
>
> "Ezekiël" wrote:
> > Hello,
> >
> > I've this problem with a database user that was restored from a backup.
If i
> > want to re-create the same username for sql login, it fails. This also
> > happens when i try to delete the user to set the ownership to someone
else.
> >
> > Is it possible to work around it.
> >
> > THX!
> >
> >
> >

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:

> Hello,
> I've this problem with a database user that was restored from a backup. If
i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else
.
> Is it possible to work around it.
> THX!
>
>|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
>
If i[vbcol=seagreen]
else.[vbcol=seagreen]|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:

> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>

create index with drop existing

Create index with drop existing after succesfull creation,
when dbcc checktable is run, at times it does not finish
at all, when killed and again submitted runs okay.
Vinodh,
Are you saying that the dbcc does not finish or the create index? How long
did you wait? How big is the table, and how many indexes on there?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Vinodh" <anonymous@.discussions.microsoft.com> wrote in message
news:b85901c437d6$ef3b81c0$a401280a@.phx.gbl...
> Create index with drop existing after succesfull creation,
> when dbcc checktable is run, at times it does not finish
> at all, when killed and again submitted runs okay.
>

create index with drop existing

Create index with drop existing after succesfull creation,
when dbcc checktable is run, at times it does not finish
at all, when killed and again submitted runs okay.Vinodh,
Are you saying that the dbcc does not finish or the create index? How long
did you wait? How big is the table, and how many indexes on there?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Vinodh" <anonymous@.discussions.microsoft.com> wrote in message
news:b85901c437d6$ef3b81c0$a401280a@.phx.gbl...
> Create index with drop existing after succesfull creation,
> when dbcc checktable is run, at times it does not finish
> at all, when killed and again submitted runs okay.
>

create index with drop existing

Create index with drop existing after succesfull creation,
when dbcc checktable is run, at times it does not finish
at all, when killed and again submitted runs okay.Vinodh,
Are you saying that the dbcc does not finish or the create index? How long
did you wait? How big is the table, and how many indexes on there?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Vinodh" <anonymous@.discussions.microsoft.com> wrote in message
news:b85901c437d6$ef3b81c0$a401280a@.phx.gbl...
> Create index with drop existing after succesfull creation,
> when dbcc checktable is run, at times it does not finish
> at all, when killed and again submitted runs okay.
>

Create full sql script from existing mdf

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

Take a look at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1

|||

Or just use “generate scripts wizard”:

Right-click on the database,

choose “TASKS-> Generate Scripts” and simply follow the wizard steps.

Regards,

Alfred.

Create full sql script from existing mdf

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

Take a look at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1

|||

Or just use “generate scripts wizard”:

Right-click on the database,

choose “TASKS-> Generate Scripts” and simply follow the wizard steps.

Regards,

Alfred.

CREATE DATABASE from Template?

Hello,
I need to be able toCREATE DATABASE by copying an existing database.
I would be doing this inside of a web app during an event.
How do I set this up on SQL 2005 ?

Thanks!

Here's an article on how to do it in PostgreSQL http://www.enterprisedb.com/documentation/manage-ag-templatedbs.htmlSQL Server 2005 has 'Copy Database Wizard' in Management Studio; you can also copy databases with Backup and Restore. But both methods seems not so easier to be done inside of web app during an event. Anyways you can take a look at 'Copying Databases to Other Servers' topic in SQL2005 Books Online.|||What if i made a backup of my "template" db
then had a stored proc like:

create procedure restoredb
@.dbname sysname
as
restore database @.dbname from disk='c:\backup.bak'
with move 'file_data' to 'd:\mssql\mssql\data\' + @.dbname + '_data.mdf',
move 'file_log' to 'd:\mssql\mssql\data\' + @.dbname + '_log.ldf',
replace

that created the new db

create custom rendering extension

Hi,
I want to create a custom rendering extension based on a existing rendering,
I have seen in the MSDN that it is possible but the documentation is not
complet
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_security_87oi.asp
If somebody know how i can do ...
Thanks in advance...here is an article from MSDN mag:
http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/default.aspx
"blue-ice" <blueice@.discussions.microsoft.com> wrote in message
news:570C5B8F-50F3-4A58-9BB1-3790D5D72B7A@.microsoft.com...
> Hi,
> I want to create a custom rendering extension based on a existing
> rendering,
> I have seen in the MSDN that it is possible but the documentation is not
> complete
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_security_87oi.asp
> If somebody know how i can do ...
> Thanks in advance...
>|||Thanks but I want create a new rendering extension based on the existing PDF
extension
"Dmitry Nechipor. [MCDBA]" wrote:
> here is an article from MSDN mag:
> http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/default.aspx
>
> "blue-ice" <blueice@.discussions.microsoft.com> wrote in message
> news:570C5B8F-50F3-4A58-9BB1-3790D5D72B7A@.microsoft.com...
> > Hi,
> >
> > I want to create a custom rendering extension based on a existing
> > rendering,
> > I have seen in the MSDN that it is possible but the documentation is not
> > complete
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_security_87oi.asp
> >
> > If somebody know how i can do ...
> >
> > Thanks in advance...
> >
>
>