|
Hi, I searched the boards and found how to do this, but I have come up against a problem that I cannot seem to resolve.
This is the code to create the stored procedure: (It was create first but I have been playing with it)
ALTER PROCEDURE [dbo].[sproc_CreateMarriageTable]
-- Add the parameters for the stored procedure here
@Surname varchar(50)
AS
BEGIN
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''),
[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC @SQLStatement
END
This parses and compiles without any errors. Then I tried to execute the procedure:
USE [BMD2005]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sproc_CreateMarriageTable]
@Surname = N'M_VAISEY'
SELECT 'Return Value' = @return_value
GO
It fails with the message:
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTable, Line 29
The name 'CREATE TABLE [M_VAISEY]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAISEY_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_PerMar] DEFAULT (''),
[BirthDate] [smalldatetime] ' is not a valid identifier.
The declaration of the statement variable is plenty long enough (The text is about 1200 characters) and the place where it stops is about 687 characters.
Is there a problem with NULL? Do I need to enclose that in something?
If anyone can see what the problem is I would be most grateful. 
David
UPDATE: I removed a couple of the filds from the end and got this message when I executed
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTableTest, Line 25
The name 'CREATE TABLE [M_VAUGHAN]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAUGHAN_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Qtr] DEFAULT ((1)),
[BirthDate] [smalldatetime] NULL
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTI' is not a valid identifier.
It would seem there is some lenght of string problem. Any ideas?
modified on Friday, October 17, 2008 11:07 AM
|
|
|
|
|
DECLARE @SQLStatement varchar(1024)
DECLARE @SQLStatement2 varchar(1024)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''), '
SET @SQLStatement2 = '[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC (@SQLStatement + @SQLStatement2)
|
|
|
|
|
Hi,
It looks like the length is the issue. I guess you might have some tab characters in the code for formatting perhaps, not sure. Anyway, I just fixed a similar problem in a Sybase IQ procedure and just removed all the white space.
So for example...
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname
+ '] ([AutoID] [int] IDENTITY(1,1) NOT NULL,'
+ '[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_'
+ @Surname
+ '_FName] DEFAULT (''''), '
+ '[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
+ '_SName] DEFAULT (''''), '
+ '[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
... etc
... etc
... etc
+ ' STATISTICS_NORECOMPUTE = OFF, '
+ ' IGNORE_DUP_KEY = OFF, '
+ ' ALLOW_ROW_LOCKS = ON, '
+ ' ALLOW_PAGE_LOCKS = ON)'
+ ' ON [PRIMARY]) '
+ ' ON [PRIMARY]'
EXEC @SQLStatement
I hope that helps.
Good luck  ,
Kevin
|
|
|
|
|
Took me a second or two to work out what you were saying. *david scratches head* Ah! The light dawned - concatenation
Yes of course the wrapping or rather formatting may be adding spurious characters.
Strange thing is that it worked when I split the line into two. Perhaps I just hit the right spot by luck rather than by judgement! 
I will give it a try.
Thanks
David
|
|
|
|
|
Worked a treat. Thanks again.
|
|
|
|
|
I'm missing the 'Open Table' right click menu item in SSMS 2008. I am running SQL Compact 3.5 sp1. Any ideas on how to get the menu option back.
It's getting extremely annoying having to view, modify, insert/delete by using queries.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I was hoping someone had a fix for this. So annoying. Only with compact DBs.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
Hi all.., How to find refresh percentage of a database through QUERY?
D.Willington
|
|
|
|
|
What do you mean exactly? Your question makes no sense.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have an application to refresh a database. I have to select database, backup file path and all.., it is working well.., while the database in refresh i have to display this much percentage has fininshed and so on.
so i want to get the completed percentage of refresh database.
D.Willington
|
|
|
|
|
Well, while your database is restoring you can't run queries so you'll just have to show them a progress bar or something that runs on a timer.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
can't run the query from master database?
D.Willington
|
|
|
|
|
You can run a query from the master database, but the database you are restoring is being restored, so there is nothing to query - it doesn't do it table by table etc.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok.., Thank u very much
D.Willington
|
|
|
|
|
Hi,
I have a table (tblregfundprice) with the column name "Unit_Price, Fund_Code, and Date".
The table contains the following data.
Fund_Code.....Unit_Price.....Date
AF............0.1254.........10/17/2008
AF............0.5487.........10/16/2008
AF............0.2114.........10/15/2008
AF............0.8744.........10/14/2008
AF............0.1254.........10/13/2008
AF............0.5487.........10/12/2008
AF............0.2114.........10/11/2008
AF............0.8744.........10/10/2008
AF............0.1254.........10/09/2008
SC............0.5487.........10/14/2008
SC............0.2114.........10/13/2008
SC............0.8744.........10/12/2008
GH............0.1254.........10/11/2008
GH............0.5487.........10/10/2008
GH............0.2114.........10/09/2008
GH............0.8744.........10/08/2008
Now if you notice, the most recent date is 10/17/2008 and SC is having the most date of 10/14/2008. How can i do to create below result table for TOP 10 Fund_Code = "SC"?
Unit_Price.....Date
NULL..........10/17/2008
NULL..........10/16/2008
NULL..........10/15/2008
0.5487.......10/14/2008
0.2114.......10/13/2008
0.8744.......10/12/2008
0.1254.......10/11/2008
NULL..........10/10/2008
NULL..........10/09/2008
NULL..........10/08/2008
I need the NULL value to appear as i need it for my system to capture it... I hope there's a way for this.
Pls help....Many thx.
|
|
|
|
|
try this
SELECT *<br />
FROM (SELECT DISTINCT Date FROM TABLE) D<br />
LEFT JOIN (SELECT * FROM TABLE WHERE Fund = 'SC') SC ON SC.Date = D.Date
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am retreiving a data from a table by self join. I am getting all the clients of a doctor and and group them into pairs. I need to have a unique record for each pair that is pair clientAclientB is the same as pair clientBclientA.How can this be done please, since I am retreiving both records. The sql I am using is written be;low
Select Distinct c.id, c2.id
FROM Client c, Client c2
Where c.doc = c2.doc
And c.id != c2.id
Thanks for your time
|
|
|
|
|
A quick thought (not tested)
Select Distinct c.id, c2.id
FROM Client c, Client c2
Where c.doc = c2.doc
And c.id < c2.id --less than instead of not equal
Eslam Afifi
|
|
|
|
|
Can anyone tell me that how can I store images in database. I am using SQL 2005 and I want to store image in that. I know there is a image datatype but it is not working. Please remember I want to store the image not it's path.
|
|
|
|
|
I have already answered in C# forum
|
|
|
|
|
i use identity_insert for a column in one of my tables. when I first insert 5 rows, this column will show number from 1 to 5 (identity set as 1,1), but when I delete this 5 rows and re-insert this 5 rows, the number get incremented (6-10), how can I reset it and make it still start from 1 ??? can I run any simple query to do that?? thanks.
|
|
|
|
|
The intent of an identity column is that it uniquely defines a row in a table, FOREVER ! You can always create another row in that table with the exact other values, but it will never be known by an identity value previously assigned.
It may be possible to re-assign the identity sequence generator, but don't do it. Have you considered what would happen if another table was referencing your master table with this identity column ?
|
|
|
|
|
For example DBCC CHECKIDENT (Transact-SQL)
[^].
However, identity is used to generate surrogate key values so the number generated should always be irrelevant. The database does not quarantee anything but uniqueness.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
In case you did not get it - DO NOT CHANGE THE IDENTITY VALUES - this is a basic tennant of relational databases.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
again, I tried to run the following query
INSERT INTO table1 (SID, Time) VALUES (1, SYSDATE)
but got the error message as below:
Msg 128, Level 15, State 1, Line 1
The name "SYSDATE" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Can anyone tell me what the problem is? Thanks....
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
