I already post one thread to list all SPs that are starting with sp_.
I found 150 SPs on average for each database.
The next step in my assignment is to find all SP's that are using these SPs.
Here is an example. Lets say I have a procedure that is 'sp_appname' I need to find out all SPs that are using this SP.
For this I wrote following statement.
SELECT name from sysobjects so INNER JOIN syscomments sc
ON so.id=sc.id
WHERE sc.text LIKE '%sp_appname%' AND so.type='P'
Let me know the the above statement will give accurate result. If anybody can make this statement more efficient ....Let me know.
I need to call a stored procedure in order to list products by either a text box based search or by clicking on a category.I am very new to this and would really appreciate any knowledge anyone could share.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Ok, I want to write a stored procedure / query that says the following: Code: If any of the items in list 'A' also appear in list 'B' --return false If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF (SELECT values FROM tableA) IN(SELECT values FROM tableB) Return False ELSE Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2 Windows Server 2003 1GB Ram....(I know, I know)
TIA
Code Snippet GO CREATE TABLE [dbo].[Tokens]( [TokenID] [int] IDENTITY(1,1) NOT NULL, [Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED ( [TokenID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens] ( [Token] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE FUNCTION [dbo].[SplitTokenList] ( @TokenList varchar(max) ) RETURNS @ParsedList table ( Token varchar(255) ) AS BEGIN DECLARE @Token varchar(50), @Pos int SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ',' SET @Pos = CHARINDEX(',', @TokenList , 1) IF REPLACE(@TokenList , ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1))) IF @Token <> '' BEGIN INSERT INTO @ParsedList (Token) VALUES (@Token) --Use Appropriate conversion END SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos) SET @Pos = CHARINDEX(',', @TokenList, 1) END END RETURN END GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList] @TokenList varchar(max) AS BEGIN SET NOCOUNT ON; create table #Tokens (TokenID int default 0, Token varchar(50)) create clustered index Tind on #T (Token) DECLARE @NewTokens table ( TokenID int default 0, Token varchar(50) )
--Split ID's into a table INSERT INTO #Tokens(Token) SELECT Token FROM SplitTokenList(@TokenList) BEGIN TRY BEGIN TRANSACTION --get ID's for any existing tokens UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0) FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token) OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old SELECT TokenID, Token FROM #Tokens WHERE TokenID <> 0 UNION SELECT TokenID, Token FROM @Tokens COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @er nvarchar(max) SET @er = ERROR_MESSAGE(); RAISERROR(@er, 14,1); ROLLBACK TRAN END CATCH; END GO
Hi, I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task-> dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
Hi all, i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy. I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work. Can anyone tell me what i may be doing wrong. heres the calling code from C# if(f.Name.Substring(13,7).ToLower() == "product") { try { activity.Log("Starting Product DTS Package..."); DTS.Package2Class package = new DTS.Package2Class(); object pVarPersistStgOfHost = null; package.LoadFromSQLServer( "192.168.8.8", "username", "thepassword", Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, (string)ConfigurationSettings.AppSettings["productDTSPackage"], ref pVarPersistStgOfHost); package.GlobalVariables.Item(1).Value = f.FullName.ToString(); package.Execute(); package.UnInitialize(); //force release of COM object System.Runtime.InteropServices.Marshal.ReleaseComObject(package); package = null; } catch(Exception e) { activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message); } and the activex i tried to use for the transformations is: '**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() DTSDestination("Yesmail Id") = DTSSource("product series") DTSDestination("Customer CKM CustId") = DTSSource("product family") DTSDestination("Product SKU") = DTSSource("transaction date") DTSDestination("product model name") = DTSSource("serial number") DTSDestination("serial number") = DTSSource("product model name") DTSDestination("transaction date") = DTSSource("Product SKU") DTSDestination("product family") = DTSSource("Customer CKM CustId") DTSDestination("product series") = DTSSource("Yesmail Id") DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable. Main = DTSTransformStat_OKEnd Functionthe weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor? BTW: i do have my assembly signed in C# for the COM wrapper.thanks in advance, mcm
Hello, I have done extensive work with Classic ASP for 9 years now. Working with Stored Proceedures etc However now am working in c# am adviced by a friend that its best to use the 1. The "Object data source" to call SP 2. Are there any documentations pointers on best practices, how its done ? I want to use the "on click event" in my class file. thanks Ehi
Hello, are there any sample codes that show you how to execute a SP in .net 1. Using a class 2. Calling the class in the onlick_button function ? thanks Ehi
I'm developing a new Stored Proc that will be taking information in to enter customer and order info. Looking at the current Stored Proc its using alot of If statements and I'm thinking of breaking the sp up into different Stored Procs and calling them all from One main stored proc. I know how to do this but I was wondering how it would effect performance. So should I simply keep all the stuff in one proc or module it out into multiple ones to make it easier to follow and read?
I'm working in SQL Server 2005. I have an existing SP that does exactly what I need (it's the aspnet_UsersInRoles_IsUserInRole SP). I want to reuse this SP and use it's return value as a field in the SP I'm writing. How do I go about doing this? I could take the logic out of the called SP and wrap it in a function; but, I would really like to reuse the SP aspnet_UsersInRoles_IsUserInRole. Thanks in advance for any assistance.
hi, i'm trying to call a dts package from vb.net. i got 2 examples which both don't work. first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error. source code:Dim serverName As String = "SERVERNAME"Dim oPackage As New DTS.Package()Dim oStep As DTS.StepDim pVarPersistStgOfHost As Object = Nothing oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _ "DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost) For Each oStep In oPackage.Steps oStep.ExecuteInMainThread = TrueNext oPackage.Execute() Dim err As LongDim source, description, message As StringFor Each oStep In oPackage.StepsIf oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then oStep.GetExecutionErrorInfo(err, source, description) message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source, description)Else message = "Success"End IfNext oPackage.UnInitialize()oPackage = Nothing second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface. source code:Dim oPackage As New Package2()Dim oConnection As Connection2Dim oStep As Step2Dim oTask As TaskDim oCustomTask As BulkInsertTask oConnection = oPackage.Connections.[New]("SQLOLEDB")oStep = oPackage.Steps.[New]()oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error With oConnection .Catalog = "CATALOG" .DataSource = "SERVERNAME" .ID = 1 .UseTrustedConnection = True .UserID = "USERID" .Password = "PASSWORD"End With oPackage.Connections.Add(oConnection)oConnection = Nothing With oStep .Name = "InsertGemal" .ExecuteInMainThread = TrueEnd With With oCustomTask .Name = "InsertGemal" .DataFile = "D:ImportGemal.dat" .ConnectionID = 1 .DestinationTableName = "Gemal" .FieldTerminator = ";" .RowTerminator = " "End With oStep.TaskName = oCustomTask.Name With oPackage .Steps.Add(oStep) .Tasks.Add(oTask) .FailOnError = TrueEnd With oPackage.Execute() oPackage.UnInitialize()oPackage = Nothing any help highly appreciated! t.i.a.,ratjetoes.
Hello, I have the class below. And trying to execute it on a button click event. What am i doing wrong ? Thanks Here is the button click event1 protected void Button1_Click(object sender, EventArgs e) 2 { 3 signup_data_entry signup = new signup_data_entry(); 4 signup.signup_data_entry(); 5 6 }
Here is my class file. please advice 1 public class signup_data_entry 2 { 3 public signup_data_entry() 4 { 5 //SqlConnection con = new SqlConnection("cellulant_ConnectionString"); 6 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"]); 7 8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con); 9 command.CommandType = CommandType.StoredProcedure; 10 11 con.Open(); 12 13 //string IP = new string(); 14 15 16 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID")); 17 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription")); 18 19 command.Parameters[0].Value = 4; 20 command.Parameters[1].Value = "SouthEast"; 21 22 int i = command.ExecuteNonQuery(); 23 24 25 } 26 }
I have problem in calling the SP from ASP.NET application @DefApp nvarchar(255)= '' ,@DefBusFunction nvarchar(255)= '' ,@DefImpact nvarchar(255) = '',AS Begin declare @sql nvarchar(4000)declare @whereClause nvarchar(4000)DECLARE @return_value intdeclare @sqlWhere nvarchar(4000) select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)' if @DefImpact <> '' Set @whereClause = ' where DefImpact ' SET @whereClause = @whereClause + ' = ''' + @DefImpact + '''' set @sqlWhere=@sql + @WhereClauseEXEC @sqlWhereEnd and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like: sqlCmd = new SqlCommand("subbusample", conn); sqlCmd.CommandType = CommandType.StoredProcedure;sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString())); sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString())); sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString())); RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");RptViewer.ShowParameterPrompts = false; RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";
when i execute it, i am not able to fill the report with data.
In my shared hosting environment where I am making large volumes of database calls, I am very anxious to preserve my heavily throttled threads-I presume when I make a database call, that it is going to a separate server and any threads that server may be using are in addition to my " hosting plan ". My question (s) is, is it therefore a no-brainer to always make calls to:Dim oResult As IAsyncResult = iDBCmd.BeginExecuteReader()arWaitHandles(0) = oResult.AsyncWaitHandleiIndex = WaitHandle.WaitAny(arWaitHandles, 60000, False) i.e. run all calls to the database asynchronously? Is it really releasing a thread back to my asp.net App while it is waiting? (I.e. Is it worth doing)
I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting" Error Number: -2147467259 {Integer}
Error Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}
This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.
If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp
Hi all, I want to call dts package from visual basic. The dts package will do a backup of the database. I am not sure how this can be achieved. Any help will be great Thanks
There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server. Clustering is only useful for a failover situation and does not allow active/active balancing.
Is is possible to use merge replication between two identical OLTP servers and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?
Will this work? If not, why not? What will work? Will federated servers work for an entire database??
