alternate line shading in a table

Is there a way to set up line shading on a report that is created from a table in SRS? The report is great but I want to shade every other line to make it easy to read and being a beginner with SRS I have not been able to find a way to do this.

Any help would be appreciated.

Thanks,

Do you mean like setting an expression in the Backgroudcolor property like: =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White") ?

Jens K. Suessmeyer.

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

Jens,

Thanks that did it.

Altering linked tables

Greetings,
I am using an Access mdb with all linked tables in MSDE. I know that you
can't change linked tables from the MDB, so I created an ADP that imports
all the tables, so that I can alter them in the ADP. The ADP let's me add a
column to a table, but when I open the mdb up again that column isn't there.
What do I do?
Thanks in advance.
Hi, Yair.
Drop the link, then recreate the link to the table. An external table's
structure and connection properties are only recorded at the time of
linking, so any later changes that you make to a linked table's structure
(i.e., add/change/delete/rename fields) or connection properties (i.e.,
add/change/delete database password) are unknown. Dropping and recreating
the link will re-establish the correct properties needed to access the data
in the external table.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I am using an Access mdb with all linked tables in MSDE. I know that you
> can't change linked tables from the MDB, so I created an ADP that imports
> all the tables, so that I can alter them in the ADP. The ADP let's me add
a
> column to a table, but when I open the mdb up again that column isn't
there.
> What do I do?
> Thanks in advance.
>
|||Thanks Gunny.
How do I drop the link and relink?
Will it affect all my forms and reports?
I'll check the help too but if it's an easy answer...
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Thanks. I used the linked table manager to refresh the tables an it worked
perfectly.
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Check out http://www.mvps.org/access/tables/tbl0010.htm at "The Access Web"
for one approach to relinking ODBC tables, or see
http://members.rogers.com/douglas.j...LessLinks.html for how to do
it without requiring a DSN.
Assuming you do it when you first start up the application, it won't affect
your forms or reports unless table changes have occurred, and your forms or
reports reference fields or tables that are no longer present.
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss#HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>
|||Hi, Yair.

> How do I drop the link and relink?
Select the linked table in the database window with your mouse and hit the
<DELETE> key. Then use the menu "File -> Get External Data -> Link Tables"
and browse for the file that contains the table that you want to link to,
then follow the prompts in the dialog window just like you did when you
originally linked the table.

> Will it affect all my forms and reports?
Sort of. It will allow you to add this new field to all of the forms and
reports bound to this table, and any queries and Recordsets that utilize the
table, but won't automatically make these changes for you.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss%23HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>

ALTER temp table - unexpected behaviour

Funny little problem with a temp table created by an sp and then
altered within the scope of the same sp. It doesn't seem to work when I
try it.
Here's some sample code;
/**** start create code ***/
use northwind
create procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp
GO
/**** end code ***/
If this is executed you get;
ProductName RowID
--
Alice Mutton 1
Aniseed Syrup 2
Boston Crab Meat 3
Camembert Pierrot 4
Carnavon Tigers 5
Now try this;
/**** start alter code ***/
use northwind
alter procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp where RowID = 1 --added where clause
GO
/**** end code ***/
When executed you get;
Result:
Error "Invalid column name 'RowID'"
So RowID is returned as part of the result set but if you try to add a
where clause on it, it doesn't exist? The same is true for inserts (if
the column being added is not an identity column).
I worked around the problem by creating the temp table with the
identity column first and then doing an insert but I'm curious about
the error. What did I miss?
Thanks.This is by design. At compile time, the engine has to validate the existence
of the columns. At this time, the alter statement is not yet committed,
thus, the error on compilation for the select statement.
You should change your proc to include the identity column as part of your
select/into.
e.g.
create procedure dbo.uspTest as
select top 5 productname,RowID=identity(int,1,1)
into #myTemp
from products
order by productname
select * from #myTemp
where RowID = 1 --added where clause
go
-oj
"Wolf" <spamcatcher5050@.hotmail.com> wrote in message
news:1124249380.861639.272680@.g14g2000cwa.googlegroups.com...
> Funny little problem with a temp table created by an sp and then
> altered within the scope of the same sp. It doesn't seem to work when I
> try it.
> Here's some sample code;
>
> /**** start create code ***/
> use northwind
> create procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp
> GO
> /**** end code ***/
> If this is executed you get;
> ProductName RowID
> --
> Alice Mutton 1
> Aniseed Syrup 2
> Boston Crab Meat 3
> Camembert Pierrot 4
> Carnavon Tigers 5
> Now try this;
> /**** start alter code ***/
> use northwind
> alter procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp where RowID = 1 --added where clause
> GO
> /**** end code ***/
> When executed you get;
> Result:
> Error "Invalid column name 'RowID'"
> So RowID is returned as part of the result set but if you try to add a
> where clause on it, it doesn't exist? The same is true for inserts (if
> the column being added is not an identity column).
> I worked around the problem by creating the temp table with the
> identity column first and then doing an insert but I'm curious about
> the error. What did I miss?
> Thanks.
>|||Awesome. This is going to save me so much time.
Thanks oj.|||You should note that the ORDER BY clause may effectively be ignored by the
server. In particular there is no guarantee that the IDENTITY values will be
assigned in Productname order. Don't use ORDER BY on SELECT INTO or
INSERT... SELECT.
David Portas
SQL Server MVP
--

