The best way would be to use the INFORMATION_SCHEMA views and in this case the one that presents the columns definitions:
SELECT * FROM
INFORMATION_SCHEMA.COLUMNS
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks very much for your help!Bob K.
There is not designer support for doing this with SQL CE, so you must use:
Code Snippet
ALTER TABLE Orders ADD FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
Designer support for this is promised for the next version of SQL CE (3.5) - in the "Orcas" timeframe.
|||thanks Eric.is there a way to enter the above sql statement from with Studio Express?
|||Connect to the SDF file, and select File, New, New query, type the text in the query editor and press F5 to execute...|||once again, thank you.
|||uhhh... and the beat goes on...
i've entered the following sql statement:
ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)
and received the following error:
Major Error 0x80040E14, Minor Error 25501
> ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)
There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = FOREIGN ]
i've attached a link that shows the tables and columns i'm attempting to update. i just can't see what i'm doing wrong. could someone help?
bob k.
|||the link:
http://www.rkamarowski.com/errors/error.jpg
|||
Sorry, the correct syntax is (using the sample Northwind.sdf):
Code Snippet
ALTER TABLE Orders ADD CONSTRAINT Reference FOREIGN KEY ([Customer ID]) REFERENCES Customers ([Customer ID])
|||perfect! thanks eric.Good Afternoon Erik,
Can you help me out with a project of my?! I programming for the first time in the .net cf and i create my database all manualy thought SqlCeEngine..
But i want to know if there is a better approach to do this by the "next, next theory" (visuali) . if i create the database using a GUI of sql serv mobile edition, how can i choose where this database will be created and how can i indicate to the datasource in the connection string of an sqlConnection where is my database?! can you help me? i have a checkpoint day 17 to present my work and i would like to finish the hole project will that date to get some time to another project that is coming up, so its kind of urgent subject.
Thank you anyway!
I have been developing a .NET Web app with an SQL Server 2005 Express database. Since I've been testing insert and delete code with a large data source, the Primary Key column which is also an autoincrement integer column is now at very large numbers (starting at over 40,000 now). I've tried doing a shrink on the database, but other then reducing the database size, it has done nothing to reduce the Primary Key numbers. Is there a command way to reduce the starting number back to 1 or do I need to completely reconstruct the database from scratch?
TIA
hi,
in order to "reset" the identity column value, you can "truncate" the table, that will implicitely reset the aut generated identity to your initial value via the
TRUNCATE TABLE schema.object;
statement... you will need high permissions on the object itself, and the table must not be referenced by a foreign key, ...
you can read further about requirements and permissions at http://msdn2.microsoft.com/en-us/library/ms177570.aspx...
or you can execute a
DBCC CHECKIDENT ('schema.object', RESEED);
command... start reading http://msdn2.microsoft.com/en-us/library/ms176057.aspx for further info and requirements..
regards
|||Thanks - I'll give it a try.
Carl
|||No dice. Neither of those options does it. That I want is, for example, a way of changing the lowest Primary/Identity Key value from 40,000 to 1. There are no records at lower values than 40,000. I'd also like to move all the Primary/Identity Key values down as well.
I suspect I need to effectively copy all the non-Primary/Identity Key data into a newly created database. If I do that, I think it is a characteristic of the INSERT statement that the Primary/Identity Key values will start at the seed values and autoincrement from there as rows are inserted and the incoming Primary/Identity Keys are ignored. I can then delete the original database and rename the new one to the original name.
Sound right?
Carl
|||hi,
ok. you want to "re-assign" an autogenerated value starting from "1" to existing rows...
you actually do not have to "drop" the database...
just perform a "SELECT ... INTO" another table that will be created on the fly for you, as a temporary storage... then drop all rows from the "real" table and reset the identity value and repopulate the original table from the temp storage...
I mean something like
SET NOCOUNT ON;USE tempdb;
GO
CREATE TABLE dbo.myData(
Id int NOT NULL IDENTITY PRIMARY KEY,
Data varchar(10) DEFAULT 'test data'
);
GO
PRINT 'this will add some data';
DECLARE @.i int;
SET @.i = 1;
INSERT INTO dbo.myData VALUES ( DEFAULT );
WHILE @.i < 20 BEGIN
INSERT INTO dbo.myData SELECT Data FROM dbo.myData;
SET @.i = @.i + 1;
END;
GO
SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;
GO
PRINT 'Deleting rows 1 to 200000';
DELETE FROM dbo.myData WHERE Id < 200000;
GO
SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;
PRINT 'now we do have an initial unused ''identity range''';
GO
PRINT 'Move rows to a temp table';
SELECT * INTO dbo.tempTable FROM dbo.myData;
PRINT 'truncating the original myData table';
PRINT 'this will actually reseed the identity value as well';
TRUNCATE TABLE dbo.myData;
SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;
GO
PRINT 'move data again to the original table, but this time omitting the Id col';
INSERT INTO dbo.myData (Data) SELECT Data FROM dbo.tempTable ORDER BY Id;
SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData;
GO
PRINT 'deleting temp table';
DROP TABLE dbo.tempTable;
GO
PRINT 'final clean up';
DROP TABLE dbo.myData;
--<--
this will add some data
Count Max Min
-- -- --
524288 524288 1
Deleting rows 1 to 200000
Count Max Min
-- -- --
324289 524288 200000
now we do have an initial unused 'identity range'
Move rows to a temp table
truncating the original myData table
this will actually reseed the identity value as well
Count Max Min
-- -- --
0 NULL NULL
move data again to the original table, but this time omitting the Id col
Count Max Min
-- -- --
324289 324289 1
deleting temp table
final clean up
but this is usually not an efficient task you should do in production systems as, after all, key values are not interesting to human beings when autogenerated... you usually choose such a primary key for performance reasons or when you can not find an actual natural key in your data modelling desing phase (very bad 
regards
|||Thank you, Sir.
Yes, it did work and in the process I've learned more about SQL Express and T-SQ, important for a relative newbieL. Not only the original question I asked, but a seperate isuue I wasn't aware I was messing up until I kept getting an error message from the SELECT phrase in the second INSERT statement. I was insisting on putting parens around the column name list and SELECT didn't like that. When I ceased and desited, everything went smoothly. I initially tried this on the smaller of the two existing tables and each time it bombed out, it actually added to the PRIMARY IDENTITY Key. Since I expect more blanks from delete and insert statements as I develop the C# access code, that's not important now and I can fix it later. Reseeding the second table (the big one) went through without a hitch once I knew the right syntax. I expect that I'll need to write a script or something to do this and similar cleanup as I develop this app since I have more big tables to add with similar situations. Hopefully, I won't need to mess with it anymore when the app coding job's done, but at least I know how to do it now.
I'll take your advice on the autogenerated key into account and redesign accordingly. One of the two current tables I can change; the other not based on expected content.
Thanks for the help. I think I'm off and running again
Carl
|||hi Carl,
Speedo wrote:
.... I'll take your advice on the autogenerated key into account and redesign accordingly. One of the two current tables I can change; the other not based on expected content.
wait before redesigning... I did not say that autogenearated keys are bad 
they are just another (available) candidate key in our entity definition.. it should not be the only one, but such a candidate key can be good primary key, as it's very compact (and this is very usefull in relation where the referencing table must map to the referenced table) and this is a "nice to have" feature in indexes implementation...
very often database architechts implement a surrogate key in the design phase.. sometime these surrogate keys become primary keys and sometime not...
a nice article about nautural vs surrogate keys is available at http://www.informit.com/articles/article.asp?p=25862&redir=1&rl=1 , even if related to GUID columns..
regards
I have been developing a .NET Web app with an SQL Server 2005 Express database. Since I've been testing insert and delete code with a large data source, the Primary Key column which is also an autoincrement integer column is now at very large numbers (starting at over 40,000 now). I've tried doing a shrink on the database, but other then reducing the database size, it has done nothing to reduce the Primary Key numbers. Is there a command way to reduce the starting number back to 1 or do I need to completely reconstruct the database from scratch?
TIA
hi,
in order to "reset" the identity column value, you can "truncate" the table, that will implicitely reset the aut generated identity to your initial value via the
TRUNCATE TABLE schema.object;
statement... you will need high permissions on the object itself, and the table must not be referenced by a foreign key, ...
you can read further about requirements and permissions at http://msdn2.microsoft.com/en-us/library/ms177570.aspx...
or you can execute a
DBCC CHECKIDENT ('schema.object', RESEED);
command... start reading http://msdn2.microsoft.com/en-us/library/ms176057.aspx for further info and requirements..
regards
|||Thanks - I'll give it a try.
Carl
|||No dice. Neither of those options does it. That I want is, for example, a way of changing the lowest Primary/Identity Key value from 40,000 to 1. There are no records at lower values than 40,000. I'd also like to move all the Primary/Identity Key values down as well.
I suspect I need to effectively copy all the non-Primary/Identity Key data into a newly created database. If I do that, I think it is a characteristic of the INSERT statement that the Primary/Identity Key values will start at the seed values and autoincrement from there as rows are inserted and the incoming Primary/Identity Keys are ignored. I can then delete the original database and rename the new one to the original name.
Sound right?
Carl
|||hi,
ok. you want to "re-assign" an autogenerated value starting from "1" to existing rows...
you actually do not have to "drop" the database...
just perform a "SELECT ... INTO" another table that will be created on the fly for you, as a temporary storage... then drop all rows from the "real" table and reset the identity value and repopulate the original table from the temp storage...
I mean something like
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.myData( Id int NOT NULL IDENTITY PRIMARY KEY, Data varchar(10) DEFAULT 'test data' ); GO PRINT 'this will add some data'; DECLARE @.i int; SET @.i = 1; INSERT INTO dbo.myData VALUES ( DEFAULT ); WHILE @.i < 20 BEGIN INSERT INTO dbo.myData SELECT Data FROM dbo.myData; SET @.i = @.i + 1; END; GO SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData; GO PRINT 'Deleting rows 1 to 200000'; DELETE FROM dbo.myData WHERE Id < 200000; GO SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData; PRINT 'now we do have an initial unused ''identity range'''; GO PRINT 'Move rows to a temp table'; SELECT * INTO dbo.tempTable FROM dbo.myData; PRINT 'truncating the original myData table'; PRINT 'this will actually reseed the identity value as well'; TRUNCATE TABLE dbo.myData; SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData; GO PRINT 'move data again to the original table, but this time omitting the Id col'; INSERT INTO dbo.myData (Data) SELECT Data FROM dbo.tempTable ORDER BY Id; SELECT COUNT(*) AS [Count], MAX(Id) AS [Max], MIN(Id) AS [Min] FROM dbo.myData; GO PRINT 'deleting temp table'; DROP TABLE dbo.tempTable; GO PRINT 'final clean up'; DROP TABLE dbo.myData; --<-- this will add some data Count Max Min -- -- -- 524288 524288 1 Deleting rows 1 to 200000 Count Max Min -- -- -- 324289 524288 200000 now we do have an initial unused 'identity range' Move rows to a temp table truncating the original myData table this will actually reseed the identity value as well Count Max Min -- -- -- 0 NULL NULL move data again to the original table, but this time omitting the Id col Count Max Min -- -- -- 324289 324289 1 deleting temp table final clean upbut this is usually not an efficient task you should do in production systems as, after all, key values are not interesting to human beings when autogenerated... you usually choose such a primary key for performance reasons or when you can not find an actual natural key in your data modelling desing phase (very bad
regards
|||Thank you, Sir.
Yes, it did work and in the process I've learned more about SQL Express and T-SQ, important for a relative newbieL. Not only the original question I asked, but a seperate isuue I wasn't aware I was messing up until I kept getting an error message from the SELECT phrase in the second INSERT statement. I was insisting on putting parens around the column name list and SELECT didn't like that. When I ceased and desited, everything went smoothly. I initially tried this on the smaller of the two existing tables and each time it bombed out, it actually added to the PRIMARY IDENTITY Key. Since I expect more blanks from delete and insert statements as I develop the C# access code, that's not important now and I can fix it later. Reseeding the second table (the big one) went through without a hitch once I knew the right syntax. I expect that I'll need to write a script or something to do this and similar cleanup as I develop this app since I have more big tables to add with similar situations. Hopefully, I won't need to mess with it anymore when the app coding job's done, but at least I know how to do it now.
I'll take your advice on the autogenerated key into account and redesign accordingly. One of the two current tables I can change; the other not based on expected content.
Thanks for the help. I think I'm off and running again
Carl
|||hi Carl,
Speedo wrote:
.... I'll take your advice on the autogenerated key into account and redesign accordingly. One of the two current tables I can change; the other not based on expected content.
wait before redesigning... I did not say that autogenearated keys are bad
they are just another (available) candidate key in our entity definition.. it should not be the only one, but such a candidate key can be good primary key, as it's very compact (and this is very usefull in relation where the referencing table must map to the referenced table) and this is a "nice to have" feature in indexes implementation...
very often database architechts implement a surrogate key in the design phase.. sometime these surrogate keys become primary keys and sometime not...
a nice article about nautural vs surrogate keys is available at http://www.informit.com/articles/article.asp?p=25862&redir=1&rl=1 , even if related to GUID columns..
regards
sqlUsing VS05 SP1 Pro SQL Express…
Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....
SELECT fld_IX_UserID
FROM UserIDs
WHERE (fld_UserID_Table_ID = @.USERID_TABLE_ID)
It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.
Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.
In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.
Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.
Thanks,
Bob
i am not good in .net ... so i cannot comment on that.. but if you are getting different result while u run the same SP means the input parameter which you pass is differ for each run. check what is the value set for @.USERID_TABLE_ID in each run
Madhu
|||No the stored procedure returnes the correct data every time.
It is the Preview Data, in the IDE table adapter designer view, that returns incorrect data everytime, but only for Store Procedures used as table adapters.
Bob
|||Hi Bob,
I would suggest you post this bug to http://connect.microsoft.com. You should be able to find the VS Connection and enter the issue there. That will put the issue into their tracking system. Since bad data is happening only through VS, it should be filed with VS.
Mike
