how to rename files before send files?

I'm using script task to rename files inside foreach loop files,code like shown below

Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))

dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "\" & newname

Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"

I tried to use file system task to perform that,but failed either

Rename file operation in file system only can rename a file in a specified location,who can help me?

TIA

Have you looked at what outputname does contain? Does the file specified exist as it should for the FTP task?

The File System Task can use variables, which themselves can be used to supply expressions, see the EvaluateAsExpression property and set the Expression property. This way you can use dynamic paths in the File System Task.

sql

How to reference in an expression a cell in a table

Want to reference from a cell on one table to cell in another table.
What is the specific code to do this?=ReportItems!<TextboxName>.Value
<TextboxName> is case-sensitive!
Charles Kangai, MCDBA, MCT
"Smartbiz" wrote:
> Want to reference from a cell on one table to cell in another table.
> What is the specific code to do this?

How to reference datasets in custom code

Can datasets associated with a report be referenced in custom code? If so,
how? I've tried referencing them like one does in expressions in the report,
e.g., Fields!Personal_Amt_Paid.Value, but this does not work. I get the
error
"There is an error on line 6 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."I doubt you can do it... The way you reference a parameter in code is
Report.Parameters!whatever...
So try Report.Dataset.something... How would it know which row?... probably
will not work...
you'll just have to pass the values in as parameters.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Kilmer" <Bob.Kilmer@.S.com> wrote in message
news:uODIYYrnFHA.1048@.tk2msftngp13.phx.gbl...
> Can datasets associated with a report be referenced in custom code? If so,
> how? I've tried referencing them like one does in expressions in the
> report,
> e.g., Fields!Personal_Amt_Paid.Value, but this does not work. I get the
> error
> "There is an error on line 6 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
>

How to refer to Custom function in a query

I want to be able to refer to a custom function in a query
The Function is defined in the report proprties "Code" tab as follows:
public shared function MyCustomer as String
Return "ALFKI"
end function
I have tried different query formats, and I currently have:
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
I am using the Northwind database as an example
Though the code compiles, I am unable to refer to any of the fields in
"customers"
I know there are other ways to accomplish this. What I am interested in
is how to refer to user defined code in QueriesYou might be able to pull a field list first with simply
SELECT * FROM customers
and then change the SQL to include the code. You might want to try the
latter using View Code and see of that gets around the auto-population of
fields step.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109783555.987331.113460@.g14g2000cwa.googlegroups.com...
>I want to be able to refer to a custom function in a query
> The Function is defined in the report proprties "Code" tab as follows:
> public shared function MyCustomer as String
> Return "ALFKI"
> end function
> I have tried different query formats, and I currently have:
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> I am using the Northwind database as an example
> Though the code compiles, I am unable to refer to any of the fields in
> "customers"
> I know there are other ways to accomplish this. What I am interested in
> is how to refer to user defined code in Queries
>|||Thanks, that works. A bit "klugy" but it works!
So, I set my query to
select * from customers, then execute the query, then click another
tab, causing the field list to populate, then switch back to the data
tab and change it to
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
Behind the scenes that is accomplishing the step of adding the dataset
definition to my RDL|||I'm glad it's working.
I found that editing directly in the data tab works fine until you have a
complex SQL statement that uses its own declared variables (which for SQL
Server look similar to parameters: @.ParmName). When you add parameters
manually to the dataset, sometimes the data tab editing blows away those
manually entered parameters. That's when I use Bruce's suggestion to edit
SQL using View Code.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109862516.895060.241340@.l41g2000cwc.googlegroups.com...
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||I have a similar issue however I am using a DB2 stored procedure so I dont
have the option to manipulate the sql. I was trying to put a translation on
the parameters list somehow and that doesnt seem to work although in the
Hitchhikers Guide it states that you can use and expression anywhere you have
the opportunity presented by <expression> or Fx ... which I see as an option
on the datasource parameters tab. I need to translate the client I receive
in as a parameter into a different value.
On the dataset parameters tab I have Parameter Name PM_Client_Code and the
value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
The function TranslateClient is this:
Function TranslateClient(ByVal clientnum As String) As String
TranslateClient = "ABC"
End Function
It doesnt work. Although the code works in vb .net just fine.
"cab0san" wrote:
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||That looks good to me and should work. I suggest creating a report just with
your parameter and a textbox and assign this expression to the textbox (so
you can see what happens). Make sure that your parameter is exactly the name
you have here, the parameter is case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:0EF45D79-4C8A-4B99-9D5A-30610FFB1F9D@.microsoft.com...
> I have a similar issue however I am using a DB2 stored procedure so I dont
> have the option to manipulate the sql. I was trying to put a translation
on
> the parameters list somehow and that doesnt seem to work although in the
> Hitchhikers Guide it states that you can use and expression anywhere you
have
> the opportunity presented by <expression> or Fx ... which I see as an
option
> on the datasource parameters tab. I need to translate the client I
receive
> in as a parameter into a different value.
> On the dataset parameters tab I have Parameter Name PM_Client_Code and the
> value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
> The function TranslateClient is this:
> Function TranslateClient(ByVal clientnum As String) As String
> TranslateClient = "ABC"
> End Function
> It doesnt work. Although the code works in vb .net just fine.
> "cab0san" wrote:
> > Thanks, that works. A bit "klugy" but it works!
> >
> > So, I set my query to
> >
> > select * from customers, then execute the query, then click another
> > tab, causing the field list to populate, then switch back to the data
> > tab and change it to
> > ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> > "'"
> > Behind the scenes that is accomplishing the step of adding the dataset
> > definition to my RDL
> >
> >

