Executing .sql File From Vb.net

Aug 15, 2007

Hi,

I have a .sql file that contains sql statement to create tables. Is there a way where I can execute the codes in this file creating the tables?

Thanks

View 6 Replies


ADVERTISEMENT

Management Studio Express && SQLcmd: Syntax Errors In Executing A MS-DOS Batch File && A Microsoft SQL Server Query File?

Nov 2, 2007

Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.

In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:

'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.

I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.

Thanks in advance,
Scott Chang




View 3 Replies View Related

Executing .sql File

Oct 4, 2006

hi

i have a .sql file, which i can run using query analyzer. is there any other way to execute this file, like using a .bat file or .exe file. user should be able to execute this file without using query analyzer or any other database tools. i cannot call this from my main programe.
this should be a self executable file.

thanks

suji

View 2 Replies View Related

Batch File Not Executing.. Please Help

Nov 6, 2000

Hi all,
I am executing a backup batch job scheduled daily. It runs
successfully but havent backed up any of the databases. The message
is
"The name specified is not recognized as an
internal or external command, operable program or batch file."

I am trying this for a full day and i gave up..please can anyone help
me or suggest me what to do?..The same batch file is executed in all
servers and is working fine..

Thank you in advance

Rani.

View 2 Replies View Related

Executing Sql Query From File

Aug 26, 2006

Hi everybody,
I need a syntax in SQL Server 7.0. By using which I need to excecute a query from file. By using xp_cmdshell I am getting it. But whole results comes in a single column. I need the result set as it appears while executing the query. Can you help me in this. Any help will be appreciated.

Thanks in advance,

Gopakumar N.Kurup

View 1 Replies View Related

Executing SQL From Text File.

Jun 2, 2008



I am using SQL Server 2005 Compact Edition.

I have a bunch of sql in a text file I would like to execute from my .net/c# program, (Mostly DDL and inserts I run via SSMSE when I create a new database) I would rather not go through the laborious process of opening the file, reading through line by line, concatenating strings, and sending it to the database. I would just like to say "here's a file of SQL, run it".

Any help would be awesome. Thanks.

View 4 Replies View Related

Executing VBS File Through Server Agent

Jun 28, 2007

I want to execute a vbs file through SQL server agent using following command using operating system command exec .

wscript E:website est.jewelexonline.comReadFile.vbs F:sqlbackDiffljewelexgroupcom,,3

It does not execute and the status remains executing only .
What could be the problem .

View 1 Replies View Related

Executing Querys From A Batch File

Aug 10, 2005

Someone asked me a curious question.Can I execute a query to sql server from a bat file? how?If not is there a simple scripting laguage that this person might use todrive his process that is similar to a dos bat file?

View 5 Replies View Related

Executing An External File Through SSIS

Nov 21, 2007

This is how I used to create DTS in SQL SERVER 2000 and then create a job for scheduling.



' Visual Basic ActiveX Script
'************************************************************************
Function Main()
url="http://www.pageName.com/abc.aspx"
set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.open "post", url, false
xmlhttp.send ""
'msgbox xmlhttp.responseText
Main = DTSTaskExecResult_Success
End Function


Now how will I do the same in the SQl SERVER 2005.

Please help as it is very critical to my work.

Thanks in advance.Manpreet

View 1 Replies View Related

Executing SQL Statement From Flat File

Jul 20, 2007

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.



I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.



I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.



Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

View 4 Replies View Related

Passing Parameters To Batch File And Executing Batch File Loop

Aug 7, 2007

HELP,

I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.

Please help

Phil

View 4 Replies View Related

Executing Osql Commands Through Batch File

Sep 13, 2006

Hello

I have a script ,which runs with osql

The script is :

osql -E
declare @cmd nvarchar(1000)
declare @cmd2 nvarchar(1000)
declare @state1 varchar(100)
declare @message varchar(100)
set @message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' +
+ char(10) + 'select @state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @cmd2 = 'declare @state varchar(100)' + char(10) +
'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' +
'N''@state varchar(100) out'',' +
'@state out' + char(10) +
'set @state1 = @state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out
-- Is the service that was monitored not
IF (UPPER(@state1) <> 'RUNNING.')
--if @state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail

It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.

But when I save it as bat file and try to run , it stops and doesnot even give an error.

Can anyone let me know what I can do.

