Adding a sysdate to a tablename

Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:

SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename

So the tablename should looksomething like this

tablename20031210

The error message is: incorrect syntax near +

Can anybody help.

ThanksUse dynamic Sql within pl/sql:

l_statement varchar2(100);
begin
l_statement:=' create table name_'||to_char(sysdate,'DDMMRRRR');
l_statement:=l_statement||define colums here

execute immediate l_statement;
end;

eventually you must append an ; at the end of the varchar2 because it is an DDL-Statement

Adding a sysdate to a table

Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:

SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename

So the tablename should looksomething like this

tablename20031210

The error message is: incorrect syntax near +

Can anybody help.

ThanksMaybe...

Declare @.Query nVarchar(1000)

SET @.Query=N'Select * From ' + 'tablename' + CONVERT(char(10), GETDATE(), 112)

EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35|||Thanks a lot this has been driving me crazy. I had to modify the code slightly but it works fine.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'Select * INTO ' + 'tablename' + CONVERT(char(10), GETDATE(), 112) + 'FROM tablename' EXECUTE sp_executesql @.Query, N'@.level tinyint',
@.level = 35

adding a new record takes longer and longer archive? (was "Table help")

Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be greatThat's not really a lot of records

And 4,000 a day is not a lot of transactions

How do you "add" the rows?

And what the Table DDL look like...post that, and don't forget the indexes, keys, contraints and triggers if you have them|||Here's the info on the table that's causing the probs. We are updateing this table using an Access front end with odbc linked tables. Any help on how to maintain a table would be great.
Cheers