How to reduce the time when using a variable

Hi,

I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @.i and now my select statement is like this: 'Select substring(column_name,@.i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.

Why it is like this? Is there any way I can reduce the time taken to execute each iteration?

Thanks.

how many records of this kind are you processing.

the number of records must ne put into consideration.

a cursor that iterate with every records first must be put in place

then place the while loop inside every fetch of the cursor

do the processing(while loop) of the column

i think there should be two loops to handle this case.

one that iterates with the records

and one that iterates for every codes in the the column.

if this is an SP. a with recompile option will make your query faster

regards.

|||

What about

INSERT INTO table1

SELECT SUBSTRING(column_name,1,3) from table WHERE SUBSTRING(column_name,1,3) <> ''

UNION

SELECT SUBSTRING(column_name,4,3) from table WHERE SUBSTRING(column_name,4,3) <> ''

UNION

...

? Copy/paste above line until substring index matches column_name size; or, if you don't want to hardcode it - construct this query referring to column_name size and run it through EXEC.

This could be faster - try it out...

|||

If you are using SQL Server 2005 then you can do the following:

1. Create a table-valued function that takes a string and splits it based on space character. You can search the web for several sample functions that does this operation

2. Use it in a SELECT statement using APPLY operator like:

insert into tbl(i, ...)

select s.val

from table as t

cross apply splitstr(t.column_name) as s

In SQL Server 2000, you can do similar operation except there is no APPLY operator so you cannot use a TVF. You can use a table of numbers to split the string and then do a cross join with this table instead. Or the approach using UNION as suggested in another post will also work. Make sure you use UNION ALL though for better performance and to eliminate the unnecessary sorting.

|||Here is an article on handling arrays and lists - quite a few methods and instruments, may be useful.|||

Hi Umachandar,

Your tip was very helpful to me. Now the whole process completes in 2 mins. Thx.

sql

how to read the query in store procedure

i wrote a stored procedure here is the code

CREATE Procedure dm_deleteBBPost
(
@.TopicIDnumeric,
@.ForumIDnumeric

)
As
declare @.netCount as int

BEGIN
Select @.netCount=count(*) from tablename where TopicID=@.TopicID

update tablename set TotalTopics=TotalTopics-1,TotalPosts=TotalPosts-@.netCount where ForumID=@.ForumID

Delete From tblBBPosts Where TopicID = @.TopicID
Delete From tblBBTopics Where TopicID =@.TopicID

end
declare @.pr_ForumsToday as varchar

////////// select Top 1 * from tablename order by column name desc
GO

IN that last line one row will be displayed. how can i read one of the field from the query (selct top1 etc)

Quote:

Originally Posted by madhu7sudan

i wrote a stored procedure here is the code

CREATE Procedure dm_deleteBBPost
(
@.TopicIDnumeric,
@.ForumIDnumeric

)
As
declare @.netCount as int

BEGIN
Select @.netCount=count(*) from tablename where TopicID=@.TopicID

update tablename set TotalTopics=TotalTopics-1,TotalPosts=TotalPosts-@.netCount where ForumID=@.ForumID

Delete From tblBBPosts Where TopicID = @.TopicID
Delete From tblBBTopics Where TopicID =@.TopicID

end
declare @.pr_ForumsToday as varchar

////////// select Top 1 * from tablename order by column name desc
GO

IN that last line one row will be displayed. how can i read one of the field from the query (selct top1 etc)

Try this...

SELECT TOP 1 COLUMNNAME FROM TABLENAME ORDER BY COLUMN NAME DESC

How to read the error message #Error.