Alter table weird bug?

I have created the following test SQL code to illustrate a real
problem I have with some SQL code.

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe'
, 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill
Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

When I run this SQL via the query analyser I get the errors:-

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.

It appears the SQL Server is trying to "pre-parse" the query and
hasn't picked up on the ALTER TABLE line that adds this column and so
complains that it doesn't exist. However it doesn't end there.

If I then run this query a line at a time by highlighting each line in
the query analyser and running it all works (not unexpected). However
then dropping the table and then running the full SQL code once more
(I.E. not highlighting each line), it then works as expected. I assume
it was somehow remembering "state" in my session so closed and
re-started the Query Analyser with the same result that the code does
now work.

However changing the table name to something new brings back the
errors once more. Can anyone explain what is going on here? I am
dropping the table before re-running the code each time.

I'm using SQL Server 2000 if that makes a difference.

Thanks.
Jon.Not a bug. As you rightly suspected, SQL Server tries to validate your
code first by attempting to resolve any column or object references to
existing objects and columns. If the table doesn't exist at compile time
then the resolution of that table's columns is deferred until the
statement executes. However, if the table exists then you will receive
an error if the referenced columns don't also exist. A workaround is to
put a GO batch separator after the ALTER TABLE statement so that the
second batch will be compiled independently.

For this reason among others it is good practice always to separate DDL
(Data Definition Language, such as CREATE and ALTER table statements)
and DML (Data Manipulation Language, such as SELECT, UPDATE, INSERT,
DELETE statements). I would create separate scripts for your DDL and DML
statements.

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||David Portas wrote:

> Not a bug. As you rightly suspected, SQL Server tries to validate your
> code first by attempting to resolve any column or object references to
> existing objects and columns. If the table doesn't exist at compile time
> then the resolution of that table's columns is deferred until the
> statement executes.

David,

Thanks, this does make sense however the table in my statement did not exist
at compile time (otherwise the create table line would fail), so that is
why I cannot understand why I get that message.

Thanks.
Jon.|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Thanks, this does make sense however the table in my statement did not
> exist at compile time (otherwise the create table line would fail), so
> that is why I cannot understand why I get that message.

Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> Your batch gets compiled several times. First you have:
> CREATE TABLE JCTable ( CustomerName varchar(50) )
> ALTER TABLE JCTable ADD CustomerNo int
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Jon Combe' , 1 )
> INSERT INTO JCTable ( CustomerName , CustomerNo )
> VALUES ( 'Bill Gates' , 1 )
> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
> SELECT * FROM JCTable
> On the first compile, all but the first statement is deferred. Once the
> table has been created, SQL Server hits the ALTER TABLE, finds that the
> statement is deferred, and recompiles the batch. This time, all statements
> are scrutinized, since once all tables in a query exist, SQL Server per-
> form full checks on the query.

Thanks Erland,

Why doesn't it spot that I've added a column and then either defer the last
three statements again, or recognise that the column I'm using does now
exist? I'd expect that sort of behaviour from it although I take the point
made earlier that it's best to split the statements with a GO between them.

Also given the way it is compiling the code it doesn't explain why if I run
these statements one line at a time, then drop the table, reload Query
Analyser and re-run this full batch of code it works yet running the full
batch before the table has ever existed generates an error. That just seems
weird, so if anyone can explain why I'd be interested to hear it! The
database should be in the same state in both cases, but as the behaviour is
different it can't be.

