How to input and output simultaneously in SQL 2005 Express?

I want to process data of an old VS6 project in VisualStudio2005.
First part is done. With SQL Management Studio I created the database file 'dbTest4' and attached it to SQL 2005 Express server (.\SQLEXPRESS).
Then I recoded the VS6_C++ project feeding its data into the database file using the ConnectionString:
"Provider='sqloledb';Data Source='.';Initial Catalog='dbTest4';Integrated Security='SSPI')"
Until here its working fine.

Assecond part I want to catch these data with VisualStudio2005 for processing there.
In VisualStudio 2005 I created a Windows Application as new project added the database file 'dbTest4' as data source, waited two minutes till VisualStudio2005 had created the DataGridTools bound to the tabbles of my database file and dropped a grid tool on to the surface of the new Form.
Great, within 5 minutes I had created a working database solution without writing a single line of code and it worked well. Starting the new build exe as standlolone it showed the data of my database file in a nice grid view.
But it worked as standolone only. When I tried to combine both parts one part was blockedalways producing error messages as ' ..error 32 (The process cannot access the file because it is being used by another process.) while attempting to open the file '...\dbTest4'.

Does anybody know
how to get this input/output combination running
or is ist impossible to reach my target with SQL 2005 Express?

After reading through Roger Wolters November 2005 article SQL Server 2005 Express Edition User Instances and some hours of testing I came to the conclusion:
When using Visual Studio not exclusively but simultaneously with other clients as SQLcmd, SQL Managent Studio or what else, as shown in my screenshot 325 you should consider to
set 'User Instance=False'
if you are struck by connection problems.
For details you are welcome to visit my sreenshot folder. By clicking the numbered screenshots, enlarging them and using your browsers back button you can follow a detailed installation and see why, where and how to set 'User Instance=False'.
Martin

How to increment ID sequentially?

I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?

Look up DBCCCHECKIDENT. You can use it to view and / or update the identity seed value.

|||Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx|||

Hi mychucky,

You would have to use 'identity seed' property in your table design window in your SQL Server.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Thanks! I'll give it some reading. It's complicated then I thought. I thought there is a button in SQL Management Studio where I can just click and all is reset.|||

ZLA:

Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Thanks so much! I got it working now. I would never have found the solution if without your link.

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
Olav
Olav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>
|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>
|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.

> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
OlavOlav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>> Olav,
>> Follow the menu path:
>> Tools
>> Options
>> Then in the popup box:
>> Query Execution
>> SQL Server
>> General
>> Set Execution Timeout to 0
>> RLS
>> "Olav" <x@.y.com> wrote in message
>> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>> Olav,
>> Follow the menu path:
>> Tools
>> Options
>> Then in the popup box:
>> Query Execution
>> SQL Server
>> General
>> Set Execution Timeout to 0
>> RLS
>> "Olav" <x@.y.com> wrote in message
>> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>> I'm trying to execute a "DELETE FROM table" command from a query window
>> in SQL Server 2005 Management Studio, but the query times out after 30
>> seconds.
>> How do I increase this timeout value?
>> Olav
>>
>

How to increase timeout?

I'm trying to execute a "DELETE FROM table" command from a query window in
SQL Server 2005 Management Studio, but the query times out after 30 seconds.
How do I increase this timeout value?
OlavOlav,
Follow the menu path:
Tools
Options
Then in the popup box:
Query Execution
SQL Server
General
Set Execution Timeout to 0
RLS
"Olav" <x@.y.com> wrote in message
news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
> I'm trying to execute a "DELETE FROM table" command from a query window in
> SQL Server 2005 Management Studio, but the query times out after 30
> seconds.
> How do I increase this timeout value?
> Olav
>|||The timeout in that dialog is set to 0 and still the query times out in 30
seconds.
Olav
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
> Olav,
> Follow the menu path:
> Tools
> Options
> Then in the popup box:
> Query Execution
> SQL Server
> General
> Set Execution Timeout to 0
> RLS
> "Olav" <x@.y.com> wrote in message
> news:%23rf%23mTLnHHA.4848@.TK2MSFTNGP05.phx.gbl...
>|||"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
I believe it's timing out because some other process is blocking it. Not
because the query itself is taking too long.
Find out what's blocking it and fix that.

> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Olav,
Greg could be right, but I would not expect a block to cause a timeout
unless the connection timeout period was exceeded. There is no limit to how
long a block can be held, just a limit to how long you are willing to wait
for it.
Could you check your server to see if the "query wait" option is configured
to something other than -1?
RLF
"Olav" <x@.y.com> wrote in message
news:eodLwsLnHHA.3968@.TK2MSFTNGP06.phx.gbl...
> The timeout in that dialog is set to 0 and still the query times out in 30
> seconds.
> Olav
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OTsNMkLnHHA.5068@.TK2MSFTNGP05.phx.gbl...
>sql

How to import/export databases from remote server to local SQL 2005 Express

I've successfully installed SQL2005 express edition and SQL Server Management Studio Express. All seems well except there does not appear to be any way to import databases from other servers to my local server. The remote servers are running SQL Server 7.