Hi,
My custom code i have written try catch end try block and in the catch
section just i returned the err.description. But locally it was working fine.
Once deployed it was not working fine. just showing #error. Any help would
be appreciated.
Regards,
Sri.On Jun 25, 2:36 pm, Sriman <Sri...@.discussions.microsoft.com> wrote:
> Hi,
> My custom code i have written try catch end try block and in the catch
> section just i returned the err.description. But locally it was working fine.
> Once deployed it was not working fine. just showing #error. Any help would
> be appreciated.
> Regards,
> Sri.
This is a long shot, but you could try checking the Event Viewer:
Application Errors log -or- you could try adjusting the Custom Errors
section of the Reports or ReportServer virtual directories (via: Right-
click -> Properties -> Custom Errors tab) -or- add custom code to the
report (via: Layout view -> Report drop-down -> Report Properties... -
> Code). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

how to read from sql data set

Code Snippet

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select MaxID,ValueName from tblSettings where ID=1", cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

the ds.Tables(0).Columns("MaxID") showing a value like this. ( Please tell how to read from sql data set)

{System.Data.DataColumn}

AllowDBNull: True

AutoIncrement: False

AutoIncrementSeed: 0

AutoIncrementStep: 1

Caption: "MAxid"

ColumnMapping: Element {1}

ColumnName: "MAxid"

Container: Nothing

DataType: {Name = "Decimal" FullName = "System.Decimal"}

DateTimeMode: UnspecifiedLocal {3}

DefaultValue: {System.DBNull}

DesignMode: False

Expression: ""

ExtendedProperties: Count = 0

MaxLength: -1

Namespace: ""

Ordinal: 0

Prefix: ""

ReadOnly: False

Site: Nothing

Table: {System.Data.DataTable}

Unique: False

Replace your SQL statement with this:

"Select isNull(MaxID, 1) As GetMaxID, ValueName from tblSettings where ID=1"

No need to check null value in your VB code.

How to read database compatibility level from c#

I'm trying to access the db compatibility level of a database from c# code.
If you execute sp_dbcmptlevel with no parameters, the current level is
returned to the output stream. My problem is how to get at this from c#
since it is not a scalar or tabular result. Any help gratefully received
JamesLook like it's returned via raiserror(). You need to catch it via
InfoMessage.
http://msdn.microsoft.com/library/d...ctionEvents.asp
-oj
"James E. Clarke" <james@.r> wrote in message
news:OacPMeLOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I'm trying to access the db compatibility level of a database from c#
> code. If you execute sp_dbcmptlevel with no parameters, the current level
> is returned to the output stream. My problem is how to get at this from c#
> since it is not a scalar or tabular result. Any help gratefully received
> James
>

How to reach report variables from custom code

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

I could try and guess what you are doing but chances are I wouldn't be close. Someone might be able to suggest something if you give the background/intention of using those two global reporting service parameters.|||Hello Mguvu,

sorry for replying so late, but I (almost) gave up on looking into the forum...

I'll elaborate on my problem/intentions:
I want to localize the output of textfields in my reports. As there is no assistance from the SSRS for this requirement, I wrote my own assembly which contains the following method:

public static string Localize(string reportName, string key, string language)

This method gets a localized string from a database for a given report, key and language (culture). In the report definition I'd like to shorten the call to this method to something like this

Code.Localize("myKeyValue")

For this to work, I'd need access to the global report variables "Globals!ReportName" and "User!Language" from custom code

Function Localize(ByVal key As String) As String
Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

But this is not working....

werner

|||

You should be able to pass an instance of Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals to your function. No?

(I think that's the right class, it might not be)

>L<

|||Actually I have a working version of my custom code and it looks like this

Function Localize(ByVal report As String, ByVal key As String, ByVal language As String) As String
Return RemoteReportUtil.Localizer.Localize(report, key, language)
End Function

In my report definition I call it in the following way

Code.Localize(Globals!ReportName, "ReportType", User!Language)

This works just fine, but is a little to verbose for my taste. I'd really like to just write Code.Localize("ReportTitle") in my report definition and reference the "global report variables" Globals!ReportName and User!Language directly in my custom code:

Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)

But if I do this, the preview function of the report designer fails with the message:

An error occurred during local report processing.
The definition of the report '/Report' is invalid.
There is an error on line 2 of custom code:[BC30469] Reference to a non-shared member requires an object reference.

werner
|||

>>Actually...

It helps if you post your real question . And I'm not sure you have responded to my suggestion, which I do think will work for you, so I will elaborate.

These things are globals after all, they don't change for the life of the report. You should be able to initialize several variables with these values in the Report Code and never worry about them again.

