Named query not appearing in report builder

I'm having a heck of a time with named queries. At this point I just want a proof of concept in my DSV. I have a bunch of tables added to the DSV, and I would like to add a named query to it so the user can pick from the name query in report builder. What has to be done to make it appear in the report builder?

Nevermind. I didn't realize that the report model definition had to be refreshed.

Named queries ?

Hi,
Is there anyway i can use 2 databases in one dataset using named
queries ?
here is what i want to do
select name from database1.dbo.employees
union
select name from dabaase2.dbo.employees
i dont have reportmodel veiew etc. I am using plaing old reporting
services 2005.
ThanksAny valid SQL that you can run against SQL Server can be used. You'll want
to use the generic query designer (there is a button to switch from
graphical to generic).
So, yes, you can easily do this. Heck, I have cross database joins.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Prash" <PrashantiKoti@.gmail.com> wrote in message
news:1187030848.237751.264950@.w3g2000hsg.googlegroups.com...
> Hi,
> Is there anyway i can use 2 databases in one dataset using named
> queries ?
> here is what i want to do
> select name from database1.dbo.employees
> union
> select name from dabaase2.dbo.employees
> i dont have reportmodel veiew etc. I am using plaing old reporting
> services 2005.
> Thanks
>

mysql query then sql server 2000 query

Hi all

My client database was in mysql. Now I am converting its in sql server 2000. And all queries was written for mysql. for example this query.

This Query for MySql

--

CREATE TABLE `acc` (
`acc_id` int(7) NOT NULL auto_increment,
`cat_id` int(7) NOT NULL default '0',
`brand_id` int(11) NOT NULL default '0',
`item_code` varchar(100) NOT NULL default '',
`acc_name` varchar(255) NOT NULL default '',
`acc_desc` longtext NOT NULL,
`acc_spec` longtext NOT NULL,
`acc_techspec` longtext NOT NULL,
`acc_warranty` varchar(5) NOT NULL default '0',
`acc_partno` longtext NOT NULL,
`acc_serialno` longtext NOT NULL,
`acc_size` longtext NOT NULL,
`acc_weight` longtext NOT NULL,
`acc_price` int(11) NOT NULL default '0',
`acc_dprice` int(11) NOT NULL default '0',
`logos` varchar(100) NOT NULL default '',
`condition` varchar(255) NOT NULL default '',
`status` enum('available','unavailable','backorder') NOT NULL default 'available',
`approved` enum('approved','unapproved') NOT NULL default 'approved',
`addedby` varchar(10) NOT NULL default '',
`acc_date_added` varchar(50) NOT NULL default '0000-00-00',
`acc_lprice` int(11) NOT NULL default '0',
`supplier` text NOT NULL,
PRIMARY KEY (`acc_id`)
) TYPE=MyISAM;

--

I made it for sql srever 2000 like this

--

CREATE TABLE acc (
acc_id int(7) NOT NULL IDENTITY,
cat_id int(7) NOT NULL default '0',
brand_id int(11) NOT NULL default '0',
item_code varchar(100) NOT NULL default '',
acc_name varchar(255) NOT NULL default '',
acc_desc longtext NOT NULL,
acc_spec longtext NOT NULL,
acc_techspec longtext NOT NULL,
acc_warranty varchar(5) NOT NULL default '0',
acc_partno longtext NOT NULL,
acc_serialno longtext NOT NULL,
acc_size longtext NOT NULL,
acc_weight longtext NOT NULL,
acc_price int(11) NOT NULL default '0',
acc_dprice int(11) NOT NULL default '0',
logos varchar(100) NOT NULL default '',
condition varchar(255) NOT NULL default '',
--status enum('available','unavailable','backorder') NOT NULL default 'available',
status varchar(10) Not Null default 'available'
constraint chk_valid$entries$for$status
check ( status in ('available','unavailable','backorder')),
approved varchar(10) not null default 'approved'
constraint chk_valid$entries$for$approved
check ( approved in ('approved','unapproved')),
--approved enum('approved','unapproved') NOT NULL default 'approved',
addedby varchar(10) NOT NULL default '',
acc_date_added varchar(50) NOT NULL default '0000-00-00',
acc_lprice int(11) NOT NULL default '0',
supplier text NOT NULL,
PRIMARY KEY (acc_id)
) TYPE=MyISAM;

