I have many, many T-SQL scripts that create stored procedures and gives proper permissions. (uploading to production)I've been looking around for a way to run them all at once with a single script to pull them into the sql analyzer.Not copying and pasting them all, there are too many, but reading and executing.Anyone know a link with a sample to do this?Thanks,Zath
View 3 RepliesWe have a DTS package that is submitted every minute by a SQL Agent job (2000). We see instances where before the first job is completed, the second one starts running.
Has anyone encountered this type of behavior before?
Hi,
I was wondering if this can be done...
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g.
I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)
Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
Hope someone can help,
Thanks,
Stuart
I've got the stored procedure which first creates and then starts a job.
This stored procedure can be invoked from a number of triggers (after insert, after update)
The problem is:
when a sequence of statements is being executed one by one (like an insert immediately followed by several updates) and the stored procedure is invoked from the propper insert or update trigger once for each statement, the jobs are created in the right order (first for insert statement, then for updates in the order of the initial statements), but are executed in the wrong order.
Is there any explanation to this? And any solution?
The order of such jobs execution is vital for my application.
Thanx a lot in advance.
Please see the enclosed screenshot for an example list of jobs. Name of the job contains time of it's creation and another column shows the time the job was executed.
Hi,
I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.
Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.
I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.
I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?
I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.
Thanks very much,
mike123
hi !
I am using sql server 2005 sp1 standard edition.
I have a transactional replication set up from one server to another.
From last couple of days i am noticing two distribution agents running for the same subscription in the replication monitor. Before this only one distribution agent was running. Is there any problem having two distribution agents running in parallel for the same subscription on a server?
Any help would be appreciated.
Thanks
I have several SQL files that make several different table updates and data changes.
How would I create something (maybe an SSIS package?) or even write a T-SQL script that runs through these files and executes the queries contained in them?
(I put this thread in SQL General also because I wasnt sure if this could be implemented in T-SQL or not.)
Right now what I have is a ForEachLoop Container with an ExecuteSQL task component inside of it. I'm thinking that the contents of each of the sql files would be copied over to a "Temp.sql" file and then executed in the loop. So I've created a Temp.sql file for this purpose and the properties of the ExecuteSQLTask component are setup as so:
(I've created my connection managers appropriately with an OLEDB source and a File Connection pointing to Temp.SQL)
ConnectionType OLEDB
Connection servertest
SQLSourceType File connection
FileConnection Temp.sql
And for my ForEachLoop container, my properties have been set as:
Collection
--> Enumerator Foreach File Enumerator
--- Enumerator Configurations ---
Folder: C:lahblahblah
Files: *.sql
with retrieve file name set as Fully Qualified
Have I set this up properly? (obviously not, because it's not working )
Hi Techs,
I have created several DTS Packages those are working fine, tested and verified.
I need to run them by a single click, either by batchfile or some sort of command.
Thanks
Iqbal Ansari
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…
I have several sequence containers in one package that fire off execute package tasks. I would like each of the sequence containers to start at the same time when the job starts running. However when I set them up to do that, i get an error that the variable cannot be read because it is locked. I have the variables setup as readonly so not sure why they are being locked. When I run the package and have each sequence container fire off after the previous one ends it runs fine.
Any ideas on how to get around this?
Thanks!
I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
SELECT
--DISTINCT
p.SPID,
p.LogiName,
p.HostName,
[Code] ....
In my application code I am trying to invoke multiple threads in which each thread is loading an instance of the same SSIS package and would initialize the package variables with different values and execute the different instances in parallel. In each thread - after the package execution has completed successfully - I read that instance's SSIS package variables to get result information from that Instance run.
When I load the same package in different thread using LoadFromSqlServer() method
- does the code create multiple instances of the SSIS package and load the distinct instances in each of the thread
- Will the Package Execution ID be different for the different instances?
- Are the package level variables instance safe?
During customer engagement, I was told that there are Microsoft add-on to be installed on SQL 2005 and the SQL 2005 can be simulated as SQL 2000, SQL 2000 SP4, SQL 7.0 in different SQL instances.
Is this true? Are there URLs about this topic?
Thanks a lot.
Po-Shan.
I have a form that contains data that some of which needs to be inserted into one table and the rest updated to another table. I want this to happen on a single button click. Is this possible?Here's what I have so far: String id = parm_tb_tktid.Text; String update = parm_tb_desc.Text; String status = parm_cb_status.Text; SqlCommand cmd = new SqlCommand("Insert into History (tkt_id, hist_desc) values(@parm_tb_tktid, @parm_tb_desc)", HelpdeskConnectionString); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@parm_tb_tktid", id); cmd.Parameters.AddWithValue("@parm_tb_desc", update); SqlCommand cmd1 = new SqlCommand("Update Ticket (tkt_status) values(@parm_cb_status) where tkt_id = @parm_tb_tktid)", HelpdeskConnectionString); cmd1.CommandType = CommandType.Text; cmd1.Parameters.AddWithValue("@parm_cb_status", status); HelpdeskConnectionString.Open(); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); HelpdeskConnectionString.Close(); parm_tb_tktid.Text = String.Empty; parm_tb_desc.Text = String.Empty; parm_cb_status.Text = String.Empty;I know it isn't right, but I can't find any info on why it's wrong and how to code it properly.
View 9 Replies View RelatedHi, I have looked and loked for the answer to this question and made no progress. I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas). Is it alright to create another database and have 2 running simultaneous on the same data engine? Is there any type of limitations that I sould know about - such as number of allowed accesses at one time? Any help is greatly appreicated.
View 1 Replies View RelatedI have the following:
(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.
(b) Three SQL Indexed View queries that take 0 ms when run together.
When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.
What is causing the extra 363 ms of latency.
I have a website running under Windows Server 2003 which I am trying to deploy Reportin Services to using a custom security extension using Forms authentication. There is also another website which is just a Report Server running on this IIS 6.
On my development machine, XP with IIS 5.1, the Website with Reporting Services using forms authentication works fine, shares cookies for authentication and all that jazz. but trying to configure a second instance of Reporting Services on Server 2003 is not behaving.
Is there some trickery or details involved with running two instances of Reporting Services?
Also in the Configure Report Server tool it is failing and so not initializing on the last step of the Database Setup
Setting Connection Info for Reporting Server
ReportServicesConfigUI.WMIProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)
--- End of inner exception stack trace ---
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).
The problem is that somehow these two applications are sharing something either with the connection or commands.
For instance if the two instances are in the following function at the same time then they both thow an SEH exception:
Code Snippet
inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {
struct _Recordset * _result = 0;
HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _RecordsetPtr(_result, false);
}
The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."
And afterwords when they go to release the command an error occurs on
First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.
Code Snippet
inline void _bstr_t::Data_t::_Free() throw()
{
if (m_wstr != NULL) {
::SysFreeString(m_wstr);
}
if (m_str != NULL) {
delete [] m_str;
}
}
This is being called from.
Code Snippet
inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {
HRESULT _hr = putref_ActiveConnection(ppvObject);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
}
The exception that occurs: First-chance exception at ...: 0xC0000005: Access violation reading location 0xcccccccc.
Similarly when one instance releases a command using
Code Snippet
inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {
HRESULT _hr = putref_ActiveConnection(ppvObject);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
}
The second instance of the application fails when running the command at the exact same time.
Code Snippet
inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {
struct _Recordset * _result = 0;
HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _RecordsetPtr(_result, false);
}
The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.
Is it possible to run through a buffer multiple times in an asynchronous script?
Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?
Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".
I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.
I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)
Hello all -
Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.
We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...
thanks for all the fantastic support on this site
Hello;
I'm working on a large solution with SQL Server 2005 that has dozens of tsql scripts. Running these scripts through the Visual Studio UI takes an annoyingly long time and requires excessively many mouse clicks. I've been tasked with creating a script or C# file that will run all our sql scripts for a given project, but I don't really know how to do that. I'm relatively new to Visual Studio and .NET.
Briefly: Do you know a convenient and quick method to run many sql scripts?
Thanks,
Ian
I'm looking at installing 2008R2 and 2014 side by side, then using Mirroring to provide HA for the 2008R2 instance and AoHA for the 2014 instance. I'd be using the same two physical servers for both the Mirroring pair and the AoHA pair.
View 2 Replies View RelatedHello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
View 5 Replies View RelatedMy client has a server running MS 2003 Server for Smal Business Server, and it has SQL Server Express 2005 installed (I believe it is installed 3 times, as part of SharePoint, Veritas Backup Exec and ACT! applications).
I am trying to implement an application that uses SQL Server 2000, but the Server 2003 SBS does not support that version. So I need to launch an additional instance of SQL Server Exp 2005.
To do so, do I need to download and install SQL Server Express 2005 software again? Or can I just run SQLSVR.exe from an existing instance, but with a different instance name (sqlservr.exe -s<InstanceName>?
The reason I need another instance is that these existing instances limit DDL statements.
I am new to this and compare it to launching MS Word multiple times - you don't need to reinstall Word to run it multiple times.
Is launching from the command line a bad idea - and if it is, what is the better way to ensure that it launches under the local system context a service?
Thanks in advance for any help and insights. It is much appreciated.
Can multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
View 3 Replies View RelatedWe are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.
Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS
Our new server (2014) has the standard collation Latin1_General_CI_AS
Both collations have CI and AS
From experience I know different databases can reside next to eachother on the same Instance.
The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....
The application the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).
using different databases using a different collation running on the same instance?
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
View 6 Replies View RelatedHere is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.