I will illustrate by storing the information to a var up front, but you should be able to send this information into an instance of your class ONCE and have it forever.

1. I'm going to declare my instance of Global at the top of Code, like this:

Code Snippet

Public myGlobals AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals = Nothing

... your instance is not necessarily an instance of Globals, it could be an instance of your custom object that has a member of type Globals.

OK?

2. Now I have a function called Initialize(). In my example it looks like this:

Code Snippet

Public Function Initialize(ox AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals ) AS String
If IsNothing(myGlobals) Then
myGlobals = ox
End If
Return ""
End Function

... in your version it might instantiate your custom object and call a Set... function of that object, passing Globals, where it exists for future use. That is why I bothered with the ISNothing() check. I'm not sure if this would work, because I'm not trying it right now, so you could always use the same MyGlobals strategy I'm showing here.

3. Now you put this function in your page header, on any expression you like. IOW, one object at the top of the page gets to look like this:

Code Snippet

= Code.Initialize(Globals) & YourRealExpression

4. Now my Code Localize function can talk to myGlobals!ReportName, etc, and if you don't store such a reference to your custom object then it just passes on a reference to myGlobals!ReportName rather than Globals!ReportName.

You don't really need the VB Code function at all -- but if you really want to have it, you can have it say:

Code Snippet

Return RemoteReportUtil.Localizer.Localize(key, User!Language)

And in fact if you saved a a User reference during Initialize, or even just had another var that saved User!Language, as well as a Globals reference, your class' Localize function would only need one parameter...

5. Why I say that you don't need the VB Code function at all:

It might work just fine at this point to have your report expressions themselves look like this, with no intermediary :

Code Snippet

RemoteReportUtil.Localizer.Localize(Code.myGlobals!ReportName, "This key", Code.myUser!Language)

.. or some other variant, depending on what you've saved, such as:

RemoteReportUtil.Localizer.Localize(Code.myGlobals, "This key", Code.myUserLanguage)

... or, if you've got that object instantiated and initialized with its own global and user or global and language members, your report layout expressions only have to say:

Code Snippet

oRemoteReportUtil.Localizer.Localize("This key")

HTH,

>L<

|||Yes, now it works!
Thank you very much Lisa!

Regards,
werner
|||

You're welcome.

In general, though, if those are the only two items you need (report item and user language) I would just store those two string items, there's no real need to have access to the two objects repeatedly over the life of the report. Or you could pass the objects once to your custom object instance in Initialize and it could just store all the properties it could need, rather than the object itself. This seems more efficent and neater in terms of possible object cleanup.

>L<

How to reach report variables from custom code

Hello,

I'd like to use Globals!ReportName and User!Language in custom code like this:

Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?

Thanks!

Werner

I could try and guess what you are doing but chances are I wouldn't be close. Someone might be able to suggest something if you give the background/intention of using those two global reporting service parameters.|||Hello Mguvu,

sorry for replying so late, but I (almost) gave up on looking into the forum...

I'll elaborate on my problem/intentions:
I want to localize the output of textfields in my reports. As there is no assistance from the SSRS for this requirement, I wrote my own assembly which contains the following method:

public static string Localize(string reportName, string key, string language)

This method gets a localized string from a database for a given report, key and language (culture). In the report definition I'd like to shorten the call to this method to something like this

Code.Localize("myKeyValue")

For this to work, I'd need access to the global report variables "Globals!ReportName" and "User!Language" from custom code

Function Localize(ByVal key As String) As String
Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function

But this is not working....

werner

|||

You should be able to pass an instance of Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals to your function. No?

(I think that's the right class, it might not be)

>L<

|||Actually I have a working version of my custom code and it looks like this

Function Localize(ByVal report As String, ByVal key As String, ByVal language As String) As String
Return RemoteReportUtil.Localizer.Localize(report, key, language)
End Function

In my report definition I call it in the following way

Code.Localize(Globals!ReportName, "ReportType", User!Language)

This works just fine, but is a little to verbose for my taste. I'd really like to just write Code.Localize("ReportTitle") in my report definition and reference the "global report variables" Globals!ReportName and User!Language directly in my custom code:

Return RemoteReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)

But if I do this, the preview function of the report designer fails with the message:

An error occurred during local report processing.
The definition of the report '/Report' is invalid.
There is an error on line 2 of custom code:[BC30469] Reference to a non-shared member requires an object reference.

werner
|||

>>Actually...

It helps if you post your real question . And I'm not sure you have responded to my suggestion, which I do think will work for you, so I will elaborate.

These things are globals after all, they don't change for the life of the report. You should be able to initialize several variables with these values in the Report Code and never worry about them again.

