How can i connect to sql sever 2005 express from the command prompt.

Iam trying to connect to local copy of sql sever express from the command prompt with this command(sqlcmd) but i get the error below. What should i do to overcome the error. Basically what i want to do is to try out some commandline backup utilities and i deadly want to know how to do a backup from the command prompt. Help is greatly appreciated.

HResult 0x2, level 16, state 1

Named pipes provider: could not open a connection to sql sever [2]

sqlcmd: Error: Microsft sql native client: An error has occurred while estarblishing a connection to the sever. When connecting to sql sever 2005, this failure may be caused by the fact that under the default settings SQL sever does not allow remote connections..

Sqlcmd: Error: Microsoft sql native client: Login time out expired.

Assuming you have the server name correct I would check in the control panel --> administrative tools --> Services and make sure that sql express is running|||I got it right. Looks like it was a typing mistake. But i have one question here, backingup a database from the command prompt to me looks tiresome. What is likely to go wrong if i just copied my projects folder from the production sever to a nother machine where i want the backup to be insteady of going through all these good but confusing steps. I i just copied the folder to a nother location or computer, are the end results not the same with if i had follwed all these database backup procedures. Dont laugh at me, iam still new to this stuff.|||

Hi,

This might be caused since SQL Server 2005 does not allow remote connection under default configuration. Please enable this according to the following KB article.

http://support.microsoft.com/kb/914277/en-us

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

How can I access SQL server from SCO Unix?

We are running SCO Unix 5.0.5. We need a command line sql client that can connect to a MS SQL server running on a windows server. Can someone point me in the right direction? I don't want to write a sql client. I just want a command line sql client binary that is ready to work.

I want to do something like this in a unix shell script:

# sql -s 10.1.2.3 -u username -p password -f sqlcommandsinafile.txt -o sqlresults.txt

Any help would be appreciated....

I am not aware of such a cmd line tool on unix. To achieve what you want, you would need to get a hold of a 3rd party unix ODBC driver for SQL server. There is an odbcsql sample that ships with MDAC SDK, you can download the sample and port it to run on Unix.

Hope this helps.

sql

How can I access SQL server from SCO Unix?

We are running SCO Unix 5.0.5. We need a command line sql client that can connect to a MS SQL server running on a windows server. Can someone point me in the right direction? I don't want to write a sql client. I just want a command line sql client binary that is ready to work.

I want to do something like this in a unix shell script:

# sql -s 10.1.2.3 -u username -p password -f sqlcommandsinafile.txt -o sqlresults.txt

Any help would be appreciated....

I am not aware of such a cmd line tool on unix. To achieve what you want, you would need to get a hold of a 3rd party unix ODBC driver for SQL server. There is an odbcsql sample that ships with MDAC SDK, you can download the sample and port it to run on Unix.

Hope this helps.

How can control Transactions for creating Stored Procedure ?

I create StringBuilder type for

concating string to create a lot of stored procedure at once

However When I use this command

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

------- This any command

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

on any command

If I use

Create a lot of Tables

such as

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE TABLE [dbo].[Table1](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It correctly works.

But if I need create a lot of Stored procedure

as the following code :

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE PROCEDURE [dbo].[DeleteItem1]
@.ProcId Int,
@.RowVersion Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId AND
[RowVersion] = @.RowVersion
END

CREATE PROCEDURE [dbo].[DeleteItem2]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

CREATE PROCEDURE [dbo].[DeleteItem3]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It occurs Error ???

Please help me

How should I solve them ?

the stored procedure create

CREATE PROCEDURE ..

have to be first in T-SQL command batch. You can do what you need by inserting each single procedure code into varchar(max) variable and run this code using EXEC command like

DECLARE @.lcCommand as varchar(max)

SET @.lcCommand ='CREATE PROCEDURE PROC1 .....'

EXEC (@.lcCommand)

SET @.lcCommand ='CREATE PROCEDURE PROC2 .....'

EXEC (@.lcCommand)

remember if you procedure definition is longer than 8000 chars split it into chunks not longer than 8000 chars to prevent errors( for some reason string passed to varchar(max) in single assign is cut at 8000 position if longer than 8000 chars)

how can check the time?

I have a stored procedure and I want to run a command based on time so if the time is after 8pm the charge the client US$ 10 otherwise US$ 8

Code Snippet

charge = case when datepart(hour, getdate())>=20 then 10 else 8 end

|||

That covers it up to midnight. Most likely you will also want to continue past midnight up to some early morning time.

If so, then something like this: (building on phdiwakar's suggestion) to charge $10 between 8 PM and 6 AM

Code Snippet

Charge = CASE
WHEN ( datepart( hour, getdate()) >= 20
OR datepart( hour, getdate()) <= 5
) THEN 10
ELSE 8
END

How can a variable used in SQL select command

select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.You can, but you are missing some basic insights here ...

You can do this like this:

string sql = "Select * from TABLE where user = '" + name + "'"

OR use a stringbuilder of so ...

If you are using SQL Server or any decent DBMS, try using stored procedures instead|||Thank you very much!

hot rebuild all indexes

how can run a command to rebuild all indexes in a database?

Do you want to just update the statistics or rebuild the indexes? The latter does more than just update statistics of the index. You can use sp_updatestats to rebuild statistics for all tables in the current database. There is no equivalent one for rebuilding indexes however and you will have to write your own. Lastly, you do not want to perform both these operations without knowing the ramifications. Check out the blog from the SQL Server Storage Engine team for more details on these operations and also a whitepaper:

http://blogs.msdn.com/sqlserverstorageengine/default.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

|||hi
you can use ALTER INDEX for reindexing.
i think if you use from "Indexed View" or "Covering Index" then your speed is very high.
good luck|||It depends. Using indexed views or adding more columns in an index to cover a query can adversely affect DML operations on the base tables. You need to outweigh the pros and cons before using indexed views or creating a covered index. In SQL Server 2005, we also have the ability to INCLUDE columns to non-clustered indexes at the leaf level so that they are not part of the key column(s). This has slightly better performance benefits than adding columns to the index key(s). This is another option to consider and evaluate.|||

Hi,

It is neccesary to check the Index statistics to measure the health of an Index.In Order to see statistics of any index follow the sample T-SQL command you will need to run:

DECLARE

@.ID int,

@.IndexID int,

@.IndexName varchar(128)

--input your table and indexname

SELECT @.IndexName = 'AK_DepartmentName'

SET @.ID = OBJECT_ID('HumanResources.Department')

SELECT @.IndexID = IndID

From sysindexes

Where id = @.ID AND name = @.IndexName

--run the DBCC Command

DBCC SHOWCONTIG( @.id, @.IndexID)

Note:DBCC-->Database Consistency Checker is used for checking lots of entities in SQL Server.

But, as per your requirement you can also run "DBCC SHOW STATISTICS" to see when was the last time the indexes were rebuild.

The same example as above,

DBCC SHOW_STATISTICS ('Humanresources.department' , 'PK_Department_DepartmentID')

After this, you can Reorganize your index using "DBCC DBREINDEX".You can either request a particular index to be re-organized or just re-index all the indexes of the table.

The same example of HumanResources.Department.

--This will Re-index all your indexes belonging to "HumanResources.Department".

DBCC DBREINDEX ([HumanResources.Department])

--This will Re-Index only "AK_Department_Name"

DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name])

--This will Re-index with a "Fill factor"

DBCC DBREINDEX ([HumanResources.Department],[AK_Department_Name],70)

You can then again run DBCC SHOWCONTIG as in the first sample code to see the results.