Another Reporting System Stored Procedure Issue

Jan 2, 2004

Thx to all who helped me for Stored Procedure previously





Here is what I have:


3 Drop Down Boxes:


1) List of property


2) Ticket Status


3) Tech Name





All 3 Drop boxes have default value of "All"





So, if all 3 drop boxes are "All" ie.





list of property = All


Ticket status = All


Tech Name = All





Query pulls up all records from database and displays it.





Lets say if I select Tech Name is XYZ then query should pull out all property, all ticket status by Tech XYZ.





Now my previous developer has if else case and he has total 9 query for doing all this. He has used SQL along with C# code.





I am trying to modify this if-else and convert it into Stored Procedure. Is there a way I can handle all with 1 stored procedure ?





Previous Reporting works like charm........

View 17 Replies


ADVERTISEMENT

Stored Procedure For Reporting System Issue

Dec 17, 2003

Is it possible to have single stored procedure which can return Weekly, Monthly, Quaterly and Yearly Report.

User input for Queries:
Weekly : Start Date & End Date
Monthly: Month & Year (Eg. Jan 2003, May 2004)
Quarterly: Quarter & Year (First Quarter 2003, ThirdQuarter 2004)
Yearly: Year

Currently I have 4 different Stored Procedure 1 for each reporting system.

I have seen that one can use CASE in Stored procedure.....

Pls give suggestion !!!

View 20 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

System Stored Procedure

Feb 24, 2005

How can I create a dbo/system stored procedure, not dbo/user SP.
One more thing, if I created a new database TimeDB, I see nothing in stored procedure folder, how can see something (system procedures) as I knew that in SQL 97, we can see something (system procedures).

View 6 Replies View Related

Creating System Stored Procedure In 7.0

Mar 14, 2001

Can I create system stored procedure in sql 7.0?
After I used 'alter' to modify a system sp, it's category
change from 'system' to 'user'. Is there way to change it back?
Thanks a lot!

Xiao

View 1 Replies View Related

Using System.IO In A SQL2005 CLR Stored Procedure

Mar 1, 2007

I am attempting to develop a stored proc that will do the following:

1)  Take as an input parameter the filepath of a local directory

2)  Return a recordset showing all files contained in the local directory

At the code level, I am attempting to do the following:

1)  use system.io class to iterate through each file of a given local directory

2)  parse out a union query to show all file names and their system creation times

3)  insert the parsed sql into a sqldatareader, and send that out via a sqlpipe to the caller. 

After compiling and deploying the CLR stored proc, I get the following when I try to execute:

Msg 6522, Level 16, State 1, Procedure spclr_wcl_get_file_info, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spclr_wcl_get_file_info':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path)

at StoredProcedures.spclr_wcl_get_file_info(String str_dir)

This occurs even though I've set the SQL Server service to run as a local administrator on the machine (ie, the account should have full rights to virtually any local directory or file). 

I found the following article which I thought might resolve:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494009&SiteID=1

but when I include the WindowsImpersonationContext, etc., I instead get the following:

Msg 10312, Level 16, State 49, Procedure spclr_wcl_get_file_info, Line 0

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

Was hoping someone could illuminate as to what I'm doing wrong?  Below is a code sample.  TIA.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Diagnostics;

 

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spclr_wcl_get_file_info(string str_dir)

{

// this clr stored proc will read all files in the trans log directory and return

// a recordset with file names and create dates.

//impersonate the calling user

System.Security.Principal.WindowsImpersonationContext newContext;

newContext = SqlContext.WindowsIdentity.Impersonate();

//enumerate files in directory, use to parse out union select query, return table with file names and create times.

string str_sql = "";

System.DateTime d_date;

FileInfo obj_fsi;

string[] str_arr_files = Directory.GetFiles(str_dir);

foreach(string str_file in str_arr_files)

{

obj_fsi = new FileInfo(str_dir + str_file);

if (obj_fsi.Exists)

{

d_date = obj_fsi.CreationTime;

str_sql += "SELECT CHAR(39)" + str_file + "CHAR(39) AS [file_name], ";

str_sql += d_date.ToLongDateString() + " file_creation_time UNION ";

}

}

// parse off the last 'union'

if (str_sql.Length > ("UNION ").Length)

str_sql = str_sql.Substring(0, str_sql.Length - ("UNION ").Length);

// use sql to send dataset back to caller.

SqlCommand obj_cmd = new SqlCommand();

obj_cmd.Connection = new SqlConnection("Context connection=true");

obj_cmd.Connection.Open();

obj_cmd.CommandText = str_sql;

SqlDataReader obj_reader = obj_cmd.ExecuteReader();

SqlPipe obj_pipe = SqlContext.Pipe;

obj_pipe.Send(obj_reader);

//clean up

obj_reader.Close();

obj_cmd.Connection.Close();

}



};

View 4 Replies View Related

System Stored Procedure Question

Jun 27, 2007

Hi,



I'm using the sp_start_job system stored procedure to run a job on the SQL Server Agent. The sp_start_job stored procedure takes a parameter called, step_name where you can begin execution from that step. My problem is that I only want to run that one step of the job rather than that step and every step after it. Is there a way to only execute one particular step? Or a safe way to successfully terminate the process after that step has finished. Any input would be much appreciated. Thanks.

View 4 Replies View Related

System Stored Procedure Name That Allows One To Change One's Password ?

Jan 2, 2008

I am using a web application using asp.net 1.1 vs 2003.
i am using sql server authentication where users are using their sql server user id and sql server saved passwords.
how can i change their passwords inside the sql server?
is there a system sp that allows one to pass one's user id and then change password.
 I will need to call that sp from a user defined store dprocedure and pass the parameters and that will change the password on the sql server.
thanks
  
 

View 3 Replies View Related

System Stored Procedure Output Into Table

Nov 29, 1999

Hi,
Is it possible to store the output of a SQL Server 7.0/6.5 System Stored Procedure (eg. xp_fixeddrives, sp_spaceused, etc.) in a table, possibly with a Select Into?
All help will be greatly appreciated.
Thanx in advance.
Craig

View 4 Replies View Related

How Can Your Reference Columns In A System Stored Procedure

Nov 5, 2002

I want to have a Stored procedure call another stored procedure gathering the info from some of the information SP#1 and use it calling SP#2...
For example sp_Help returns columns when executed (Name, Owner, Object_type).
So how can I reference the Name column and pass it to SP#2

Exec sp_Help

...?
SP#2 name=sp_help.Name ???


Thanks


Richard

View 1 Replies View Related

Is There A System Stored Procedure That Scripts Tables?

Apr 25, 2007

Hello

I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.

Thanks a lot.

View 4 Replies View Related

System Stored Procedure To Script Table

Sep 30, 2014

Is it possible to access the procedure which scripts a table to new query? My goal is to automate the creation of some tables.

View 4 Replies View Related

Passing System Variables To Stored Procedure

Sep 24, 2007



How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @MachineName, @TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...

Thanks for the help.

View 8 Replies View Related

How Do I Change The Type Of A Stored Procedure From User To System?

Aug 11, 2005

I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?

View 4 Replies View Related

System Stored Procedure Doesn't Return Result (eg: Sp_update_schedule)

Apr 9, 2007

I am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know 

View 5 Replies View Related

CLR Stored Procedure In Reporting Services

Feb 24, 2007

Hi


I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio. After Creating the dll I have registered the dll like that CREATE ASSEMBLY MY_SP_NAME from 'C:MY_DLL_PATHMY_DLL_NAME.dll' WITH PERMISSION_SET = SAFEAfter registering I have deployed the dll :CREATE PROCEDURE [dbo].[MY_SP_NAME] @dbname [nvarchar](4000), @varTable [nvarchar](4000), .............. .............. .............. ASEXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]GOEXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'MY_SP_NAME'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'MY_SP_NAME'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'MY_SP_NAME'GOThe Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?
Please please ASAP.


Thanks
Tareqe

View 4 Replies View Related

Using Stored Procedure In Reporting Services

Apr 24, 2008



Hi all,I am using Stored procedure to design report instead of Query.
Problem is i am unable to view the fields in the dataset.
Any body please tell me the Steps to use Stored procedure in Reporting Services design.
Regards.

View 7 Replies View Related

Reporting Services Stored Procedure Dataset

Sep 26, 2004

I have a big SQL Stored Procedure which works with a cursor inside of it. During the procedure the data is inserted into a table and at the end is a SELECT statement from that table. The problem is that when i create a dataset with that stored procedure and i run it in the Data tab i get the correct select, but in the Fields section of the Report I don't get the fields from the last SELECT, but the fields from the cursor. Am I doing something wrong or is this a bug and how can i fix it.
Thanks!

View 3 Replies View Related

Reporting Services + Oracle Stored Procedure

May 14, 2008

Hello,
I'm having some problems and I wonder if anyone can help me.
I need to call a stored procedure to retrieve some data from the bank and use it in a report.
I'm using oracle DB, but I'm not using ODBC, I'm using Oracle.
I can call the SP using 2 commands:
1- in the dataset data form, I can select the command type as Stored Procedure and write the name of my procedure without the arguments, and then the VS ask for the arguments
2- in the same form, the command type as TEXT, i can use the syntax: call <SP name>(arguments)

My problems are:
-in the first way, how can I programatically set the arguments to send?
-in both of them, how can I define that the data that should be stored in the dataset is in the out cursor parameter?

Thank you,
Oscar

View 2 Replies View Related

Using Stored Procedure In Reporting Services(10th)

Mar 10, 2008

I have created stored procedure.How can i use it insted of writing Sql Statement in Report Design.

Pls Help me.

Regards.

View 1 Replies View Related

Stored Procedure Issues With Reporting Services

Jan 26, 2007

Hi all,

I have stored procedures that will run fine from query analyzer windows, from other programs, etc. When I try to run the sp from vs 2005 form with in a SSRS project, the sp just hangs. It will not return any records and the app says "Not Responding. This does not happen for all SP's only some of them. Any ideas?

View 8 Replies View Related

Stored Procedure From SQL Server Reporting Services 2005

Dec 19, 2007

Hi,

I am working with one example on SQL Server 2005 Reporting Services. I have a stored procedure with two parameters. I want to call this stored procedure from SQL Server Reporting Services 2005.

CREATE PROCEDURE spEmp
(
@id int,
@name varchar(150)
)
AS
BEGIN
SELECT EmpId, EmpName, EmpSal FROM Emp WHERE EmpId = @id AND EmpName = @name
END

I want to pass

@id AND @name

parameters from reporting services form. Can you please give me link / article / steps to follow this in SQL SERVER REPORTING SERVICES 2005 ?

Thanks
Rajesh.

View 12 Replies View Related

Reporting Services :: Different Results In Stored Procedure And In Report

Oct 21, 2015

Created a report with parameters that can have multiple values.

Ran Stored procedure with same above criteria and data is retrieved

Ran the report and no data is displayed

I am unable to figure out where it might have gone wrong.

View 3 Replies View Related

Passing Null To Stored Procedure In Reporting Services

Jun 27, 2007

Hello,



I have a report. I need to make a drop-down parameter optional. I've setup the parameter to use a dataset as its source, and I need to allow the value to be null. I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value. But, when the entry is not selected (blank), no data ever comes back. If the data is blank or null, my procedure works (I've tested it). How do I send a null value to the database through reporting services report? I was hoping to avoid creating a special query just for that purpose.



Thanks.

View 15 Replies View Related

Reporting Services :: Calling Oracle Stored Procedure

May 2, 2006

I am trying to call oracle stored procedure from SRSS 2005. I am using the syntax  { Call s_test_rcur()} . I am getting following error.An error occurred while retrieving the parameters in the query.ORA-00911: invalid characterORA-06512: at "SYS.DBMS_UTILITY", line 68ORA-06512: at line 1

ORA-00911: invalid characterORA-06512: at "SYS.DBMS_UTILITY", line 68ORA-06512: at line 1 (System.Data.OracleClient)
Here is the Oracel stored proc.
TYPE rc_test IS REF CURSOR;
PROCEDURE s_test_rcur (po_test_rc OUT rc_test, -- returns a record setpo_error OUT INTEGER)ISBEGIN g_err_level := 1;OPEN po_test_rc FORSELECT a.ssn_id,TO_CHAR (a.acad_yr) || TO_CHAR (a.acad_yr + 1) acad_yr,RPAD (NVL (last_name, ' '), 30, ' ') last_name,RPAD (NVL (first_name, ' '), 30, ' ') first_name,NVL (middle_initial, ' ') middle_initialfrom test_tableorder by last_name;po_error := 0;EXCEPTIONWHEN OTHERSTHENpo_error := -1;g_error_code := SQLCODE;g_error_msg :='Err level :' ||TO_CHAR (g_err_level) ||' ' ||SUBSTR (SQLERRM, 1, 250);END;

View 2 Replies View Related

Reporting Services :: SSRS Not Loading The Stored Procedure?

Aug 21, 2015

I have a stored procedure (sproc)  running in Firebird Database. Now I try to use this sproc to build a report in the SSRS 2012 (SSDT) Report Designer.

After I connect to the dataset, and when I choose the Query Type as 'Stored Procedure', and type the name of  my sproc, and then go to the Query Designer, and click ! (F5)  ,   I get the following error:

TITLE: Microsoft SQL Server Report Designer
------------------------------

An error occurred while executing the query.
ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 1 [sproc name] 

ADDITIONAL INFORMATION:

ERROR [HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 1 [sproc name] (OdbcFb)
------------------------------
BUTTONS:
OK
------------------------------

how to resolve this? If its a T-SQL sproc, then it will be no issue; in this case,  Firebird database connects to the SSRS via a 32-bit System ODBC connection.

When I proceed and click ok for everything, I get this error (after I exit the Query Designer), in the Dataset Properties tab:

"Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

The ODBC connection between SSRS and Firebird works fine. In fact I am able to run many queries as ad-hoc ones,  but when I use the queries as sprocs, then this issue pops up.

how to handle Firebird stored procedures  in SSRS.

View 2 Replies View Related

Reporting Services :: SSRS Not Able To Load Results From Stored Procedure

Sep 9, 2015

I have created a query in which I have used first_value, lead, lag windows functions. I tried using the query in SSRS query text, but for some reason it could not load. Then I created a stored procedure and implemented that query in stored proc.

The problem is that I am able to execute the stoured proc from sql server studio, but SSRS is not able to load data.

View 5 Replies View Related

Why It Takes Forever To Execute Stored Procedure In Reporting Services?

Sep 17, 2007

I used a stored procedure in my report. If I run the sp in Management Studio (on my pc, database is on a sql server) it takes only several minutes; but from reporting services (also on pc) I put it in the data tab and execute it, it takes forever, actually never finish. I want to know why it's taking so long to execute it from reporting services while it returns data instantly from Mgt Studio. There is cursor in the sp. I don't know whether this is the culprit. Anyone knows why? Thanks!

Below is the sp.
--------------------------------------------------------------------

create proc [dbo].[p_national_by_week]

as

set nocount on



declare @s1 nvarchar(2000), @parmdefinition nvarchar(300), @rangestart smalldatetime, @rangeend smalldatetime

, @price_low money, @price_high money, @weekdate smalldatetime


declare c1 cursor for

--- GG change for reg dates.

select weekdate from vtRealEstate_RealtorListing_WeekDates

open c1

fetch from c1 into @weekdate



while @@fetch_status =0

begin

select @rangeend = @weekdate+7, @rangestart=@weekdate

select @s1 = N'

declare @mlsid_count int, @avg_price money, @avg_day_on_market int, @median_price money, @c1 int

select @mlsid_count=count(*), @avg_price=avg(CurrentPricefilter),

@avg_day_on_market=avg(datediff(dd, FirstListedDate, LastModifiedDate))

from vtRealEstate_RealtorListings

where ((FirstListedDate <= @rangeStart and LastModifiedDate >= @rangeStart) or

(FirstListedDate >= @rangeStart and FirstListedDate < @rangeEnd)

)

and currentpricefilter is not null

and mlsidfilter is not null

select @c1=@mlsid_count/2

set rowcount @c1

select @median_price = CurrentPricefilter from vtRealEstate_RealtorListings

where

((FirstListedDate <= @rangeStart and LastModifiedDate >= @rangeStart) or

(FirstListedDate >= @rangeStart and FirstListedDate < @rangeEnd)

)

and currentpricefilter is not null

and mlsidfilter is not null

order by currentpricefilter

insert report_detail_test (weekdate, mlsid_count, avg_price, median_price

, avg_day_on_market)

values(@weekdate, @mlsid_count, @avg_price, @median_price, @avg_day_on_market)

', @parmdefinition=N'@rangestart smalldatetime, @rangeend smalldatetime, @weekdate smalldatetime'



exec sp_executesql @s1, @parmdefinition, @rangestart=@rangestart, @rangeend=@rangeend

, @weekdate = @weekdate
fetch from c1 into @weekdate

end

select weekdate

, mlsid_count

, avg_price

, median_price

, avg_day_on_market

from report_detail_test

order by WeekDate

View 2 Replies View Related

Reporting Services :: Unable To Display Time Difference Value From Stored Procedure

Nov 2, 2015

Created a report that displays the Maximum Response time (example of value 00:00:00) which is directly pulled from the Stored proc.When I ran the report, the column displays blank values.I am not sure if I should add any conversion to the Response value in the report.

View 2 Replies View Related

Generating A Pdf Report From Reporting Services Directly From Sql Server Stored Procedure

Dec 12, 2007

Hi

I was wondering if it was possible to call reporting server web service directly from my sql server stored procedure. The call that I need to make to reporting web service needs to generate the report in a PDF format.

View 1 Replies View Related

Reporting Services :: Reports In BIDS Does Not Update When Stored Procedure Is Modified

Jan 3, 2014

I am using SQL Server 2008 R2 SP1, and BIDS 2008 SP1. The problem I am about to define does NOT happen when the report is deployed to the report server, but only within the report development environment (BIDS).

From within BIDS my reports are fed by stored procedures. The problem is when I modify a stored procedure in Management Studio, and refresh the report from within BIDS, the report does not reflect the new changes made in the stored procedure. So far I have found one clumsy solution to this problem:

1) Go to the folder where the project is saved on the file system.

2)Find the ".data" file that is associated to the particular report, then delete it.  For instance, if my report name is TestReport.rdl, find the file named TestReport.rdl.data, then delete it.

View 3 Replies View Related







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