I will illustrate by storing the information to a var up front, but you should be able to send this information into an instance of your class ONCE and have it forever.

1. I'm going to declare my instance of Global at the top of Code, like this:

Code Snippet

Public myGlobals AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals = Nothing

... your instance is not necessarily an instance of Globals, it could be an instance of your custom object that has a member of type Globals.

OK?

2. Now I have a function called Initialize(). In my example it looks like this:

Code Snippet

Public Function Initialize(ox AS Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Globals ) AS String
If IsNothing(myGlobals) Then
myGlobals = ox
End If
Return ""
End Function

... in your version it might instantiate your custom object and call a Set... function of that object, passing Globals, where it exists for future use. That is why I bothered with the ISNothing() check. I'm not sure if this would work, because I'm not trying it right now, so you could always use the same MyGlobals strategy I'm showing here.

3. Now you put this function in your page header, on any expression you like. IOW, one object at the top of the page gets to look like this:

Code Snippet

= Code.Initialize(Globals) & YourRealExpression

4. Now my Code Localize function can talk to myGlobals!ReportName, etc, and if you don't store such a reference to your custom object then it just passes on a reference to myGlobals!ReportName rather than Globals!ReportName.

You don't really need the VB Code function at all -- but if you really want to have it, you can have it say:

Code Snippet

Return RemoteReportUtil.Localizer.Localize(key, User!Language)

And in fact if you saved a a User reference during Initialize, or even just had another var that saved User!Language, as well as a Globals reference, your class' Localize function would only need one parameter...

5. Why I say that you don't need the VB Code function at all:

It might work just fine at this point to have your report expressions themselves look like this, with no intermediary :

Code Snippet

RemoteReportUtil.Localizer.Localize(Code.myGlobals!ReportName, "This key", Code.myUser!Language)

.. or some other variant, depending on what you've saved, such as:

RemoteReportUtil.Localizer.Localize(Code.myGlobals, "This key", Code.myUserLanguage)

... or, if you've got that object instantiated and initialized with its own global and user or global and language members, your report layout expressions only have to say:

Code Snippet

oRemoteReportUtil.Localizer.Localize("This key")

HTH,

>L<

|||Yes, now it works!
Thank you very much Lisa!

Regards,
werner
|||

You're welcome.

In general, though, if those are the only two items you need (report item and user language) I would just store those two string items, there's no real need to have access to the two objects repeatedly over the life of the report. Or you could pass the objects once to your custom object instance in Initialize and it could just store all the properties it could need, rather than the object itself. This seems more efficent and neater in terms of possible object cleanup.

>L<

how to query rows followed by specific rows

we have transaction data as below

id tcode

1 AB

2 AB

3 CD

1 EF

2 AB

2 EF

4 GH

3 AB

We want to query only rows with transaction code AB followed by transaction code EF. I mean, when we query the sample data above the result should contain only rows in bold.

You are going to find this to be virtually impossible.

There is NO guarantee that SQL Server will store consecutive entries in contigious space, e.g, row order cannot be relied upon -UNLESS you create a CLUSTERED index. And a clustered index will most certainly change the row sequence.

OR, there is another field in the table that indicates row order, i.e., an IDENTITY field.

|||

You can use the following query...But take care Arnie Rowland suggestions..

Code Snippet

select *,Identity(int,1,1) as RowId into #Data From data2

Select id,tcode From #Data
Where rowid in
(
Select A.RowId from #Data A
Join #Data B on A.RowId = B.RowId -1 And A.id=B.Id And A.tcode='AB' And B.tcode='EF'
Union
Select B.rowid from #Data A
Join #Data B on A.RowId = B.RowId -1 And A.id=B.Id And A.tcode='AB' And B.tcode='EF'
)

How to Query a Project Server DB

I am new at this - so please forgive me. I understand the basics of writing
SQL code, but am not familiar with where the code should go or how to run it
.
I simply am trying to write a query to extract some data from Project Server
database.
I have SQL Server 2000 loaded. Where should the code for this query go, and
how do I execute it?SQL Server ships with what are called "Client TOols" , one of which is query
analyzer. This is probably your best bet top get started. As firsts step,
You will need to "Connect" to the database server softwre to do anything.
Generaly that requiresthat you know the Machine (Called "Server"), the
database on the server, what userID or Login, and the Users password...
"Diane" wrote:

> I am new at this - so please forgive me. I understand the basics of writi
ng
> SQL code, but am not familiar with where the code should go or how to run
it.
> I simply am trying to write a query to extract some data from Project Ser
ver
> database.
> I have SQL Server 2000 loaded. Where should the code for this query go, a
nd
> how do I execute it?