Calling All T-SQL Gods && Goddesses
Jul 20, 2005
Hi
I'm having trouble with the script below that it just won't do a
correct ORDER BY for a date field.
When executing the two SELECT TOP statements on their own the records
are sorted correctly but when wrapped in the main statement, the ORDER
BY is just ignored. Tried to play around with the script but with no
luck. Script is for paging, in this case, select all records meeting
criteria, order and select Top 25 to display on first page. Any
questions, please don't hesitate contacting me.
Thanks very much to anyone who's having a look at this!
Martin
SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
(i2b_address.Address1) AS PropertyAddress,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
ReturnByDate
FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESC
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
HelloI'm desperate for help with the following dynamic SQL. It used to work forages but suddenly stopped working today! I can't recall changing anything ofimportance.. So I say. Anyway, I'm getting this error: "Cannot use emptyobject or column names. Use a single space if necessary."I've identified the location within the script that causes this message it'sthis line:(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,3rd line of Set @cmdSQL =.I've been trying to insert a single space between "" which eliminates halfof the error but I can't figure out what quotes to use around 'MG' and 'MA'.I'd be grateful if you can have a look at this and let me know how tocorrect this problem.Like I said it used to work and I'm perplexed about this sudden error. Isthere any change that can cause this behaviour?Many thanks for your efforts!!Have a nice day!!MartinPaging Script:CREATE PROCEDURE dbo.sp_ListKeyOut(@page_number INT,@number_of_records INT,@cmdWHERE VARCHAR(200),@cmdORDERBY VARCHAR(200)) ASSET NOCOUNT ONDECLARE@SizeString VARCHAR(5),@PrevString VARCHAR(5),@cmdSQL varchar(2000)SET @SizeString = CONVERT(VARCHAR, @number_of_records)SET @PrevString = CONVERT(VARCHAR, @number_of_records * (@page_number - 1))SET QUOTED_IDENTIFIER OFFSET @cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,i2b_vw_keytransactionlog.KeyTransactionLogID,i2b_vw_keytransactionlog.KeyID,(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,A.ProgUserName AS ProgUserName,CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) ASTransactionDate,CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) ASReturnByDateFROM i2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID IN'IF @cmdWHERE IS NULL OR @cmdWHERE = ''BEGINEXEC(@cmdSQL +'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID NOT IN(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)ORDER BY ' + @cmdORDERBY + ')ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY)-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROMi2b_vw_keytransactionlog')ENDELSEBEGINEXEC(@cmdSQL +'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE ' + @cmdWHERE + ' AND KeyTransactionLogID NOT IN(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROMi2b_vw_keytransactionlogLEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)JOIN i2b_vw_address ON (i2b_vw_property.AddressID =i2b_vw_address.AddressID)JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =A.ProgUserID)WHERE ' + @cmdWHERE + ' ORDER BY ' + @cmdORDERBY + ')ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY)-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROMi2b_vw_keytransactionlog WHERE ' + @cmdWHERE)ENDSET QUOTED_IDENTIFIER ONRETURN 0GO
View 4 Replies
View Related
Oct 4, 2004
I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.
History:
3 Tables: Tooldb - Employee - ToolUserdb
Scenario:
I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.
My SP:
ALTER PROCEDURE dbo.InsertTool
(
@ToolNamenvarchar(250),
@Platformnvarchar(250),
@Vendornvarchar(250),
@Subplatformnvarchar(250),
@Submitternvarchar(250),
@Finders nvarchar(250),
@LTDnvarchar(50),
@JobAreanvarchar(250),
@Funcnvarchar(250),
@Ownersnvarchar(250),
@Activenvarchar(250),
@Version numeric,
@Buildnumeric,
@CWSTDnvarchar(50),
@Statusnvarchar(250),
@Costnumeric,
@Notesnvarchar(250),
@Keywordsnvarchar(250),
@Linksnvarchar(250),
@Pathsnvarchar(250),
@eidint,
@tdbidint,
@Creator nvarchar(250)
)
AS
INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@ToolName, @Platform, @Vendor, @Subplatform, @Submitter, @Finders, @LTD, @JobArea, @Func, @Owners, @Active, @Version, @Build, @CWSTD, @Status, @Cost, @Notes, @Keywords, @Links, @Paths)
INSERT INTO ToolUsersdb (@Creator) SELECT + @eid + ',[ID] FROM Employee + @tdbid + ',[ID] FROM ToolDB IN (@Creator)
Error:
Incorrect syntax near keywork IN (referring to last insert statement).
Can someone tell me how I can get this multi-insert stmt. to work?
Thank you!
Tim
View 8 Replies
View Related
Oct 25, 2006
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
View 1 Replies
View Related
Jan 6, 2008
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
View 3 Replies
View Related
Jan 9, 2008
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
View 1 Replies
View Related
Jan 20, 2004
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?
Thanks
View 1 Replies
View Related
Sep 18, 1998
Does anyone have an example of a Stored Procedure calling
another stored procedure and passing it parms.
What I would like to do is: Have a stored procedure select
data from a table and pass that data to another stored
procedure.
Thanks in advance,
Rodney
View 3 Replies
View Related
Sep 14, 2007
Hi,
Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.
Thnkx,
Rahul Jha
View 13 Replies
View Related
Mar 26, 2008
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.
View 7 Replies
View Related
Jan 8, 2007
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.
View 3 Replies
View Related
Feb 22, 2007
Hello,
I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.
Any answers will be appreciated ...............Urgent.
:)
View 4 Replies
View Related
Jan 17, 2008
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 }
View 6 Replies
View Related
Feb 8, 2008
Hi All,
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.
Please let me know what i am doing wrong.
Thanks,
SR.
View 6 Replies
View Related
Mar 7, 2008
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)
View 4 Replies
View Related
Aug 18, 2004
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.
Any ideas????
Thanks
View 1 Replies
View Related
Aug 31, 2001
How to do this?thanks :-)
View 1 Replies
View Related
Sep 15, 2000
Does anyone know the command that can be used to call a dts package directly from VB?
regards,
reshma
View 1 Replies
View Related
Jun 3, 2000
Hello,
What is the syntax of calling stored procedure from vb
Is there any1 can please give me a example of how to call sp with passing parameters, thanks a lot.
Thanks
View 1 Replies
View Related
May 27, 2004
I have a DTS package created in a databasse and need to call that DTS package from a stored procedure.Is there a way that I can do it?
View 2 Replies
View Related
Mar 10, 2006
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
View 1 Replies
View Related
Mar 22, 2002
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
View 1 Replies
View Related
May 19, 2002
SQL Server Load Balancing -- Any ideas?
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??
View 1 Replies
View Related
Oct 5, 1998
I need a simple example of calling a dll or exe with params
from a stored procedure.
Thanks
Robert Kapciuk
View 1 Replies
View Related
Jul 13, 2004
hai guys
how should we have to cal the store procedure inside the same store procedure.
for Example
Create procedure A
as
Begin
Select * from mytable
execute A
end.
is this the correct one
View 3 Replies
View Related
Sep 16, 2004
Is it possibe to call a Stored Proc on a remote server in a DTS package?
Thanks in advance,
John
View 3 Replies
View Related
Nov 23, 2004
Dear Everybody,
Hi again!
Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.
The following line of code will not work in SQL Server. How should I achieve this?
declare @Res INT
EXEC @Res = Fxn2 (CONVERT( INT, Fxn1 ))
Thanx in advance!
View 14 Replies
View Related
Mar 16, 2006
I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?
View 2 Replies
View Related
Apr 21, 2004
I am trying to call a VB script file with the following command,
xp_cmdshell 'cscript c:file.vbs'
But its giving the following output -
Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:file.vbs".
NULL
------------
If i changed the command to
xp_cmdshell 'cscript c:file.vbs'
it gives the folowing error -
Input Error: Can not find script file "C:WINNTsystem32file.vbs".
Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.
Thanks & regards,
-dkoushik
View 4 Replies
View Related
Apr 8, 2008
create procedure t1 (@var int output)
as
begin
set @var =3
end
declare @var int
execute t1 @var output
print @var
the output is ok
but i need it like
execute t1 @var output i should get value
View 1 Replies
View Related
Aug 24, 2007
I was wondering if there was a way to call a stored procedure within a stored procedure, and after that's done, it goes back where the other stored procedure left off? I was also hoping to return a value also. Thank you.
View 3 Replies
View Related
Nov 29, 2007
I have is a .exe file developed on vb.net. I'm able to run it on QA as exec master..xp_cmdshell 'D:maxCustomMX_SyncAddressBook.exe, then if I run it using a SQL job it shows the error:
Executed as user: CAPERSQLAdmin. The process could not be created for step 1 of job 0x9E75D06B9FC4474EB7503A1CCA0F9AAF (reason: The system cannot find the file specified). The step failed.
Anyone familiary with sql jobs calling .exe file developed on vb.net. Your help would be appreaciated.
http://www.sqlserverstudy.com
View 7 Replies
View Related
Feb 26, 2008
I'm calling a function with its full qualifiers.
That means servername.dbname.schemaname.functionname
I'm getting an error like this
Invalid column name 'servername'.
Is it possible to call a function name with its server name?
Thanks
Somu
View 4 Replies
View Related
