Can I Insert Line Numbers?

Hi. We are creating long reports that need to have unique reference numbers
assigned to each row. For example, the values 1-99 below would be computed at
report run time:
1 Transaction-A
2 Transaction-B
:
99 Transaction-x
I thought I could solve this by using a global variable and function within
the report code block such as:
Private Dim itemCount As Integer
Public Function GetCount() As Integer
itemCount += 1
Return itemCount
End Function
And then create a calculated field (Called LineNumber) that calls the
function:
=Code.GetCount()
But what I get is a seemingly random assignment of line numbers; I assume
due to ReptSvcs resolving the rows in a non-linear fashion.
Any thoughts on how I can get ordered line numbers?Try using static variables in the code
>--Original Message--
>Hi. We are creating long reports that need to have unique
reference numbers
>assigned to each row. For example, the values 1-99 below
would be computed at
>report run time:
>1 Transaction-A
>2 Transaction-B
> :
>99 Transaction-x
>I thought I could solve this by using a global variable
and function within
>the report code block such as:
>Private Dim itemCount As Integer
>Public Function GetCount() As Integer
> itemCount += 1
> Return itemCount
>End Function
>And then create a calculated field (Called LineNumber)
that calls the
>function:
>=Code.GetCount()
>But what I get is a seemingly random assignment of line
numbers; I assume
>due to ReptSvcs resolving the rows in a non-linear
fashion.
>Any thoughts on how I can get ordered line numbers?
>
>
>.
>|||The problem with statics in embedded code is that they are shared
among all instances of the report that are running. If two of the
reports using the static execute at the same time the results could
interleave.
--
Scott
http://www.OdeToCode.com
On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
wrote:
>Try using static variables in the code|||Thanks for the tips, but I think I got it working.
I used the same code as I mentioned below, but added an "ORDER BY"
quailifier to the dataset to sort the data in the same sequence as the report
displayed. This gave me sequentual numbers.
"Scott Allen" wrote:
> The problem with statics in embedded code is that they are shared
> among all instances of the report that are running. If two of the
> reports using the static execute at the same time the results could
> interleave.
> --
> Scott
> http://www.OdeToCode.com
> On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
> wrote:
> >Try using static variables in the code
>sql

Can I force SQL Server to use the CONTAINS operator first?

If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:

SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0

How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:

SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0

It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.

Actually CONTAINS is a predicate that can be used with the WHERE clause there are two versions of it defined by ANSI SQL and implemented by Microsoft CONTAINS and CONTAINSTABLE, the other two FULLTEXT predicates are FREETEXT and FREETEXTTBALE, try the link below for details. Hope this helps.

http://technet.microsoft.com/en-us/library/ms187787.aspx

|||

Hi Caddre,

Thanks for the reply -- but that didn't seem to have anything to do with my question!

|||

(It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.)

It does because since CONTAINS is a predicate and not a clause what you can force it to do is determined by the restrictions and limitations listed in the link I provided.

|||no, you will have no control over the freetext functionality as in SQL Server 2005 the query in FT searches still is not connected to the query engine and therefore cannot be optimized and tweaked in your requested way.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

OK, I found a way to do it:

DECLARE @.ftQuery nvarchar(32)

SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), @.ftQuery)
AND fn_TestCol(col1) = 0

OPTION (OPTIMIZE FOR(@.ftQuery = 'foo'))

This structure allows the optimizer to make assumptions about what might be in @.ftQuery -- so it correctly assumes that the CONTAINS predicate will be a faster starting point than the udf, and runs with the right plan.

|||

_jesse,
Interesting.. this as long been a "by design" problem with SQL FTS since SQL Server 7.0. You never said which version of SQL Server you are using, but I'm assuming it is SQL 2000 and not SQL Server 2005. Correct? Can you provide more details on your UDF fn_TestCol? How many rows do you have in your real table or your myTable example?

Thanks,
John

John T. Kane
Search Evangelist, Intellisearch
email: john.kane@.intellisearch.no

|||

Hi John,

It is SQL 2005.

The problem, as I understand it, is this: when you pass a parameter to CONTAINS, as in

CONTAINS(col1, @.searchText)

the optimizer isn't able to recognize that the CONTAINS operator is a good place to start. The execution plan changes if you replace @.searchText with a literal string, as in:

CONTAINS(col1, 'foo')

In my case, the table being scanned was relatively small (20,000 rows), but the UDF is slow -- it's doing a bunch of string manipulation & comparison. Since the CONTAINS clause is likely to be very selective, the query is very fast if you start with CONTAINS, then run the UDF on the few rows matching the fts, but very slow if you do it the other way around.

Declaring the @.searchText parameter as nvarchar, then adding the OPTIMIZE FOR hint gets it to consistently run in the right order.

can database name be assigned as a variable?

I need to have a query like below to run to select all the fields from different databases that each database have a table named 'table1'

select * from test...table1

select * from test1..table1

I have the following code sample:

declare @.dbname varchar(50)
set @.dbname='test'
select @.dbname
select * from @.dbname..table1

However I received the error messge when I ran this code. Can anyone help to resolve this?

Thank you!

Hi

One way of doing this is to build the query dynamically and use the Exec, as shown in the example below:

Declare @.xQuery varchar(1000)
Select @.xQuery = 'Select * from test..table1'
Exec(@.xQuery)

Select @.xQuery = 'Select * from test1..table1'
Exec(@.xQuery)

---

Best regards
Georg
www.l4ndash.com - Log4Net Dashboard / Log4Net Viewer

Can connect to sqlexpress but not the DB ?

whenever i try and choose the database i wish to work with like below. I end up with an error message about the user not being associated with a trusted sql server connection

