Executing SQL Scripts From Files
Mar 6, 2007Hi, I want to create many files with SQL statements and then I want to write one SQL script file, which executes SQL statement in the other files. How can I do it?
Thanks, Radco
ADVERTISEMENT
Executing Script Files From Transact-SQL
Jul 20, 2005Hi,I have my create statments for tables, procedures, views, etc inindividual Transact-SQL script files (.sql).I wnat to write another script file that executes these scripts in thecorrect order to create the database.What is the syntax for executing script files from Transact-SQL?Thanks, Phil
View 1 Replies View RelatedStored 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
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Jan 23, 2008Hi,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 RelatedDifferent Results When Executing From .NET Component Compare To Executing From SQL Management Studio
Oct 10, 2006Hi 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 RelatedSQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups
Jan 9, 2015I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedFor Loop - Iterate From Older Files To Newer Files Based On File's Timestamp
Mar 13, 2008In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!
Apr 24, 2008In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?
Feb 24, 2015I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
HELP!!! Cannot Import SSIS Package Files From .dtsx Files
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
Is It A Good To Replace SQL Script Files With XML Files?
Jul 23, 2005I am thinking about replacing the INSERT data scriptfiles that I have with XML files. This way I can open the XMLfile using an XML Editor and see the values in a GRID andmake changes easier.Do you see any problem with this approach?I managed to put together some code that is exportinga SQL table with its data to an XML file and also a codethat reads the XML file's data and inserts it into a table.Now I am researching on XSD, td:datatype, DTD...(I am new to XML) in order to figure out how I canuse a single xml file that will hold both the sql serverfields, the datatypes and their values.If you have links to some sample code that has anythingto do with the datatype export and import I am workingon, can you please share them with me?Most importantly what do you think about the idea of usingXML files vs sql scripts?Thank you
View 4 Replies View RelatedIntegration Services :: Converting RTF Files To PDF Files?
Jun 25, 2015I have a scenario where I need to convert RDF files to PDF files? may I know is this achievable in SSIS - writing C# code?
View 6 Replies View RelatedTransaction Log Files/Virtual Log Files
Oct 1, 2004I am wanting to reduce the amount of Virtual Log Files I have. In reading through the Online Book Documentation, I realize that I have forgotten to move the Transaction Log Files to a different drive. Now that the server is in production, I wanted to get some input about the best way of making this change.
Can I just change the directory the log files are being written to in the DB properties without having any adverse problems occurring?
Empty Log Files And Data Files
Sep 17, 2004Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
Process Many Image Files And Wav Files
Dec 11, 2005I need to write codes to access many image files and wave files and display them on the webpage. Both of these files are stored on the server (as many experts in this forum adviced) and their locations are in the SQL server database. About the wav files, I think I will have to convert them to MP3 first for fast delivery on internet. Since I am a newbie, any help would be appreciated.
Thanks,
Bcp Unicode Files Using Format Files
Jul 20, 2005I have a problem with bcp and format files.We changed our databases from varchar to nvarchar to support unicode. Noproblems so fare with that. It is working fine.But now I need a format file for the customer table and and it is notworking. It is working fine with the old DB with varchar, but withnvarchar I'm not able to copy the data. The biggest problem is, that Igot no error message. BCP starts copying to table and finished withouterror message.This is my table:CREATE TABLE [dbo].[Customer] ([ID] [int] NOT NULL ,[CreationTime] [datetime] NULL ,[ModificationTime] [datetime] NULL ,[DiscoveryTime] [datetime] NULL ,[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,[Class] [int] NULL ,[Subclass] [int] NULL ,[Capabilities] [int] NULL ,[SnapshotID] [int] NOT NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOTNULL ,[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AINOT NULL ,[LocationID] [int] NULL ,[MirrorID] [binary] (16) NULL ,[DeleteFlag] [bit] NULL ,[AdminStatus] [bit] NULL) ON [PRIMARY]GOand this is the format file:8.0131 SQLINT 1 12 "#~@~#" 1 ID ""2 SQLDATETIME 1 24 "#~@~#" 2 CreationTime ""3 SQLDATETIME 1 24 "#~@~#" 3 ModificationTime ""4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""5 SQLNCHAR 2 510 "#~@~#" 5 Name_SQL_Latin1_General_CP1_CI_AS6 SQLINT 1 12 "#~@~#" 6 Class ""7 SQLINT 1 12 "#~@~#" 7 Subclass ""8 SQLINT 1 12 "#~@~#" 8 Capabilities ""9 SQLINT 1 12 "#~@~#" 9 SnapshotID ""10 SQLNCHAR 2 510 "#~@~#" 10 CompanyNameSQL_Latin1_General_CP1_CI_AS11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountrySQL_Latin1_General_CP1_CI_AS12 SQLINT 1 12 "#~@~#" 12 LocationID ""13 SQLBINARY 1 33 "#~@~#
"13 MirrorID """#~@~#" is the field terminator. We have a lot of text files with allkind of charachers in it. So we think this is a set that will neveroccur in our files.Thanks for your help!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
MDS Descriptor Files Associated With Databases Files
Jun 14, 2007Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
Task Converting Xls Files To Csv Files ?
Jan 21, 2006Is there a way to do this with some sort of task ?
View 7 Replies View RelatedHow To Rename Files Before Send Files?
Nov 8, 2006I'm using script task to rename files inside foreach loop files,code like shown below
Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))
dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "" & newname
Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"
I tried to use file system task to perform that,but failed either
Rename file operation in file system only can rename a file in a specified location,who can help me?
TIA
Linked Server To Text Files: Is Possible To Detect Changes Made To Those Files? (SQL Server 2005)
Sep 3, 2007Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
Pb Executing An SP
Sep 25, 2006I 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
Executing A Job Using VB
Sep 17, 2007I 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.
Executing A DTS From Another DTS...
Jul 20, 2005Hi 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 RelatedExecuting More Than One Query
Jun 16, 2007Hi,
How to execute more than one query consecutively in SqlDataSource like the picture shown below.
Executing DTS (on Sql Server) From ASP.NET??
Feb 5, 2004Does Any one know how to execute DTS (on sql server) from ASP.NET??
View 2 Replies View RelatedExecuting DTS Package From VB.NET
Nov 16, 2004Hello,
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
Executing DDL In A Trigger
Nov 15, 2000Dear 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
Job Sticks On "executing"
Jan 18, 2001Hi, 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.
Executing SP In Loop
Sep 21, 2000What 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
Job Stuck While Executing
Nov 29, 2001Hi 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
Error 266 After Executing SP
Apr 24, 2003I 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
Executing Cursors With ASP
Jan 19, 2006Hi,
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
Executing Xp_cmdshell
Oct 14, 2004Hi ,
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.