Jon.|||Jon Combe wrote:
> Erland Sommarskog wrote:
>
>>Your batch gets compiled several times. First you have:
>>
>> CREATE TABLE JCTable ( CustomerName varchar(50) )
>> ALTER TABLE JCTable ADD CustomerNo int
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Jon Combe' , 1 )
>> INSERT INTO JCTable ( CustomerName , CustomerNo )
>> VALUES ( 'Bill Gates' , 1 )
>> UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
>> SELECT * FROM JCTable
>>
>>On the first compile, all but the first statement is deferred. Once the
>>table has been created, SQL Server hits the ALTER TABLE, finds that the
>>statement is deferred, and recompiles the batch. This time, all statements
>>are scrutinized, since once all tables in a query exist, SQL Server per-
>>form full checks on the query.
>
> Thanks Erland,
> Why doesn't it spot that I've added a column and then either defer the last
> three statements again, or recognise that the column I'm using does now
> exist? I'd expect that sort of behaviour from it although I take the point
> made earlier that it's best to split the statements with a GO between them.
> Also given the way it is compiling the code it doesn't explain why if I run
> these statements one line at a time, then drop the table, reload Query
> Analyser and re-run this full batch of code it works yet running the full
> batch before the table has ever existed generates an error. That just seems
> weird, so if anyone can explain why I'd be interested to hear it! The
> database should be in the same state in both cases, but as the behaviour is
> different it can't be.
> Jon.

I have to confess that this behaviour, if it is "normal", is surprising
to me. Is this the way the product is expected to behave?

Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||Jon Combe (jcombe@.acxiom.co.uk) writes:
> Why doesn't it spot that I've added a column

No, you haven't added a column. You get the error the table has been
created, but the ALTER TABLE statement has not been executed. Since the
ALTER TABLE statement was deferred, SQL Server recompiles the batch,
and it recompiles the batch, because that the lowest granularity for
compilation in SQL 2000. And since at this point the columns does not
exist, the compilation fails.

> and then either defer the last three statements again,

