executing SQL script from Command promt not working

Hello
I am trying to execute SQl script from command prom like this :

C:\Inetput\wwwroot> osql -U sa -P -i MyComics.sql

(uid=sa and pwd=)

and I got the result like this :

[Shared Memory]SQl Server deos not exist or access denied
[Shared Memory]Connection Open (Connect()).

I already check the SQL server , it's running.
what do I do now?

Thanks in advanceAre you certain that the user name and password is correct? Are you certain that this instance of SQL Server is the main instance and not a named instance?|||Hello

thank for the response.

from my understanding sa is the default user name of SQL Server 2000 and password is blank. I did not set any password for my SQL server.

I'm really new to SQL server, if you have any advice I'll appreciate.|||Try osql -E <other parameters, except user and password
-E uses a trusted connection. Unless you set an sa password while installing SQL Server, SQL Server is set to use Windows authentication. You need to specify Mixed Mode authentication. You can change to mixed mode security in Enterprise Manager. Right click database, select properties, set Authentication to SQL Server and Windows. Make sure you set a good sa password. DO NOT leave it as blank!

Executing packages in a specific sequence

I am facing some issues while working on SSIS in VS2005,

The Scenario is we have 35 excel files from which we have to import data

to Sql 2005.

We have to execute these packages in a specific sequence so we are not

using For Each Loop Control.

Now the Problem is this that we have 35 executables as an output that is

corresponding to each package, and from each executable we can Change

excel path and Sql Connection String.

The problem is that one has to open each executable to set the path; we

wanted to keep them Configurable like , as if we can have any variable

define in the packages for path, and in the packages we use that variable

and append the XLS file name to it like ..

@.PathVariable+ test.xls

And from the Configuration file at run time we can set the path to this

variable like

@.Path Variable="C: /Windows/ Test Folder/"

And then executable picks the path from there.

Is there any possibility to implement any thing similar, we have tried but

its not working, any help in this regard will be really appreciating.

Its really urgent, I will wait for feedback on this.

Yeah you can do that. just use an expression on the ConnectionStrig property of the connection managers. This should give you some clues:

SSIS Nugget: Dynamically set a logfile name
(http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx)

-Jamie

Executing Multiple Scripts

Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly....
any suggestions...
thanks,
jonathanOSQL with a batch file. DTS/SSIS. Scheduled job.
Many ways to do this...|||do you know of a site or book where I can read how to do so....
a good search string for google....|||just giving it away today...

DECLARE @.SQLServer VARCHAR(100)
DECLARE @.Database VARCHAR(100)
DECLARE @.UserName VARCHAR(100)
DECLARE @.Password VARCHAR(100)
DECLARE @.UseWindowsAuthentication BIT
DECLARE @.Path VARCHAR(1000)
DECLARE @.FilePathToSQLFiles VARCHAR(200)

/*################################################# ###########################
If you are unsure of your sql server name, you can use the following
SELECT @.@.SERVERNAME. This should be the sql server where the database resides
that you are updating.
################################################## ###########################*/
SET @.SQLServer = 'MyServer'

/*################################################# ###
@.Database is the name of the database you are updating.
################################################## ####*/
SET @.Database = 'MyDB'

/*################################################# ###
Is the path to the sql files that you wish to execute.
Example FilePath : C:\SQL Scripts\
################################################## ###*/
SET @.FilePathToSQLFiles = 'C:\SQL Scripts\Create Scripts\'

/*################################################# ###################################
If you choose to use windows auth, you do not have to fill in a user name or password,
but your network account has to be a sysadmin on the sql server.
1 = use windows auth
0 = sql auth
################################################## ##################################*/

SET @.UseWindowsAuthentication = 1
SET @.UserName = ''
SET @.Password = ''

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Path

DECLARE cFiles CURSOR FOR
SELECT DISTINCT [SQLFileName]
FROM #SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] <> 'NULL'
ORDER BY [SQLFileName]

DECLARE @.vFileName VARCHAR(200)
DECLARE @.vSQLStmt VARCHAR(4000)

OPEN cFiles

IF @.UseWindowsAuthentication = 0
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -U ' + @.UserName + ' -P ' + @.Password + ' -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

IF @.UseWindowsAuthentication = 1
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -E -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

CLOSE cFiles
DEALLOCATE cFiles

Print '################################################# ################################################'
Print 'Please review the log file located at ' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt'
Print '################################################# ################################################'
GO
DROP TABLE #SQLFiles
GO|||Much much appreciated...|||just giving it away today...