Thanks

View 1 Replies View Related

Need A Help For Executing Script File Having Creation Of Stored Procedure From C#

Jan 9, 2008

 Hi,While executing script file from c# i have faced a problems like 1)Error at Go statement2) Must declare a scalar variable "@prmEvent" The following approach i have followed to executing script file. SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=localhost;initial catalog=Parish;Integrated Security=SSPI;";conn.Open(); string commandText = GetCommandText("Script file name"); SqlCommand databaseCmd = new SqlCommand(commandText, conn); databaseCmd.ExecuteNonQuery(); GetCommandTex()  method reads the script file from starting to end and returns script text.  My script file having following script.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoif exists (select 1 from dbo.sysobjects where id = Object_id('dbo.[prcSearchEvents]') and (type = 'P' or type = 'RF'))begin drop proc dbo.[prcSearchEvents]endGoCREATE PROCEDURE [dbo].[prcSearchEvents]@prmStartDate datetime,  @prmEndDate datetime,@prmEvent char(1)ASBEGIN    SELECT Title,FirstName,MiddleName,LastName,S.SexName as Sex, (CASE @prmEvent WHEN 'B' THEN DateOfBirth             WHEN 'D' THEN DateOfDeath             WHEN 'M' THEN DateOfMarriage         END ) as EventDate    from dbo.Parishioner P    left outer join dbo.Sex S on P.SexId = S.SexId    where (CASE @prmEvent WHEN 'B' THEN DateOfBirth             WHEN 'D' THEN DateOfDeath             WHEN 'M' THEN DateOfMarriage         END )    between @prmStartDate and @prmEndDate   END  But when i executing script file  having creation of stored procedure , i got such problem but other cases ( having normal sql commands) did not get any problm 

View 1 Replies View Related

Integration Services :: Executing BAT File On Remote Server And Scheduling It

Jun 24, 2015

I have to execute a .bat file on a remote server (that is used to stop and start services of an appl). The remote server doesn't have SSIS,SSMS installed. I want to create a package on my desktop the uses Execute process task and execute the .bat file on the remote server and then schedule it using the SSMS.

View 14 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

Passing The Xml Configuration File To The Package As An Input Parameter While Executing The Package

Feb 2, 2007

Hi,

I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.

So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.

Is this possible?

Thanks for your help.

$wapnil

View 6 Replies View Related

Pb Executing An SP

Sep 25, 2006

I have an SP that has this part of code located in the line:30
select convert(datetime,CONFIGURATION1) from dbdw..configuration
where CLECONFIG = 'DATE-AJOUT-VENTES'

I get teh error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Do u know what the problem is pls

View 3 Replies View Related

Executing A Job Using VB

Sep 17, 2007

I want to execute a backend SQL job using VB on an MSACCESS frontend.
I know how to run a DTS using VB script. Can anyone give me the syntax for a SQL job? Thanks in advance.

View 1 Replies View Related

Executing A DTS From Another DTS...

Jul 20, 2005

Hi there,I am trying to execute a DTS package from another DTSpackage and change some variables in the original DTS. Ihave used the info on the following page but it doesn'twork: http://www.sqldts.com/default.aspx?215. Would youhave any input?Thanks,Andreas*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Executing More Than One Query

Jun 16, 2007

Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
 

View 3 Replies View Related

Executing DTS (on Sql Server) From ASP.NET??

Feb 5, 2004

Does Any one know how to execute DTS (on sql server) from ASP.NET??

View 2 Replies View Related

Executing DTS Package From VB.NET

Nov 16, 2004

Hello,

I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:

Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)

I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?

Any ideas?

Brian

View 2 Replies View Related

Executing DDL In A Trigger

Nov 15, 2000

Dear All,
I would like to execute a DDL statement(create table) in a trigger. The DDL statement is stored as a field in another table. I have read that sql statement into a variable of type varchar. How do I execute that sql statement.
Thanks in advance

View 3 Replies View Related

Job Sticks On &#34;executing&#34;

Jan 18, 2001

Hi, I have a DTS package that runs correctly when executed it manually, but when I schedule a job via the package, the job begins executing, but does not stop. I have to cancel it. The Package runs in less that 1 minute, but the job reports that it's being executed but does not stop until I cancel it.

The job was created by using the DTS package schedule command, so how can I check what's wrong?