cnStr = @."Data Source=server\SQLEXPRESS;Initial Catalog=sqldata;User ID=blah\me";

same with this one

cnStr = @."Data Source=STOCKYARD\SQLEXPRESS;Initial Catalog=DB;Trusted_Connection=Yes;";

i figured this was all permission based so i kept dorking around with security adding my user name to anything i could find, and also went through and dozen or so connection strings and found when i take out the DB name that i can open a connection.... as the code below works...

cnStr = @."Data Source=Server\SQLEXPRESS;Trusted_Connection=Yes;";

cn.Open();

I plan on importing another database so how will i later on choose between the two, i notice i can create logins only at the security for the sqlexpress and not for my database? did i mess up how i created the database?
i have added my username and given permissions to the security for the database i wish to connect to, but is there another step im missing? i would keep trucking but i would like to have multiple DB on the sql express and pick and choose at the code level, maybe im misunderstanding what the catalog should include or i can change the connections database at another point?

You need to have specific permissions in a database given to a Database User, and that user needs to be Associated with a Server Login. My guess is that you don't quite have things connected together correctly yet. Check ou this BOL topic that helps lay out how Windows Users, SQL Logins and Database Users all fit together.

Once you have things associated together, you should be able to work in a database.

Mike

|||

You cannot use "User ID=blah\me". If you are trying to use your domain account, just use "Trusted_Connection=Yes". User ID is used for SQL Authentication, and you cannot specify a domain account.

You must have login permission for the "Initial Catalog=DB", otherwise, you won't be able to login. Logins are for server and user id is for database. You can more inside here: http://builder.com.com/5100-6388-5055543.html

some quote:

<<<<While logins belong to servers, users belong to databases. A user ID identifies a particular user of
a particular database. Also, users are specific to databases—that is, user Fred in the Northwind database
is not the same as user Fred in the pubs database, although both Freds may be associated with the same login.>>>>>

Can anyone tell me why this returns an empty value?

@.Names is a query string passed in, I need to count the number of records as a result of the below query/

Dim

testAsStringDim sqlConnection3AsNew SqlConnection("data Source=EQ-520-WEB\SQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")Dim cmdAsNew SqlCommand

Dim returnValueAsObject

cmd.CommandText =

"SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @.Names)"

cmd.CommandType = Data.CommandType.Text

cmd.Connection = sqlConnection3

cmd.Parameters.Add(

"@.Names", Data.SqlDbType.NVarChar)

sqlConnection3.Open()

cmd.Parameters(

"@.Names").Value = testIf test =""ThenResponse.Write("An error occured")ExitSubElsereturnValue = cmd.ExecuteScalar()

sqlConnection3.Close()

Label6.Text =

"Number " & returnValue

Hi.

your steps is right but you must to be sure the (test) value that have the correct value.

also you must be sure if there is data in the specific value.for example if you trace that test=7 check if there is data must retrive in the query

SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager =7)

Hopes that help.

Can anyone help with this query...

Hi,I'm moving across to SQL Server after using MySQL and having trouble with one particular query. My tables are outlined below. I'm trying to get hold of the names of bands playing the most recently added shows but can't quite get there.

Shows table = Id.. some other columns.. DateAdded
Bands table = Id, Name
Playing table = Id, Show_Id, Band_Id

My query at the moment is

SELECT Bands.Name FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1
ORDER BY Shows.DateAdded DESC

Which gives me the right results but will return duplicate band names if there is more than one new show for that band. I've tried doing SELECT DISTINCT Bands.Name but get an error saying "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". I also tried using a GROUP BY clause but I can't seem to combine it with the order by clause without getting an error.

I've tried creating a view which handles the joining and selecting distinct Names from the view but this is ignoring the ordering and just returns band names in alphabetical order.

I hope you understand my problem and I really HOPE someone has a solution even if it means returning some extra columns which I can just ignore in my page logic. Thanks.

SELECT Bands.Name, MAX(Shows.DateAdded) FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1

GROUP BY Bands.Name
ORDER BY Shows.DateAdded DESC

|||

Thanks for the reply but that doesn't work either. The error I get the same I error I was getting when I tried using GROUP BY...

Msg 8127, Level 16, State 1, Line 1

Column "Shows.DateAdded" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

|||

You could try a slight variation of that:

SELECT Bands.Name, MAX(Shows.DateAdded) FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1

GROUP BY Bands.Name
ORDER BY 2 DESC

or

SELECT Bands.Name, MAX(Shows.DateAdded) FROM Shows
INNER JOIN Playing ON Shows.Id = Playing.Show_Id
INNER JOIN Bands ON Bands.Id = Playing.Band_Id
AND Playing.Headlining = 1

GROUP BY Bands.Name
ORDER BY MAX(Shows.DateAdded) DESC

|||They both work. Thank you.

Can a ControlParameter be used to supply a parameter for a stored procedure?

The code below is an attempt at using the value from a dropdownlist to feed into stored procedure outlined in the Select command. I have seen examples where the control parameter is used with a select command but nothing where the parameter has to be fed into a stored procedure.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString=".."
SelectCommand="procCAGetCustomerKPIs"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameterName="iCustomerGroupId"ControlID="CustomerFilterList"PropertyName="SelectedValue"/>
</SelectParameters>
</asp:SqlDataSource>

cheers-jim.

Yes. Normally, I would use the designer, and hit "refresh parameters", it will then generate a list of all the parameters the stored procedure is expecting. From that point, change the parameters from type "None" to "Control", etc.|||Forgot to add autoPostBack=true to the dropdownlist control!!