CREATE TABLE [dbo].[calls] (
[CallID] [int] IDENTITY (1, 1) NOT NULL ,
[ContactID] [int] NOT NULL ,
[CallDate] [datetime] NULL ,
[CallTime] [datetime] NULL ,
[Callername] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[CallerCompany] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ActionTakenID] [int] NULL ,
[Confirmed] [bit] NULL ,
[EmployeesID] [int] NULL ,
[ShortMessage] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[CallerNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Charged] [bit] NULL ,
[OperatorID] [int] NULL ,
[DDI] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[EmployeeName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OperatorName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[calls] WITH NOCHECK ADD
CONSTRAINT [PK_calls] PRIMARY KEY CLUSTERED
(
[CallID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[calls] ADD
CONSTRAINT [DF_calls_Confirmed] DEFAULT (0) FOR [Confirmed],
CONSTRAINT [DF_calls_Charged] DEFAULT (0) FOR [Charged]
GO|||My guess is that you're using a bound form to this table. And this is an MDB not an Access Data Project.

I would make it unbound, use sql to populate the unbound controls, then when they need to do an update or insert, perform them as regular sql calls using the values from the controls.

As for the table...there's not really much wrong.

I would do the following though

1. Combine the CallDate and CallTime Fields. It's redundant.
2. Whats the largest value in the ntext column. I'd use varchar if possible
3. A caller name of 255? This has got to be an Access default. Make it shorter. Better yet, make 2 columns. Last Name and First Name. Add a Third for Title, Mr/Mrs/Ms, ect
4. Bit...I never liked bit...I don't think it's ANSI and does not cross platforms well
5. varchar(4)...anything less than ...say 15 should be char...
6. Employee Name, Operator Name...same thing...you should use first and last
7. And identity as a PK? There's got to be a better one. CallerLastName, CallDate, CallerFirstName perhaps. Just plave a unique index un the id.

Now None of these things should be impacting your performance...maybe ntext (and why unicode, why not just text?). ALL of my money is on the Access ODBC nound form.|||As you guesed the sql database is an bit of a bodged upgrade from a access database, so needs cleaning up, am waiting for my boss to send me on some sort of course though.
As you also guesed we are using access bound forms, think I might have to bite the bullet a have been considering upgrading to a vb.net front end for a while, but don't think my programing skills are really up to it, is much more work than going to an access project, but may as well go the full hog and dump access.
Have just dome some testing and it updates like lightning from vb.net.

Any more general advice would be great (if people have nothing to says will mark of a resolved in a few days)

Thanks again

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaks
If you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:

> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sql
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no need
> additional one.
> THnaks
>
>
>
>
sql

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaksIf you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:
> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sql
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no need
> additional one.
> THnaks
>
>
>
>

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaksIf you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:

> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sq
l
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no nee
d
> additional one.
> THnaks
>
>
>
>

Add IDENTITY column in runtime in a View...

Dear All,
I'm facing a problem with creating a view.I have a table with
following description

Tab1
-----------
Col1 col2
-- --
Val1 Tom
Val2 Cat
Val3 Jack
Val4 Jim

Now I'm trying to create a view with following information from
Tab1.The output should be

Tab1View
------------
Col1 Col2 Col3
-- -- --
1 Val1 Tom
2 Val2 Cat
3 Val3 Jack
4 Val4 Jim
------------
So the create view statement add this column dynamically in this
result set.
If any body has any doubt on this query please let me inform.
Regards
Arijit Chatterjeearijitchatterjee123@.yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0308032036.206a7354@.posting.google.com>...
> Dear All,
> I'm facing a problem with creating a view.I have a table with
> following description
> Tab1
> -----------
> Col1 col2
> -- --
> Val1 Tom
> Val2 Cat
> Val3 Jack
> Val4 Jim
> Now I'm trying to create a view with following information from
> Tab1.The output should be
> Tab1View
> ------------
> Col1 Col2 Col3
> -- -- --
> 1 Val1 Tom
> 2 Val2 Cat
> 3 Val3 Jack
> 4 Val4 Jim
> ------------
> So the create view statement add this column dynamically in this
> result set.
> If any body has any doubt on this query please let me inform.
> Regards
> Arijit Chatterjee

Here is a Microsoft KB article which describes various options:

http://support.microsoft.com/defaul...kb;en-us;186133

Alternatively, you might find it easier and more efficient to add the
row numbers in the client application.

Simon

Ad: Problems Creating Instance in SQL 2005 management studio

Hello all,
Thanks in advance for any help you can provide.
I am having a problem creating a new NS instance using SQL 2005 management studio.
I am using the database element in my instance and application xml files like this,
in order to create the NS application in an existing database.
<Database>
<DatabaseName>%databasename%</DatabaseName>
<SchemaName>%schemaname%</SchemaName>
</Database>
the problem that I am having is that if I encounter an error somewhere in the process of creating this new instance the instance name and schema name I specified are still bound somewhere, and if I correct for the error and try to create the instance again, I receive a new error saying that there is already an instance with my instance name, or using the schema name.
If I try to list versions it tells me there are no instances, yet if I try to add a new instance it tells me there is already an instance using my instance name.
I have tried searching master database tables and views for anywhere where it might be stored, also tried poking around most of management studio and the registry, and just can not seem to find where it is remembering that I had tried to create this instance before.
It is like it is partially creating the instance, running into an error, and then not rolling back its changes somewhere.

Any help would be very much appreciated

-Derek-

Web Services Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation, are men who want crops without plowing up the ground. They want rain without thunder and lightning. They want the ocean without the awful roar of its many waters. Power concedes nothing without demand. It never did and it never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT*FROM NS90.NSInstanceInfo

SELECT*FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-

Ad: Problems Creating Instance in SQL 2005 management studio

Hello all,

Thanks in advance for any help you can provide.

I am having a problem creating a new NS instance using SQL 2005 management

studio.

I am using the database element in my instance and application xml files like

this,

in order to create the NS application in an existing database.

<Database>

<DatabaseName>%databasename%</DatabaseName>

<SchemaName>%schemaname%</SchemaName>

</Database>

the problem that I am having is that if I encounter an error somewhere in the

process of creating this new instance the instance name and schema name I

specified are still bound somewhere, and if I correct for the error and try to

create the instance again, I receive a new error saying that there is already

an instance with my instance name, or using the schema name.

If I try to list versions it tells me there are no instances, yet if I try to

add a new instance it tells me there is already an instance using my instance

name.

I have tried searching master database tables and views for anywhere

where it might be stored, also tried poking around most of management studio

and the registry, and just can not seem to find where it is remembering that I

had tried to create this instance before.

It is like it is partially creating the instance, running into an error, and

then not rolling back its changes somewhere.
Any help would be very much appreciated

-Derek-

Web Services

Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation,

are men who want crops without plowing up the ground. They want rain without

thunder and lightning. They want the ocean without the awful roar of its

many waters. Power concedes nothing without demand. It never did and it

never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT * FROM NS90.NSInstanceInfo

SELECT * FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-

Ad: Answer Worked!

Hello all,
Thanks in advance for any help you can provide.
I am having a problem creating a new NS instance using SQL 2005 management studio.
I am using the database element in my instance and application xml files like this,
in order to create the NS application in an existing database.
<Database>
<DatabaseName>%databasename%</DatabaseName>
<SchemaName>%schemaname%</SchemaName>
</Database>
the problem that I am having is that if I encounter an error somewhere in the process of creating this new instance the instance name and schema name I specified are still bound somewhere, and if I correct for the error and try to create the instance again, I receive a new error saying that there is already an instance with my instance name, or using the schema name.
If I try to list versions it tells me there are no instances, yet if I try to add a new instance it tells me there is already an instance using my instance name.
I have tried searching master database tables and views for anywhere where it might be stored, also tried poking around most of management studio and the registry, and just can not seem to find where it is remembering that I had tried to create this instance before.
It is like it is partially creating the instance, running into an error, and then not rolling back its changes somewhere.

Any help would be very much appreciated

-Derek-

Web Services Integration Manager

TempWorks Software

Si Vis Pacem, Para Bellum.

"Those who profess to favor freedom, and yet depreciate agitation, are men who want crops without plowing up the ground. They want rain without thunder and lightning. They want the ocean without the awful roar of its many waters. Power concedes nothing without demand. It never did and it never will." - Frederick Douglass

additional error information.
I tried to remove the database and restore from a backup, and yet it still believe the instance name is in use.
I notice it seems to be checking the NS InstanceMetaData for existing instances on this server...
"Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse"
anyone know where this metadata is ?

===================================

The specified name is used by another Notification Services instance on the server. Specify a different instance name.
Instance Name: TWNSinstance
Server Name: derekxps (Microsoft.SqlServer.NotificationServices)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=NSEventStrings&EvtID=DuplicateInstanceName&LinkId=20476

Program Location:

at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.CheckInstanceNameInUse(SqlConnection sqlConnection, NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.InstanceMetadata.Add(NSInstance instance)
at Microsoft.SqlServer.NotificationServices.Common.NSDatabaseCreator.Create(NSDBState dbState, SqlDatabaseAttributes databaseAttributes, List`1 createdStates)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.CreateDatabaseInfrastructure(Instance instance, NSSecureString argumentKey, NSInstance& nsInstance, Dictionary`2& nsApplications)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(Instance instance, NSSecureString argumentKey, Boolean uiReporting)
at Microsoft.SqlServer.NotificationServices.Common.InstanceCompiler.Create(NSSecureString sqlUserName, NSSecureString sqlPassword, String configFile, NSSecureString argumentKey, StringDictionary substitutions, Boolean uiReporting, String enforcedSqlServerSystem)
at Microsoft.SqlServer.Management.NotificationServicesUI.CreateInstanceGeneral.PerformCreate()|||

Hi Derek -

SQLNS 2005 stores its metadata in the MSDB database. Run this script in SSMS and see if your failed instance/application are listed.

USE MSDB

GO

SELECT*FROM NS90.NSInstanceInfo

SELECT*FROM NS90.NSApplicationInfo

HTH..

Joe

|||Thanks Joe,
I did find the instance names and schema names that it had "eaten" in there. cleared them and was able to install with the instance name I wanted.
Thanks for your help!

-Derek-

Ad hoc reporting Model and Security Filters

I am having some problems with creating security filters.

The way my system is setup is that I have a main table that contains all my data. I also have a security table that tells me what records each user has access to. I created an entity for my security filter and have a security filter defined that limits the data to the rows that have the logged on users id. This works fine if I look at the data I see the rows that are for the signed on user. Next I created a filter on my main table that limits the rows to the rows the user has access to. I did this by setting up another filter that says the UserCode from the main table must equal the user code from the security table. (I did this by right clicking and choosing edit as formula). This will then limit my data to the rows this user has access to. This all works but it is very slow. I captured the sql code that was running and noticed that the filter was doing a left outer join on my security table and joining on the unfiltered code then using a where clause to limit the data. When I run this logic it basically does a scan of my main table. If I change this join to be an inner join and join on the filtered data my results come back quickly.

Now to my question. Is this the way the system should work? Am I doing something wrong? Are there other ways to do this?

Thanks in advance for any help.Any success on the security filters? Mind sharing how do you exactly implement this? These security filters are really hard to understand and implement for me.

Ad hoc reporting Model and Security Filters

I am having some problems with creating security filters.

The way my system is setup is that I have a main table that contains all my data. I also have a security table that tells me what records each user has access to. I created an entity for my security filter and have a security filter defined that limits the data to the rows that have the logged on users id. This works fine if I look at the data I see the rows that are for the signed on user. Next I created a filter on my main table that limits the rows to the rows the user has access to. I did this by setting up another filter that says the UserCode from the main table must equal the user code from the security table. (I did this by right clicking and choosing edit as formula). This will then limit my data to the rows this user has access to. This all works but it is very slow. I captured the sql code that was running and noticed that the filter was doing a left outer join on my security table and joining on the unfiltered code then using a where clause to limit the data. When I run this logic it basically does a scan of my main table. If I change this join to be an inner join and join on the filtered data my results come back quickly.

Now to my question. Is this the way the system should work? Am I doing something wrong? Are there other ways to do this?

Thanks in advance for any help.Any success on the security filters? Mind sharing how do you exactly implement this? These security filters are really hard to understand and implement for me.