Hi,
I have my create statments for tables, procedures, views, etc in
individual Transact-SQL script files (.sql).
I wnat to write another script file that executes these scripts in the
correct order to create the database.
What is the syntax for executing script files from Transact-SQL?
Thanks, Phil
Hi, 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
If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?
The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems.
If I group all stroed procs in one that simply calls all of them in the needed sequence:
exec stproc1
exec stproc2
.....
exec stprocn
Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?
I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.
DECLARE @i BIGINT
SET @i = 20150315
DECLARE @S VARCHAR(MAX)
SET @S = ''
SELECT @S = @S + '
DECLARE @Count BIGINT
SET @Count = '+@i+' + 1
SELECT @Count'
EXEC(@S)
I am trying to execute the above query but it is throwing me an error.
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to bigint.
I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:--
CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVER
AFTER CREATE_DATABASE
AS
--execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
I'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something
else End
exec master.dbo.xp_cmdshell
'dir C:Test'
If possible how data will be organized in MDF and NDF files. Because we have a MDF file and 11 ndf files. when we are loading the data into one table, it is using all the files in equal ratio. Is there any address reference of ndf files in MDF file?
View 5 Replies View RelatedI am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
View 4 Replies View RelatedI am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
I am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCC command but it is taking too much time. is there any other option for deallocating the space.
View 7 Replies View RelatedI have a job which copies .txt files 24 hours 7 days a week to c:TempSource
What I am planning to do is copy the files from one location to another say c:TempTarget
So I have written the Powell shell script and when i put that in the sql agent job i get below error ;
A job step received an error at line 5 in a PowerShell script. The corresponding line is '$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A parameter cannot be found that matches parameter name 'Property'. '. Process Exit Code -1. The step failed.
$sourceFiles = "c:TempSource*.txt"
$targetFolder = "c:TempTarget"
$files = Get-ChildItem $sourceFiles
$newestFile = ($files | sort LastWriteTime -Descending)[0]
$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name
$filesToMove | ForEach { Move-Item $_ $targetFolder }
I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.
View 21 Replies View RelatedI have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
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
I have around 100 XL Files in a folder ,i want to import all the files dynamically and load all the data in a single table in sql server 2008. Without using SSIS i want to query using openrowset.
View 11 Replies View RelatedI am trying to trace few events using profiler Via T SQL like below, the below scripts works fine, but once the file reaches 1GB , it is not rolling over to another file and start trace again.I tried using URL...once it reaches max file size provided between the time interval..
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2015-08-18 16:00:00.000'------Stop Time 4 PM
set @maxfilesize = 1024 --1GB max File size
[code]...
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 RelatedHi 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 RelatedIn SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
View 3 Replies View RelatedI 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 RelatedIn 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?
In 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
I 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?
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!!!!
I 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 RelatedI 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 RelatedI 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?
Hi!
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!!!
I 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,
I 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!
Hi 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
Is there a way to do this with some sort of task ?
View 7 Replies View Related