Slut

This message is to short

Executing MDX StoredProcedures using AdomdClient.dll

Hi fellow developers,

At the moment, i have a SSAS 2005 installed and a working cube on it. Everything is working fine. However i want to have an ASP.NET Page to access some of the data in the cube and present it.

Well this would be no problem at all, if i use the AdomdClient and send queries over it, but this destroy my layer architecture. I dont want any Queries in my Application, but in the SQL Server.

When i used a sql server database i always used stored procedures to access the data because of security and consistence.

Now is there a possible way to create MDX Stored Procedures and execute them with the Adomd API like i did with sql server databases?

Or other clean solutions like creating mdx libraries on server and access it with the client?

I appreciate your help.

Sincerely

David

PS: If you find any gramatically mistakes, keep em :-))

Moving to SQL Server Analysis Services forum.

Thanks,
Sarah

|||So far there is no support for stored procedures. There is support for parameterized queries though.

So you do not want ASP.NET code contain explicit text of MDX queries, right? What is your real goal here? Do you want to hide the text of the MDX queries from the writer of ASP.NET code?

How about creating some .NET component accepting a reference to the connection object, a name of the "stored procedure" and list of parameters? The component would fetch the real text from some other store, use the passed connection and parameters and pass the query to the server. Then return the results back to the client.

This is really not that useful, but maybe it will cover your goal.|||

Hi Andrew

Thanks for your answer.

Well the goal would be that, other applications could access the same Queries, without copying the Queries in their application code. However we decided in the meanwhile that we use the queries in the code, because we dont have any time to search other clean solutions :-)

But still i am interested in a clean splitted Query Logic -> Application Logic solution. If anyone knows? Please let me know.

Sincerelly

David

|||By other applications you probably mean those also developed by you, right? The applications could load the component. It could also accept references to a store of MDX queries so that different scenarios would load different stores and keep the queries in memory (so that not to have performance problems in multithreaded scenarios).sql

Executing a SQL Script with MSDE

Hello,

I am learning about .NET apps and working with the ASP.NET Unleashed book. The project I am working on says to execute the SQL script using the SQL Query Analyzer, which I assume is available with Enterprise Server.

Could anyone guide me through the process of executing a SQL Script with the MSDE? Or point me in the right directions (white papers, etc)

Much appreciated

MPYou can use OSQL which is a command line tool that comes with MSDE.

This should get you started|||Thanks, that should get me started on something. I appreciate the prompt reply.

MP

executeScalar - count(*)

i have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)

my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"

what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!ow... i forgot the .Text of the textboxes...
it's now solved!|||please don't write code like this unless you want criminals to steal your data.

see http://www.dbforums.com/showpost.php?p=6263508&postcount=7|||ow... tnx 4 ur concern! actually i don't know securities like that because im just doing a project in my subject and don't care about those things. but i really appreciate ur response and i will study those links. tnx again!

ExecuteNonQuery crash my application

I'm working on an application with Compact Framework 2 and SQL Server Mobile
2005. When the application start, if there is no data base in its folder, one
is created using CreateDataBase from SqlCeEngine (I'm sure that every object
used for that operation is closed after create the file). After that, it
pulls the data from a SQL Server 2000.
In Windows Mobile 2005, everything works fine... but when my application is
running under Windows CE 4.2, after any synchronization, if I throw any
instruction like "UPDATE" or "INSERT" (not with "DELETE") with
ExecuteNonQuery, the application crashes and exits without an error
notification. When I try to open the application without synchronization
because it's unnecessary, everything works fine. If I try to make another
synchronization, works... but then I throw another instruction with
ExecuteNonQuery, it crashes the same way.
So, my solution was to close the application everytime there is a
synchronization, but that's useless.
I've tried not to create the database with code, dropping all the tables
before the synchronization and it works... but it crashes anyway when I use
ExecuteNonQuery.
?Is there any solution for this? ?Anyone has the same problem?
I too have an application developed in .NET CF 2.0 , with SQL CE 3.0
database. I am executing the application on HP-iPAQ Win CE 4.2 device. When
there is a call to
update, and ExecuteNonQuery, the application crashes.
Synchronization is done with ADO.Net. Is there a way to resolve this?
Thanks,
Sangeetha

ExecuteNonQuery - Add working/Update not working

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:

Dim cmd As New SqlCommand("pContact_Update", cn)
 'Dim cmd As New SqlCommand("pContact_Add", cn)
 Try
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.Add("@.UserId", SqlDbType.VarChar).Value = UserId
 cmd.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = TextBox1.Text
 [...etc more parameters...]
 cmd.Parameters.Add("@.Id", SqlDbType.VarChar).Value = ContactId
 cn.Open()
 cmd.ExecuteNonQuery()
 Label1.Text = "done"
 cn.Close()
 Catch ex As Exception
 Label1.Text = ex.Message
 End Try

When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...Before you do your executeNonQuery - - make sure (do a response.write or a Trace.Write) to make sure your USERID and textbox1.text values are actually populated.

Many times, if the update statement has a where clause, and it continues through, the WHERE arguments are not being fulfilled.|||Thanks for your reply...
I followed the code and the Id field is getting a value. I also added:

Dim NbrRows As Integer = cmd.ExecuteNonQuery()
Label1.Text = NbrRows

And I do receive a message that 1 row has been affected. However, the value I entered changes back to the original value and the record is not updated.

Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute Process Task Error on copy

Hi,

I am trying to run a very simple copy command from an execute process task:

copy O:\myFolder\myFile*.txt D:\myFolder

I have a working directory set also.

However, there is an error icon on the task, and if I attempt to run it I get the following error:

Error at Execute Process Task [Execute Process Task]: File/Process "" does not exist in directory "copy O:\myFolder\myFile*.txt D:\myFolder".

Error at Execute Process Task: There were errors during task validation.

This command works fine from the command line. What am I doing wrong here?

Thanks

O:\myFolder\myFile*.txt D:\myFolder

The above should go in the arguments parameter box if you're not doing that.|||

It seems that it's looking for an executable, like a .bat file. It doesn't seem to recognize the dos copy command.

for example:

Executable: copy

Arguments: o:\myFolder\myFile.txt D:\myFolder

but it still complains that copy is not an executable.

So, it seems I have to put this in a batch file, which seems dumb, or I need to use the File System Task, but I don't know how to specify which files to copy using this method.

?

|||Instead, try using the full path to xcopy

c:\windows\xcopy.exe|||

xcopy is not installed

where can one find just the "copy" command exe?

|||

sadie519590 wrote:

xcopy is not installed

where can one find just the "copy" command exe?

xcopy is not installed? You're kidding me? It's distributed with Windows.

There is no "copy.exe" as it's an internal command within "command.exe or cmd.exe."|||

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

|||

sadie519590 wrote:

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

You need to specify the full path to xcopy.exe in the Executable parameter..|||But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.|||

sadie519590 wrote:

But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.

I think the way to do this is to use a foreach loop to scan the source directory for the files you want to copy (you can use a wildcard there) and then have it populate some variables (source filename, etc...). Then, inside the foreach loop, you use a filesystem task to perform the operation for each file found by the foreach loop.

Does that make sense?|||Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?|||

sadie519590 wrote:

Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?

Sure thing! http://connect.microsoft.com/sqlserver/feedback

Execute Package Task Not Working

I am trying to run two execute package tasks. I want the second task NOT to
execute if the first one fails, but I cant seem to get this working. I have
put the workflow in place and check fail package on first error in every
place I can think of, yet the second package always executes regardless if
the first one fails. HELP!!!!Do you have an error? What does the package log say?
Anith

Execute ftp script

Hi !

We have a sql 2000 with sp2.

We wan't to execute a *.cmd file with a ftp command.
The ftp.cmd file is working ok interactive.
But if add it to enterprice manager/SQL Server Agent/Jobs we get failed.

Here are the cmd file:

@.echo off
del d:\mtdaxel\ftppmlogg.txt

date /T >> ftppmlogg.txt
time /T >> ftppmlogg.txt

ftp -n -s:ftppm.ftp >> ftppmlogg.txt
exit

Any sugestion "please" for our problem.

Regards Jan RockstedtHave you tested this script outside of SQL Server Agent jobs ?

The message you are receiving indicates that something is invalid with your ftp statement. The only thing that might be incorrect in your statement is the filename "ftppm.ftp". Since you are not using an absolute path, when the agent runs the job the path is probably incorrect. Try using an absolute path.

Good luck.|||Yes i have test it outside.

I will try the absolute path tomorrow.

Thanks !!!!

//Jan|||Yes it was the path.

Thanks rnealejr !!!!

:)|||Yes it was the path.

Thanks rnealejr !!!!

:)