Multi value parameter help needed

Hi
I have a problem my database table has my parameter value
UserId integer
I need to set up my report to accept more than one id at a time (ex.
2, 44, 5). I went through the report parameters in reporting services
and set the parameter to accept multi value. When I tried to run the
report I was allowed to put in multi values with no problem, but it is
returning an error saying that it cannot convert data type nvarchar to
int. Any ideas?
thanks for the help
twoI'm guessing that your multi-value parameter has a lable and a value.
You probably set the parameter up wrong in Report Parameters. You
have a select with (label, value) pairs (apple, 2), (orange, 44),
(peach, 5). When you select apple, orange and peach you are expecting
2, 44, and 5, but you have probably set it up to pass in apple,
orange, peach. Which leads to the conversion problem you are seeing.
Are the options coming from a datasource or did you just type them
into the report parameters section?|||On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> I'm guessing that your multi-value parameter has a lable and a value.
> You probably set the parameter up wrong in Report Parameters. You
> have a select with (label, value) pairs (apple, 2), (orange, 44),
> (peach, 5). When you select apple, orange and peach you are expecting
> 2, 44, and 5, but you have probably set it up to pass in apple,
> orange, peach. Which leads to the conversion problem you are seeing.
> Are the options coming from a datasource or did you just type them
> into the report parameters section?
I am using a stored procedure that requires only one paramter which is
the id (int). The options for the parameters are not coming from a
datasource, Its pulling from my datasource that id (int) is a
parameter. So there is no label since the available values section is
set to non-queried.|||On Oct 5, 10:10 am, Twobridge <Twobri...@.gmail.com> wrote:
> On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> > I'm guessing that your multi-value parameter has a lable and a value.
> > You probably set the parameter up wrong in Report Parameters. You
> > have a select with (label, value) pairs (apple, 2), (orange, 44),
> > (peach, 5). When you select apple, orange and peach you are expecting
> > 2, 44, and 5, but you have probably set it up to pass in apple,
> > orange, peach. Which leads to the conversion problem you are seeing.
> > Are the options coming from a datasource or did you just type them
> > into the report parameters section?
> I am using a stored procedure that requires only one paramter which is
> the id (int). The options for the parameters are not coming from a
> datasource, Its pulling from my datasource that id (int) is a
> parameter. So there is no label since the available values section is
> set to non-queried.
I am thinking my problem isnt the parameter setting but my stored
procedure on the sqlserver. It is set up to accept a integer as it
parameter not an array of int. And to be honest i have no idea how to
set a stored procedure up to accept an array of integers.|||I have never used multi-select as a textbox that I put values in. I always
put in the values to select from, either a manual list I put in for the
parameter or a query. To make sure multi-select is working for you put in a
list of parameter values. This will make sure the issue is not your textbox
input.
The second issue I see is that you are trying to pass an multi-select list
to a stored procedure of type int. What the multi-select does is allow you
to multi-select from an option list. If you put in 1,12,34 into a listbox
this is by definition a string, it is not integers.
OK, now on to your stored procedure. There is absolutely no way to pass
multiple integers into a singe integer parameter. You are trying to get RS
to do something impossible. A way to look at this, if you cannot do it from
SQL Management Studio where you invoke the SP yourself, then there is no way
RS can do it either.
Also, for another problem what you want cannot be done without special code
in a stored procedure. If you have a query this would work:
select * from sometable where somefield in (@.id)
In this case the data type of id can be int.
Now with your stored procedure with a parameter of type int called id, it
only accepts a single integer.
Below is a post of mine from previous that describes the issue with
multi-parameters and stored procedures. What the problem is and how to get
around it:
>>>>>
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Twobridge" <Twobridge@.gmail.com> wrote in message
news:1191597037.153027.72250@.w3g2000hsg.googlegroups.com...
> On Oct 5, 9:30 am, jer...@.gmail.com wrote:
>> I'm guessing that your multi-value parameter has a lable and a value.
>> You probably set the parameter up wrong in Report Parameters. You
>> have a select with (label, value) pairs (apple, 2), (orange, 44),
>> (peach, 5). When you select apple, orange and peach you are expecting
>> 2, 44, and 5, but you have probably set it up to pass in apple,
>> orange, peach. Which leads to the conversion problem you are seeing.
>> Are the options coming from a datasource or did you just type them
>> into the report parameters section?
> I am using a stored procedure that requires only one paramter which is
> the id (int). The options for the parameters are not coming from a
> datasource, Its pulling from my datasource that id (int) is a
> parameter. So there is no label since the available values section is
> set to non-queried.
>|||On Oct 5, 10:41 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never used multi-select as a textbox that I put values in. I always
> put in the values to select from, either a manual list I put in for the
> parameter or a query. To make sure multi-select is working for you put in a
> list of parameter values. This will make sure the issue is not your textbox
> input.
> The second issue I see is that you are trying to pass an multi-select list
> to a stored procedure of type int. What the multi-select does is allow you
> to multi-select from an option list. If you put in 1,12,34 into a listbox
> this is by definition a string, it is not integers.
> OK, now on to your stored procedure. There is absolutely no way to pass
> multiple integers into a singe integer parameter. You are trying to get RS
> to do something impossible. A way to look at this, if you cannot do it from
> SQL Management Studio where you invoke the SP yourself, then there is no way
> RS can do it either.
> Also, for another problem what you want cannot be done without special code
> in a stored procedure. If you have a query this would work:
> select * from sometable where somefield in (@.id)
> In this case the data type of id can be int.
> Now with your stored procedure with a parameter of type int called id, it
> only accepts a single integer.
> Below is a post of mine from previous that describes the issue with
> multi-parameters and stored procedures. What the problem is and how to get
> around it:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Twobridge" <Twobri...@.gmail.com> wrote in message
> news:1191597037.153027.72250@.w3g2000hsg.googlegroups.com...
>
> > On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> >> I'm guessing that your multi-value parameter has a lable and a value.
> >> You probably set the parameter up wrong in Report Parameters. You
> >> have a select with (label, value) pairs (apple, 2), (orange, 44),
> >> (peach, 5). When you select apple, orange and peach you are expecting
> >> 2, 44, and 5, but you have probably set it up to pass in apple,
> >> orange, peach. Which leads to the conversion problem you are seeing.
> >> Are the options coming from a datasource or did you just type them
> >> into the report parameters section?
> > I am using a stored procedure that requires only one paramter which is
> > the id (int). The options for the parameters are not coming from a
> > datasource, Its pulling from my datasource that id (int) is a
> > parameter. So there is no label since the available values section is
> > set to non-queried.- Hide quoted text -
> - Show quoted text -
Thanks everyone who helped me out. Bruce you were correct that it was
more to do with my stored procedure that RS. I found a really good
example of a function that basically took in my string of numbers and
was able to break up the string into my needed id's . Here is a link
if anyone needs it.
http://www.sommarskog.se/arrays-in-sql-2005.html

