Multiple Default Values of Parameter

I have a report parameter that will either have 1 value to pick from, or multiple values to pick from depending on who is logged in. I want to be able to set the default value to the first result that is returned in the dataset. Is there a simple way to do this without making a new dataset?Hi,

you either need a new dataset or a static value for the default property. You can′t use the dataset as a expression to get the first value of the dataset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

multiple datasets

Is it possible to have more than one dataset in a report?

I'm trying to create a parameter that draws its drop down box values from a query that is seperate from the result set.

Thanks

Yes, you can have multiple DataSets in reports. You can set up a DatsSet to be the source for valid parameter values like you describe.

-Chris

Multiple Columns with different Values OR Single Column with Multiple Criteria?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1

ON

TBL.col1=TBL1.[key]

INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2

ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

Before you merged the two columns could you not write your query something like this

select *

from TBL

where col2 = 'engine' and col3 = 'toyota'

You could put variables in place of the engine and toyota and create a stored procedure that passes in the values you want to search on.

I don't think there is a need to merge the columns matter of fact it will most likely run faster without being merged.

Or maybe I missed you rquestion.

Multiple Columns with different Values OR Single Column with Multiple Criteria?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1

ON

TBL.col1=TBL1.[key]

INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2

ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4

FROM

TBL

INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1

ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

Before you merged the two columns could you not write your query something like this

select *

from TBL

where col2 = 'engine' and col3 = 'toyota'

You could put variables in place of the engine and toyota and create a stored procedure that passes in the values you want to search on.

I don't think there is a need to merge the columns matter of fact it will most likely run faster without being merged.

Or maybe I missed you rquestion.

Multiple columns and row values

Hi. I am trying to write a single stored procedure which would trace the
changes made by a user on a table. I would like this implemented on multiple
tables having the most efficient code possible. Is it possible to browse to
a
table and extract all its columns (column_name from information_schema) and
get the row value for these columns having only a record id. I was able to
get the column_name but unable to make a sql statement retrieving the values
of the column_name.
Any help is appreciated.Can you post more detail? What do you mean "columns having only a record
id"? If you post actual DDL, sample data, and sample output, that would be
best...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> Hi. I am trying to write a single stored procedure which would trace the
> changes made by a user on a table. I would like this implemented on
multiple
> tables having the most efficient code possible. Is it possible to browse
to a
> table and extract all its columns (column_name from information_schema)
and
> get the row value for these columns having only a record id. I was able to
> get the column_name but unable to make a sql statement retrieving the
values
> of the column_name.
> Any help is appreciated.
>|||OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is in
t
and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
COL1 as primary key and an int. I want to write a stored procedure to extrac
t
a record from TABLE1 or TABLE2 (table_name being passed as parameter) with a
record id (COL1) and loops over the columns and its row values. So I can use
this stored procedure in these two tables or in any other table as long as
the primary key is int. better if the int (primary key) is eliminated as a
constraint as well..
so a query from information_schema would give me the column names (given the
table as a parameter) but how do i extract the row value of those columns if
i know the record primary key value (COL1).
Thanks.
"Adam Machanic" wrote:

> Can you post more detail? What do you mean "columns having only a record
> id"? If you post actual DDL, sample data, and sample output, that would b
e
> best...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> multiple
> to a
> and
> values
>
>|||"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is
int
> and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
> COL1 as primary key and an int. I want to write a stored procedure to
extract
> a record from TABLE1 or TABLE2 (table_name being passed as parameter) with
a
> record id (COL1) and loops over the columns and its row values. So I can
use
> this stored procedure in these two tables or in any other table as long as
> the primary key is int. better if the int (primary key) is eliminated as a
> constraint as well..
Why do you want to do this? You're completely eliminating most of the
benefits of using stored procedures, and DBMSs in general -- keeping the
application out of the data management business! My advice to you is to
very carefully consider your motives for doing this -- I can guarantee that
you will not end up simplifying anything by tightly coupling your
application to your database (which is what this stored procedure will
accomplish). You can take that with however many grains of salt as you
choose, but you may want to search the archives of this group for lots of
threads about these kinds of techniques and the problems they invariably
cause.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||i know.. well. i have written a large application and i everything is almost
in place. I just need this user tracking history. who inserts what and who
updates which record.. and so on.. i can use triggers but that would mean
going to each one of the tables which is 100+. I want a single stored
procedure, called from a class that I can reuse on every page. So i need the
columns and the current values so i can record them in a table where it can
be retrieved in a report. but this has to go on a per column value.. and the
pages are a lot less that the tables..
"Adam Machanic" wrote:

> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> int
> extract
> a
> use
>
> Why do you want to do this? You're completely eliminating most of the
> benefits of using stored procedures, and DBMSs in general -- keeping the
> application out of the data management business! My advice to you is to
> very carefully consider your motives for doing this -- I can guarantee tha
t
> you will not end up simplifying anything by tightly coupling your
> application to your database (which is what this stored procedure will
> accomplish). You can take that with however many grains of salt as you
> choose, but you may want to search the archives of this group for lots of
> threads about these kinds of techniques and the problems they invariably
> cause.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

multiple column values into a single value

hi all,
I am trying to take all the values from a single varchar column and to inset
them into a variable so that they are seperated with the '-' sign.
I am trying to avoid cursors.
by the way- all the rows have a column with the same value in the table
for example:
create table test (col1 int, col2 varchar(20))
insert test values(1,'aaa')
insert test values(1,'bbb')
insert test values(1,'ccc')
the result sould look like this 'aaa-bbb-ccc'
thaks,
alonA cursor/iterative solution is best suited for this.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon|||create table #test (col1 int, col2 varchar(20))
insert #test values(1,'aaa')
insert #test values(1,'bbb')
insert #test values(1,'ccc')
DECLARE @.concat VARCHAR(100)
SET @.concat=''
SELECT
CASE WHEN @.concat='' THEN '' ELSE '-' END + col2
FROM #test
DROP TABLE #Test
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon|||Maybe this can help:
http://milambda.blogspot.com/2005/0...s-as-array.html
Of course you'll need to change it to meet your specific needs.
ML|||So you want to destroy First Normal Form (1NF) with a proprieetary
kludge? In a tiered archtiecture, display is done inthe front end and
not in the database. This is far more basic than just SQL programming.|||http://www.aspfaq.com/2529
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon

Multi-parameters issue in data flow task SSIS

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

The "Parse Query" never works when you have ? in your SQL command. Just ignore the error.