I've tried to find this documented on the Internet and found Crystal Reports User's Guid online documentation (582 pages of it!) It looks like it will be helpful for my other questions, but what I'm trying to do right now is call a stored procedure from my crystal report. So in Field Explorer under Database Fields my stored procedure is there with the three columns it selects. How do I put these three columns on my report? I try dragging them to Section 3 details and they're there but when I preview the report all I get are the headers, not the records. Is there something else I need to do? It requires an input parameter which I am not getting prompted for - maybe that is my problem, without an input parameter it can't successfully run the stored procedure, so how do I make it prompt me?
Thanks for helping me with my first Crystal Report!
I'm working on a stored procedure to populate a Crystal report. My company insists that we put the report parameters in the stored procedure instead of in Crystal...so that the SQL server (rather than the desktop)does the work of restricting the data. Is there anything I can do on the SQL side(possibly User Defined Data Type) to get Crystal to prompt me for a date WITHOUT the time? I started with this:
When Crstal prompts me for the parameters, I can type the date or use the calendar to pick a date, but I AM FORCED to enter a time. I know I could choose to ignore the time in the stored procedure, but the users don't want to see the time section of the parameter. Apparently SQL doesn't have a plain "date" parameter without a time.
However if I do it this way, I can't seem to find a way to make sure a valid date is entered when Crystal prompts the user for the dates.
If using char(10) turns out to be the best method, is there a way I can pre-populate the Crystal prompts like this: @BeginDate = 1st day of the current month @EndDate = the current system date
Crystal seems to allow hard coded default values, but I can't find a way to do calculated default values.
i am developing a Performance Management System for my organisation and reports are quite complex. so i thought to create a CLR stored procedure and then use this in Crystal Report.
The problem I am facing is that the CLR stored procedures I create using Visual Studio 2005 are not visible from Crystal Reports XI come with Visual Studio 2005. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.
I Simply create a CLR store procedure in Visual Basic and debug it. This CLR procedure then appeares under stored procedure node in SQL Server 2005.
I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?
Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?
I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen. Please help--Iam stumped.
There are no infinite loops in the report. I checked.
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
Details : Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message. "The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM. If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab. Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I am new in Crystal reports so please help me on this one.
I have to create a report that will bring back the TOP 20 customers per Sales Rep. My SQL stored procedure needs a parameter @SalesRep to execute. My stored procedure links between 2 tables. How do I get this stored procedure executed in Crysal Reports to give me results. The SP works fine.
After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
Situation: If possible, create one stored procedure for Crystal Reports(CR). For any CR users ou there, looking for coding suggestion also. Thank you for your assistance.
Currently, CR has a header(Report Header) coming from 1 to 1 tables, there is a parameter which is passed in, allowing it to retrieve one record for the header(report header or RH).
The CR then has 4 subreports in which each has its own stored procedure. This I believe happens because the Report Header records relationship to the subreport is 1 to many. The 3 remaining subreports relationship to the Report Header is also a 1 to many. The main problem is the subreports is that there may or may not have any records based on this, the subreport is suppressed within CR and thus there can be alot of unused white space on the 1st page and one of the subreports prints on a 2nd page when it could have been on the 1st page.
Example:
Main Stored Procedure(sp) RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.
Results of subreports and their associated procedure are varchar(8000) decriptions. Each line should be counted in some manner in the stored procedure(sp) and then should be counted in CR to avoid excess white space. To complicate matters subreport 2 to has font, bold, showbox but can have different font sizes. These variations could cause different line space requirements. Any ideas?
Each line should be counted in some manner in the stored procedure and then should be counted in CR. There is a count of records for each the main stored procedure.
RH has 1 record to many records in subreport 1 with same @xyz parameter. RH has 1 record to many records in subreport 2 with same @xyz parameter. RH has 1 record to many records in subreport 3 with same @xyz parameter. RH has 1 record to many reords in subreport 4 with same @xyz parameter.
Because of the relationships, its seems impossible to create one stored procedure which give in one select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought concatinating i.e. 3 records together and then parsing it out some how in CR, along with utilizing the i.e. 3 record count to help count lines. Thought of some how creating a temporary table matrix for 8pt - 28pt for line and spacing considerations.
We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :
sp executes spA,spB and spC Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.
In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.
We use SET NOCOUNT ON in sp (not in the others). If we remove SET NOCOUNT ON we get now rows in Crystal.
I enclose an example (with 3 sub- SPs). Help. Martin
--output from qryEmployeeNICHol @SumOfdEmployersNICHoliday FLOAT
SET NOCOUNT ON
DECLARE employee_period CURSOR FOR SELECT tblEmployees.lUniqueID, tblEmployees.lEmployeeNumber, tblEmployees.szEmployeeSurname, tblEmployees.szInitials, tblEmployees.szDeptNo, tblEmployees.dtDateLeft, tblEmployees.dTotalGrossPayThis, tblEmployees.dTaxableGrossPayThis, tblEmployees.dTotalGrossPayPrevious, tblEmployees.dTaxableGrossPayPrevious, tblEmployees.dTaxPaidThis AS EdTaxPaidThis, tblEmployees.dTaxPaidPrevious, tblEmployees.dSMPPaidToDate, tblEmployees.dSSPPaidToDate FROM tblEmployees WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)
DECLARE employee_ni_categories SCROLL CURSOR FOR SELECT DISTINCT tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND (tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
OPEN employee_period
FETCH NEXT FROM employee_period INTO @lUniqueID, @lEmployeeNumber, @szEmployeeSurname, @szInitials, @szDeptNo, @dtDateLeft, @EdTotalGrossPayThis, @EdTaxableGrossPayThis, @EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious, @EdTaxPaidThis, @EdTaxPaidPrevious, @EdSMPPaidToDate, @EdSSPPaidToDate
WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC qryPayEmp8b @lUniqueID, @dtQryPayrollYearStartDate, @SumOfdEmployersNICHoliday OUTPUT EXEC qryPayEmp8c @lUniqueID, @dtQryPeriodEndDate, @PdTotalGrossPayThis OUTPUT, @PdTaxableGrossPayThis OUTPUT, @PdTaxPaidThis OUTPUT, @PdSMPPaid OUTPUT, @PdSSPPaid OUTPUT OPEN employee_ni_categories FETCH FIRST FROM employee_ni_categories INTO @lCCUniqueID, @szNICategory IF (@@FETCH_STATUS <> -1) WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC qryPayEmp8a @lUniqueID, @szNICategory, @dtQryPayrollYearStartDate, @SumOfdNIablePay OUTPUT, @SumOfdEmployersNI OUTPUT, @SumOfdEmployeeNI OUTPUT, @SumOfdContractedOutEarnings OUTPUT, @SumOfdEmployeeNIContractedOut OUTPUT
SELECT @lUniqueID AS lUniqueID, @lEmployeeNumber AS lEmployeeNumber, @szEmployeeSurname AS szSurname, @szInitials AS szInitials, @szDeptNo AS szDeptNo, @dtDateLeft AS dtDateLeft, @EdTotalGrossPayThis AS EdTotalGrossPayThis, @EdTaxableGrossPayThis AS EdTaxableGrossPayThis, @EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious, @EdTaxPaidThis AS EdTaxPaidThis, @EdTaxPaidPrevious AS EdTaxPaidPrevious, @EdSMPPaidToDate AS EdSMPPaidToDate, @EdSSPPaidToDate AS EdSSPPaidToDate, @SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday, @PdTotalGrossPayThis AS PdTotalGrossPayThis, @PdTaxableGrossPayThis AS PdTaxableGrossPayThis, @PdTaxPaidThis AS PdTaxPaidThis, @PdSMPPaid AS PdSMPPaid, @PdSSPPaid AS PdSSPPaid, @szNICategory AS szNICategory, @SumOfdNIablePay AS SumOfdNIablePay, @SumOfdEmployersNI AS SumOfdEmployersNI, @SumOfdEmployeeNI AS SumOfdEmployeeNI, @SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings, @SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut
FETCH NEXT FROM employee_ni_categories INTO @lCCUniqueID, @szNICategory END ELSE SELECT @lUniqueID AS lUniqueID, @lEmployeeNumber AS lEmployeeNumber, @szEmployeeSurname AS szSurname, @szInitials AS szInitials, @szDeptNo AS szDeptNo, @dtDateLeft AS dtDateLeft, @EdTotalGrossPayThis AS EdTotalGrossPayThis, @EdTaxableGrossPayThis AS EdTaxableGrossPayThis, @EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious, @EdTaxPaidThis AS EdTaxPaidThis, @EdTaxPaidPrevious AS EdTaxPaidPrevious, @EdSMPPaidToDate AS EdSMPPaidToDate, @EdSSPPaidToDate AS EdSSPPaidToDate, @SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday, @PdTotalGrossPayThis AS PdTotalGrossPayThis, @PdTaxableGrossPayThis AS PdTaxableGrossPayThis, @PdTaxPaidThis AS PdTaxPaidThis, @PdSMPPaid AS PdSMPPaid, @PdSSPPaid AS PdSSPPaid CLOSE employee_ni_categories FETCH NEXT FROM employee_period INTO @lUniqueID, @lEmployeeNumber, @szEmployeeSurname, @szInitials, @szDeptNo, @dtDateLeft, @EdTotalGrossPayThis, @EdTaxableGrossPayThis, @EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious, @EdTaxPaidThis, @EdTaxPaidPrevious, @EdSMPPaidToDate, @EdSSPPaidToDate END
CLOSE employee_period DEALLOCATE employee_period
DEALLOCATE employee_ni_categories RETURN
-----------------
CREATE PROCEDURE coral.qryPayEmp8a @lUniqueID INT = 1, @szNICategory VARCHAR (1) = 'A', @dtPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000', @SumOfdNIablePay FLOAT OUTPUT, @SumOfdEmployersNI FLOAT OUTPUT, @SumOfdEmployeeNI FLOAT OUTPUT, @SumOfdContractedOutEarnings FLOAT OUTPUT, @SumOfdEmployeeNIContractedOut FLOAT OUTPUT AS SELECT DISTINCT @SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay), @SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI), @SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI), @SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s), @SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out) FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND (tblEmployeePeriodDetails.cNICategory = @szNICategory) AND (tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate)) GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory
RETURN
-----------------------------
REATE PROCEDURE coral.qryPayEmp8b @lQryUniqueID INT = 1, @dtQryPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000', @SumOfdEmployersNICHoliday FLOAT OUTPUT AS SELECT @SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday) FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND (tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND (tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
When I am trying to call a function I made from a stored procedure of my creation as well I am getting:
Running [dbo].[DeleteSetByTime].
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[DeleteSetByTime].
This is my function:
ALTER FUNCTION dbo.TTLValue
(
)
RETURNS TABLE
AS
RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'
This is my stored procedure:
ALTER PROCEDURE dbo.DeleteSetByTime
AS
BEGIN
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
CreatedTime is a datetime column and TTL is an integer column.
I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.
Hi, i've had this query method: 34 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)35 {36 SqlConnection oConn = new SqlConnection(_connectionString);37 string strSql = "Insert into LogDetail (LogID, CategorieID, Inhoud)";38 strSql += "values(@logID, @categorieID, @inhoud)";39 SqlCommand oCmd = new SqlCommand(strSql, oConn);40 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;41 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;42 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;43 44 try45 {46 oConn.Open();47 int rowsAffected = oCmd.ExecuteNonQuery();48 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");49 oCmd.CommandText = "select @@IDENTITY";50 oCmd.Parameters.Clear();51 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();52 }53 catch (Exception ex)54 {55 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);56 }57 finally58 {59 if (oConn.State == ConnectionState.Open) oConn.Close();60 }61 } which i've converted to a stored procedure: 1 ALTER PROCEDURE [dbo].[insert_DagVerslagDetail] 2 -- Add the parameters for the stored procedure here 3 @dagverslagdetailID int, 4 @logID int, 5 @categorieID int, 6 @inhoud varchar(100) 7 AS 8 BEGIN 9 -- SET NOCOUNT ON added to prevent extra result sets from 10 -- interfering with SELECT statements. 11 SET NOCOUNT ON; 12 SET @dagverslagdetailID = SCOPE_IDENTITY() 13 14 -- Insert statements for procedure here 15 BEGIN TRANSACTION 16 INSERT LogDetail (LogID, CategorieID, Inhoud) 17 VALUES(@logID, @categorieID, @inhoud) 18 COMMIT TRANSACTION 19 END
Now i would like to call that stored procedure in my previous method, so i've changed it to this: 1 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail) 2 { 3 SqlConnection oConn = new SqlConnection(_connectionString); 4 string strSql = "insert_DagVerslagDetail"; 5 strSql += "values(@logID, @categorieID, @inhoud)"; 6 SqlCommand oCmd = new SqlCommand(strSql, oConn); 7 oCmd.CommandType = CommandType.StoredProcedure; 8 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID; 9 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID; 10 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud; 11 12 try 13 { 14 oConn.Open(); 15 int rowsAffected = oCmd.ExecuteNonQuery(); 16 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail"); 17 oCmd.CommandText = "select @@IDENTITY"; 18 oCmd.Parameters.Clear(); 19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar(); 20 } 21 catch (Exception ex) 22 { 23 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message); 24 } 25 finally 26 { 27 if (oConn.State == ConnectionState.Open) oConn.Close(); 28 } 29 }
Do i still need the lines 17 oCmd.CommandText = "select @@IDENTITY"; 19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar(); Because i've declared the identity in my stored procedure
Hi, I have a stored procedure, and it is expecting an output. If I declared the passing varaible as ref, it compiles fine, but it is not returning any value. If I pass the varaible as out, and add the paramater MyComm.Parameters.Add(new SqlParameter("@ReturnValue", returnValue)); it gives the following error. Compiler Error Message: CS0269: Use of unassigned out parameter 'quoteID'. And if I don't supply the previous statement, the following error occurs. System.Data.SqlClient.SqlException: Procedure 'CreateData' expects parameter '@ReturnValue', which was not supplied. How Can I fix this? thanks.
I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:
Stored Procedure:
CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUSsqlServer113 /NdtsPackage /UuserID /Ppassword' GO
VB to run DTS:
Dim myCommand As SqlCommand myCommand.CommandType = CommandType.StoredProcedure myCommand.CommandText = "spSampleData" myCommand.ExecuteNonQuery()
I'm not sure what I am doing wrong but any help would be great.
I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:
<%@ Page Language="VB" %> <%@ import Namespace="System" %> <%@ import Namespace="System.Data.SqlClient" %> . . . Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn) cmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter() InsertForm.InsertCommand = cmdInsert
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'CommandType' is not declared.
This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure
I'm trying to call a job from a stored procedure. To do so, I've found that sp_start_job is doing just that. My problem is that the sp_start_job is not in my master database stored procedures and I don't know how to add it. I'm working with SQL Server 7 Enterprise without the SP.
Am I looking for the right thing ? (sp_start_job) Where can I find it ?
Hey, I have a parent SP, and within that parent I want to call a a child what is the code to call that child procedure? or teh easiest way to make that happen?
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem? Thanks...
I know this thread is sql, and i'm asking a vb.net question but I cannot find a straight forward answer anywhere else. I am using visual studio 2008 designing a vb.net application where I created a stored procedure using sql management studio 2005. Here is the stored procedure:
CREATE PROCEDURE dbo.StoredProcedure2 @intPID char(10) AS SELECT SUM(Financial.Fee) from dbo.Financial WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, getdate())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Financial.Date))) AND [SPatient Number]=@intPID /* SET NOCOUNT ON */ RETURN
It takes the sum of my Fee column in my financial table where the current date is equal to the column named Date in my Financial table and SPatient Number equals my variable intPID, which the value is defined for in my application (yes, i know that is a horrible name for a column but I cannot change it for it is not my project).
Now, to my knowledge this procedure works fine and should output a single value. However, like i said, i am using visual studio 2008 and therefore am using vs's more automated way of connecting to sql servers(and by that I mean configuring the server through visual studio rather than manually defining datasets, dataadapters, and connection strings through code) All of the tutorials I find use data adapters and are done by manually declaring sql connections and so forth. I like visual studios more automated method of doing sql tasks, and would like to know if there is a simple way to call a stored procedure using visual studio in such a fashion where I would write something like "exec dbo.StoredProcedure2 'intPID' "
Any help is much appreciated, thank you
edit: If i did not provide enough information please let me know, i'm using a strongly typed dataset
I have three stored procedure already created ABC. Now I need to create another one and call other three in each situation. Like If Apple then use Sp_A, if Orange then use Sp_B, and if Mango then use sp_C.
Hi all, I'm new to SQL Server and I'm trying to call BCP from a stored procedure with a parameter passed in as the path to which to export the datafile. This parameter is also the name of a network PC. However, I keep getting this error: SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file NULL
This is the stored procedure: PROCEDURE DownloadLinkEvents @localPath varchar(80) AS declare @bcpCommand varchar(200) begin set @bcpCommand = 'bcp <dbName> out ' + @localPath + '-c -t"|" -S<dbServer> -Usa -P<passowrd>' exec master..xp_cmdshell @bcpCommand end
This is probably the most basic of solutions, but I have spent the last 3 hours trying to work it out, and searching google!
I am trying to call information from a stored procedure (B), from within another stored procedure (A). The select statement from Procedure A contains information to be passed to Procedure B, to get some information.
This is the Procedure I have come up with so far, and I have included dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) as part of the SELECT clause, in a vain attempt that this would work....but it dosn't!
Anyone got any ideas of how to do this? Or even what it would be called so I can start making inteligent searches on google?
USE [QP] GO /****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/29/2008 18:24:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[USERS_LIST] @STYPE Int, @UserID Int, @Username varchar(100), @Surname varchar(100), @DOB VarChar(40), @Location varchar(100), @Email Varchar(100), @Mobile varchar(100)
As SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender, dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Mobile.Value AS Mobile, dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3, dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age, dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) FROM dbo.Members INNER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID WHERE @STYPE = '1' AND ((dbo.Members.EntryID = @UserID) or (dbo.Members.Username = @Username) or (dbo.Members.Surname = @surname) or (dbo.Members.DateofBirth = Convert(datetime, @DOB)) or (dbo.ActiveMember_Location.Location2 = @Location) or (dbo.ActiveMember_Location.Location3 = @Location) or (dbo.ActiveMember_Location.Location4 = @Location) or (dbo.ActiveMember_Email.value = @Email) or (dbo.ActiveMember_Mobile.value = @Mobile))