Multi Value Parameter and Filtering

Hi All
I am trying to filter a table on my report based on a multi-value parameter,
I have tried several methods to no avail. When I use the following I do not
get an error but I do not get data showing on the report.
Expression is set to =Fields!Account.Value
Operator is set to In
Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
Can anyone help this newbie style question
RegardsI think you should be able to leave out the Join function.
Set the value as: =Parameters!Select_Individual_Account.Value
I do that in one of my reports and it works fine. Just make sure there is
not a (0) at the end of the paramerter.
"Are friends electric?" wrote:
> Hi All
> I am trying to filter a table on my report based on a multi-value parameter,
> I have tried several methods to no avail. When I use the following I do not
> get an error but I do not get data showing on the report.
> Expression is set to =Fields!Account.Value
> Operator is set to In
> Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> Can anyone help this newbie style question
> Regards
>
>|||Thanks for helping Matt
I now get the following error
The filter expression for the table cannot be performed,
cannot compare data of type system.string and system.object
any ideas
Thanks
Steve
"Matt M" wrote:
> I think you should be able to leave out the Join function.
> Set the value as: =Parameters!Select_Individual_Account.Value
> I do that in one of my reports and it works fine. Just make sure there is
> not a (0) at the end of the paramerter.
> "Are friends electric?" wrote:
> > Hi All
> >
> > I am trying to filter a table on my report based on a multi-value parameter,
> >
> > I have tried several methods to no avail. When I use the following I do not
> > get an error but I do not get data showing on the report.
> >
> > Expression is set to =Fields!Account.Value
> > Operator is set to In
> > Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> >
> > Can anyone help this newbie style question
> >
> > Regards
> >
> >
> >
> >

Multi Table Source

I am wondering how I can create an OLE DB Source component that can store a multi-table DataSet object. Is this something that is possible or do I need some custom object to do this? I'm sure I can create a multi-table destination object and create sources for each data table needed however, I need to get the data for 5 tables and do this about 30K times. I'm thinking this approach will perform better.

Here is what I've been trying to get working. (Note there is only one parameter that all the queries use - @.keyName)

SELECT * FROM Table1
WHERE (Key = ?)

SELECT * FROM Table2
WHERE (Key = ?)

SELECT * FROM Table3
WHERE (Key = ?)

SELECT * FROM Table4
WHERE (Key = ?)

SELECT * FROM Table5
WHERE (Key = ?)

TIA

Ian

You can have more than one OLE DB source on a given data flow. From there you can merge/union records as required.|||

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

|||

enizin wrote:

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

A SQL statement doesn't return a table. It returns a result set. Either write a SQL statement that selects from all of your tables and does the necessary joins or unions and then use that statement in an OLE DB source, or you can use an OLE DB source for each table -- which will have to be merged together to get one "result set."|||

Sorry, I'm used to referring to data tables within ADO.NET DataSets...

In the Management Studio, if I run this set of statements against the AdventureWorks database I can get a "dataset" containing each result set - all of which have different columns.

SELECT * FROM HumanResources.Employee WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeAddress WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeePayHistory WHERE EmployeeId = ?

It sounds like this wouldn't work in SSIS because one source cannot contain multiple result sets without performing a union as it can only contain one set of columns.

The reason for needing the data like this is I need to add/update/delete rows to/from each of these tables then save them to my destination database. For my purposes it sounds like using the multiple source option will be the best route.

Thanks for your help.

Multi Table Query

Does anyone know how to create a query using tables from different sql server database's? looking for the simplest solution.

I have two databases in sql server. Both are in the same "server registration"
How do i reference a table in another database?

Do i do something like this?

SELECT Database1.Table1.Fields, Database2.Table1.Fields
FROM Database1.table1.PKField INNER JOIN Database2.table1.FKField;You may try this

SELECT Database1.dbo.Table1.Fields,
Database2.dbo.Table1.Fields FROM
Database1.dbo.table1.PKField INNER JOIN
Database2.dbo.table1.FKField|||you might also want to look at the OPENQUERY funtion. buddu's suggestion will work but you can generate a buch of unwanted I/O if the table on the remote server is big.|||You only need to do the full naming convension in the FROM clause

SELECT x.column1,
x.column2,
y.column1,
y.column3
FROM database1.dbo.tableX x
JOIN database2.dbo.tabley y
ON x.pk = y.pk|||can anyone help me please...
what query/syntax could i use for retrieving fields from various tables in Access for my one FORM.?
Ive tried using every possible codes but then it didn't work out.
SHould i declare diffrent recordsets for accesing this?

Multi table query

Hi

Need hellp with some query I'm trying to develope.

How do I count the number of records from table X1, colum X1 and from table Y2, colum Y2?

I whould need it to presente it like this:

Total X1 Total Y2

1313412 12341324

Thanks for any help.

One way to do this would be similar to:

Code Snippet

SELECT

TotalX1 = ( SELECT count(1) FROM TableX1 ),

TotalY2 = ( SELECT count(1) FROM TableY2 )

sql

multi table or one big table?

I want to add my site a Hebrew English dictionary, I have a MS Access table with 55,000 words which I want to use.

I wondered if I should divide the table to little ones? (Lets say, to put each letter (a, b, c...) in different table. to check what the first word the user clicked and to get the data from this table. I just thought that 50,000 words is too much for each search, but I wonder if the code that I'll need to make the right table name will take longer?

Does 55000 rows is a big table? Should I worry about it or not?

Thanks...don't worry about it. 55000 rows isn't going to be too big of an issue. Just index the table based on any field you do a where clause on, and you'll be in good shape.|||Would you be interested in sharing your database of Hebrew words?

Multi table designations in sql string

I'm putting together a resultset in an sql string
(it will be used in asp.net) where I'm drawing from a few different
tables, and I do have a relation with the tblDeal and tblSalesrep ID :
''
strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.salesrep_id WHERE d.Orderdate = '" &
request.querystring("boarddate") & "';"
'''
I am not getting an error, but no response when running this sql string
(i did response.write the "boarddate" variable successfully, so thats
not the problem)
'
.NetSports> strSQLQuery = "SELECT d.salesrep_id,s.fname,s.lname,
> s.boardtotal_note,s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
> d.Orderdate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,b.Amount_Attempted
> FROM tblBankTrans as b,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.salesrep_id WHERE d.Orderdate = '" &
> request.querystring("boarddate") & "';"
If I read your post correctly, you are asking SQL Server to figure out your
boarddate as a parameter to your query.
If I am correct in your interpretation, perhaps your SQL query [VSS script]
(which should be a stored procedure) should look like this:
<sql>
IF EXISTS (
SELECT *
FROM SysObjects
WHERE Type = 'P'
AND Name = 'WhateverYouCallIt '
)
DROP PROCEDURE dbo.WhateverYouCallIt
GO
CREATE PROCEDURE dbo.WhateverYouCallIt (
@.boardDate INT
) AS
SELECT d.salesrep_id,s.fname,s.lname, s.boardtotal_note,
s.BoardTotal_BonusPerc, s.ID,s.Hire_date,s.Term_date,
d.Orderdate,d.salesOff_loc_ID,d.SplitGross, d.SplitRep_ID,
d.Sale_Type_ID,d.DBA_ID,b.Amount_Revenue,
b.Amount_Attempted
FROM tblBankTrans b,
tblDeal d
INNER JOIN tblSalesrep s
ON s.ID = d.salesrep_id
WHERE d.Orderdate = @.boardDate
GO
GRANT EXEC ON dbo.WhateverYouCallIt TO PUBLIC
GO
</sql>
Then you call it like this:
<vb.NET>
' Assumes that your connection string is in your Web.config or
App.config
' and that you have an Imports System.Configuration item in your code
class
Dim cnstr As String = ConnectionSettings.AppSetting("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim da As New SqlDataAdapter("dbo.WhateverYouCallIt", cn)
Dim dt As New DataTable()
With da.SelectCommand
.CommandType = CommandType.StoredProcedure
' Assumes that you have a VB variable called boardDate that is the
' same as the stored procedure's (above) @.boardDate parameter:
.Parameters.Add("@.boardDate", boardDate)
End With
da.Fill(dt)
' etc...
</vb.NET>
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||actually, I would probably like to see if the multi-table inner joins
are the correct way to make this sql string work, seeming that I am
also using the tblBankTrans table, but with no joins or relationships.

multi rows SELECT

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = TransactionID FROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

Quote:

Originally Posted by Cshrek

helo,
i have no problems executing that query, but it only returnes the last row from each table.
how would i be able to retrive all the rows?

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,
@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

SELECT @.VolID = VolID, @.TransactionID = VolIDFROM Transactions WHERE TransactionTime BETWEEN @.From AND @.To

SELECT * FROM Transactions WHERE TransactionID = @.TransactionID

SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID

RETURN
GO

hi
I am not clear with your code. try with this code .this is not exactly suit for ur requirement but this idea will help you

[code]
declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end|||hey, thanks.
wehn i try to run that code:

----------------------------------

ALTER PROCEDURE dbo.AccountTrakingSELECT

@.From smallDateTime,

@.To smallDateTime

AS

DECLARE @.VolID int

DECLARE @.TransactionID int

declare cur1 for select VolID from Transactions WHERE TransactionTime BETWEEN @.From AND @.To
open cur1
fetch next from cur1 into @.VolID
while @.@.fetch_status=0
begin
SELECT VolFrstNameEN, VolLastNameEN FROM VolMain WHERE VolID= @.VolID
fetch next from cur1 into @.VolID
end

----------------------------------
it all seems to be good, but i get that error:

"Msg 156, Level 15, State 1, Procedure AccountTrakingSELECT, Line 22
Incorrect syntax near the keyword 'for'."

and i defently have no idea what is that mean? or what am i doing worng?

appriciate your help.sql

Multi Row Insert

According to the CTP3 BOL the following code should work just fine, unfortunately is giving me the error message (below the code):

create table FamilyNames (

Id int identity,

[Name] varchar(30)

);

go

insert into FamilyNames values (

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen')

);

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Does anybody know why this code fails?

Hi,

Try

insert into FamilyNames values

('Andersson'),

('Ben-Gan'),

('Carlsson'),

('Davidsen');

Kind regards,

Wesley

|||Thanks.

Multi Page Report

Hi All,
I have two tables in a SQL 2000 database. One table contains a few text
fields and the other contains any overflow data from the original table.
I am required to print a one page report with the data from the first table
and then if there are any data in the overflow table, to automatically print
a second page containing the data from the second table.
Page 2 of the report doesn't look anything like page 1. It's layout is
different and it only contains the data from the second table.
These text fields (in the overflow) may be a max of 10K.
I'm new to RS and this requirement has been beating me up.
Any ideas?
Thanks.Two options. You can use either multiple datasets and put a page break
between them or you can use a sub report. Again with a page break between
them.
Bruce L-C
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:20F24200-5B1D-449F-BA9D-E9AD48C2B5B4@.microsoft.com...
> Hi All,
> I have two tables in a SQL 2000 database. One table contains a few text
> fields and the other contains any overflow data from the original table.
> I am required to print a one page report with the data from the first
> table
> and then if there are any data in the overflow table, to automatically
> print
> a second page containing the data from the second table.
> Page 2 of the report doesn't look anything like page 1. It's layout is
> different and it only contains the data from the second table.
> These text fields (in the overflow) may be a max of 10K.
> I'm new to RS and this requirement has been beating me up.
> Any ideas?
> Thanks.|||Thanks.
We actually started looking at the Sub-Report option.
Thanks for your help.
"Bruce Loehle-Conger" wrote:
> Two options. You can use either multiple datasets and put a page break
> between them or you can use a sub report. Again with a page break between
> them.
> Bruce L-C
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:20F24200-5B1D-449F-BA9D-E9AD48C2B5B4@.microsoft.com...
> > Hi All,
> >
> > I have two tables in a SQL 2000 database. One table contains a few text
> > fields and the other contains any overflow data from the original table.
> >
> > I am required to print a one page report with the data from the first
> > table
> > and then if there are any data in the overflow table, to automatically
> > print
> > a second page containing the data from the second table.
> >
> > Page 2 of the report doesn't look anything like page 1. It's layout is
> > different and it only contains the data from the second table.
> >
> > These text fields (in the overflow) may be a max of 10K.
> >
> > I'm new to RS and this requirement has been beating me up.
> >
> > Any ideas?
> >
> > Thanks.
>
>

multi lingual where clause

I have a table with nvarchar column. In query analyzer when I run query with foreign language words that are already there in database it dose not return any rows

To test it I returned rows with Select * table. Then from result window I copy chinese characters and put it in SQL where clause in SQL analyzer .When I run query it dose not return results.

What could be the cause..its SQL 2k

found it add N infront of characters|||

As kyus94 indicated, to use UNICODE characters, including Chinese, you must preface the string with the character [ N ].

For example,

SELECT

Column1,

Column2,

etc

FROM MyTable

WHERE Column3 = N'ThisCouldBeChinese'

(Note the character immediately before (no space) the string.)

sql

Multi Language Form

Hi,

I have a table in an MS SQL 2000 database that represents fields on a form.

CREATE TABLE [dbo].[TagData](
[FieldName] [nvarchar](255),
[UserID] [int] NOT NULL,
[Data] [nvarchar](255)
)

A requirement has come up where some of these fields must contain Hebrew, or any other unicode character, data and some of the fields will be English. How can I go about saving and retrieving this information.

The current solution is a legacy Classic ASP application and I suspect I am going to have to redo this in ASP.Net

Thanks,
Leon

Since the data type in the table is nvarchar, you can store any unicode values.

You select/insert into the table as normal way of inserting the data there will not be any difference in doing DML operations.

Sample insert statement is as follows:

INSERT INTO TagData values(N'Sample Field', 10, N'Sample Data'); --> please "N" is for specifying it as nvarchar.

When you want to display content in ASP.Net depending on the language setting, we have to use localization and Globalization concepts in ASP.Net

Following URL will provide you more info on the same in ASP.Net:

http://www.codeproject.com/useritems/localization.asp

|||

I found this link which helped a lot.

http://www.microsoft.com/globaldev/getWR/steps/wrg_codepage.mspx

sql

Multi column report as a subreport

I have a table report where amoung other things there is a list of names
from another data set. I don't want the names to print in one column because
the list maybe more than one page. I can create a multi column report for
just the names and when printed or print preview will display in multiply
columns (good). But when I add that report as a subreport to my main report
I believe it ignores the subreport's 'report properties' and uses the main
'report's properties'?
Any suggestions?
JohnI found this in 2005 Books online, I would guess that 2000 is the same.
Apparently the column setting is inherited from the parent report.
Denny
SQL Server 2005 Books Online
Writing Multi-Column Reports
Updated: 5 December 2005
You can design a report that uses a multi-column layout, similar to a
traditional newspaper column where data flows down multiple adjacent
columns. A multi-column layout applies to the entire report. It is not
possible to specify a multi-column layout on the top half of the report, and
a tabular layout on the bottom half of the report. When you specify a
multi-column layout, the report server creates each column as a series of
very narrow pages that are rendered in close sequence, giving the appearance
of multiple columns. Properties that you set at the page level are applied
to each column in the report. You can define as many columns you want.
For best results, use data regions that provide repeating rows of data (for
example, table or list box). A list box placed within a multi-column report
will display data from the top left of the page to the bottom left of the
page, and then continue the list in the adjacent column at the top of the
page. If you want to use text boxes or images, put them in a list so that
they repeat in each column.
If you are accustomed to using subreports to embed a separate report within
a parent report, be aware that you cannot use subreports to get the same
outcome in a multi-column layout. In a multi-column report, a subreport
inherits the column settings of the parent report. This means that if you
define a multi-column layout on a subreport, the subreport ignores the
column settings that are specified for it. It also means that you cannot use
subreports to create a free-form or single column layout within the
multi-column report. Subreports that you include in a multi-column report
always use the column settings of the parent report
"johnsh" <johnsh@.axiumae.com> wrote in message
news:%23%23usVHbhGHA.4368@.TK2MSFTNGP03.phx.gbl...
>I have a table report where amoung other things there is a list of names
>from another data set. I don't want the names to print in one column
>because the list maybe more than one page. I can create a multi column
>report for just the names and when printed or print preview will display in
>multiply columns (good). But when I add that report as a subreport to my
>main report I believe it ignores the subreport's 'report properties' and
>uses the main 'report's properties'?
> Any suggestions?
> John
>

Multi Column Report

I can't get my table to wrap to column 2. When I run the report it makes the overall width the width of one column. There is plenty of room for this 2nd column. Why isn't it showing?Multi column reports appear as one column in Preview and the HTML renderers.
To render the report so all columns show you must use Print Preview, PDF, or
TIFF.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> I can't get my table to wrap to column 2. When I run the report it makes
the overall width the width of one column. There is plenty of room for this
2nd column. Why isn't it showing?|||I am having a problem with multi-column report displaying the multiple
columns when viewed as a sub-report, whether or not I am in Print Preview
mode or in Preview mode.
When I view the report as a master report, all columns show up as expected
in the Print Preview mode (not in Preview mode).
Does anyone have a work-around or is there a fix for this problem?
"Bruce Johnson [MSFT]" wrote:
> Multi column reports appear as one column in Preview and the HTML renderers.
> To render the report so all columns show you must use Print Preview, PDF, or
> TIFF.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> > I can't get my table to wrap to column 2. When I run the report it makes
> the overall width the width of one column. There is plenty of room for this
> 2nd column. Why isn't it showing?
>
>|||Can you tell us if there are any plans in the future to have the html
renderer display multiple columns?
Can you suggest any workarounds for this?
Thanks,
John
"Bruce Johnson [MSFT]" wrote:
> Multi column reports appear as one column in Preview and the HTML renderers.
> To render the report so all columns show you must use Print Preview, PDF, or
> TIFF.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> > I can't get my table to wrap to column 2. When I run the report it makes
> the overall width the width of one column. There is plenty of room for this
> 2nd column. Why isn't it showing?
>
>sql

Multi Column grouping

I have a table with 9 code columns in it. I want a listing of every
possible code in any of the 9 columns with a count of each. Is there a way
to do this without creating a new table that has 9x the rows that the
current table has or without 9 queries plus a sumation query?
The two ways I can get the correct number now are more time consuming that I
would like because they both require "running the table" a number of times
and the table is very large (20MM+ rows).
Thanks,
ScottGROUP BY, of course
SELECT
Col1
, Col2
, Col3
, etc.
, count(1)
FROM MyTable
GROUP BY
Col1
, Col2
, Col3
, etc.
This will provide a row (and its count) for each distinct combination of cod
es.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Scott Cadreau" <scadreau@.aros.net> wrote in message news:2uDmg.289$Mz3.260@.fed1read07...[c
olor=darkred]
>I have a table with 9 code columns in it. I want a listing of every
> possible code in any of the 9 columns with a count of each. Is there a wa
y
> to do this without creating a new table that has 9x the rows that the
> current table has or without 9 queries plus a sumation query?
>
> The two ways I can get the correct number now are more time consuming that
I
> would like because they both require "running the table" a number of times
> and the table is very large (20MM+ rows).
>
> Thanks,
>
> Scott
>
>[/color]

Mulit table insert

Can some one point me in the right direction on this. I am trying to insert data into two different table. The problem is, even though table 2 had a "not null" on it's primary, the insert command still allow it to be null.

Here is what I am trying to do. When I click the submit button on my web app, it should send this information in for to table 2. The procedure should then pull the id2 and and enter it into the table as FK to table 1.

Like I said before, the id2 doesn't seem to pass any data because the procedure is passing a null, even thout a set the pk value to not null.


DECLARE @.identHolder int
DECLARE @.ID2 int

BEGIN TRANSACTION
IF NOT EXISTS (SELECT ID2 FROM [tbl2] WHERE ID1 = @.ID2)
BEGIN
(SELECT 2ID FROM [tbl2] WHERE ID1 = @.ID1) SET @.identHolder = @.@.Identity

END
ELSE
BEGIN
INSERT INTO [t2] ([fName], [lName], [shift], [userName], [emailAdd])
VALUES ( @.fName, @.lName, @.shift, @.userName, @.emailAdd) SET @.identHolder = @.@.Identity

END
COMMIT

SET @.ID2 = (@.@.Identity)
INSERT INTO [tbl1]([ID1], [ID2], [event], [removed])
VALUES (@.ID1, @.ID2, @.event, @.removed)I suggest you go back to old school debugging.
Stick a handful of

PRINT @.ID2
--Actually, you'll probably need:
PRINT Convert(varchar, ID2)

And watch the value.
Should the value perhaps be before the COMMIT?|||I figured it out. The "IF Not Exist" needed to be "If exist". I was telling the DB to check for a record but instead of creating the record if not exist, I was selecting it. Once I change that it work. I think I had to fix a datatype too.

Thank you.|||You should also put some error handling after the INSERT to rollback (or whatever) in case of an error (constraint, FK, unique index etc). Use the @.@.ERROR function in SQL Server 2000 and TRY...CATCH in SQL Server 2005.

Many people seems to forget about TSQL error handling.
What should happen if a statement fails? If xact_abort is on the transaction is rolled back and the execution of the batch stops, but if xact_abort is off the transaction remains open and then the batch continues to execute. This can and will lead to lots of problems.|||Do you know of a good site I can go to where I can learn how to write it? I just starting to learn TSQL and not to sure how I would go about creating a proper @.@.ERROR message. Does the @.@.ERROR have to return a value to the application or does it stay with in the db? Thank for the heads up.|||Are you using 2000 or 2005?

@.@.ERROR is the only way in 2000, but as I said, in 2005 you should rather use TRY...CATCH.|||and you probably should use scope_identit() instead|||Linky! (http://codebetter.com/blogs/john.papa/archive/2006/04/07/142503.aspx)
I did not know that :)|||Are you using 2000 or 2005?

@.@.ERROR is the only way in 2000, but as I said, in 2005 you should rather use TRY...CATCH.

I am using 2000

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||select count(IdentityColumn) from tablename
I get ~87,000,000 in return
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||also to just be sure:
Select count(*) IdentityColumn
group by IdentityColumn
having count(*)>1
Returns 0
Thank you
ktf
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||It is:
SQL enterprise 2000 clustered
NT.5.0.(2195)
8.00.760. SP3
7GB memory
4 processor
"Jasper Smith" wrote:
> What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
> and see if that works. It sounds like this bug
> FIX: A parallel query may return unexpected results
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||Sounds like you're running into the bug then. Did you try doing a count with
option(maxdop 1) ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> It is:
> SQL enterprise 2000 clustered
> NT.5.0.(2195)
> 8.00.760. SP3
> 7GB memory
> 4 processor
>
> "Jasper Smith" wrote:
>> What service pack are you on? Also try doing a count with OPTION(MAXDOP
>> 1)
>> and see if that works. It sounds like this bug
>> FIX: A parallel query may return unexpected results
>> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> > The problem I have is the count(*) or count(table identity column) show
>> > a
>> > much bigger number than the actual number of rows.
>> >
>> > When I run
>> > select count(*) from tablename I get ~87,000,000 in return.
>> >
>> > But the MAX number of table identity is in 10 M range also rowcnt from
>> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
>> > not
>> > help me.
>> >
>> > Why I get such different result. What should I do to correct this.
>> >
>> > Thank you,
>> > ktf
>>|||Before I do that:
Is it going to reconfigure and change the server setting or it is only
within the session?
Is it going to put a big impact on the server?
Because I do not want to make that change yet.
Do I have to install SP4? Because we are not on 64-bit server.
Thank you,
"Jasper Smith" wrote:
> Sounds like you're running into the bug then. Did you try doing a count with
> option(maxdop 1) ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> > It is:
> > SQL enterprise 2000 clustered
> > NT.5.0.(2195)
> > 8.00.760. SP3
> > 7GB memory
> > 4 processor
> >
> >
> > "Jasper Smith" wrote:
> >
> >> What service pack are you on? Also try doing a count with OPTION(MAXDOP
> >> 1)
> >> and see if that works. It sounds like this bug
> >>
> >> FIX: A parallel query may return unexpected results
> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> > The problem I have is the count(*) or count(table identity column) show
> >> > a
> >> > much bigger number than the actual number of rows.
> >> >
> >> > When I run
> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >
> >> > But the MAX number of table identity is in 10 M range also rowcnt from
> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> >> > not
> >> > help me.
> >> >
> >> > Why I get such different result. What should I do to correct this.
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
> >>
>
>|||It's a query hint, it only affects the specific query in question. It won't
impact anything else. To avoid any issues just run
select count(*)
from tablename with(nolock)
option(maxdop 1)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> Before I do that:
> Is it going to reconfigure and change the server setting or it is only
> within the session?
> Is it going to put a big impact on the server?
> Because I do not want to make that change yet.
> Do I have to install SP4? Because we are not on 64-bit server.
> Thank you,
>
> "Jasper Smith" wrote:
>> Sounds like you're running into the bug then. Did you try doing a count
>> with
>> option(maxdop 1) ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> > It is:
>> > SQL enterprise 2000 clustered
>> > NT.5.0.(2195)
>> > 8.00.760. SP3
>> > 7GB memory
>> > 4 processor
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> What service pack are you on? Also try doing a count with
>> >> OPTION(MAXDOP
>> >> 1)
>> >> and see if that works. It sounds like this bug
>> >>
>> >> FIX: A parallel query may return unexpected results
>> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> > The problem I have is the count(*) or count(table identity column)
>> >> > show
>> >> > a
>> >> > much bigger number than the actual number of rows.
>> >> >
>> >> > When I run
>> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >
>> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> > from
>> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
>> >> > will
>> >> > not
>> >> > help me.
>> >> >
>> >> > Why I get such different result. What should I do to correct this.
>> >> >
>> >> > Thank you,
>> >> > ktf
>> >>
>> >>
>> >>
>>|||Jasper,
It gave me the correct number.
the config_value and run_value are set to 0 for "max degree of parallelism.
do you think I should turn it on.
Is it necessary to install sql sp4. The site does not say much about sp4.
Thank you,
ktf
"Jasper Smith" wrote:
> It's a query hint, it only affects the specific query in question. It won't
> impact anything else. To avoid any issues just run
> select count(*)
> from tablename with(nolock)
> option(maxdop 1)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> > Before I do that:
> > Is it going to reconfigure and change the server setting or it is only
> > within the session?
> > Is it going to put a big impact on the server?
> > Because I do not want to make that change yet.
> > Do I have to install SP4? Because we are not on 64-bit server.
> >
> > Thank you,
> >
> >
> > "Jasper Smith" wrote:
> >
> >> Sounds like you're running into the bug then. Did you try doing a count
> >> with
> >> option(maxdop 1) ?
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> >> > It is:
> >> > SQL enterprise 2000 clustered
> >> > NT.5.0.(2195)
> >> > 8.00.760. SP3
> >> > 7GB memory
> >> > 4 processor
> >> >
> >> >
> >> > "Jasper Smith" wrote:
> >> >
> >> >> What service pack are you on? Also try doing a count with
> >> >> OPTION(MAXDOP
> >> >> 1)
> >> >> and see if that works. It sounds like this bug
> >> >>
> >> >> FIX: A parallel query may return unexpected results
> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >> http://www.sqldbatips.com
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> >> > The problem I have is the count(*) or count(table identity column)
> >> >> > show
> >> >> > a
> >> >> > much bigger number than the actual number of rows.
> >> >> >
> >> >> > When I run
> >> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >> >
> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
> >> >> > from
> >> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
> >> >> > will
> >> >> > not
> >> >> > help me.
> >> >> >
> >> >> > Why I get such different result. What should I do to correct this.
> >> >> >
> >> >> > Thank you,
> >> >> > ktf
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||The fix for this bug is in SP4. Changing the server wide maxdop settings
will obviously affect all other queries so should only be done after
extensive testing. If you are not seeing any application related issues due
to this bug and it is only affecting "DBA" type activities then you don't
necessarily need to get on SP4 however it's probably worth doing anyway to
keep upto to date with the latest bug fixes
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:30CBAF3D-FC19-4F06-89A8-ED074AF96A98@.microsoft.com...
> Jasper,
> It gave me the correct number.
> the config_value and run_value are set to 0 for "max degree of
> parallelism.
> do you think I should turn it on.
> Is it necessary to install sql sp4. The site does not say much about sp4.
> Thank you,
> ktf
> "Jasper Smith" wrote:
>> It's a query hint, it only affects the specific query in question. It
>> won't
>> impact anything else. To avoid any issues just run
>> select count(*)
>> from tablename with(nolock)
>> option(maxdop 1)
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
>> > Before I do that:
>> > Is it going to reconfigure and change the server setting or it is only
>> > within the session?
>> > Is it going to put a big impact on the server?
>> > Because I do not want to make that change yet.
>> > Do I have to install SP4? Because we are not on 64-bit server.
>> >
>> > Thank you,
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> Sounds like you're running into the bug then. Did you try doing a
>> >> count
>> >> with
>> >> option(maxdop 1) ?
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> >> > It is:
>> >> > SQL enterprise 2000 clustered
>> >> > NT.5.0.(2195)
>> >> > 8.00.760. SP3
>> >> > 7GB memory
>> >> > 4 processor
>> >> >
>> >> >
>> >> > "Jasper Smith" wrote:
>> >> >
>> >> >> What service pack are you on? Also try doing a count with
>> >> >> OPTION(MAXDOP
>> >> >> 1)
>> >> >> and see if that works. It sounds like this bug
>> >> >>
>> >> >> FIX: A parallel query may return unexpected results
>> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >>
>> >> >> Jasper Smith (SQL Server MVP)
>> >> >> http://www.sqldbatips.com
>> >> >> I support PASS - the definitive, global
>> >> >> community for SQL Server professionals -
>> >> >> http://www.sqlpass.org
>> >> >>
>> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> >> > The problem I have is the count(*) or count(table identity
>> >> >> > column)
>> >> >> > show
>> >> >> > a
>> >> >> > much bigger number than the actual number of rows.
>> >> >> >
>> >> >> > When I run
>> >> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >> >
>> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> >> > from
>> >> >> > sysindexes shows the correct number of ~10 M. So DBCC
>> >> >> > UPDATEUSAGE
>> >> >> > will
>> >> >> > not
>> >> >> > help me.
>> >> >> >
>> >> >> > Why I get such different result. What should I do to correct
>> >> >> > this.
>> >> >> >
>> >> >> > Thank you,
>> >> >> > ktf
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>sql

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktf
What do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf
|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default...en-us%3b814509
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/defaul...ben-us%3b814509
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf

MStudio Always "helps" me to connect to server

From database tree I want to open table trigger (I think this problem for all objects).

MStudio ALWAYS offer me to log on. Why?

I would like to open object from the SAME database, not other.

Can you provide more detail on exactly what you're trying to accomplish and the steps you're taking. Start with "I launch SSMS".