Filter in stored procedure

I am trying to edit this query i already have that is fully functional for another report in reporting services. The report has visits sales reps made to stores, broken down by period, week and then date. Now, i need to filter it out by Visits. They want the Sales Reps who had less then 6 visits a day. How would i code that out in my stored procedure? When i have Stores and Account Status( which makes it pretty broken down). Any suggestions. Thanks..heres the code.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

END

SET NOCOUNT OFF

If dbo.Qry_Visits.Visits stores the cumulative count of the visits made then you can add 'Qry_Visits.Visits < 6' to the where clause. To make it more generic instead of hard coding values, you may want to have to edit the signature of the proc to have an input parameter that represents the number of visits also.

|||

try:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.Visits >= @.visit_req

END

SET NOCOUNT OFF

|||it does not show the cumulative. It shows one visit per sales person, per date. So its really broken down. So if i put <6 it will show everything, because each record has one visit. I only want the ones with less then 6 visits by a Sales Rep. Which means the sum of visits per Sales Rep, per day. How would i get that?|||

I don't really understand the data structure, but is possible to just do an aggregate similar to:

Code Snippet

Pseudo Code:

SELECT YourColumns, SUM(Visits) AS Visits
FROM AllThoseTables
GROUP BY YourColumns
HAVING SUM(Visits) > 5

|||

Then do:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.[SR Code] NOT IN(select [SR Code] from dbo.Qry_Visits

group by [SR Code]

having count(Visits) < @.visit_req)

END

SET NOCOUNT OFF

Filter by a concatenated column?

I am using a query in a stored procedure, where the user can dictate which
field they want to sort by. That I can do. What I also want to do is
filter out the null values for the selected field, and one of the fields is
a concatenated result. This is the query:
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
so the stored procedure would be something like this:
CREATE PROCEDURE [dbo].ClientSearch]
@.SearchField as varchar(50)='' AS
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
where @.SearchField is not null order by @.SearchField
If the user is selecting FullName, I get an error saying the @.SearchField is
an invalid column name -- however this works if other field names are
selected, and it also works if I only want to order by FullName, but not
filter.
Thanks for your help.I hope you are using exec since its a dyamic query.
fullname is a concatenationo of two fields LName + ', ' + FName
therefore LName + ', ' + FName is not null does not work.
make sure that LName is not null and FNAME is not NULL
HTH
Rajesh Peddireddy.
"news.microsoft.com" wrote:

> I am using a query in a stored procedure, where the user can dictate which
> field they want to sort by. That I can do. What I also want to do is
> filter out the null values for the selected field, and one of the fields i
s
> a concatenated result. This is the query:
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> so the stored procedure would be something like this:
> CREATE PROCEDURE [dbo].ClientSearch]
> @.SearchField as varchar(50)='' AS
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> where @.SearchField is not null order by @.SearchField
> If the user is selecting FullName, I get an error saying the @.SearchField
is
> an invalid column name -- however this works if other field names are
> selected, and it also works if I only want to order by FullName, but not
> filter.
> Thanks for your help.
>
>

Filling SQLDataReader with stored procedure recordset

Hi All,

I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read.

Dim CurrentUserAs String, Pos1As Int16, EmployeeIdAs Int32Dim cnAs New System.Data.SqlClient.SqlConnectionDim paramAs New System.Data.SqlClient.SqlParameterDim readerAs System.Data.SqlClient.SqlDataReaderDim cmdAs New System.Data.SqlClient.SqlCommand CurrentUser =CStr(User.Identity.Name) Pos1 = InStr(CurrentUser,"\") + 1 CurrentUser = Mid(CurrentUser, Pos1) Session("User") = CurrentUser Session("CID") =Nothing cn.ConnectionString ="Data Source=LAPTOP-4\SQLEXPRESS;Initial Catalog=SCMdb;Integrated Security=True" cn.Open() cmd.Connection = cn cmd.CommandText ="CurrentUser" cmd.CommandType = CommandType.StoredProcedure param = cmd.CreateParameter param.ParameterName ="@.UserName" param.SqlDbType = SqlDbType.VarChar param.Value = CurrentUser cmd.Parameters.Add(param) reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) EmployeeId = reader.Item("EmployeeID") reader.Close()

Any help would be much appricated this is driving me mad.

Thank You

Shaft

To read items from reader,

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection):

while (reader.Read())
{
int _employeeId = (int) reader["EmployeeID"];
}

Thanks

|||

Hi there,

If you are just reading a single value of the stored procedure I will advice you to use OUTPUT parameters to achieve this.Datareader will also do the job, but you leave yourself more vulnerable with connection not closing properly and also with overhead of creating a reader object.Just a thought.. what you are doing is also correct and will work

|||

e_screw:

To read items from reader,

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection):

while (reader.Read())
{
int _employeeId = (int) reader["EmployeeID"];
}

Thanks

I've tried that all ready but it still says there is no data.

Any other ideas anyone?

|||

The actual error is "Invalid attempt to read when no data is present." so I'm thinking the Command Object hasn't pulled any data which leads me think there is a problem with the way I've declared the parameter because it is running the stored procedure just not retrieving any data.

|||

Sorry may mistake it did work I'd just forgot to comment out the statment that was wrong.

Here's the code that e-screw posted but converted to vb .net as I needed it

Do While reader.Read EmployeeId = reader("EmployeeID")Loop

Cheers E-Screw

Filling a DDL from a SQL DB?

Hello All
I am wanting to fill a drop down list in ASP.NET using C# from a SQL database table using a stored procedure. I have my Sproc. But using ASP.NET C# I have no idea how to do this. Can someone give me a good example, and if not too much trouble, place comments in the code, and give an explanation. I am just learning ASP.NET after moving from Classic. Things are alot different.

Thank You in advnace for all your help

Andrewhttp://aspauthors.com/aspnetbyexample/ch06/

Filling a dataset created from stored procedure

How Do I fill as dateset that i created from a stored procedure in SQL SERVER?
I dragged an stored procedure onto the dataset template from the server explorer.
Do I use the exact same sql statement as in the stored procedure, or can I say something like "SELECT * FROM myStoredProcedure", but that is not working?

Thanks in advanceIf the stored procedure outputs a result set (or more than one), you can set the CommandText of your SqlCommand object to the name of the stored procedure and set the CommandType of your SqlCommand object to CommandType.StoredProcedure.

If your stored procedure does not output a result set, you need to issue the same select statement you have in the stored procedure, but without the variable or temporary table that is accepting the results.

If your stored procedure is returning a table variable, you need to put "select * from @.tablevariablename" at the end of the stored procedure, I think, because I don't think the ASP.NET API will accept a table variable as a stored procedure return value. I could be wrong about that, though.

Filetime

How can I convert filetime to datetime in sql server inside a sored procedure.
Thanks
BVR"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> How can I convert filetime to datetime in sql server inside a sored
> procedure.
> Thanks
> BVR
What do you mean by filetime? Can you give an example of what the filetime
data looks like?
Rick Sawtell
MCT, MCSD, MCDBA|||Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > How can I convert filetime to datetime in sql server inside a sored
> > procedure.
> >
> > Thanks
> > BVR
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

fileLen function in stored procedure

Heres an extract of a stored procedure creating a column.
Path = FileLen([CacheServers].[CachePath]+
(left([DOCUMENT]. [PHYSICAL_DOC_GUID],6))+''''+[DOCUMENT]
.[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType])
Reult:
\\comp-ap- 70c\Imxxs$\data\docs\70393C\70393CE0EC6D
11D8BB64000D568A4637.tif
The above is a file path of an image stored on the SAN server. The code
above works perfectly fine in MS Access but not in SQL server 2K
It should return the size in bytes of the file.
Books online state the VBA function fileLen() works in SQL Analyser,
however, when I execute the stored procedure I receive an error message
"FileLen is not a recognised function name".
I cant find a thing on the Microsoft Tech communities the specifically
relates to calling VBA functions in SQL.
Any ideas?
Learning SQL and AccessIs there something wrong with the LEN function? Or DATALENGTH?
MC
"sebastian stephenson" <sebastianstephenson@.discussions.microsoft.com> wrote
in message news:E9F88AB0-05A3-4BE6-9EB0-6314A7685359@.microsoft.com...
> Heres an extract of a stored procedure creating a column.
> Path = FileLen([CacheServers].[CachePath]+
> (left([DOCUMENT]. [PHYSICAL_DOC_GUID],6))+''''+[DOCUMENT]
.[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType])
> Reult:
> \\comp-ap- 70c\Imxxs$\data\docs\70393C\70393CE0EC6D
11D8BB64000D568A4637.tif
> The above is a file path of an image stored on the SAN server. The code
> above works perfectly fine in MS Access but not in SQL server 2K
> It should return the size in bytes of the file.
> Books online state the VBA function fileLen() works in SQL Analyser,
> however, when I execute the stored procedure I receive an error message
> "FileLen is not a recognised function name".
> I cant find a thing on the Microsoft Tech communities the specifically
> relates to calling VBA functions in SQL.
> Any ideas?
> --
> Learning SQL and Access|||> Books online state the VBA function fileLen() works in SQL Analyser,
> however, when I execute the stored procedure I receive an error message
Where does it say that?
If you really need to check for file sizes from T-SQL you should look at the
sp_OA* system procedures. I'd suggest using an appropriate client applicatio
n
to supply the values to the server.
ML
http://milambda.blogspot.com/|||I obviously didnt understand you correctly. Is SQL Server 2005 an option?
You could use CLR for something like this.
MC
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:u%23FgSFLVGHA.524@.TK2MSFTNGP10.phx.gbl...
> Is there something wrong with the LEN function? Or DATALENGTH?
>
> MC
>
> "sebastian stephenson" <sebastianstephenson@.discussions.microsoft.com>
> wrote in message
> news:E9F88AB0-05A3-4BE6-9EB0-6314A7685359@.microsoft.com...
>

File transfer

I have to make a process/stored procedure that will either send or receive from a specific location (parameters: ftp server, username, password, filename, etc). Ne direction/help in order to do this. Or if anyone has done this before please help.
thxCould you supply some more specifics?|||The specifies are:

I have to make a sp that will send/receive files to/from an ip address or ftp site.
The sp will query the information such as (filename, send_time, receive/send, servername, usernid, password, size_of_file, etc).
Once this info is queried, the script should transfer the file to respective location using, either exec xp_cmdshell or ne other way.

Since it's critical that the file being sent/received, must succesfully be sent/received. The script must also handle that.

This script will be running on SQL server. Any sample code?
thx|||I guess you're going to use ftp...

OK...build an ftp script...and execute it from a bat file with xp_cmdshell...don't forget to redirect the output...maybe load it to a log table...

I guess you'll interogate a directory..

Use xp_cmdshell 'DIR D;\whatever\*.*

and load that to a table to interogate that...

something like that?

File system error in created local cubes

Hi everyone,

I have created a stored procedure in SQL 2005 which will create (sliced) local cubes using the CREATE GLOBAL CUBE (MDX) statement. This procedure will be started from a SSIS package which is deployed on the same server. A SQL server login with sysadmin permissions can start this SSIS package and create multiple (sliced) off line cubes on the file system.

Everything seems to work fine and no errors where raised during execution. However when I try to open the .cub file in ProClarity 6.2, or even Excel, I get the following message. "File System Error: An error occurred while opening the <file path and name> local cube file."

The strange thing is...., whenever I exectue this package using a domain account with admin permission, the cube file turns out fine.

Furthermore the same package works just fine in our test enviroment. (32 bit virtual server)

The machine that is causing these symptoms is a 64 bit server. Logically I suspected the difference between these enviroments could be a cause. I have tried to narrow the possibilities down to just the creation of off line cubes (and SSAS).

Could security be the cause of this problem? If so, what to do?

Have you checked the file permissions on the .cub file? Try explicitly granting access to the user account that you are running ProClarity or Excel under.|||

Also make sure when you try and open local cube you got correct version of AS OLEDB provider installed.

You can get it from http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi everyone,

Thanks a lot for your replies. I have found the cause of the problem. As stated before we use a stored procedure to execute a package. However on a 64 bit server command "dtexec" has two versions. One for 32 bit which is installes on the folder "Program Files (x86)" and a 64 bit version which is installed on folder "Program Files". Is T-SQL adresses dtexec from a stored procedure it will by default use the 64 bit version. This was the cause of the problem and not security or Analysis Services.

To solve this, please execute the "dtexec" by using the full path ("Program Files (x86)") and referring to the version you want to use.

For this reason, this post is not directly related to Analysis Services. Appologies for any inconvenience.

With kind regards,

Wan Chi

File Name to be changed

Hello,

I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.

I would appreciate, if anybody has done so and can give me some steps for me to work out.

TIA

Hello,

Take a look at data-driven subscriptions in Books Online. I believe this is the only way to update the filename without writing a custom app to handle it.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks for your reply. I apologize if my information is not correct.

I would like to change the filename which is sent as an attachment in an email and not the filename as it is.

Thanks

file ldf too BIG

Hi all!
I would like to reduce the size of MyDataBase_Log.ldf
I use Sql Server 2000.
If it was possible which procedure i have to follow?
Thanks
FractalBlueDo you have any maintenence going on?

You need to Dump the log and truncate at checkpoint

Then lookup DBCC SHRINKFILE

in BOL

File I/O in PL/SQL

Hi,

Please help!
I need to write a stored procedure that will replace a word in a number of files in a directory.

I am new to PL/SQL and will really appreciate the help. I have just learned about UTL_FILE.FOPEN thing and is not able to write it properly.

Thanks in advance for your help.

Regards,
ArunHi arun1581,

First of all: using PL/SQL for manipulating texts in an plain text-file is not the tool I would use. Much better (and easier to handle) form my expierinece is unsing Perl.

But if you prefere to use PL/SQL you must first make sure sure, that your have set the init.ora-parameter 'utl_file_dir' to a directory, which you can use. This is not dynamic, so you have to reboot the Instance after changing it.

furthermore read the documtenation of how to use the built-in package utl_file: http://tahiti.oracle.com/pls/db901/db901.tabbed?section=33316

basically you need to take care for:

1. havind a utl_file_dir defined and acces-rights to it
2. a file-handle
3. the open/close functions of utl_file package
4. the read/write operations from that package

hope it helps LaoDe|||Hi,

Example :
----

set echo on
!mkdir /tmp/public_access

connect sys/change_on_install as sysdba;
drop user tcopy01 cascade;
grant connect, resource to tcopy01 identified by tcopy01;
grant select_catalog_role to tcopy01;

create or replace directory public_access as '/tmp/public_access';
grant read on directory public_access to public;

connect tcopy01/tcopy01
create table tcopy01_out (line varchar2(500), i number);
create procedure tcopy01_p as errbuf varchar2(50);
dir varchar2(512) := 'PUBLIC_ACCESS';
f1 utl_file.file_type;
type t_files is table of utl_file.file_type index by binary_integer;
files t_files;
i number := 0;
ok boolean := TRUE;
pos number;
len number;
blk number;

procedure insertoutput (line varchar2) is
begin
insert into tcopy01_out values (line, i);
i := i+1;
end insertoutput;

begin
f1 := utl_file.fopen('PUBLIC_ACCESS', 'tcopy01.dat', 'w'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 1.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 2.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 3.'); utl_file.fclose(f1);