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

Calling All Dynamic SQL Gods!

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

Any SQL Gods Out There????

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

Calling DTS From C#

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

Calling A SP

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

Calling A SP In .net

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

Calling SP From SP

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

Example Of A Sp_ Calling Another Sp_.

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

Calling SP

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

Calling One SP From Another SP

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

Calling A Dts Package From VB.NET

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

Calling SQL Function In C#

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

Calling A SP In A Class

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

Calling The SP From ASP.NET Application

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

Calling Asynchronously

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

Help Calling DTS Package.

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

Calling A DTS Package From An Sp

Aug 31, 2001

How to do this?thanks :-)

View 1 Replies View Related

Calling A Dts Package From VB

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

Calling SP With Parameters

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

Calling DTS Package

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

Rollback Of Sp Calling Another Sp

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

Calling A DTS Package From VB

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

Calling All GURUS ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

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

Calling Dll`s/exe`s From SQL Server

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

Calling SP Inside The SP

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

Calling Sp From DTS On Different Server

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

Calling Functions

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

Calling VBA Functions From SQL

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

Calling Vbscript From SP

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

Calling A Procedure

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

Calling A SP Within A SP And Back

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

Calling Exe Using Xp_cmdshell.

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

Calling A Function.

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







Copyrights 2005-15 www.BigResource.com, All rights reserved