SQL Server could defer compilation because of unknown columns too, but it
has quite some ramifications, and I am very happy that unknown columns is
reason for deferral. It is bad as it is. To wit, when you create a stored
procedure, you want to be alerted if you have misspelled a table name of a
column name. Due to deferred name resolution, you don't get alerts for
misspelled table names, but since SQL Server checks the query once all
tables are there, you do at least sometimes get alerts about misspelling
column names. (And in our in-house load tool, I scan the code for table
references to find the missing tables, and also perform some tricks to
get SQL Server check queries with temp tables too.

But, there is light at the end of the tunnel. Your script runs as you
expected in SQL 2005. This is because SQL 2005 is able to recompile a
single statement in a batch, so the entire batch is not recompiled at
once.

> Also given the way it is compiling the code it doesn't explain why if I
> run these statements one line at a time, then drop the table, reload
> Query Analyser and re-run this full batch of code it works yet running
> the full batch before the table has ever existed generates an error.
> That just seems weird, so if anyone can explain why I'd be interested to
> hear it! The database should be in the same state in both cases, but as
> the behaviour is different it can't be.

This has to do with cached plans. The plans are in the cache, even if
the table is dropped. (This makes sense with temp tables.) Yes, it's
certainly a bit confusing, but for the situations for which the behaviour
is designed, it gives the best result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

>> Also given the way it is compiling the code it doesn't explain why if I
>> run these statements one line at a time, then drop the table, reload
>> Query Analyser and re-run this full batch of code it works yet running
>> the full batch before the table has ever existed generates an error.
>> That just seems weird, so if anyone can explain why I'd be interested to
>> hear it! The database should be in the same state in both cases, but as
>> the behaviour is different it can't be.
> This has to do with cached plans. The plans are in the cache, even if
> the table is dropped. (This makes sense with temp tables.) Yes, it's
> certainly a bit confusing, but for the situations for which the behaviour
> is designed, it gives the best result.

So basically with the query I have, whether I get an error or not (when the
database is in the same state) depends entirely on what has been run
previously? That doesn't seem very satisfactory!

Jon.

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.
> Thanks,
> Andre
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that tabl
e
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.

> Thanks,
> Andre
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.

> Thanks,
> Andre
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

Alter Table

Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.As you are removing some of the fields using alter it can work for you provided that it follows the mentioned condition in the warning.

Contact your DBA regarding the warning message .|||

Quote:

Originally Posted by balasach82

Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.

try doing a

SELECT only, selected, fields, here INTO NEWTABLE from DAILYTABLE..

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
I saw it work in a cartoon once so I am pretty sure I can do it.
Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.
|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

Alter login issue

I created a SQL server login "LOG1", granted him "ALTER" privilege on anothe
r
sql login "log2"
When I connect using "LOG1", right click on "log2" in "logins, security",
try to change its password I get the following error :
Change password failed......Additional info........
Can not alter login 'log2' because it does not exist or you dont have
permissions error 15151
Thanks for your helpProbably because you aren't supplying the old password when
you go through SSMS. Read the rest of the permissions
section in books online for ALTER LOGIN.
-Sue
On Thu, 26 Oct 2006 13:59:01 -0700, SalamElias
<eliassal@.online.nospam> wrote:

>I created a SQL server login "LOG1", granted him "ALTER" privilege on anoth
er
>sql login "log2"
>When I connect using "LOG1", right click on "log2" in "logins, security",
>try to change its password I get the following error :
>Change password failed......Additional info........
>Can not alter login 'log2' because it does not exist or you dont have
>permissions error 15151
>Thanks for your help|||Hello Salam,
I understand that you cannot change password of another login even you have
grant the alter login permission to the login. As Sue mentioned, this
behavior is as designed and you could refer to Books Online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e247b84e-c99e-4af8-8b50-
57586e1cb1c5.htm for details. Any users without SQL admin rights/control
server permissions shall provide old password informaiton to change a
password of logins. This even occurs if the login want to change its own
password. This is a security purpose design.
You may try the following statement to change the password
Alter login testuser with password='newpass' old_password='oldpass'
Actually, this calls the following API in SQL Server.
ChangePassword(System.String oldPassword, System.String newPassword)
I understand it might be not convenient under some situation though it may
bring more security to SQL Server. Your feedback on this issue is routed to
the product team, and I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have concerns on the issue, please feel free
to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Salam,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Peter Yang [MSFT]" wrote:

> Hello Salam,
> I understand that you cannot change password of another login even you hav
e
> grant the alter login permission to the login. As Sue mentioned, this
> behavior is as designed and you could refer to Books Online
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e247b84e-c99e-4af8-8b5
0-
> 57586e1cb1c5.htm for details. Any users without SQL admin rights/control
> server permissions shall provide old password informaiton to change a
> password of logins. This even occurs if the login want to change its own
> password. This is a security purpose design.
> You may try the following statement to change the password
> Alter login testuser with password='newpass' old_password='oldpass'
>
Thanks - this answer solved my problem. Nowhere in the indicated BOL page
does it say about the requirement to supply the old password to change the
new password - it even has the OLD_PASSWORD section in [optional] square
brackets in the Syntax section.
(When we create user accounts we set a default password, then get the user
to login and change it to something only they know. We were getting an
unhelpful "Login doesn't exists or permission denied" message, when the
users tried to change their details on our new SQL Server 2005 Server)|||I think your right in terms of the documentation not being
clear. The help topic for sp_password is alludes to the
issue a bit more. Nothing all that direct though.
-Sue
On Wed, 1 Nov 2006 03:51:02 -0800, chrisredburn
<chrisredburn@.discussions.microsoft.com> wrote:

>
>"Peter Yang [MSFT]" wrote:
>
>Thanks - this answer solved my problem. Nowhere in the indicated BOL page
>does it say about the requirement to supply the old password to change the
>new password - it even has the OLD_PASSWORD section in [optional] squar
e
>brackets in the Syntax section.
>(When we create user accounts we set a default password, then get the user
>to login and change it to something only they know. We were getting an
>unhelpful "Login doesn't exists or permission denied" message, when the
>users tried to change their details on our new SQL Server 2005 Server)|||I opened a request for updating the permissions section of ALTER LOGIN.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hjmik29h0qm3lnrjfgkpostj8h7mjuu1ob@.
4ax.com...
>I think your right in terms of the documentation not being
> clear. The help topic for sp_password is alludes to the
> issue a bit more. Nothing all that direct though.
> -Sue
> On Wed, 1 Nov 2006 03:51:02 -0800, chrisredburn
> <chrisredburn@.discussions.microsoft.com> wrote:
>
>|||Thanks Laurentiu!
-Sue
On Thu, 2 Nov 2006 11:55:24 -0800, "Laurentiu Cristofor
[MSFT]" <Laurentiu.Cristofor@.nospam.com> wrote:

>I opened a request for updating the permissions section of ALTER LOGIN.
>Thanks

ALTER data type

I have created a table from a text file delimited by comma's. The date within the text file is YYYYMMDD and needs to be stored within a smalldatetime column. Unfortunatley whenever I import the data it errors out. If I import the data into a nvarchar column, it imports correctly and then allows me to change the datatype to smalldatetime(thus giving me the correct format). Is there a way I could run a command pre and post to the import?

ALTER TABLE PO MODIFY x_column nvarchar

IMPORT DATA

ALTER TABLE PO MODIFY x_column smalldatetime

hope this is clear enough, thanks for the helpalter table tblname alter column colname smalldatetime

You would be safer importing to a staging table then inserting from there.|||thanks for the quick response, worked like a charm

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers

Almost there (I think)...SQL Update problem...

I am trying to update a single field in a SQL database table. I created a SQLDataSource, configured Select and Update queries, and wrote some code in the script block to do the update after a button click. The SQLDataSource is in a contentplaceholder. What am I doing wrong in the data source, the script block, or both? Thanks so much in advance...

Here is the code for the SQLDataSource:

Dim ImageUploaded As Integer = 2

srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded

srcUpdateImageUploaded.Update()

Here is the code in the script block:

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = ?">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="EmilyTheKitty" PropertyName="Text" Type="Object" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Here is the error that I get:

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 164: Dim ImageUploaded As Integer = 2
Line 165:
Line 166: srcUpdateImageUploaded.UpdateParameters("@.ImageUploaded").DefaultValue = ImageUploaded
Line 167: srcUpdateImageUploaded.Update()
Line 168:

Source File: C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx Line: 166

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\PC_Dev\Profiles_BuildProfile.aspx:166
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +104
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5614

There is no @.ImageUploaded parameter in your SQLDataSource, See the modified code below

<asp:SqlDataSource ID="srcUpdateImageUploaded" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"
UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">
<UpdateParameters>
<asp:ControlParameter ControlID="TextBox1" Name="ImageUploaded" PropertyName="Text" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="UserName" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

|||

I appreciate the response...but it didn't work. I tried the modified code you posted. I pasted it into my page, tried it, and got the "Object reference not set to an instance of an object" again. Here is the code that I copied out of my page (it's the same code you posted)...