--

Only last line give me error. here is the error anyone could plz help me.

Server: Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near '='.

Thanks

Remove this from the definition -it is unknown to SQL Server...

TYPE=MyISAM

Change the [ longtext ] datatypes to [ varchar(max) ]

Change the

enum('available','unavailable','backorder')

to a [ CHECK ]CONSTRAINT (and the other enum as well)

I think that you will probably be much happier if you were to change the [ acc_date_added ] datatype from varchar(50) to a datetime datatype with a default of [ 0 ]

Change the Supplier datatype from [ text ] to varchar(LengthAsAppropriate)

|||Here you go...

CREATE TABLE acc (
acc_id int NOT NULL IDENTITY,
cat_id int NOT NULL default '0',
brand_id int NOT NULL default '0',
item_code varchar(100) NOT NULL default '',
acc_name varchar(255) NOT NULL default '',
acc_desc text NOT NULL,
acc_spec text NOT NULL,
acc_techspec text NOT NULL,
acc_warranty varchar(5) NOT NULL default '0',
acc_partno text NOT NULL,
acc_serialno text NOT NULL,
acc_size text NOT NULL,
acc_weight text NOT NULL,
acc_price int NOT NULL default (0),
acc_dprice int NOT NULL default (0),
logos varchar(100) NOT NULL default '',
condition varchar(255) NOT NULL default '',
status varchar(10) Not Null default 'available',
approved varchar(10) not null default 'approved',
addedby varchar(10) NOT NULL default '',
acc_date_added varchar(50) NOT NULL default '0000-00-00',
acc_lprice int NOT NULL default (0),
supplier text NOT NULL,
constraint pk_acc primary key
(acc_id)
)
ALTER TABLE acc WITH CHECK ADD CONSTRAINT [CK_acc_Status]
CHECK (Status in ('available','unavailable','backorder'))
ALTER TABLE acc WITH CHECK ADD CONSTRAINT [CK_acc_approved]
CHECK (approved in ('approved','unapproved'))

MyDataTable.NewRow() - Looping issue

Code:

DataRow dr;foreach (object chkedin chklst.CheckedItems)

{

dr = qrequest.NewRow();

dr = queries.Tables[0].Rows.Find(chked);

qrequest.Rows.Add(dr);

}

it makes it through the loop once just fine but when it goes around for time number 2 it says the object is already in the table. I cant really say i know what im doing here, but i do (sort of) understand why its doing this. the question is, how do i get around this?

I actually got this code from MSDN (they used a for loop and added multiple rows to a table just like this)

Thanks

Justin

You should try declaring your row inside the for loop; that way the variable is initialized during each cycle. Otherwise, the row still holds the reference to the last row (which you just added to the table).

foreach (object chkedin chklst.CheckedItems) {DataRow dr; dr = qrequest.NewRow();dr = queries.Tables[0].Rows.Find(chked);qrequest.Rows.Add(dr);}

Also, is qrequest empty to begin with? If it has any of the rows that are returned from queries.Tables(0).Rows.Find() then that will cause a redundancy error. But mostly I think your issue is what I described above.

|||

I actually tried that and i got the same error, did it ever so slightly different, did the declare as one line: DataRow dr = new DataRow();

i cant realy imagine thats any different than what you did but it still gave me the same error.

|||

Hi,

I'm not quite sure what you're trying to do. But there are some problems in the code.

1. The dr = qrequest.NewRow(); line will have no use because the next line points dr to the other DataRow instance.
2. The found row already belongs to a certain table and it cannot be added to another DataTable. In this case, you will need to use ImportRow() method instead.

