|
What kind of constraint do you want to drop?
For example, if you want to drop check constraint on a column, you first find it's name in system tables (for example in ALL_CONSTRAINTS) and then use ALTER TABLE command to drop the constraint.
|
|
|
|
|
Hi. Is it possible to reset the autonumber of a ID in MS SQL Server?
Regards,
Matjaž
|
|
|
|
|
OK, found it on Google.
I had to run a query "DBCC CHECKIDENT (yourtable, reseed, 34)". Got it off of a . Pinal Dave blog
Regards,
Matjaž
|
|
|
|
|
I'm connecting to SQL Server 2005 Express Edition using C++/CLI.
Here's my code, actually from the book I'm following
#include "stdafx.h"
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
void main()
{
SqlConnection^ connection = gcnew SqlConnection();
#ifdef SQLAuth
connection->ConnectionString =
"User ID=sa; Password=;"
"Data Source=(local); Initial Catalog=DCV_DB;";
#else
connection->ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DCV_DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
#endif
try
{
connection->Open();
Console::WriteLine("We got a connection!");
}
catch (SqlException ^e)
{
Console::WriteLine("No connection the following error occurred: {0}",
e->Message);
}
finally
{
connection->Close();
Console::WriteLine("The connection to the database has been closed");
}
}
I copied the connection string from the connection string property when you select the database connection in the Server Explorer in Visual Studio 2005.
And when I execute this i get the following error (exception thrown)
<br />
An error has occured 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: Named Pipes Provider, error 40: - Could not open a connection to SQL Server)
Sorry for the too wide post
|
|
|
|
|
At least one problem is that you should escape the backslashes, like:
connection->ConnectionString
= "Data Source=.\\SQLEXPRESS;"
+ "AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\Data\\DCV_DB.mdf;"
+ "Integrated Security=True;Connect Timeout=30;User Instance=True";
Also check that the SQL Server (SQLEXPRESS) service is running in Windows services.
I got a bit confused about the different connection strings and the database location. Is it your intention that your database is running under the service all the time. If it is, you should use a connection string like in SQL Server authentication, with integrated security set to false and without user instance.
|
|
|
|
|
What do you mean you got confused? I copied the connection string from the Database properties in Visual Studio 2005 Server Explorer...
I, at first created the database on another PC, then copied the .mdf and .ldf files of the database from \Microsoft SQL Server\MSSQL.1\MSSQL\Data
directory. After copying, I pasted it in the same directory on the other PC and added a new database connection in the Server Explorer (Visual Studio 2005). It popped up a dialog, to choose a data source I selected Microsoft SQL Server Database File and then pointed it to the files I pasted. Maybe that's why the connection string is different. Although I saw the connection string on the PC in which I created the database originally, it was different.
By the way how do you manually copy a database from one PC to another? The database isn't connected to anything. I'm just using it for education purpose and I'm using Visual Studio 2005 and SQL Server 2005 Express is installed in my PCs.
Anyways the problem is solved... I'll try it out on the other PC and see if it works there too. Thanks
|
|
|
|
|
The confusing part was the connection strings and how different they were:
manzoor10 wrote: // SQL Server authentication
connection->ConnectionString =
"User ID=sa; Password=;"
"Data Source=(local); Initial Catalog=DCV_DB;";
The above means that you connect to a default instance and use a database named DCV_DB, which is running all the time.
manzoor10 wrote: // Windows Integrated Security
connection->ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DCV_DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
This again means that you connect to a named instance (SQLEXPRESS) on local host and when connecting a database is attached to the SQL Server instance. So the database available only on the instance when this connection exists. Also since the database is located under C:\Program Files\Microsoft SQL Server\MSSQL.1, it gives the impression that the database is designed to be available in the SQL Server all the time.
In many cases database files that are attached to an instance while connecting to it, are not located under that folder (although there's nothing wrong technically). This is because attached db files usually ship as plain files with a product and the product installation may not necessary modify files under a different installation (SQL Server in this case).
manzoor10 wrote: By the way how do you manually copy a database from one PC to another
In many cases it's done by:
- creating a backup on the source and then restoring a backup on target or
- detaching a database on source, copy files to target and then attach the files to the target instance
Currently you do the attaching phase every time you connect but it can also be done only once and then you just use the database.
manzoor10 wrote: Anyways the problem is solved
That's the most important thing.
manzoor10 wrote: Thanks
You're welcome.
|
|
|
|
|
manzoor10 wrote: SQL Server does not allow remote connections
I have had this error in the past. It was along time ago but I am pretty sure that I remember the cause.
manzoor10 wrote: "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DCV_DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
This line from your code is a string. To use the '\' character in C# there are two options
1. Convert all the '\' characters in your string to '\\' (C# requires that you 'escape' '\' (amongst other characters) 'escaping' means precede it with the '\' character. This means that you end up with "Data Source=.\\SQLEXPRESS;Attach......." and continue right through the path for the database which would then start "AttachDBFilename=C:\\Program Files\\Microsoft.......................".
2. Precede the entire string with '@' which means I want you to use this string exactly as I have typed it.
Quote from MSDN Help
The advantage of @-quoting is that escape sequences are not processed, which makes it easy to write, for example, a fully qualified file name:
@"c:\Docs\Source\a.txt" // rather than "c:\\Docs\\Source\\a.txt"
Try either of these.
[Mod]
Darn it, beaten to the punch again!
[/Mod]
Henry Minute
If you open a can of worms, any viable solution *MUST* involve a larger can.
|
|
|
|
|
Hello everyone,
I tried to install SQL Server enterprise on my Windows Vista x64 Enterprise version, but failed. Seems SQL Server can not be installed on client platform (e.g. Vista)?
I want to setup DB and debug store procedure on my desktop. Anyone knows whether SQL Server can be installed on Vista Enterprise x64 version?
thanks in advance,
George
|
|
|
|
|
Im version 2008 Enterprise edition can be installed on Windows Server 2003 or 2008, see: http://msdn.microsoft.com/en-us/library/ms143506.aspx#EEx64[^]
You can install Standard or Express on Vista to debug a procedure or perhaps you can use Virtual Server and install Windows Server in that and then install SQL Server Enterprise Edition on the virtual server.
|
|
|
|
|
Thanks for your good points, Mika!
regards,
George
|
|
|
|
|
You're welcome
|
|
|
|
|
George_George wrote: I tried to install SQL Server enterprise on my Windows Vista x64 Enterprise version, but failed
You should install the Developer Edition on your client platform. It has all the features of Enterprise, but it works on Vista.
|
|
|
|
|
Thanks for your good idea!
regards,
George
|
|
|
|
|
|
Thanks Hamid,
Good stuff!
regards,
George
|
|
|
|
|
hi iam trying to use the SQL STATMENT LIMIT in Microsoft SQL
however it keeps saying error
is there another syntex that serve the same purpose?
and also is the LIMIT Syntex works only in mysql?
|
|
|
|
|
There is not LIMIT on MS_SQL
this is a MySql syntax code
and this means you can fetch rows between two range of results .
You can do this with TOP and ROW_NUMBER() keywords
for more information search for this keywords on MSDN
Human knowledge belongs to the world
|
|
|
|
|
hi iam trying to make a some wat special sql filter
my database is something like this
700
701
702
801
802
803
so i need to uniquely filter out the data by the First Character only
e.g
7
8
will be the end result
is there a way to do this via sql statement or shld i just select the whole thing and do it programmically
|
|
|
|
|
If the data is in numeric format, first convert it to string like:
CONVERT(varchar(50), YourColumn)
After that you can use SUBSTRING function along with DISTINCT on the string:
SELECT DISTINCT SUBSTRING( CONVERT(varchar(50), YourColumn), 1, 1)
FROM TableName
WHERE CONVERT(varchar(50), YourColumn) LIKE '[78]%'
Edited: Added the WHERE clause if needed. Also note that this could be more efficient with an inline view, depending on the table structures and indices.
modified on Sunday, January 11, 2009 5:29 AM
|
|
|
|
|
|
How can I use MSDataGrid in a WTL/ATL project
without using MFC ?
Is there an example in VC++6.0 ?
Thanks,
Manos.
|
|
|
|
|
Why did you decide to post this question on 5 articles and a forum named General Database?
Since your question is C++ related, I'd suggest you post the question only on C++ forum. I believe you would get much better answers from there. Posting the same question several times across the site is considered rude.
|
|
|
|
|
I know the following statement is incorrect. I had something going wrong in one set of insert statements and it was inserting the entry I wanted it to insert and then inserting a second entry for the next record that contained no data. Now I need to correct both tables, but the only way to know what to correct in the IncomingRecords table is from data in the Completed table. Any help would be greatly appreciated.
UPDATE IncomingRecords
SET Complete = NULL
WHERE (RecordID = (SELECT IncomingRecords.RecordID FROM Completed INNER JOIN
IncomingRecords ON CompletedCRD.RecordID = IncomingRecords.RecordID
WHERE (Completed.A_firm_name = N'') AND (Completed.A_broker_found = N'broker_fround') AND (IncomingRecords.Complete = N'Complete'))
|
|
|
|
|
With a quick look the only problem I see is that your select most likely returns several RecordID's so you mest use IN instead of equal. Like:
...
WHERE (RecordID <code>IN </code>(SELECT IncomingRecords.RecordID ...
Also you seem to miss one closing parenthesis in the end.
Was this the problem you were haviong?
|
|
|
|