MS SQL 2005 Identity Lost New Database
Oct 4, 2007
Hello
I am creating a TABLE
CREATE TABLE [dbo].[TbTest](
[id_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Title] [varchar](100) NULL,
) ON [PRIMARY]
when looking at the Column Properties in MS Server Management Studio I get :
Identity Specification = Yes
(Is Identity) = Yes
Increment = 1
Seed = 1
now if I want to make a copy of that database, importing datas and tables
I get :
Identity Specification = No
(Is Identity) = No
How can I avoid this problem ? my application is not working anymore
ALTER TABLE [dbo].[TbTest] WITH NOCHECK ADD
CONSTRAINT [PK_TbTest] PRIMARY KEY CLUSTERED
(
[id_TbTest]
) ON [PRIMARY]
is not solving that problem
thank you
View 5 Replies
ADVERTISEMENT
Mar 19, 1999
Hi,
I face a different behaviour between SQL Server 6.5 and 7.0 regarding
the global variable @@identity.
I insert a row into a table with an identity column. This table has
an insert trigger attached which performs another insert into a second table.
Now I seem to lose the identity value (@@identity is NULL).
Example:
Take the following 2 tables:
create table a
(aaintnot nullidentity,
bbintnot null
)
create table b
(bintnot null
)
go
Table a has a trigger attached:
create trigger a_trigger
on a
for insert
as
insert b
select aa
from inserted
go
After this I execute the following SQL statement:
insert a (bb) values (1)
select @@identity
On 6.5 I get the result:
------------------------------------------
1
On 7.0 I get:
------------------------------------------
(null)
Which means that outside the trigger I have lost my identity value.
This feature of Version 6.5 I usually use to maintain
event logs. Using a trigger hides this maintenance nicely from the
application.
My question is now, is this a new feature of Version 7.0 or is it a bug?
BTW: The books online describe this behaviour in the case when the trigger
inserts value into a table with an identity property, which is not the case
in my example.
Regards
Marco
Marco Ruggli
ruggli_m@simultan.ch
Simultan P&I
Kantonsstrasse 1
6246 Altishofen, Switzerland
View 1 Replies
View Related
Mar 25, 2008
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
View 4 Replies
View Related
Dec 3, 2007
Hi all,
In the last one and half years, I used the Northwind Database in SQL Server Management Studio Express (SSMSE) to learn the programming of SqlConnections, Data sources, Database Exploere, ADO.NET 2.0, etc. via VB 2005 Express.
The Northwind Database in my SSMSE got lost very often, but I was not aware of it. How can I know where the Northwind Database is used or processed by my VB 2005 Express projects that were the examples of some tutorial books or my trial projects? How can I release the Northwind Database back to my SSMSE from the VB 2005 Express projects? Please help and advise.
Thanks in advance,
Scott Chang
View 2 Replies
View Related
Oct 3, 2007
I need to retrieve the SA Password through MS SQL 2005. The reason why is I am trying to connect a number of computers in the same building for an Address Book to be shared by everyone. I will be using the program, Addressinator! to do this. But I need to have the SA password in order to connect the computers.
I am running Windows XP. I have read a few instructions on how to retrieve or make a new password on the net, but most of it doesn't make sense to me.
For Example, this is one that I found:
To reset the sa password, you can make the following:
1. Login to the SQL Server box as the Administrator.
2. Run SQL Server Enterprise Manager. (I don't have the Enterprise Manager)
3. Right-click the server name and choose 'Edit SQL Server Registration properties'. (I can't find the properties!)
4. Choose 'Use Windows authentication' and click OK button.
5. Expand a server, expand a Security and click Logins.
6. Double-click the sa login and specify new password on the General tab.
I hope someone can help me out with this.
Thanks!
View 1 Replies
View Related
Jan 30, 2004
I really hope someone has an idea or two that they'd like to share. Here's the deal...I have a customer who has SQL 7 running on an NT 4 server. Under normal circumstances, our client software connects to a DB running on that server. They recently had to reboot the server and now all of the clients on the network can no longer connect to our database...they get an error message similar to this:
Error -2147467259: SQL Server does not exist or access denied.
Additional Info:
When logging into the server, the service manager shows a "green arrow" in the system tray so I'm assuming SQL is running.
They are running some form of replication to another identical server.
They recently had to replace some network equipment on that segment, but everyone can still ping the server by name and IP. Also, they can access the virtual website on that machine's IIS.
Nobody knows the SA password.
As a test, I tried logging in using one of our software's "internal" login/passwords, but still could not login.
As a test, I tried to create a DSN to connect to SQL using the "guest" account (which should exist) but the wizard wouldn't get past the screen where you provide the authentication info.
Anybody have any advice for me/us?
View 5 Replies
View Related
Mar 15, 2007
Hello, I'm running an MS SQL 2005 database which supports our entire website and it's fairly huge. I seem to have lost the sa password for the database.
I COULD get it back if there was a way to convert the SQL 2005 DB to an SQL 2000 DB, seeing as how I found a password cracker for that version. Is there anyway to convert a 2005 DB back to a 2000 DB?
Does anyone have any other ideas they might want to lay on me?
I'm kind of sick about this... I'm usually a really good note-taker; In fact, I do have the sa password recorded in an IT database that I keep. Unfortunately the password just doesn't work. I've tried the caps lock and other variations, but the password just eludes me.
Thanks for your help,
Chris
View 4 Replies
View Related
Feb 26, 2007
I've had a VB 6 / SQL Server 2000 app running in a network enviroment for a couple of years now at a client of mine. Recently, he upgraded to the latest version of Quickbooks which installed SQL Server 2005 on the server. Now there is SQL Server 2000 and 2005 residing on the same server. My VB app still runs on the server but will not run on other machines. I can ping the server from the other machines. Has SQL 2005 taken over the port I was using or maybe has new security features that won't let me access SQL 2000 using my current connection string? Here is my current connection string:
mstrConnStr = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & strServerLogin & ";Password=" & strServerPW & ";Initial Catalog=" & strDBName & ";Data Source=" & strServer.
Any help will be greatly appreciated.
Keith
View 4 Replies
View Related
Aug 21, 2007
I need help!
I have Outlook 2007 w/BCM running on XP Pro workstation connecting to a SBS 2003 R2 hosting my SQL 2005 database. I have two problems: First I can get the BCM to connect to the remote data but periodically the connection is lost and many times it requires a reboot to reconnect. Secondly related to teh first, I can never get MS Accounting 2007 to connect to the same server and database from my client.
I have already set up the server to use port 5356 via TCP amd added a hole through my client windows firewall for the same port. Both client and server are set to share files and printers.
Any suggestions?
View 2 Replies
View Related
Apr 21, 2006
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
I would appreciate any suggestions.
Thank you.
LitePipe
View 4 Replies
View Related
Mar 15, 2007
Hello, I'm running an MS SQL 2005 database which supports our entire website and it's fairly huge. I seem to have lost the sa password for the database.
I COULD get it back if there was a way to convert the SQL 2005 DB to an SQL 2000 DB, seeing as how I found a password cracker for that version. Is there anyway to convert a 2005 DB back to a 2000 DB?
Does anyone have any other ideas they might want to lay on me?
I'm kind of sick about this... I'm usually a really good note-taker; In fact, I do have the sa password recorded in an IT database that I keep. Unfortunately the password just doesn't work. I've tried the caps lock and other variations, but the password just eludes me.
Thanks for your help,
Chris
View 10 Replies
View Related
Jul 26, 1999
My question has to do with the ability to recover a suspect database in the event that the transaction log device file becomes corrupt or is deleted.
Specifically, consider the case where a user database has it's data device on one hard disk and the log device file on a 2nd hard disk. The database is set to truncate log on checkpoint.
If the disk fails on which the log device file resides, the user database will be marked as suspect. Assume this disk is replaced and rebuilt from a tape backup, including the log device file (which was backed up to tape during off hours.)
Will SQL Server have any problems with this? Will database recovery have any problems with this (assuming the restored transaction log was empty)? Any other issues?
Thanks in advance!
View 2 Replies
View Related
Nov 15, 2006
Hi there,
I just upgraded from SQL2000 to 2005 and noticed that any DB engines you register do not get re-registerd when you restart the Management Studio, you have to reconnect every time you start Management Studio! SQL2000 used to keep your server connections.
Am i missing something or is this just how it works now!?
Thanks
Ben
View 5 Replies
View Related
Feb 9, 2006
I used a DTS package to import data from one database to a new database (different servers) and it seemed to work fine with the exception that it lost all the passwords. I got one error telling me that for security reasons, it left all passwords NULL. Sure enough - they are now all blank.
Ideas - any good articles with quick fixes ???
Thanks a bunch - I am just getting into a lot of unusual problems this week.
Nancy
By the way - what happened to the SQL*Pass message boards ??? Nobody visits there now....
View 2 Replies
View Related
Oct 4, 2005
Hi.
I've following problem:
- Customer played with his logical drives in a CPQ smart array
- He played too much: he lost the drive with the LDF-File
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
In fact the mode is not wired: what shall he do other when
most important file is missing... :o
- The problem for me now is:
> How can I get the Enterprise Manager or Query Analyzer
up and running again to start a restore?
PS: The restore is to be done from a tape-library running
Veritas Backup Exec + SQL-Agent
PPS: Error-log says (what I already know):
2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:DEVLOG1DEVLOG1.ldf' may be incorrect.
Thanks in advance for assistance.
Regards,
Carsten Loeffler
View 8 Replies
View Related
May 4, 2006
OS: Windows XP Pro, SP2
SQL: SQL Server 2005 (Server 9.0.2047) SP1
VS: Visual Studio 2005 RTM.050727-4200
--------
After upgrading our SQL Servers to SP1 we have lost the ability to debug .net CLR stored procedures. The debugging process worked fine until we installed the Service Pack. All other features we use test out fine after the upgrade. The only and very important feature that does not work is debugging CLR procs. In fact this problem is happening in every environment we have. Be it SQL2005 running on Windows XP SP2, Windows Server 2003 SP1, Window Server 2000 SP4. SQL Server remote or local on the machine doing the debugging. All 5 developer workstations with Visual Studio in virtual machines or installed on in base OS are behaving exactly the same. Every developer on our team has the exact same problem regardless of their configuration after the upgrade to the service pack 1. All developers and environments were working correctly before the upgrade.
Even building a new SQL box from scratch and installing all tools and creating a new clean database we fail with same error.
We have verified CLR Debugging is enabled for the database connection. We have dropped the connection and recreated it.
Here is an example of the output from the output window:
This is the output up to the CLR call
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
Auto-attach to process '[1072] [SQL] sql_test2' on machine 'sql_test2' succeeded.
Output AFTER the CLR Call
Auto-attach to process '[1072] sqlservr.exe' on machine 'sql_test2' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'CIS_Release6a', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILMicrosoft.VisualBasic8.0.0.0__b03f5f7f11d50a3aMicrosoft.VisualBasic.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.
Program Locks up with blank screen and hangs. If we stop the Debugging then the correct results will appear in the output window.
Column1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<CIS><Header><MessageType>FosterParent</MessageType><Status>-1</Status><ErrorMsg><Code>-100</Code><Description>Requested Record Not Available</Description></ErrorMsg></Header></CIS>
No rows affected.
(1 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[qsp_FF_FD_FP_FosterParent].
Any ideas? TIA
View 1 Replies
View Related
Mar 6, 2000
I use detach/attach method to move database to a new SQL Server 7 on MSCS. The security information was lost in the new server. Database Access permission was gone and I failed to select it again because message showed the database role is already exist. The default databases were also posted incorrectly. Would you please to tell me how to fix this type of peoblem or let me know how to move the login info such as login id, password, database permisson to the new server which locates in a different machine.
Thank you very much.
Rich
View 3 Replies
View Related
Mar 3, 2005
Can anyone help me to recreate the .NDF file if it was lost and not restorable? I have a stored procedure written that will then help me rebuild all indexes on all tables, but I can't seem to figure out how to create the index file again so I can run it. I have tried index files from other databases to no avail. There is no backup to go to.
View 10 Replies
View Related
Mar 28, 2008
here is my error message wich can also be seen live at www.aspdoug.com/menu log in with user: doug pass: macromedia!
--------------------------------------------------------------------------------------------------------------------------------
Server Error in '/menu' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +421
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +133
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68
[HttpException (0x80004005): Unable to connect to SQL Server database.]
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +124
System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +86
System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +25
System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +397
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Below is my web.config file.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------<?xml version="1.0"?><configuration>
<appSettings/>
<connectionStrings>
<add name="aspdougcomConnectionString" connectionString="Data Source=65.36.214.248;Initial Catalog=aspdougcom;Persist Security Info=True;User ID=*&^%$#@;Password=*&^%$#@"providerName="System.Data.SqlClient" /></connectionStrings>
<system.web><!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
Visual Basic options:
Set strict="true" to disallow all data type conversions
where data loss can occur.
Set explicit="true" to force declaration of all variables.-->
<compilation debug="false" strict="false" explicit="true"></compilation>
<pages><namespaces>
<clear /><add namespace="System" />
<add namespace="System.Collections" /><add namespace="System.Collections.Generic" />
<add namespace="System.Collections.Specialized" /><add namespace="System.Configuration" />
<add namespace="System.Text" /><add namespace="System.Text.RegularExpressions" />
<add namespace="System.Web" /><add namespace="System.Web.Caching" />
<add namespace="System.Web.SessionState" /><add namespace="System.Web.Security" />
<add namespace="System.Web.Profile" /><add namespace="System.Web.UI" />
<add namespace="System.Web.UI.WebControls" /><add namespace="System.Web.UI.WebControls.WebParts" />
<add namespace="System.Web.UI.HtmlControls" /></namespaces>
</pages>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user. -->
<authentication mode="Forms" />
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>-->
</system.web>
</configuration>
-------------------------------------------------------------------------------------------------------------------------
MY host told me that they do not support sqlexpress and to change it MySql 2005 or something. im using hostmysite.com builder plan.
View 1 Replies
View Related
Nov 8, 2015
I recently installed a trial version of Sql12k on my laptop. Since 1st of November I've been creating tables without problems (15) and different schemas.I would like to emphasize that both mdf and ldf files are allocated in my USB3 portable disk. is that a problems for Sql12K? SSMS had stuck just for 20 sec or so. Then, system itself kill the task. A huge surprise when I restarted again... all the databases including the last one.. without objects. I mean, user databases...
Where are my logins? Where are my tables and schemas? Jesus christ.Sql logs inform the following (in all the moment my USB hardisk was properly connected)
11/08/2015 16:00:30,spid19s,Unknown,The operating system returned error 21(El dispositivo no está listo.) to SQL Server during a read at offset 0x000000 00140000 in file 'f:enriccopia_de_bbbb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency
[code]....
View 4 Replies
View Related
Jan 11, 2008
Here's my scenario. I've got an web site that is going to allow access across two different domains. So I pull out the user name and domain with a split. At that point I want to say If user name = a value in bug_user table then redirect ~default.aspx Else redirect to other page. I'm kind of stuck. Here is what I got so far. Imports System.DataImports System.Data.SqlClientPartial Class MasterPage Inherits System.Web.UI.MasterPage Sub page_load() Dim user As System.Security.Principal.IPrincipal user = System.Web.HttpContext.Current.User Dim username As String username = user.Identity.Name Dim namer As String Dim domain As String Dim firstname As String namer = username.Split("")(1) domain = username.Split("")(0) Dim objcon As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("bug_trackerConnectionString") Dim str As String = objcon.ConnectionString Dim con As New SqlConnection(str) Dim com As New SqlCommand("", con)?????????????????????????? con.Open() com.commandText = "Select FirstName, LastName, Userid, Domain from bug_user" con.Close()??????????????????????? Between the question marks is where i start to get lost. I don't know how to look into the column Userid in the table bug_user table to see if my variable namer is present and I don't know where I should start my if statement. Thank for your help.
View 1 Replies
View Related
Jun 19, 2006
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
View 54 Replies
View Related
Aug 21, 2007
Hi there,
Having problems exporting data from a database (sql2000 db) to a development db (also sql2000) using SQL 2005 Server Studio and Import/Export wizard (error: Violation of PRIMARY KEY constraint <keynamehere>. Cannot insert duplicate). The destination table has no records in it.
I followed the instructions here:
----------------------------------------------------
Run the following on the 2000 database
exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
Then use the export wizard on 2005,
select all tables and views
Edit Mappings and check the Enable Identity Insert box
UN check the Optimise for many tables box
Deselect ALL the views so that it is only tables that are copied
Run the export ignoring the warning
After the export is complete
Run the following on the 2000 database
exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
----------------------------------------------------
but it gives me:
Violation of PRIMARY KEY constraint <keynamehere>. Cannot insert duplicate.
Ive burnt a few hours on this now and thinking there must be something I am missing!
View 1 Replies
View Related
Jul 6, 2007
Hello all!
There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @@IDENTITY function to update FOREIGN KEY of some table.
When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @@IDENTITY function does not actually shows just inserted row's identity value.
This issue also generated when performing inserts via ADO.
For details, see my Feedback to Microsoft:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165
Also, there are other comments on this problem:
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124
View 5 Replies
View Related
Dec 8, 2005
I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.
View 13 Replies
View Related
Jan 12, 2008
Hi Guys,
The ReorderList control has driven me to desperate ends tonight. I found that it will only work if my database has a field (OrderID in this case) of type int that it can use to keep track of the order. The problem is that I'm not using the ReorderList control for inserting into the database. This requires me to keep track of the OrderID myself and I have found that simply setting the OrderID field as the "Identity" will cause it not to work.
Common sense tells me that using the Identity is the best way to keep track of a ReorderList because it automatically numbers things as ther're inserted. If something is deleted, it shouldn't matter because it will still be in order even to there's a gap in the numbering.
The only solution I can think of is trying having TWO identity fields in the database, but since that's not possible, a field that mirrors the Identity. Can anyone advise me on how to do that? The below datasource does not insert anything into the OrderID field:
<asp:SqlDataSource ID="sdsItems" runat="server" ConnectionString='<%$ ConnectionStrings:testConnectionString %>'
SelectCommand="SELECT * FROM [Items]"
InsertCommand="INSERT INTO [Items] ([Name]) VALUES (@Name) SET @OrderID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE [Items] SET [Name] = @Name, [OrderID] = @OrderID WHERE [ItemID] = @original_ItemID"
OldValuesParameterFormatString="original_{0}" DeleteCommand="DELETE FROM [Items] WHERE [ItemID] = @original_ItemID">
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Direction="Output" Name="OrderID" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="OrderID" Type="Int32" />
<asp:Parameter Name="original_ItemID" Type="Int32" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_ItemID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Jun 12, 2006
Hi,
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @@IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
Best regards,
George
View 21 Replies
View Related
Jan 22, 2008
Hi
I am trying to use the identity data type ( column)
I am using examples from the book and management studio in sql server 2005.
I am using the identity property for the customer Id in customers table.I accidentatly executed the querry twice and I had two same record with different customer id numbers of 1 and 2 . I realised the problem and I had to delete the second record.
The problem is now even if I have deteted the 2nd record with customer id 2 , when I insert a new record the identity value ( customer Id) increments with a number after the value I deleted. i.e if I deleted a second row with customer id 2 ( identity 2) when I enter a new record it enters with a customer id of 3 and whenever i add a new record it increments from there.
So instead of first record have cust id 1 and second record customer id 2 etc , I get first record with cust id 1 , second record with cust id 3 , third record cust id 4 etc.
How can I get rid of this wrong values of identity values whenever I delete a record and try to add a new record?
Thanks
View 1 Replies
View Related
May 19, 2014
How to merge the data from one database to another if we have identity column on both the database. If we are merging two companies,we need employee table of 2 database and insert them into first database and corresponding fkey tables say some 7 tables.how to merge if the table is having identity column.
DatabaseA has 7 Tables
Namely T1,T2,....T7
DatabaseB has 7 Tables
Namely T1,T2,....T7
T1 is master
T2 is Child
DatabaseA
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
DatabaseB
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
All the tables have interrelationship as shown pkey and Fkey
All the T1...T7 have Pkey with identity column starting from 1 and corresponding Fkey column in their child tables
Database A Table information Rows
T1-10000
T2-5000
T3-5000
T4-5000
T5-5000
T6-5000
T7-5000
Database B Table information Rows
T1-20000
T2-10000
T3-10000
T4-10000
T5-10000
T6-10000
T7-10000
Now i want to merge all the data from Database B to DatabaseA
How can i merge since in DatabaseA id for T1 starts from 1,2,3,4...and so on...
DatabaseB id for T1 also starts from 1,2,3,4...and so on...
and the fkey tables also have same 1,2,3,4...and so on... with reference of parent table
View 3 Replies
View Related
Apr 14, 2008
I need a script that returns the last identity value generated for each table in a database that has an identity column.
I am using SQL Server 2005.
Thanks.
View 6 Replies
View Related
Feb 3, 2006
I have a database that I am trying to copy to sql server 2005 and when I copy it the "Identity Seed" property loses its value.
I have tried to use the "Export" method by right clicking on the database then going to "Tasks" then "Export Data"
This copies all of the data but it removes the "Identity Seed" value.
When I run the "copy database" function it will also copy all of the data in the database but it will still not keep the "Identity Seed" value
I have many tables in the database with the identity seed field set to 1 on the primary key so that it will automatically create the primary key for each record.
Without this errors will occur in my web application because it thinks that I am trying to enter a null value into the primary key when it is supposed to auto populate.
When I export it with sql server 2005 it turns it back to zero.
The export function worked fine in Sql server 2000
The only way I can get it to work is by backing up the database and restoring it, but this is very time consuming and adds many steps to the process, and also makes it so that I have to overwrite my old database with the same name.
Is there a way to use the export function or the copy database function to retain the Identity Seed value
I really need this to continue do the switch to 2005 server. It seems like this may be a bug of some sort
View 13 Replies
View Related
Feb 20, 2007
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table
string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//insert an aerobics record into the aerocibs table
SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";
pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
int RowsAffected = 0;
try
{
RowsAffected = pgpDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}
finally
{
pgpDataSource = null;
}
//same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
Scotty
View 8 Replies
View Related
Feb 26, 2006
It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column
[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,
When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be
[ref] [int] NOT NULL,
instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.
-Eric
View 7 Replies
View Related