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 template, Management Studio Express

I accidentally overwrote the CREATE TABLE template in SQL Server Management Studio Express. Could someone please post the original template?

FYI

-- =========================================
-- Create table template
-- =========================================
USE <database, sysname, AdventureWorks>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

CREATE TABLE Template

I accidentally altered the CREATE TABLE template from SQL Server Management Studio Express. Now I don't have the original. Could somebody please post CREATE TABLE template.

-- =========================================

-- Create table template

-- =========================================

USE <database, sysname, AdventureWorks>

GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL

DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>

GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>

(

<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,

<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,

<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,

CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)

)

GO

|||

Or the simplified version:

Create Table MyTable(FirstField varchar(50), SecondField int)

or...

when in doubt >> Right Click in Management Studio to create a new table manually

Adamus

|||

Thanks, joeydj,

johncelmer

sql

Create table in schema in Managment Studio

Hi all, if I create a new schema:
CREATE SCHEMA MySchema
it appears in Sql Server Management Studio's schema, but it's not
obvious to me how to create a new table (or other object) under this
schema, without using the CREATE TABLE statement. How can I create a
new table under this schema using the designed in Managment Studio?
This is Sql Server 2005 (Standard Edition) I'm using.
Thanks!
Richard
Hello, Richard
When you create a new table using Management Studio, choose "View /
Properties Window" (or press F4) and select the desired schema in the
combo for the "Schema" property (under the "(Identity)" category).
Razvan

Create table in schema in Managment Studio

Hi all, if I create a new schema:
CREATE SCHEMA MySchema
it appears in Sql Server Management Studio's schema, but it's not
obvious to me how to create a new table (or other object) under this
schema, without using the CREATE TABLE statement. How can I create a
new table under this schema using the designed in Managment Studio?
This is Sql Server 2005 (Standard Edition) I'm using.
Thanks!
RichardHello, Richard
When you create a new table using Management Studio, choose "View /
Properties Window" (or press F4) and select the desired schema in the
combo for the "Schema" property (under the "(Identity)" category).
Razvan

Create table in schema in Managment Studio

Hi all, if I create a new schema:
CREATE SCHEMA MySchema
it appears in Sql Server Management Studio's schema, but it's not
obvious to me how to create a new table (or other object) under this
schema, without using the CREATE TABLE statement. How can I create a
new table under this schema using the designed in Managment Studio?
This is Sql Server 2005 (Standard Edition) I'm using.
Thanks!
RichardHello, Richard
When you create a new table using Management Studio, choose "View /
Properties Window" (or press F4) and select the desired schema in the
combo for the "Schema" property (under the "(Identity)" category).
Razvan

Create Table and Alter table:

Hi All,

I am using SQl server studio management to create table.

How to set two attributes as a primary key: composite key. like ( proj_id, emp id) both as primary key.

How to specify the forign key constraint. using alter table

Please give me the example: don't syntax which msdn gives.

Thanks and Regards

Abdul M.G

Hi,

Look at this.

1CREATE TABLE Menu2(3 MenuIdint,4 Titlevarchar(50),5 Urlvarchar(256),6 ParentIdintNULL7)89ALTER TABLE MenuADD CONSTRAINT MenuIdPRIMARY KEY1011ALTER TABLE MenuADD CONSTRAINT ParentIdREFERENCES Menu(MenuId)

sql

Create script in 2005 without the [] delimiters around the object names

Anyone know how to permanently set up SQL 2005 Server Management Studio to NOT put those silly [ and ] delimiters around object names when using the CREATE SCRIPT to... funtionality from object explorer?

Moving thread to the Tools General forum because they'll be better able to answer your question.

-Jeffrey

Create Package in SQL Server Management Studio Express?

Our server currently has the following components installed for SQL Server 2005: 1. SQL Server Management Studio Express and 2. Configuration Tools (SQL Server Configuration Manager, SQL Server Error and Usage Reporting, and SQL Server Service Area Configuration).

Is there a way to setup a package using the software currently installed (if not, what needs to be installed in order to setup a package)?

I'm looking to schedule running an executible, it was fairly easy with SQL Server 2000 (using DTS), but I'm unsure how to set this up using the software we currently have installed.

It looks like you are using SQL Sever 2005 express?

Sorry but SSIS doesn't come with express, you'll need Standard edition or better:

see the section, "Integration and Interoperability":

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||P.S. Installing standard edition tools seems to do the trick (be sure to install Integration Services).

Create new schema using management studio

instead of CREATE SCHEMA using T-SQL

Open the database node > Your Database > Security > Schemas (be sure that your database is a SQL Server 2005 database)

Jens K. Suessmeyer