|||

yeah my biggest problem was #2. which is i guess a direct result of #1. either way i did find a way around it but i must have over looked the importrow which is exactly what i need.

If i dont define columns for the table im importing to does it create them automatically based on the row im importing?

Thanks

Justin

|||

No, Justin. We can only import rows according to the current schema.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

My SQL is rusty

Hi Guys,
Im really rusty on SQL queries so a little help would be appreciated. I have
a users table
UserID Int
UserName String
Authoriser Int
I am trying to work out one query to return the authorisers name given the
users ID. Can this be done in one query string ?
Best Regards
The Inimitable Mr NewbieHope I'm not wrong here, but just a simple select statement would suffice.
Select Authoriser
From users
Where UserID = <whatever userid is passed>
"Mr Newbie" wrote:

> Hi Guys,
> Im really rusty on SQL queries so a little help would be appreciated. I ha
ve
> a users table
> UserID Int
> UserName String
> Authoriser Int
> I am trying to work out one query to return the authorisers name given the
> users ID. Can this be done in one query string ?
>
> --
> Best Regards
> The Inimitable Mr Newbie o?o
>
>|||
select Authorisers.AuthoriserName
from Users,
Authorisers
where UserID = @.UserId (variable or literal)
and Authorisers.Authoriser = Users.Authoriser
Assuming you have a table "Authorisers"
Authoriser int
AuthoriserName String
"Todd S" <Todd S@.discussions.microsoft.com> wrote in message
news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
> Hope I'm not wrong here, but just a simple select statement would suffice.
> Select Authoriser
> From users
> Where UserID = <whatever userid is passed>
>
> "Mr Newbie" wrote:
>|||Actually, I only have the one table so I was looking for a way to select the
Authorisers name using the ID stored in same table.
Best Regards
The Inimitable Mr Newbie
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:u6CvscU6FHA.808@.TK2MSFTNGP09.phx.gbl...
>
> select Authorisers.AuthoriserName
> from Users,
> Authorisers
> where UserID = @.UserId (variable or literal)
> and Authorisers.Authoriser = Users.Authoriser
> Assuming you have a table "Authorisers"
> Authoriser int
> AuthoriserName String
>
> "Todd S" <Todd S@.discussions.microsoft.com> wrote in message
> news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
>|||SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
HTH,
Stu|||That looks right.
Thanks very much for your help, I will test it out and post a feedback post.
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||That does not work it returns all the rows, its very odd
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||Try
SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
Where Users.UserID= [the value of the user]
"Mr Newbie" wrote:

> That does not work it returns all the rows, its very odd
> --
> Best Regards
> The Inimitable Mr Newbie o?o
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
>
>|||Perfect !!!
Many Thanks Mike. - Appreciated Greatly
Best Regards
The Inimitable Mr Newbie
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:939CA4BD-3119-4013-B35C-F56B972904A3@.microsoft.com...
> Try
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> Where Users.UserID= [the value of the user]
>
> "Mr Newbie" wrote:
>|||Sorry, I misread your original post. I thought you were tyring to
return all rows.
Stu

My Sql Express is so slow?

Has anyone else noticed delays with SQL Express? I'm not really talking about delays on the queries but just delays in general response. For example: everything is running great, then for about 2 minutes I get connection timeouts etc can't even open stuff in the management studio without getting timeouts ... then as strangely as it started everything goes back to normal and requests are served again.

The server has nothing on except 1 website, its Win 2003 Server. 512MB Ram on a PIV. The memory usage is low and during the "lockups" the machine isn't showing any processor usage and SQL mem usage is around 40Megs.

I am not using User Instances either. Nothing in the event logs. What is odd, is its happening on 3 of my machines .... all with different sites, the only thing in common between them is SQL.

thanks,

-c

Are you closing and destroying your connection objects?

If you dont close and set them to nothing they will sit in the application pool taking up resources.

Just a though