Thanks in advance.

View 1 Replies View Related

Executing SP In Loop

Sep 21, 2000

What is way for executing SP in loop? I have to delete some records in table X depends on records from table Y. Simple way is:

DELETE FROM X
WHERE X.ID IN
(
SELECT ID FROM Y WHERE ...
)

But if deleting from X is not so simply (after deleting from X I have to delte it from 5 others tables) ? I've created SP 'SP_DELETE_X' which has input parameter 'ID'. My question is how execute this SP in above loop???

Fly

View 6 Replies View Related

Job Stuck While Executing

Nov 29, 2001

Hi All

I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.

Any idea why it is happening ?

If any body ahs any solution you can call me at 1 860 520 7454.

Sujit

View 1 Replies View Related

Error 266 After Executing SP

Apr 24, 2003

I am facing this error when using a SP called by a DTS package (using ADO connection).
The SP gets three parameter and looks like the following (just major part of it):

SET @STR = 'SET ROWCOUNT 10000' + ' WHILE 1 = 1 ' + 'BEGIN' + ' BEGIN TRAN'
SET @STR = @STR + ' DELETE FROM ' + @TableToBeCleaned + ' WHERE ' + @SELECTEDCOLUMN + ' <

DATEADD (m,' + ' -' + @KeepMonthsInDatabase + ', + GETDATE())'
SET @STR = @STR + 'IF @@ROWCOUNT = 0 ' + 'BREAK ' + 'COMMIT ' + 'END ' + 'SET ROWCOUNT 0'
EXEC (@str)

Following error appears:

Server: Msg 266, Level 16, State 2, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

Previous count = 4, current count = 5.
Server: Msg 266, Level 16, State 2, Procedure p_DBCleanerDeleteRows, Line 56
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

Previous count = 4, current count = 5.

I use the SET ROWCOUNT to execute the delete in batches. When checking with SELECT @@TRANCOUNT it

returns me 5. Somehow it is clear to me that @@TRANCOUNT exited the SP with another value than when

entering it but there is commit in the dynamic SQL statement and I do not know what is wrong.

Is it neccessary to indicate BEGIN TRAN and COMMIT in the statement at all or can I ommit it?

Thanks

mipo

View 1 Replies View Related

Executing Cursors With ASP

Jan 19, 2006

Hi,
I have written a Stored Procedure which opens a Cursor , carries out some functionality and the I De-Allocate and Close it.When I execute the Stored Procedure from Query Analyzer it works fine.However, When I execute it through ASP it does not.

Conn.Execute "Execute Proc_Name"

The Connection Object (Conn) works fine for other pages as it is in an include file.

If I remove the Cursor from the Stored_Proc the other Statements get executed , however the Execution stops when Cursor Code is there.(Though works fine with Query Anaylzer)

No error is thrown, I have checked with Query Profiler and it shows that though Stored Procedure is called but the statements in it are not executed.

There are no Parameters to it, as all it is doing is comparing values of two tables and inserting it into 1st table and deleting from the second.


Pl. Help, As Stored_Proc works fine when executed through
Query analyzer but not thru ASP.

Thanx
Dkashyap

View 4 Replies View Related

Executing Xp_cmdshell

Oct 14, 2004

Hi ,

I want to execute xp_cmdshell from normal sql server account not sysadmin member in SQL Server 2000. I had give execute privileges on xp_cmdshell to normal account... but its not working...

Is it possible to execute xp_cmdshell from non sysadmin member account in SQL Server2000...

Thanks in advance...

-Mohit.

View 1 Replies View Related

Executing Set Of SPs As JoB Is Hanging..

May 27, 2004

I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?

Thanks.

View 5 Replies View Related

Executing DTS Package

Dec 27, 2006

Hi Friends

I am trying to execute the DTS package from the T-Sql
Its basically Exporting the table from Sql Server to tab delimited text file




DECLARE @Command varchar(1000)
declare @partcode varchar(100)

set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" '


EXEC master..xp_cmdshell @Command


It Comes up with the Following Error



output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)

Error string: Error opening datafile: The system cannot find the path specified.



Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0



Error Detail Records:



Error: 3 (3); Provider Error: 3 (3)

Error string: Error opening datafile: The system cannot find the path specified.



Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0



DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun: Package execution complete.

NULL

(22 row(s) affected)



Vic

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved