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.

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...
>

Filegroups and BLOBs

Hi,
As books online mentions, BLOBs are not stored in row by default but they
are out of the row. I'm wondered that if they are separate from searchable
data(rows), how placing them into other filegroup can help performance?
Basically does it?
For example:
CREATE TABLE Table1 (
C1 int,
C2 ...
...
Logo Image) ON FG1 TextImage_On FG2
Is it recommended to keep BLOBs in other filegroup for performance?
Thanks,
Leila
In addition to what I mentioned in your other post placing data in separate
filegroups does nothing for performance in and of itself. If the filegroup
is on a separate drive array and the current one is overburdened it may
help. But only if you have I/O issues now.
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:%23DEbknApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As books online mentions, BLOBs are not stored in row by default but they
> are out of the row. I'm wondered that if they are separate from searchable
> data(rows), how placing them into other filegroup can help performance?
> Basically does it?
> For example:
> CREATE TABLE Table1 (
> C1 int,
> C2 ...
> ...
> Logo Image) ON FG1 TextImage_On FG2
> Is it recommended to keep BLOBs in other filegroup for performance?
> Thanks,
> Leila
>
>

Filegroups and BLOBs

Hi,
As books online mentions, BLOBs are not stored in row by default but they
are out of the row. I'm wondered that if they are separate from searchable
data(rows), how placing them into other filegroup can help performance?
Basically does it?
For example:
CREATE TABLE Table1 (
C1 int,
C2 ...
...
Logo Image) ON FG1 TextImage_On FG2
Is it recommended to keep BLOBs in other filegroup for performance?
Thanks,
LeilaIn addition to what I mentioned in your other post placing data in separate
filegroups does nothing for performance in and of itself. If the filegroup
is on a separate drive array and the current one is overburdened it may
help. But only if you have I/O issues now.
--
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:%23DEbknApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As books online mentions, BLOBs are not stored in row by default but they
> are out of the row. I'm wondered that if they are separate from searchable
> data(rows), how placing them into other filegroup can help performance?
> Basically does it?
> For example:
> CREATE TABLE Table1 (
> C1 int,
> C2 ...
> ...
> Logo Image) ON FG1 TextImage_On FG2
> Is it recommended to keep BLOBs in other filegroup for performance?
> Thanks,
> Leila
>
>

Filegroup Question