http://www.sqlserver2005.de

Create login problem in SQL Server 2005

Hey guys,

I'm having a problem making a new login inside the sql management studio, the problem is, when i create a new login, i selected SQL Authentication, then type a password, then uncheck Enforce password policy.

i then select the database i want the login to be associated with, but once i click ok i get this exception:
Create failed for Login ''. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)

I even tried with Northwind and a brand new database with a table and 2 columns but it's the same story every time.
Any ideas?

Thanks a bunchMake sure you have entered "Login Name" in the text box provided at the top of the window

thanks
Anoop

Create folders in Object Explorer - wish...

Here's one thing that I'd like to see come out in some version of the SQL Server Management Studio...

The ability to create folders under the database node so that databases can be grouped on one server.

We have over 100 databases on our development server and these are created by a range of consultants and developers and even support staff as needed.

Being able to group the databases by product, etc would be a nice touch since we have client databases that don't fit naming conventions etc.

Multiple instances are another way around this but are expensive and resource hungry - we develop and support models, not use them for transactions too much.

Yes there are 'better' ways such as setting security correctly but we are too busy working and not maintaining.

Folders or database groups would be a nice touch.

Cheers

I encourage you to offer the suggestion here, and to encourage others so inclined to 'vote' for it. (Enhancement suggestions run somewhat like a popularity contest.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

CREATE FILE access denied

I get this error message when trying to create a database. This happens from
the Management Studio, from regular queries, etc. No other posts seem to
resolve this problem exactly that I can find. Thanks for any insight into
this problem...
USE MASTER
CREATE DATABASE XYZ
ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
Error message:
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'c:\XYZ_data.mdf'.
Hi
Check that the account that SQL Server runs under has file system
permissions on c:\
OS Error 5 = access denied.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"XCode247" <XCode247@.discussions.microsoft.com> wrote in message
news:7DE6CBB0-087A-4CD2-9B45-AEAE45532C06@.microsoft.com...
>I get this error message when trying to create a database. This happens
>from
> the Management Studio, from regular queries, etc. No other posts seem to
> resolve this problem exactly that I can find. Thanks for any insight into
> this problem...
> USE MASTER
> CREATE DATABASE XYZ
> ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
> LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
> Error message:
> Msg 1802, Level 16, State 4, Line 2
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> Msg 5123, Level 16, State 1, Line 2
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'c:\XYZ_data.mdf'.
>
|||Yes, sure enough...
In Configuration Manager, Service properties. The 'Log on as' account was
'Network Service'. The help button from that page says a 'domain user account
with minimal rights' is recommended. Changing the account causes it to work.

CREATE FILE access denied

I get this error message when trying to create a database. This happens from
the Management Studio, from regular queries, etc. No other posts seem to
resolve this problem exactly that I can find. Thanks for any insight into
this problem...
USE MASTER
CREATE DATABASE XYZ
ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
Error message:
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'c:\XYZ_data.mdf'.Hi
Check that the account that SQL Server runs under has file system
permissions on c:\
OS Error 5 = access denied.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"XCode247" <XCode247@.discussions.microsoft.com> wrote in message
news:7DE6CBB0-087A-4CD2-9B45-AEAE45532C06@.microsoft.com...
>I get this error message when trying to create a database. This happens
>from
> the Management Studio, from regular queries, etc. No other posts seem to
> resolve this problem exactly that I can find. Thanks for any insight into
> this problem...
> USE MASTER
> CREATE DATABASE XYZ
> ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
> LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
> Error message:
> Msg 1802, Level 16, State 4, Line 2
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> Msg 5123, Level 16, State 1, Line 2
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'c:\XYZ_data.mdf'.
>|||Yes, sure enough...
In Configuration Manager, Service properties. The 'Log on as' account was
'Network Service'. The help button from that page says a 'domain user account
with minimal rights' is recommended. Changing the account causes it to work.

CREATE FILE access denied

I get this error message when trying to create a database. This happens from
the Management Studio, from regular queries, etc. No other posts seem to
resolve this problem exactly that I can find. Thanks for any insight into
this problem...
USE MASTER
CREATE DATABASE XYZ
ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
Error message:
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'c:\XYZ_data.mdf'.Hi
Check that the account that SQL Server runs under has file system
permissions on c:\
OS Error 5 = access denied.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"XCode247" <XCode247@.discussions.microsoft.com> wrote in message
news:7DE6CBB0-087A-4CD2-9B45-AEAE45532C06@.microsoft.com...
>I get this error message when trying to create a database. This happens
>from
> the Management Studio, from regular queries, etc. No other posts seem to
> resolve this problem exactly that I can find. Thanks for any insight into
> this problem...
> USE MASTER
> CREATE DATABASE XYZ
> ON (Name = XYZ_data, FILENAME='C:\\XYZ_data.mdf')
> LOG ON (Name = XYZ_log, FILENAME='C:\\XYZ_log.ldf')
> Error message:
> Msg 1802, Level 16, State 4, Line 2
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> Msg 5123, Level 16, State 1, Line 2
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'c:\XYZ_data.mdf'.
>|||Yes, sure enough...
In Configuration Manager, Service properties. The 'Log on as' account was
'Network Service'. The help button from that page says a 'domain user accoun
t
with minimal rights' is recommended. Changing the account causes it to work.

Create DTS Package in SQL server 2005 (Ver:9.0.1116).

Hi,
I am using SQL server 2005 (Ver:9.0.1116).
I am unable to find how to create a DTS package from Sql Server Management Studio.
It is showing "DTS 2000 Packages" in the "Object Explorer" of the Sql Server Management Studio.
I want to know how to start a Integration services in Yukon.
As,the DTS 2000 Packages in the Services tree does not Open nor can Import any package.

Thanks!
TechFreakYou need to use the "Business Intelligence Development Studio" to build SQL Server Integration Services (SSIS) packages, not SQL Server management Studio.

Go to: Start-->Programs-->Microsoft SQL Server 2005-->SQL Server Business Intelligence Development Studio

This is all in the documentation (Books Online) so look in there, and in the tutorials, to see how to go about building your first package.

-Jamie|||Yeah Jamie,

Thanx for the concern..

But using "Business Intelligence Development Studio" also it shows an error message as below:

"Could not load file or assembly 'Microsoft.DataTransformationServices.Wizards' or one of its dependencies.The System cannot find the file specified"

In the Yukon tutorials there is no info provided for Integration Services.

-TechFreak|||

Does anybody has a solution for this? I am getting the same error. I have Visual Studio 2005, SQL Server 2005 (Developer Edition) on Windows 2003 machine. I was trying to create an Integration Services Project, but getting the above error. Please help!

Thanks

|||

even i am getting the same error, any help regarding this ?

Thanks

http://techyfreak.blogspot.com

|||You have to be sure that SQL Server Integration Services installed on your PC.
To check go Help -> About Microsoft Visual Studio.

I got the same error after reinstalled MS Visual Studio 2005 from default location
C:/Program Files/Microsoft Visual Studio 8 to D:/Program Files/Microsoft Visual Studio 8
due to insufficient disk space on C.

SQL Server Integration Services just disappeared from installed list.
I was trying repairing VS from CD. Did not help.
I had to uninstall VS from D to C drive again and leave MSDN on D. After I could start Integration project again.
Why this happened I do not know. May be because MS SQL Server 2005 was still on C drive. Not sure.

I hope this help.

Create DTS Package in SQL server 2005 (Ver:9.0.1116).

Hi,
I am using SQL server 2005 (Ver:9.0.1116).
I am unable to find how to create a DTS package from Sql Server Management Studio.
It is showing "DTS 2000 Packages" in the "Object Explorer" of the Sql Server Management Studio.
I want to know how to start a Integration services in Yukon.
As,the DTS 2000 Packages in the Services tree does not Open nor can Import any package.

Thanks!
TechFreakYou need to use the "Business Intelligence Development Studio" to build SQL Server Integration Services (SSIS) packages, not SQL Server management Studio.

Go to: Start-->Programs-->Microsoft SQL Server 2005-->SQL Server Business Intelligence Development Studio

This is all in the documentation (Books Online) so look in there, and in the tutorials, to see how to go about building your first package.

-Jamie|||Yeah Jamie,

Thanx for the concern..

But using "Business Intelligence Development Studio" also it shows an error message as below:

"Could not load file or assembly 'Microsoft.DataTransformationServices.Wizards' or one of its dependencies.The System cannot find the file specified"

In the Yukon tutorials there is no info provided for Integration Services.

-TechFreak|||

Does anybody has a solution for this? I am getting the same error. I have Visual Studio 2005, SQL Server 2005 (Developer Edition) on Windows 2003 machine. I was trying to create an Integration Services Project, but getting the above error. Please help!

Thanks

|||

even i am getting the same error, any help regarding this ?

Thanks

http://techyfreak.blogspot.com

|||You have to be sure that SQL Server Integration Services installed on your PC.
To check go Help -> About Microsoft Visual Studio.

I got the same error after reinstalled MS Visual Studio 2005 from default location
C:/Program Files/Microsoft Visual Studio 8 to D:/Program Files/Microsoft Visual Studio 8
due to insufficient disk space on C.

SQL Server Integration Services just disappeared from installed list.
I was trying repairing VS from CD. Did not help.
I had to uninstall VS from D to C drive again and leave MSDN on D. After I could start Integration project again.
Why this happened I do not know. May be because MS SQL Server 2005 was still on C drive. Not sure.

I hope this help.|||Try Start>Run> DTSwizard

Create DTS Package in SQL server 2005 (Ver:9.0.1116).

Hi,
I am using SQL server 2005 (Ver:9.0.1116).
I am unable to find how to create a DTS package from Sql Server Management Studio.
It is showing "DTS 2000 Packages" in the "Object Explorer" of the Sql Server Management Studio.
I want to know how to start a Integration services in Yukon.
As,the DTS 2000 Packages in the Services tree does not Open nor can Import any package.

Thanks!
TechFreak
You need to use the "Business Intelligence Development Studio" to build SQL Server Integration Services (SSIS) packages, not SQL Server management Studio.

Go to: Start-->Programs-->Microsoft SQL Server 2005-->SQL Server Business Intelligence Development Studio

This is all in the documentation (Books Online) so look in there, and in the tutorials, to see how to go about building your first package.

-Jamie|||Yeah Jamie,

Thanx for the concern..

But using "Business Intelligence Development Studio" also it shows an error message as below:

"Could not load file or assembly 'Microsoft.DataTransformationServices.Wizards' or one of its dependencies.The System cannot find the file specified"

In the Yukon tutorials there is no info provided for Integration Services.

-TechFreak|||

Does anybody has a solution for this? I am getting the same error. I have Visual Studio 2005, SQL Server 2005 (Developer Edition) on Windows 2003 machine. I was trying to create an Integration Services Project, but getting the above error. Please help!

Thanks

|||

even i am getting the same error, any help regarding this ?

Thanks

http://techyfreak.blogspot.com

|||You have to be sure that SQL Server Integration Services installed on your PC.
To check go Help -> About Microsoft Visual Studio.

I got the same error after reinstalled MS Visual Studio 2005 from default location
C:/Program Files/Microsoft Visual Studio 8 to D:/Program Files/Microsoft Visual Studio 8
due to insufficient disk space on C.

SQL Server Integration Services just disappeared from installed list.
I was trying repairing VS from CD. Did not help.
I had to uninstall VS from D to C drive again and leave MSDN on D. After I could start Integration project again.
Why this happened I do not know. May be because MS SQL Server 2005 was still on C drive. Not sure.

I hope this help.

Create DTS Package in SQL server 2005 (Ver:9.0.1116).

Hi,
I am using SQL server 2005 (Ver:9.0.1116).
I am unable to find how to create a DTS package from Sql Server Management Studio.
It is showing "DTS 2000 Packages" in the "Object Explorer" of the Sql Server Management Studio.
I want to know how to start a Integration services in Yukon.
As,the DTS 2000 Packages in the Services tree does not Open nor can Import any package.

Thanks!
TechFreak
You need to use the "Business Intelligence Development Studio" to build SQL Server Integration Services (SSIS) packages, not SQL Server management Studio.

Go to: Start-->Programs-->Microsoft SQL Server 2005-->SQL Server Business Intelligence Development Studio

This is all in the documentation (Books Online) so look in there, and in the tutorials, to see how to go about building your first package.

-Jamie|||Yeah Jamie,

Thanx for the concern..

But using "Business Intelligence Development Studio" also it shows an error message as below:

"Could not load file or assembly 'Microsoft.DataTransformationServices.Wizards' or one of its dependencies.The System cannot find the file specified"

In the Yukon tutorials there is no info provided for Integration Services.

-TechFreak|||

Does anybody has a solution for this? I am getting the same error. I have Visual Studio 2005, SQL Server 2005 (Developer Edition) on Windows 2003 machine. I was trying to create an Integration Services Project, but getting the above error. Please help!

Thanks

|||

even i am getting the same error, any help regarding this ?

Thanks

http://techyfreak.blogspot.com

|||You have to be sure that SQL Server Integration Services installed on your PC.
To check go Help -> About Microsoft Visual Studio.

I got the same error after reinstalled MS Visual Studio 2005 from default location
C:/Program Files/Microsoft Visual Studio 8 to D:/Program Files/Microsoft Visual Studio 8
due to insufficient disk space on C.

SQL Server Integration Services just disappeared from installed list.
I was trying repairing VS from CD. Did not help.
I had to uninstall VS from D to C drive again and leave MSDN on D. After I could start Integration project again.
Why this happened I do not know. May be because MS SQL Server 2005 was still on C drive. Not sure.

I hope this help.