<asp:SqlDataSourceID="srcUpdateImageUploaded"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

ProviderName="System.Data.SqlClient"

SelectCommand="SELECT [ImageUploaded] FROM [profiles_BasicProperties] WHERE ([UserName] = @.UserName)"

UpdateCommand="UPDATE profiles_BasicProperties SET [ImageUploaded] = @.ImageUploaded">

<UpdateParameters>

<asp:ControlParameterControlID="TextBox1"Name="ImageUploaded"PropertyName="Text"Type="Int32"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="TextBox1"Name="UserName"PropertyName="Text"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

|||

where you are trying to update your code?? is it in the page load event ?? and also try to change asp:controlParameter into asp:FormParameter. If still not solved pls paste the whole code I will look into it

|||

Okay...I changed my mind on how I want to do this. I did away with the SQL data source connection object and I want to do this entirely with code in the script block. There is a command button that is clicked which invokes the following code. A textbox is included on the page and it is called by the code. The new error I get is this:

" Error updating table. Must declare the scalar variable "@.updatevalue". "

Here is the entirety of the code that is called:

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.updatevalue "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

|||

Okay, I fixed my own problem. I also figured out how these lines are put together so I am beyond merely cutting and pasting code in from books. Here is the correct code (corrected lines in bold, italics, and underlined):

ProtectedSub cmdUpdate_Click(ByVal senderAsObject, _

ByVal eAs EventArgs)Handles cmdUpdate.Click

'a temporary variable that is hard coded to 2 for testing...

Dim updatevalueAs Int32

updatevalue = 2

Dim usernameAsString

username = txtUserName.Text

Dim connectionstringAsString

connectionstring ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserProfilesDB.mdf;Integrated Security=True;User Instance=True"

' Define ADO.NET objects.

Dim updateSQLAsString

updateSQL ="UPDATE profiles_BasicProperties SET "

updateSQL &="ImageUploaded=@.ImageUploaded "

updateSQL &="WHERE username=@.username"

Dim conAsNew SqlConnection(connectionString)

Dim cmdAsNew SqlCommand(updateSQL, con)

' Add the parameters.

cmd.Parameters.AddWithValue("@.ImageUploaded", updatevalue)

cmd.Parameters.AddWithValue("@.username", username)

' Try to open database and execute the update.

Try

con.Open()

