Stored Procedures Hangs Application On First Execution
Apr 19, 2007
Hello
I have a .Net application which invokes an stored procedure (SQL Server 2005 Express installed on the same machine). When the stored procedure is called the first time, the application hangs because the sp never ends execution and the application's process has to be killed. But when the application is executed again, the sp runs as expected. What could be happening?
The stored procedure references remote tables by means of synonyms. If the Management Studio is used instead, the sp never ends execution when invoked the first time, but the query can be cancelled.
Now, if the sp is invoked first in the Management Studio first and then by the application, this does not hang (the sp executes as expected).
Thanks a lot.
View 3 Replies
ADVERTISEMENT
Oct 28, 2005
Hi,
I need advice on how to automate stored procedures within my SQL2003 DB.
I have a stored procedure I want to automate on 120 second cycle - to link to some external software.
I have little experiance of this and need some pointers on where to research or how to achieve my goal.
Many thanks,
J
View 4 Replies
View Related
Feb 22, 1999
We have vendor from we puchased an application. It is going into production next week
and we have our final set of table ddl and it includes stored procedures.
Since I am fairly new to MSSQL 6.5, and forced to include the stord procedures within
the application, I would like to get some feedback as to whether or not stored proceures
are efficient or are going to cause me problems?
Aside from periodically running sp_recompile what else will I need to do?
Any information that can be provided willl be greatly appreciated. THanks.
David Spaisman
View 1 Replies
View Related
Jan 26, 2004
Our current concern deals with stored procedures from a third-party application that were modified in order to correct future data inconsistency that was being generated. Since the stored procedures were not encrypted, I was able to modify them and correct the problem. At the same time, we developed a small in-house application to correct the current data inconsistency and we created new stored procedures in the same database. Now I'm concern about if I had the right to modify those stored procedures and additionally, created new ones inside this database? Am I restricted somehow to use our full version of MS SQL Server with a scenario like this?
View 14 Replies
View Related
Dec 3, 2006
Background: We have SQL Server 2005 x64 running on a quad-core (dual dual-core) machine with 16GB of RAM. The database is about 10GB in size and we execute around a million stored procedures a day on it. Our application uses about 1000 different stored procedures on this machine. The application is a transactional B2B web-app with about 2000 users.
The problem we have is a really odd one that I can't seem to find much information on. We have a small number (3-4) of stored procedures that's exibiting this problem.
The stored proc in question takes on average 100ms CPU time to execute. It's a fairly complex stored proc, about 300 lines long, 6-7 select statements and it uses temp tables. No updates / inserts except for on the temp tables. It's executed about 5000 times per day. About once a week, though, execution times will suddenly jump up to 3000 ms average. This happens randomly during the day, although it seems to happen more often on Monday mornings (the DB is mostly unutilized over the weekend)
To fix this, I force the DB to recalculate the execution plan by adding / removing (depending what I did last time around) the line 'set arithabort on' at the top of the stored procedure. I have no idea why this works, but it does. Within seconds of changing it, the stored proc execution time will go back to it's normal range of 60-150ms.
I've tried setting the execution plan of the stored procedure but I can't get it to work - the execution plan is very long and I don't know how to debug the error I get.
What is happening? This happens with a couple of stored procedures - usually the more complex ones. Has anyone seen anything like this?
View 4 Replies
View Related
Mar 14, 2000
Can anyone tell me how to turn logging on to get statistics on execution times for stored procedures
Thanks
View 2 Replies
View Related
Oct 13, 2005
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
View 4 Replies
View Related
Mar 7, 2007
Hello everyone,
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Any thoughts or ideas?
Thanks for your time, Adrian
View 11 Replies
View Related
Jan 30, 2008
I'm writing a Windows application (Visual Studio 2005, c#) utilizing a local SQLExpress database. It consists of about 10 tables and I've created about 15 Stored Procedures to address various functions... I can run the update Stored Procedures interactively within the designer and the data tables update as designed. However, if I run the update Stored Procedures from within my windows application is where they fail. I get no error messages, if I return a rowcount variable from the Stored Procedure it tells me that one row was updated (SELECT @RtnVal == @@rowcount)... but when I open the subject table in the designer, there is no new data.
My update queries address both insert and update functions, so if it is new row of data, it performs the insert action, otherwise it updates an existing row.
I can query the data using my stored procedures to load default values into my windows form, I can search and find client records to display in the form... I just can't update records. I'm of the opinion that it is a rights issue, but I can't find any resources that address user access accounts with SQLExpress and windows apps. The current connection string for my local database is set for Integrated Security = true; User Instance = true
I've tried two approaches... one utilizes my stored procedure....
bool bSave;
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand("SavAuthTesterData", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CtrId", SqlDbType.NVarChar, 20).Value = TestCenterID.ToString();
cmd.Parameters.Add("@AT_Id", SqlDbType.NVarChar, 15).Value = GnuAuthTesterId.ToString();
cmd.Parameters.Add("@AT_Name", SqlDbType.NVarChar, 50).Value = GnuAuthTesterName.ToString();
cmd.Parameters.Add("@AT_Pwd", SqlDbType.NVarChar, 15).Value = GnuAuthTesterPwd.ToString();
cmd.Parameters.Add("@Maint", SqlDbType.NChar, 1).Value = GnuAuthTesterStatus.ToString();
cmd.Parameters.Add("@ModBy", SqlDbType.NVarChar, 15).Value = sAuthTester.ToString();
try
{
conn.Open();
cmd.ExecuteNonQuery();
bSave = true;
}
Then I tried a direct insert...
SqlConnection conn = new SqlConnection(KadaDesk.Properties.Settings.Default.dbKadaConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlTransaction myTrans;
conn.Open();
cmd.Connection = conn;
myTrans = conn.BeginTransaction();
cmd.Transaction = myTrans;
string disDte = DateTime.Now.ToShortDateString();
try
{
string sCmdText = "INSERT INTO Tester (CenterId, AuthTesterId, AuthTesterName, AuthTesterPwd, "
+ "CreateDte, Maint, ModBy) "
+ "VALUES ('" + TestCenterID.ToString() + "','"
+ GnuAuthTesterId.ToString() + "','"
+ GnuAuthTesterName.ToString() + "','"
+ GnuAuthTesterPwd.ToString() + "','"
+ disDte.ToString() + "','"
+ GnuAuthTesterStatus.ToString() + "','"
+ sAuthTester.ToString() + "')";
cmd.CommandText = sCmdText;
cmd.ExecuteNonQuery();
myTrans.Commit();
bSave = true;
}
Both fail... which points to the only common point in both approaches, the connection string and user rights... but I can't find any place to address user accounts or configurations for windows apps.
Thanks for whatever help you can offer...
Jim-
View 5 Replies
View Related
Apr 17, 2008
We have an application that is built with WPF/.net 3.5 and leverages LINQ to SQL. Everything runs as expected when our application connects to MS SQL Server 2005. We have made a SQL CE version of our database using the Microsoft SYNC framework, and SQLMetal.exe to generate our .dbml.
The issue we are running into is that our application hangs while our LINQ to SQL objects are delay loading an EntitySet at random places throughout our code. After futher looking into the issue, and testing our published application on various workstations, we have discovered that the application does work on one computer that has SQL CE version 3.05.5365.0000. It does not however work on any computers that have SQL CE version 3.05.5386.0000.
We are logging the SQL queries that the LINQ to SQL is generating and when we execute them in the the Visual Studio 2008 Query Analyzer, they run successfully.
Any ideas are appreciated as we have hit a dead end.
Thanks!
View 5 Replies
View Related
Dec 7, 2005
I have a SSIS (CTP June 2005) package with several data flow tasks. One data flow has 2 OLEDB data sources which are then unioned together, followed by a conditional split, then a derived column transformation, which feeds into an OLEDB destination. When I run the package, this particular data flow seems to run fine and then just stops. There are no errors and the records counts don't move. I've used data viewers to look at the data and it seems fine. I've switched the OLEDB destination with a flat file and execution runs fine, then switched back to OLEDB and it hangs again (over and over). There's nothing unusual about the OLEDB destination, and all the other OLEDB destinations work fine. It also seems to hang on the same destination row number count, but again that row has been verified as valid. In fact, I dropped the DB table and recreated it with no constraints and all fields nullable, but the problem persists. Help?
View 6 Replies
View Related
Apr 30, 2001
I have a trigger on a table which when I do a direct update to throught QA seems to work fine. When a user developed application updates the record it locks their screen and record. I can't see where it could be causing it, but I am pasting it below. Any thoughts/comments greatly appreciated
CREATE TRIGGER tr_Patient_Updt2
ON patient_
FOR UPDATE
AS
if update(df_last_cholestero)
begin
declare @tstyears int
declare @tdate datetime
declare @sdate datetime
declare @bdate datetime
declare @ptage int
declare @actid varchar(12)
select @bdate = convert(datetime,df_last_cholestero,101), @actid = account_id
from inserted
select @ptage = dbo.GetAge(convert(datetime,patient.Date_Of_Birth, 101), GETDATE())
from patient where patient.account_id = @actid
select @sdate = convert(datetime,patient.Date_Of_Birth,101)
from patient where patient.account_id = @actid
select @tstyears = datediff(month,@bdate,getdate())
if @ptage between 18 and 65
begin transaction
begin
if @tstyears < 12
begin
set @ptage = @ptage + 5
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
else if @tstyears >= 12 and @tstyears < 24
begin
set @ptage = @ptage + 4
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
else if @tstyears >= 24 and @tstyears < 36
begin
set @ptage = @ptage + 3
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
else if @tstyears >= 36 and @tstyears < 48
begin
set @ptage = @ptage + 2
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
if @tstyears > 48
begin
set @ptage = @ptage + 1
set @sdate = DATEADD(year, @ptage, @sdate)
update patient_
set df_next_cholestero = convert(char(08),@sdate,112)
where patient_.account_id = @actid
end
end
end
commit transaction
View 1 Replies
View Related
Jul 11, 2006
Hi,
I'm having the problem that my application hangs on SqlCeConnection.Dispose() when I'm closing the app.
public class DatabaseManager : IDisposable
{
private DbConnection connection = null;
// Singleton
private DatabaseManager()
{
this.CreateConnection();
}
~DatabaseManager()
{
this.Dispose();
}
public void Dispose()
{
if (this.connection != null)
{
this.connection.Dispose();
}
}
// ...
}
This only happens when I'm calling Application.Exit(); and Dispose is called through the destructor of the DatabaseManager class. When I'm disposing the connection during normal work the call works as intended. BTW I'm using SQL Server Mobile 3.0.5214.0 on a PPC 2003 AKU2 (Symbol PPT8846 industrial device).
Thanks for support,
Klaus
View 1 Replies
View Related
Oct 5, 2007
Hi! I am developing a CF 2.0 application for WM 6.0. In the application I'm doing a replication between a Sql Server 2005 database and a Sql Server 2005 Compact Edition database. When I'm trying to syncronize the databases for the first time, e.g., creating a new database with AddSubscription(AddOption.CreateDatabase)I cannot do a save afterwards the synchronization procedure. The synchronization works just fine and I get the right data to my device and so, but when I try to a save, the database hangs doing the commit().
If I'm on the otherhand restarts the application and then do a ReinitializeSubscription(true), e.g., doesn't not create a new database with AddSubscription(AddOption.CreateDatabase), and calls Synchronize(), everything works just fine. Anyone who has an explanation of this? (I do a Dispose() each time).
View 1 Replies
View Related
Aug 23, 2007
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies
View Related
Apr 29, 2008
Hi,
I'm encounter lately some weird behaviour of SSRS in BIDS as well as after deploying on Reporting Server.
I'm running basic classical SQL SSRS Reports with Oracle DB connection. My reports have header and footer pagination and well defined report layer configuration. All the reports are simple based on one table and some basic parameters passed to query.
In query builder from BIDS from data tab, the query is running fine without any other execution delays. Also the query is runnign fine in PL/Dev environment.
When I switch in "Preview" mode the report hangs in execution and just showing "Report is being generated" without any resuts. After a while is generating an execution error (after 20 min).
The behaviour is the same after deployment on Report Server .
I now...here it comes...the problem is intermitent...some times the report is working...but most of the times it hangs on execution.
I checked the report from top to bottom and nothing's wrong from design perspective.
On other reports I don't have this problem and I don't understand where to start to investigate.
Any suggestions...I'm stuck here.
Thanks
View 1 Replies
View Related
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
View 4 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Aug 11, 2006
Hello All,
I have a SSIS package which run well when stand-alone. However
it was failed when executed from .NET web application. It just simply return Failure. Code snip below:
Dim packagekgResults As DTSExecResult
.........
packagekgResults = package.Execute()
Environment: Windows 2003 Shared Server, IIS 6.0
Any idea?
Thanks in advance!
Tina
View 7 Replies
View Related
Oct 28, 2015
issues with triggers in Sql Server 2014.
A few weeks ago I've done a SQL Server migration from SQL Server 2000 to SQL Server 2014.It was a bit tricky but anything worked fine.
I have some legacy VB6 (Visual Basic 6) applications written in house which worked with Databases on the old SQL server 2000.Surprisingly, these applications worked well after the upgrade to SQL Server 2014 without having to change a piece of code.
Now, some users tell me that they receive some unusual message when saving data from these legacy applications.After investing for a few hours, I discovered that triggers are not executed when those users try to save data from grids or forms in their applications.Trying to reproduce the INSERT statement in SQL Server Management Studio, the triggers run well.From the application, they don't.
These applications connect to Database Server thru OLEDB connection with the following ADO connection string :
Provider=SQLOLEDB.1;Password={password};User ID={user};Initial Catalog={db};Data Source={datasource}.the {user} is a true SQL account who have read/write/delete access in the databases.
On the web there is a lot of questions involving the same issue, but only from SSIS.I found some articles about an OLEDB connection parameter named FastLoadOptions with a value of FIRE_TRIGGERS, but nowhere how to put it in the ADO OLEDB connection string.
how to reactivate the "normal" use of triggers from an ADO OLEDB connection ?Either with some obscur parameter in the connection string or options somewhere in the SQL Server 2014.
View 4 Replies
View Related
Aug 14, 2007
I have database in the database there are a few users that no one is used. When I try to drop thpse users I got next error message:
"The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped." (Msg 15136)
Indeed, I think that those users have execute rights on store procedures.
How do I find for wich procedures or other database objects those users have grants?
How do I delete them from database (and maybe from logins of the server)?
How can I see what grants a user has?
How can I see what grants does STP has?
View 10 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Aug 9, 2007
What are the initial steps to investigate why a stored procedure hangs?
Thanks
View 4 Replies
View Related
Oct 2, 2007
Hi
I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000.
But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs.
Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers?
I'm using .NET Framework 1.1ODBC (SQL Native Client)
Here is the Store procedure code:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[CreateProjectDataBase]
@AFModelDatabaseProject varchar(200),
@TemplateDbBackupFileAndName varchar(200),
@DatabaseName varchar(200),
@DataFilePathAndName varchar(200),
@LogFilePathAndName varchar(200)
AS
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName
WITH INIT
RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName
WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName,
MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure;
this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName;
this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName;
this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }";
this.odbcCreateDataBaseCommand.ExecuteNonQuery();
RegardsTomas
View 2 Replies
View Related
Sep 30, 2013
I have a problem where bcp command is called and executed successfully sometimes but fails randomly.My stored procedure looks like this:
select @SQL = 'select * from [' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'
execute(@SQL)
set @Count = @@ROWCOUNT
if @Count > 0
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
[code]....
The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.
View 2 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Nov 8, 2006
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
Thanks,
Jim
View 4 Replies
View Related
Jul 20, 2005
I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!
View 1 Replies
View Related
Nov 29, 2014
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
View 1 Replies
View Related
Jun 23, 2006
I am attempting to write a Windows service that watches a database for uploaded files to import. When a new file is found, the corresponding SSIS package is run from the file system with variables passed through. I started development as a Windows app and copied the functionality to a service.
The app runs fine. The service does not. I get a "Failure" each time a package is executed. Everything is identical behind the scenes with the obvious exceptions that OnStart and OnStop handlers are buttons in the app. I added a script task at the beginning of one of the SSIS packages to notify me that it is even running at all. It doesn't even hit that initial task.
Again, the app will run all packages just fine. The data is imported and the results return as "Success."
The following is the code executing the package. Any help is appreciated. I've been banging my head on this one for a few days now. (Is there a tag to format a code sample?)
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = sPackageFolder & PackageName & ".dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
Dim vars As Variables = pkg.Variables
vars("ImportId").Value = ImportId
vars("ProductionServer").Value = ProductionServer
vars("ProductionDatabase").Value = ProductionDatabase
vars("SourceFileName").Value = FileName
vars("SourceFilePath").Value = FilePath
pkgResults = pkg.Execute()
View 3 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