In the Management Studio Express I can connect to the remote databases and work with them, but I can neither import them into my local machine, nor can I export from remote to local (the option doesn't seem to exist). By contrast, using the Enterprise Manager in SQL 7 there has always been the option to do this under the All Tasks menu.

If this cannot be done, is there a way to backup a remote database to my local disk and then restore it into my local DB?

Is this by design? Can anyone help with this?

Thanks in advance.

HD

First, you can always detach the databases on the old server using sp_detachdb, move the files to the new server, and attatch them using sp_attachdb. Check BOL for detailed documentation on these stored procedures.

Second, I've moved this thread to the tools forum where experts can tell you if there's a way to do this through Management Studio Express (I'm guessing not).

Paul

How to Import Data from AS400 files to Sql 2005 tables?

Hi All,

I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis

IBM DB2 UDB for isereis IBMDA400 OLE DB Provider

Regds,

Anu

If this is going to be a regular task, might as well dump your AS400 data in a delimited text file and import from there|||Moving to the SSIS forums

How to import an mdf file into SQL Server Management Studio Express?

Hi

I had to recover my computer but before I did that I grabbed a new version of my mdf file but I don't know how to actually import it into SQL Server Management Studio Express evertime I try to open the file up it just crashes.

I rather not have to redue that whole database again. I am a noob so step by step instructions are needed.

Thanks

Open SQL Management Studio Express and log in to the server to which you want to attach the database. In the 'Object Explorer' window, right-click on the 'Databases' folder and select 'Attach...' The 'Attach Databases' window will open; inside that window click 'Add...' and then navigate to your .MDF file and click 'OK'. Click 'OK' once more to finish attaching the database and you are done. The database should be available for use.

|||

Thanks but I get an error.

|||

I think I got it working.

|||

aggiekevin:

Open SQL Management Studio Express and log in to the server to which you want to attach the database. In the 'Object Explorer' window, right-click on the 'Databases' folder and select 'Attach...' The 'Attach Databases' window will open; inside that window click 'Add...' and then navigate to your .MDF file and click 'OK'. Click 'OK' once more to finish attaching the database and you are done. The database should be available for use.

This is true. One important note I would like to menion is, it is highly recomended to take a copy of .mdf file bedore attaching it to the new database. Why? Because if this .mdf file was one of the SQL Server 2000 databases and you attached it to one of the SQL Server 2005 databases. There is no way -if you wish in futute- to detach that .mdf file from SQL Server 2005 and attached it again in SQL Server 2000 or eailer version.

Good luck.

|||

Thanks

How to import a text database file into SQL Server Management Studio Express

I have a text file with a bunch of zip codes:

99546,"AK","Adak",162.70,55.20,.00
99551,"AK","Akiachak",161.39,60.89,.00
99552,"AK","Akiak",161.19,60.89,.00
99553,"AK","Akutan",165.78,54.14,.00

...............

I want to import this in Sql Server Express. Is it possible to do this using Management Studio Express? If not, does anyone know a script that would import the data?

Hi Banks,

the easiest way to do it is to convert your text files to .csv file. and in the first row of the csv file insert relevent column names. You can yous DTS service to populate the data by specifying the csv file as a datasource. the DTS service identifies 1st row of the csv file as column names and you can do the mapping.

Cheers,

Raj

|||

HI

A single query will import the text file content into the Table.

BULK INSERT [TableName]
FROM 'c:\ZipDetails.txt'
WITH (FIELDTERMINATOR = ',')

You can run this SQL Query directly from the Query Analyzer

|||

Both seem like good answers but didn't work for my situation. I tried the Bulk upload but my web host provider disabled that command and SQL Server Express doesn't come with DTS.

What I did instead was I ran find and replace on the text file and inserted "insert into zips values " at the beginning of each line. I then copied it into query analyzer and ran about 40,000 of the insert commands on my database and that did the trick.

How to hide unauthorized databases with SQL 2005?

Hi,
I have configure permission for userA and he can access only one database.
When user estabilish the connection via management studio, though he cannot
access other databases, he can see them. Is it possible to hide other
databases for userA?
Appreciate all your reply.
ShaneVIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one databas
e while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:%2
300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||SL
Well, this unwanted user must be connected via SSMS (am I right?) and if you
have not added him/her to the database , he/she will see the database's nam
e but cannot access to
"SL Coder" <sl_coder@.hotmail.com> wrote in message news:eV8jH9BaGHA.4116@.TK2
MSFTNGP05.phx.gbl...
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one databas
e while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:%2
300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||After VIEW ANY DATABASE is denied, only master, tempdb, and databases that
the login owns are visible. Other databases that the user can access are
not enumerated but can still be accessed directly by setting the database
context (e.g. USE). Unfortunately, SSMS Object Explorer functionality is
limited to visible databases.
The reason for this behavior is that it is necessary to open each database
on the server to determine whether or not a non-privileged login has
database access. This caused performance issues on servers with a lot
(100's) of databases.
If this feature is important to you, make a suggestion (or vote on the
importance if already submitted) at the product feedback center:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:eV8jH9BaGHA.4116@.TK2MSFTNGP05.phx.gbl...
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one database
while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%2300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one
database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>