Dim updatedAsInteger = cmd.ExecuteNonQuery()

lblResults.Text = updated.ToString() &" records updated."

Catch errAs Exception

lblresults.Text ="Error updating table. "

lblResults.Text &= err.Message

Finally

con.Close()

EndTry

EndSub

almost done but stuck

Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so

created a new database with text indexing enabled and the following table

createtable support
(problemIdVARCHAR(50)NOTNULLPRIMARYKEY,
problemTitlevarchar(50)NOTNULL,
problemBodytextNOTNULL,
linkOnevarchar(50),
linkTwovarchar(50),
linkThreevarchar(50),
linkFourvarchar(50),ftid int NOT NULL)

next

createfulltextcatalog remoteSupportCatalog

createuniqueindex ui_remotesupportON support(ftid)

then

createfulltextindexon support(problemBody)keyindex PK__support__7C8480AEon remoteSupportCatalog

---

I then populated some rows and issues a quesry

Select * from support where freetext(problemBody, 'test database')

it works pulls back all the data I expected it to pull back

In my asp page I created a database connection with the folling select command

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @.srchBox)">

created the search parameter

<SelectParameters>

<asp:ControlParameterControlID="srchBox"PropertyName="Text"Type="String"Name="srchBox"/> //this is a text box that is searchable with a button

</SelectParameters>

and it doesnt give me back an error or data it does nothing. What am I missing???

here is the whole asp page

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

|||

here is the whole asp page

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script> <htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title> </head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td></tr>

</table>

</div>

</form> </body>

</html>

|||

quarinteen:

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

Have you tried to use Control parameter ? One more thing, what is the code you have written to execute the select method ? Can you post the code behind code also ?

|||

There is not any c# or vb to control the select statement. Just the asp.

|||

What I think is that you must be executing the datasource.select() method on the button click event, otherwise when would you bind the data to your grid after the user writes something in the textbox ? You've created everything and shown everything to the user.

Now, when user writes something in the textbox and presses the "Search It" button, you must execute the datasource.select() method. This is my assumption after reading your code. Post some more details as to what you are doing now and what is happing ( does any error occur ? ) so that I can help you better.

|||

no there is no c# or vb on this page that I have writter. Also I know varyations of this works I I put in the select statement "Select * from support where ((problemBody '%' + @.srchBox + '%') OR @.srchBox IS NULL)" it brings back the data

|||

Take the page you posted in the 2nd message. Change the parameter to a control parameter like you posted in the first message. Then change the sqldatasource, so that the "CancelSelectOnNullParameter" property is set to false. Change the search textbox, so it's autopostback property is set to true.

You may also need to change the parameter's properties to keep it from changing empty string to null, depending on how the freetext function interprets null parameters. For that matter, it might not like the empty string either.

allows blank value for integer Parameter

Hi have a problem to solve and I hope that this is not a SSRS Bug.

I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.

One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.

I starts to define tha parameter:

Datatype = integer

Allow blank value

Available: Non queried

Default: Null

if I want to Preview the report I have to provide an integer to the parameter's field ...

If for instance I set:

Default: Not queried = 0

In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!

So I tried this solution:

Datatype = integer

Allow blank value

Allow null value

Available: Non queried

Default: Null

In the preview the checkbox: NULL is checked and I click on the View Report.

But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.

Do I forget something during my setting?I have to control it programmatically?

N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!

Thank you for any help!

hi,

On the report parameters form do this please:

Add a parameter, name it to something,

choose data type integer,

mask as checked the "Allow null value",

select null as default value.

It was working on that way on my box as you wished. I'm using SSRS 2005 wih SP2

Regards,

Janos

Allowing an exception to a trigger

I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.

Hello Echo88,

Echo88:

I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.

A short answer is, yes, you would solve this with an additional column.

A slightly more detailed answer would be, check your "architecture" because your access paths here are "imbalanced". I mean, it sounds like sometimes you directly write to the table, sometimes you write through the stored procedure. My advice is go with stored procedures only. Great chance is your trigger's logic here really belongs to its own procedure which you would call instead of straight writing to the table.

Hope this makes sense. -LV

|||

Thanks for the advice. All writes to any table are done through stored procedures, except triggers because I need to access the trigger tables, but I do it for code maintenance reasons - it just simply easier for to pack it up that way.
Anyway, I am not in love with adding columns after my "architecture" has already been established and I found a better to handle the conditional update. I remember that MS SQL handles UPDATES by placing the new row in the INSERTED trigger table and the old row in the DELETED. I simply just compared the two rows and obtained the results I needed.

