Database

Reset autonumber (ID) in SQL Server

12-Jan-09 2:31

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.

The need to optimize rises from a bad design.My articles[^]

Matjaz-xyz11-Jan-09 19:53

Matjaz-xyz

11-Jan-09 19:53

Hi. Is it possible to reset the autonumber of a ID in MS SQL Server?

Regards,
Matjaž

Re: Reset autonumber (ID) in SQL Server

Matjaz-xyz11-Jan-09 20:31

Matjaz-xyz

11-Jan-09 20:31

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ž

sql connection error

staticv11-Jan-09 8:01

staticv

11-Jan-09 8:01

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
    //  SQL Server authentication
    connection->ConnectionString = 
        "User ID=sa; Password=;"
        "Data Source=(local); Initial Catalog=DCV_DB;";
#else
    //  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";
#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 Frown | :(

Top Web Hosting Providers[^]

Do, or do not. There is no 'try'.

Wendelius11-Jan-09 10:28

11-Jan-09 10:28

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.

The need to optimize rises from a bad design.My articles[^]

staticv12-Jan-09 0:23

staticv

12-Jan-09 0:23

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

Top Web Hosting Providers[^]

Do, or do not. There is no 'try'.

Wendelius12-Jan-09 3:36

12-Jan-09 3:36

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.

The need to optimize rises from a bad design.My articles[^]

Install SQL Server on Vista

Henry Minute11-Jan-09 10:35

Henry Minute

11-Jan-09 10:35

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.

George_George11-Jan-09 2:41

11-Jan-09 2:41

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

Wendelius11-Jan-09 3:05

11-Jan-09 3:05

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.

The need to optimize rises from a bad design.My articles[^]

George_George12-Jan-09 0:21

12-Jan-09 0:21

Thanks for your good points, Mika!

regards,
George

Wendelius12-Jan-09 2:26

12-Jan-09 2:26

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

* Developer Day Scotland 2 - Free community conference
* The Blog of Colin Angus Mackay

Colin Angus Mackay11-Jan-09 3:20

Colin Angus Mackay

11-Jan-09 3:20

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.

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

George_George12-Jan-09 0:24

12-Jan-09 0:24

Thanks for your good idea!

regards,
George

Hamid_RT15-Jan-09 2:26

Hamid_RT

15-Jan-09 2:26

See SQL SERVER - 2008 - Hardware and Software Requirements for Installing SQL Server 2008[^] and Installation Notes: SQL Server 2005 on Windows Vista with VS2008[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

George_George15-Jan-09 18:36

is there the sql syntex Limit in MS SQL?

15-Jan-09 18:36

Thanks Hamid,

Good stuff!

regards,
George

neodeaths11-Jan-09 0:44

neodeaths

11-Jan-09 0:44

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?

Re: is there the sql syntex Limit in MS SQL?

Reza Raad11-Jan-09 1:09

Reza Raad

11-Jan-09 1:09

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

how do i make a special sql statment filter?

neodeaths10-Jan-09 22:55

neodeaths

10-Jan-09 22:55

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

Re: how do i make a special sql statment filter? [modified]

Wendelius10-Jan-09 23:10

Re: how do i make a special sql statment filter?

10-Jan-09 23:10

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.

The need to optimize rises from a bad design.My articles[^]

modified on Sunday, January 11, 2009 5:29 AM

neodeaths11-Jan-09 0:43

neodeaths

11-Jan-09 0:43

thanks nice method

WTL/ATL

manosza10-Jan-09 21:21

manosza

10-Jan-09 21:21

How can I use MSDataGrid in a WTL/ATL project
without using MFC ?
Is there an example in VC++6.0 ?

Thanks,
Manos.

Re: WTL/ATL

Wendelius10-Jan-09 21:58