I know that tables and indexes can belong to a certain filegroup, and that
this can be specified at creation time of the object, but can stored
procedures, views, or functions belong to a specific filegroup? Or are they
defaulted to the Primary filegroup since their information is stored in the
system tables for the database?
Also, is there a Information Schema view that will return a list of
filesgroups and the objects that belong to those filegroups?
Thanks!
JasonNo you can not specify where those objects live. Only the data and indexes.
I don't believe there is a view for what you want. You will most likely
have to do something custom to get that without using some third party tool.
--
Andrew J. Kelly SQL MVP
"Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>I know that tables and indexes can belong to a certain filegroup, and that
> this can be specified at creation time of the object, but can stored
> procedures, views, or functions belong to a specific filegroup? Or are
> they
> defaulted to the Primary filegroup since their information is stored in
> the
> system tables for the database?
> Also, is there a Information Schema view that will return a list of
> filesgroups and the objects that belong to those filegroups?
> Thanks!
> Jason|||Thanks for the quick response Andrew. That's what I thought, but I figured I
would ask the group to see if my thoughts were wrong.
Jason
"Andrew J. Kelly" wrote:
> No you can not specify where those objects live. Only the data and indexes.
> I don't believe there is a view for what you want. You will most likely
> have to do something custom to get that without using some third party tool.
> --
> Andrew J. Kelly SQL MVP
>
> "Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
> news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
> >I know that tables and indexes can belong to a certain filegroup, and that
> > this can be specified at creation time of the object, but can stored
> > procedures, views, or functions belong to a specific filegroup? Or are
> > they
> > defaulted to the Primary filegroup since their information is stored in
> > the
> > system tables for the database?
> >
> > Also, is there a Information Schema view that will return a list of
> > filesgroups and the objects that belong to those filegroups?
> >
> > Thanks!
> > Jason
>
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?
No. (Re)-create the clustered index on a table to move it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>
|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
previous
>
|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> previous
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > When a database is created, all system objects will be stored in this
> > default filegroup, and also the later user tables, right ?
> > So if I then create another filegroup as default filegroup, are all
previous
> > user tables also move to this default filegroup ?
> >
> >
>|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> > No. (Re)-create the clustered index on a table to move it.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > > When a database is created, all system objects will be stored in this
> > > default filegroup, and also the later user tables, right ?
> > > So if I then create another filegroup as default filegroup, are all
> previous
> > > user tables also move to this default filegroup ?
> > >
> > >
> >
> >
>

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previo
us
> user tables also move to this default filegroup ?
>|||So how about :
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
previous[vbcol=seagreen]
>|||Current or future tables? To have current tables move to the filegroup, (re)
create the tables
clustered index (as I mentioned earlier). For future tables, either specify
ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups fo
r the database (see the
ALTER DATABASE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about :
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> previous
>

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 properties for files stored in IMAGE field

Is there a way (in SQL Server 2005 full-text search) to search the
properties (DocType, Keyword, etc) of files stored and indexed in image
fields? I'm searching the indexed contents with no problem but I also
want to search the properties. I know that this isn't possible in
versions prior to 2005 but I've seen referances that 2005 enables this,
I just can't find the details on how to do it. Was it maybe a feature
that got pulled before the finial release?
Hello,
Its not available directly. You would have to use index server and then your
performance will plummit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Is there a way (in SQL Server 2005 full-text search) to search the
> properties (DocType, Keyword, etc) of files stored and indexed in
> image fields? I'm searching the indexed contents with no problem but
> I also want to search the properties. I know that this isn't possible
> in versions prior to 2005 but I've seen referances that 2005 enables
> this, I just can't find the details on how to do it. Was it maybe a
> feature that got pulled before the finial release?
>
|||Its possible, I'll post a repro later.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ddaiker@.gmail.com> wrote in message
news:1166469151.256705.174690@.48g2000cwx.googlegro ups.com...
> Is there a way (in SQL Server 2005 full-text search) to search the
> properties (DocType, Keyword, etc) of files stored and indexed in image
> fields? I'm searching the indexed contents with no problem but I also
> want to search the properties. I know that this isn't possible in
> versions prior to 2005 but I've seen referances that 2005 enables this,
> I just can't find the details on how to do it. Was it maybe a feature
> that got pulled before the finial release?
>
|||try this - save this as createdocument.vbs, make sure you have a c:\temp
directory.
set wordobj=createobject("Word.application")
set activedoc=wordObj.documents.Add
activeDoc.BuiltInDocumentProperties.item(2)="summa ry info is written here"
set docProp=ActiveDoc.CustomDocumentProperties
docProp.add "Property1", 0,4,"Property1Value"
activedoc.saveAs "C:\temp\Document1.doc"
activedoc.close
wordobj.quit
set docprop=nothing
set activedoc=nothing
set wordobj=nothing
After this has run this script in your SQL 2005 database.
create database test
use test
sp_fulltext_database 'enable'
GO
Create table DocumentPropertyTest(pk int not null identity constraint
DocumentPropertyTestPK primary key, imagecol image, documenttype char(4))
GO
create fulltext catalog doc as default
GO
create fulltext index on DocumentPropertyTest(imagecol type column
documenttype) key index DocumentPropertyTestPK
GO
then run this, save it as loadme.vbs
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.RecordSet")
Set objStream=CreateObject("ADODB.Stream")
objConn.Open
"Provider=SQLNCLI;Server=dev-hcotter;Database=fulltext;UID=sa;PWD=se1cure#;"
Set objFileSystem=createobject("Scripting.FileSystemOb ject")
Set objDir=objFileSystem.GetFolder("c:\temp")
for each objFile in objDir.Files
count=count+1
wscript.echo objFile.name
objConn.Execute "insert into DocumentPropertyTest (ImageCol,documenttype)
values ('Jibberish','doc')"
objRs.Open "select imagecol from DocumentPropertyTest where pk=" & count,
objConn, 1, 3
objStream.Type = 1
objStream.Open
objStream.LoadFromFile objFile.Path
objRs.Fields("ImageCol").Value=objStream.Read
objRs.Update
objRs.Close
objStream.Close
next
objConn.Close
Set objStream=nothing
Set objShell=nothing
Set objConn=nothing
Set objFileSystem=nothing
Set objDir=nothing
then try this
select * from DocumentPropertyTest where contains(*,'Property1Value') -- no
hit
select * from DocumentPropertyTest where contains(*,'summary') --hit
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OgxTfxwIHHA.320@.TK2MSFTNGP06.phx.gbl...
> Its possible, I'll post a repro later.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> <ddaiker@.gmail.com> wrote in message
> news:1166469151.256705.174690@.48g2000cwx.googlegro ups.com...
>
|||Wow, thank you for taking the time to write and post all that.
Unfortunatly I'm getting the same results with your sample as I got
with my own testing. The query with "summary" doesn't return a hit
either. I checked the poperties of the doc file from explorer and from
Word and the "Subject" and "Property1" properties are set correctly.
To make sure the indexing was working I put some content in the .doc
and added it agian. If I search for data in the file I get a hit, but
not for anything in the "Subject" property. I tried "summary" and
"written" with no luck.
Any idea what might be wrong?
Here is my setup of everything that I think could be relavant.
Windows XP SP2
IE7
SQL Server 2005 Developer Editition installed as second instance beside
SQL Server 2000 Developer Edition
Office 2003 SP2
Visual Studio 2003 and Visual Studio 2005
My Offfilt.dll version is 2003.5.28.0
|||So my sample does not work on your machine? It works on mine with the same
setup. It is the SQL 2005 instance you can't get it to work on right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166535897.899606.47840@.73g2000cwn.googlegrou ps.com...
> Wow, thank you for taking the time to write and post all that.
> Unfortunatly I'm getting the same results with your sample as I got
> with my own testing. The query with "summary" doesn't return a hit
> either. I checked the poperties of the doc file from explorer and from
> Word and the "Subject" and "Property1" properties are set correctly.
> To make sure the indexing was working I put some content in the .doc
> and added it agian. If I search for data in the file I get a hit, but
> not for anything in the "Subject" property. I tried "summary" and
> "written" with no luck.
> Any idea what might be wrong?
> Here is my setup of everything that I think could be relavant.
> Windows XP SP2
> IE7
> SQL Server 2005 Developer Editition installed as second instance beside
> SQL Server 2000 Developer Edition
> Office 2003 SP2
> Visual Studio 2003 and Visual Studio 2005
> My Offfilt.dll version is 2003.5.28.0
>
|||Ok, it seems I'm having bigger problems. filtdump only brings back the
content of 2 Word and 1 Excel document that has properties on it. I
ran one of my files through an Index Server catalog and queried it on a
word in it's subject and it didn't get a hit either. I'm having issues
with the iFilter but I need to look into more. Do our versions of
offfilt.dll match?
|||Can you send me some of your problem docs?
My version has a date stamp of 8/18/2006 at 8:34, and has a version of
2006.0.5486.108.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166556861.260065.50920@.t46g2000cwa.googlegro ups.com...
> Ok, it seems I'm having bigger problems. filtdump only brings back the
> content of 2 Word and 1 Excel document that has properties on it. I
> ran one of my files through an Index Server catalog and queried it on a
> word in it's subject and it didn't get a hit either. I'm having issues
> with the iFilter but I need to look into more. Do our versions of
> offfilt.dll match?
>
|||There was an error in my repro. There were some pre-existing word docs in
the doc directory and one of them had the word summary in it.
I can't get it to work now either, except with html. Once upon a time it did
work on RTM.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166556861.260065.50920@.t46g2000cwa.googlegro ups.com...
> Ok, it seems I'm having bigger problems. filtdump only brings back the
> content of 2 Word and 1 Excel document that has properties on it. I
> ran one of my files through an Index Server catalog and queried it on a
> word in it's subject and it didn't get a hit either. I'm having issues
> with the iFilter but I need to look into more. Do our versions of
> offfilt.dll match?
>
|||I received word from Microsoft that SQL FTS 2005 does index and allow
querying of document properties should they be emitted by the iFilters as
strings.
The problem David and myself were having was that for the Word and Excel
documents the properties were not part of the Office documents themselves
but were stored in the file system.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23FOeh9KJHHA.3936@.TK2MSFTNGP02.phx.gbl...
> There was an error in my repro. There were some pre-existing word docs in
> the doc directory and one of them had the word summary in it.
> I can't get it to work now either, except with html. Once upon a time it
> did work on RTM.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "ddaiker" <ddaiker@.gmail.com> wrote in message
> news:1166556861.260065.50920@.t46g2000cwa.googlegro ups.com...
>

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 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);