Allow Multiple Parameters form Windows Application

I have been working through a solution to allow multiple parameters to be
passed into a SQL RS report. I have created a windows application that calls
sql reporting services. The problem that I am having now is passing the
multiple values the user selects from the dropdown to sql reporting services.
I am setting: returnValues.Value
When I try to set this parameters to 1;2;3, report fails. However, setting
that value to 1 works.
Is this possbile?RS 2000 does not support multiple selections. For instance,
select * from blah where somefield in (@.Param)
will not work. What you can do is use either an expression or call a stored
procedure that takes the parameter and handles appropriately).
This will work:
= "select * from blah where somefield in (" & Parameters!Paramname.value &
")"
Note that this assume you have dealt with putting in all the proper syntax
like single quotes around charater type parameters, etc and that this will
be a valid query when done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>I have been working through a solution to allow multiple parameters to be
> passed into a SQL RS report. I have created a windows application that
> calls
> sql reporting services. The problem that I am having now is passing the
> multiple values the user selects from the dropdown to sql reporting
> services.
>
> I am setting: returnValues.Value
> When I try to set this parameters to 1;2;3, report fails. However,
> setting
> that value to 1 works.
> Is this possbile?|||I have been able to get the mulitple selection parameters to work if I call
the SQL RS report from a web page. I basically created dropdown listboxes
and have the form post to the url of the page I want to run. The multiple
selection values are sent to the report and the report works fine. However,
I am trying to call SQL RS report from a Windows Application. I have my
report created in a way that it uses a stored procedure to parse the multiple
values passed to in and joins to those values from a temp table.
"Bruce L-C [MVP]" wrote:
> RS 2000 does not support multiple selections. For instance,
> select * from blah where somefield in (@.Param)
> will not work. What you can do is use either an expression or call a stored
> procedure that takes the parameter and handles appropriately).
> This will work:
> = "select * from blah where somefield in (" & Parameters!Paramname.value &
> ")"
> Note that this assume you have dealt with putting in all the proper syntax
> like single quotes around charater type parameters, etc and that this will
> be a valid query when done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >I have been working through a solution to allow multiple parameters to be
> > passed into a SQL RS report. I have created a windows application that
> > calls
> > sql reporting services. The problem that I am having now is passing the
> > multiple values the user selects from the dropdown to sql reporting
> > services.
> >
> >
> > I am setting: returnValues.Value
> >
> > When I try to set this parameters to 1;2;3, report fails. However,
> > setting
> > that value to 1 works.
> >
> > Is this possbile?
>
>|||OK, so you are doing the stored procedure method. That a good way to do it.
This whole thing work if from a web page passing in the multiple selections,
the only difference is that you are doing this from a windows app?
How are you integrating your windows app? URL integration or web services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>I have been able to get the mulitple selection parameters to work if I call
> the SQL RS report from a web page. I basically created dropdown listboxes
> and have the form post to the url of the page I want to run. The multiple
> selection values are sent to the report and the report works fine.
> However,
> I am trying to call SQL RS report from a Windows Application. I have my
> report created in a way that it uses a stored procedure to parse the
> multiple
> values passed to in and joins to those values from a temp table.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 does not support multiple selections. For instance,
>> select * from blah where somefield in (@.Param)
>> will not work. What you can do is use either an expression or call a
>> stored
>> procedure that takes the parameter and handles appropriately).
>> This will work:
>> = "select * from blah where somefield in (" & Parameters!Paramname.value
>> &
>> ")"
>> Note that this assume you have dealt with putting in all the proper
>> syntax
>> like single quotes around charater type parameters, etc and that this
>> will
>> be a valid query when done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >I have been working through a solution to allow multiple parameters to
>> >be
>> > passed into a SQL RS report. I have created a windows application that
>> > calls
>> > sql reporting services. The problem that I am having now is passing
>> > the
>> > multiple values the user selects from the dropdown to sql reporting
>> > services.
>> >
>> >
>> > I am setting: returnValues.Value
>> >
>> > When I try to set this parameters to 1;2;3, report fails. However,
>> > setting
>> > that value to 1 works.
>> >
>> > Is this possbile?
>>|||I actually need the ability to write the report to a file or display the
report in a web browser on the screen. In both cases I have to set the
Parameter values using an array.
"Bruce L-C [MVP]" wrote:
> OK, so you are doing the stored procedure method. That a good way to do it.
> This whole thing work if from a web page passing in the multiple selections,
> the only difference is that you are doing this from a windows app?
> How are you integrating your windows app? URL integration or web services?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
> >I have been able to get the mulitple selection parameters to work if I call
> > the SQL RS report from a web page. I basically created dropdown listboxes
> > and have the form post to the url of the page I want to run. The multiple
> > selection values are sent to the report and the report works fine.
> > However,
> > I am trying to call SQL RS report from a Windows Application. I have my
> > report created in a way that it uses a stored procedure to parse the
> > multiple
> > values passed to in and joins to those values from a temp table.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2000 does not support multiple selections. For instance,
> >> select * from blah where somefield in (@.Param)
> >>
> >> will not work. What you can do is use either an expression or call a
> >> stored
> >> procedure that takes the parameter and handles appropriately).
> >>
> >> This will work:
> >>
> >> = "select * from blah where somefield in (" & Parameters!Paramname.value
> >> &
> >> ")"
> >>
> >> Note that this assume you have dealt with putting in all the proper
> >> syntax
> >> like single quotes around charater type parameters, etc and that this
> >> will
> >> be a valid query when done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
> >> message
> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >> >I have been working through a solution to allow multiple parameters to
> >> >be
> >> > passed into a SQL RS report. I have created a windows application that
> >> > calls
> >> > sql reporting services. The problem that I am having now is passing
> >> > the
> >> > multiple values the user selects from the dropdown to sql reporting
> >> > services.
> >> >
> >> >
> >> > I am setting: returnValues.Value
> >> >
> >> > When I try to set this parameters to 1;2;3, report fails. However,
> >> > setting
> >> > that value to 1 works.
> >> >
> >> > Is this possbile?
> >>
> >>
> >>
>
>|||What I was trying to clarify is what does and does not work.
It sounds like calling the report from a web page using URL integration does
work. But, you are trying to use web services from your windows app (as an
alternative you can embed an IE control and use URL integration). Since you
say that a single value selected work, I wonder if some seperator character
is causing a problem. In your windows app try having a textbox that you key
in the correct value and see if that works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:A07E6166-50E6-44D8-8ED4-3E3C279EAB1E@.microsoft.com...
>I actually need the ability to write the report to a file or display the
> report in a web browser on the screen. In both cases I have to set the
> Parameter values using an array.
> "Bruce L-C [MVP]" wrote:
>> OK, so you are doing the stored procedure method. That a good way to do
>> it.
>> This whole thing work if from a web page passing in the multiple
>> selections,
>> the only difference is that you are doing this from a windows app?
>> How are you integrating your windows app? URL integration or web
>> services?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>> >I have been able to get the mulitple selection parameters to work if I
>> >call
>> > the SQL RS report from a web page. I basically created dropdown
>> > listboxes
>> > and have the form post to the url of the page I want to run. The
>> > multiple
>> > selection values are sent to the report and the report works fine.
>> > However,
>> > I am trying to call SQL RS report from a Windows Application. I have
>> > my
>> > report created in a way that it uses a stored procedure to parse the
>> > multiple
>> > values passed to in and joins to those values from a temp table.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2000 does not support multiple selections. For instance,
>> >> select * from blah where somefield in (@.Param)
>> >>
>> >> will not work. What you can do is use either an expression or call a
>> >> stored
>> >> procedure that takes the parameter and handles appropriately).
>> >>
>> >> This will work:
>> >>
>> >> = "select * from blah where somefield in (" &
>> >> Parameters!Paramname.value
>> >> &
>> >> ")"
>> >>
>> >> Note that this assume you have dealt with putting in all the proper
>> >> syntax
>> >> like single quotes around charater type parameters, etc and that this
>> >> will
>> >> be a valid query when done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >>
>> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >> >I have been working through a solution to allow multiple parameters
>> >> >to
>> >> >be
>> >> > passed into a SQL RS report. I have created a windows application
>> >> > that
>> >> > calls
>> >> > sql reporting services. The problem that I am having now is passing
>> >> > the
>> >> > multiple values the user selects from the dropdown to sql reporting
>> >> > services.
>> >> >
>> >> >
>> >> > I am setting: returnValues.Value
>> >> >
>> >> > When I try to set this parameters to 1;2;3, report fails. However,
>> >> > setting
>> >> > that value to 1 works.
>> >> >
>> >> > Is this possbile?
>> >>
>> >>
>> >>
>>

Allow a user to alter views.

Hi everyone,
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>