ALTER TABLE to add a column between other columns

I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>

ALTER TABLE table ADD column_name

Hello,
I=B4m trying to add a new column in SQL Server 2000, but=20
in the specific table I don=B4t get, in other tables I get.=20
This table has 7000 rows and when I try, the server get=20
processing and dont=B4t finish. I already wait for 20=20
minutes and nothing.=20
Anyone can help me?
Thanks,
DanielaDaniela
I assume you did it by EM and not by QA
This is T-SQL example how to add a new column
CREATE TABLE Test
(
col INT
)
GO
INSERT INTO Test VALUES (1)
GO
ALTER TABLE Test ADD col1 CHAR(1) NOT NULL DEFAULT 'A'
GO
SELECT * FROM Test
go
DROP TABLE Test
"Daniela" <daniela@.perfilcs.com.br> wrote in message
news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
Hello,
Im trying to add a new column in SQL Server 2000, but
in the specific table I dont get, in other tables I get.
This table has 7000 rows and when I try, the server get
processing and dontt finish. I already wait for 20
minutes and nothing.
Anyone can help me?
Thanks,
Daniela|||I did it by QA. In other tables I get, but only one I=20
don=B4t get. This table has many relations.=20
I don=B4t know what to do... Help.
Thanks,
Daniela

>--Original Message--
>Daniela
>I assume you did it by EM and not by QA
>This is T-SQL example how to add a new column
>CREATE TABLE Test
>(
> col INT
> )
>GO
>INSERT INTO Test VALUES (1)
>GO
>ALTER TABLE Test ADD col1 CHAR(1) NOT NULL DEFAULT 'A'
>GO
>SELECT * FROM Test
>go
>DROP TABLE Test
>
>"Daniela" <daniela@.perfilcs.com.br> wrote in message
> news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
>Hello,
> I=B4m trying to add a new column in SQL Server 2000, but
>in the specific table I don=B4t get, in other tables I get.
>This table has 7000 rows and when I try, the server get
>processing and dont=B4t finish. I already wait for 20
>minutes and nothing.
> Anyone can help me?
> Thanks,
>Daniela
>
>.
>|||Perhaps you are bang blocked by another process. Check current activity
with sp_who.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniela" <daniela@.perfilcs.com.br> wrote in message
news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
Hello,
Im trying to add a new column in SQL Server 2000, but
in the specific table I dont get, in other tables I get.
This table has 7000 rows and when I try, the server get
processing and dontt finish. I already wait for 20
minutes and nothing.
Anyone can help me?
Thanks,
Daniela|||Hi,
Uri can probably confirm this, but I've always understood that if you change
a table with Enterprise Manager, what it actually does is to copy the data
to a temp table, delete the original, then create a new table with the same
name as the old one, but with the changed properties, then copy all the data
back. This is why a) it can take so long, and b) when you later try to pick
up the table's properties in Query Analyzer it says that the object has been
dropped and re-created, so you have to refresh at the database level.
The most time-effective day I ever spent was learning how to use the ALTER
TABLE commands :-)
Richard
"Daniela" <daniela@.perfilcs.com.br> wrote in message
news:113de01c41015$b05c1980$a501280a@.phx
.gbl...
Hello,
Im trying to add a new column in SQL Server 2000, but
in the specific table I dont get, in other tables I get.
This table has 7000 rows and when I try, the server get
processing and dontt finish. I already wait for 20
minutes and nothing.
Anyone can help me?
Thanks,
Danielasql

Alter Table - Add a field to a table in a specific location

I'm using the command to add a field to my table which has 20 fields.

ALTER TABLE table_name ADD column_name datatype

This adds the new field to the bottom of the table as the 21st first. How can I make it so it shows up as the 5th field in the table?

Wow you would have to do some work to get that to happen. It can be done but why?

The quick and dirty way is to copy all records into a temp table. DROP and reCREATE the table with the fields in the order of your preference. Then import the records from the temp table.

Adamus

|||The reason why I want to add it to a specific spot is to keep my table organized. The field that I am adding is a status field and I want it to be next to the other status fields and not just put it randomly at the bottom.

When you modify tables in Enterprise Manager it's real easy to change the location of fields...simply by drag and drop. This leads me to believe that there's a sql command that will do the same thing I'm just not sure what the command is.|||

Behind the 'scenes', Enterprise Manager does just like Adamus indicated. It creates a temp table, transfers the data, drops the old table, and renames the temp table.

There is no 'magic' to Enterprise Manager -it just writes the code for you -and sometimes not the best code either...

|||

Bank5,

this order is actually important only for the human user, as applications do not care so much about it (at least SHOULD NOT). Why don't you just create a view with correct column order which you can later use instead of table? Garnet Chaney posted an article about this, you can read it here

Also, "ALTER TABLE syntax for changing column order" feature is considered for the next release - if you think it would be useful you can vote here.

Cheers,

michalz

Alter a column to allow null or not null values if meets criteria

I'm very new working with SQL server and I'm trying to create a check
constraint that will allow the an specific field to be null only if the
result on a second field is zero and not null if this result is greater
than zero.
My boss is pushing me to implement this criteria on my SQL database
right away please Help...For example:
ALTER TABLE your_table
ADD CONSTRAINT ck_constraint_name
CHECK ((col1 IS NULL AND col2 = 0)
OR (col1 IS NOT NULL AND col2 > 0)) ;
I hope your boss intends that you apply this to a TEST system and TEST
the impact rather than go right away into production...
David Portas
SQL Server MVP
--|||Thanks David, It works perfect you are the best...|||May be easier to write a trigger that examines the inserted table to check
for the values.
"imagabo" <imagabo@.hotmail.com> wrote in message
news:1130424448.351441.168020@.g47g2000cwa.googlegroups.com...
> I'm very new working with SQL server and I'm trying to create a check
> constraint that will allow the an specific field to be null only if the
> result on a second field is zero and not null if this result is greater
> than zero.
> My boss is pushing me to implement this criteria on my SQL database
> right away please Help...
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||Hello Jen,
The person in question is probably already in, but using a
role / group such as BUILTIN\ADMINISTRATORS.
Thats the reason why they can still do the delete, insert
and update stuff as well as the select.
Look under the security, logons and see which types are
Windows Groups, then have a chat to your Server Bods to
see if the user is already included in the group.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Hi,
>I am currently having problems assigning read only
privileges to a database
>for a specific user.
>Basically, I have an application which uses Windows
Authentication to access
>a database on SQL server 2000. I only want this user to
have read only access
>to this database. I have added the user into the Logins
in SQL server (i.e.
>domain\username) and granted them the db_datareader role
to that database. I
>was assuming that this would only allow them to have read
access to the
>database, but this is not the case as they can add,
modify and delete records.
>Any help/advice would be appreciated as this is driving
me mad.
>Thanks,
>Jen
>.
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
--
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only acces
s
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete record
s.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a databas
e
> for a specific user.
> Basically, I have an application which uses Windows Authentication to acce
ss
> a database on SQL server 2000. I only want this user to have read only acc
ess
> to this database. I have added the user into the Logins in SQL server (i.e
.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete reco
rds.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing access to Enterprise Manager without giving admin rights.

I have a user that will be doing specific updates to a specific table
in SQL. Rather than have them working at the server when these needed
to be done, I thought I would install SQL Admin Tools at their
workstation. Does anyone know if I can do this, and allow him use of
Enterprise Manager to access this table, without giving him admin
rights. He will need to import an excel file into this table
periodically.
Thanks.There's no need to give him Admin rights in order to update a table on
occaison.
Simply grant him insert/update/delete permissions to the specific table.
Then write some vb code to insert the data from Excel to SQL.
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
Or
Create a DTS Package on the server. Have the user put his Excel file on a
server share, and then
periodically have the DTS package scheduled to run and process the data.
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data
Transformation
http://support.microsoft.com/?id=319951
Or
You could simply give him db_datareader, db_datawriter in the database.
See Fixed Database Roles in SQL Books Online
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

all tablenames having a specific column

Hi
Is there a way to get a list of all tables having a specific tablename
Ex. Give me all tables names having a column name 'customer_id'
Kind Regards
RoelHi,
select object_name(id) , name from syscolumns where name = 'customer_id'
Thanks
Hari
MCDBA
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>|||SELECT c.table_name
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE t.table_type = 'BASE TABLE'
AND c.column_name = 'customer_id'
Jacco Schalkwijk
SQL Server MVP
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:%23nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>|||Syscolumns not only includes the columns in each table, but also columns for
views and functions and parameters of stored procedures and functions.
What you want is:
select object_name(id) AS table_name , name from syscolumns where name ='customer_id'
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
but using the information_schema views (see my other post) is simpler.
Jacco Schalkwijk
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPj021a6DHA.1664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> select object_name(id) , name from syscolumns where name = 'customer_id'
> Thanks
> Hari
> MCDBA
> "Roel" <rvdbrand@.tycoint.com> wrote in message
> news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > Is there a way to get a list of all tables having a specific tablename
> > Ex. Give me all tables names having a column name 'customer_id'
> >
> > Kind Regards
> > Roel
> >
> >
>|||SELECT SO.Name
FROM SysObjects SO INNER JOIN SysColumns SC
ON SO.ID = SC.ID
WHERE (
SO.XType = 'U'
AND SC.Name = 'YourColumnName'
)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||thx
all queyies have the same output
Regards
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

all tablenames having a specific column

Hi
Is there a way to get a list of all tables having a specific tablename
Ex. Give me all tables names having a column name 'customer_id'
Kind Regards
RoelHi,
select object_name(id) , name from syscolumns where name = 'customer_id'
Thanks
Hari
MCDBA
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

|||SELECT c.table_name
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE t.table_type = 'BASE TABLE'
AND c.column_name = 'customer_id'
Jacco Schalkwijk
SQL Server MVP
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:%23nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

|||Syscolumns not only includes the columns in each table, but also columns for
views and functions and parameters of stored procedures and functions.
What you want is:
select object_name(id) AS table_name , name from syscolumns where name =
'customer_id'
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
but using the information_schema views (see my other post) is simpler.
Jacco Schalkwijk
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPj021a6DHA.1664@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> select object_name(id) , name from syscolumns where name = 'customer_id'
> Thanks
> Hari
> MCDBA
> "Roel" <rvdbrand@.tycoint.com> wrote in message
> news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
>

---

|||SELECT SO.Name
FROM SysObjects SO INNER JOIN SysColumns SC
ON SO.ID = SC.ID
WHERE (
SO.XType = 'U'
AND SC.Name = 'YourColumnName'
)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||thx
all queyies have the same output
Regards
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

All parameters as default

Using SSRS 2000, I want to be able to have all the results displayed as
default rather than a specific value set for default before the user clicks
on choosing a specific parameter value.
Is this possible?
Thanks for any suggestions!If what you want to do is use default parameter values and hide their
values from the user then open the Report Parameters dialogue box in
report designer and remove any values from the Prompt window. This
will cause the parameters to not display.|||Thank you for your reponse!
I actually want the user to be able to see and choose the parameters, but I
want the default to be all possible values and not one value for the cases
where they don't want to limit the results.
"toolman" wrote:
> If what you want to do is use default parameter values and hide their
> values from the user then open the Report Parameters dialogue box in
> report designer and remove any values from the Prompt window. This
> will cause the parameters to not display.
>|||I have on occasion provided an "ALL" option to my parameter and set the
default parameter to "ALL".
In SQL, the parameter list is loaded from a dataset as
SELECT <FieldName> AS OutName FROM <TableName>
UNION ALL
SELECT "All" AS OutName FROM <TableName>
In my data dataset the code becomes:
SELECT...
FROM ...
WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
"SK" wrote:
> Thank you for your reponse!
> I actually want the user to be able to see and choose the parameters, but I
> want the default to be all possible values and not one value for the cases
> where they don't want to limit the results.
> "toolman" wrote:
> > If what you want to do is use default parameter values and hide their
> > values from the user then open the Report Parameters dialogue box in
> > report designer and remove any values from the Prompt window. This
> > will cause the parameters to not display.
> >
> >|||Thank you very much William, for responding!
I think this is exactly what I was looking for!
I created a stored procedure in SQL Server and it works fine in the data
tab. When I run it the "Define Query Parameters" dialog box comes up and I
put 'All' and it works fine, giving me all the values. The problem is when I
preview it, it gives me only one value. I've put 'All' as the default under
the report parameter so I'm not sure what's wrong exactly!
"William" wrote:
> I have on occasion provided an "ALL" option to my parameter and set the
> default parameter to "ALL".
> In SQL, the parameter list is loaded from a dataset as
> SELECT <FieldName> AS OutName FROM <TableName>
> UNION ALL
> SELECT "All" AS OutName FROM <TableName>
> In my data dataset the code becomes:
> SELECT...
> FROM ...
> WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
>
> "SK" wrote:
> > Thank you for your reponse!
> >
> > I actually want the user to be able to see and choose the parameters, but I
> > want the default to be all possible values and not one value for the cases
> > where they don't want to limit the results.
> >
> > "toolman" wrote:
> >
> > > If what you want to do is use default parameter values and hide their
> > > values from the user then open the Report Parameters dialogue box in
> > > report designer and remove any values from the Prompt window. This
> > > will cause the parameters to not display.
> > >
> > >|||Have you altered your detail dataset to test the parameter for the "All'
option?
SELECT...
FROM ...
WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
AND ....
"SK" wrote:
> Thank you very much William, for responding!
> I think this is exactly what I was looking for!
> I created a stored procedure in SQL Server and it works fine in the data
> tab. When I run it the "Define Query Parameters" dialog box comes up and I
> put 'All' and it works fine, giving me all the values. The problem is when I
> preview it, it gives me only one value. I've put 'All' as the default under
> the report parameter so I'm not sure what's wrong exactly!
>
> "William" wrote:
> > I have on occasion provided an "ALL" option to my parameter and set the
> > default parameter to "ALL".
> >
> > In SQL, the parameter list is loaded from a dataset as
> >
> > SELECT <FieldName> AS OutName FROM <TableName>
> > UNION ALL
> > SELECT "All" AS OutName FROM <TableName>
> >
> > In my data dataset the code becomes:
> >
> > SELECT...
> > FROM ...
> > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >
> >
> >
> > "SK" wrote:
> >
> > > Thank you for your reponse!
> > >
> > > I actually want the user to be able to see and choose the parameters, but I
> > > want the default to be all possible values and not one value for the cases
> > > where they don't want to limit the results.
> > >
> > > "toolman" wrote:
> > >
> > > > If what you want to do is use default parameter values and hide their
> > > > values from the user then open the Report Parameters dialogue box in
> > > > report designer and remove any values from the Prompt window. This
> > > > will cause the parameters to not display.
> > > >
> > > >|||Yes, I tried it again and it gives me the correct number of records, but they
all have the same values when "All" is chosen. The data tab gives perfect
result, but as I found it often to be the case, the preview does not mirror
the result in the data tab!
"William" wrote:
> Have you altered your detail dataset to test the parameter for the "All'
> option?
> SELECT...
> FROM ...
> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> AND ....
>
> "SK" wrote:
> > Thank you very much William, for responding!
> >
> > I think this is exactly what I was looking for!
> > I created a stored procedure in SQL Server and it works fine in the data
> > tab. When I run it the "Define Query Parameters" dialog box comes up and I
> > put 'All' and it works fine, giving me all the values. The problem is when I
> > preview it, it gives me only one value. I've put 'All' as the default under
> > the report parameter so I'm not sure what's wrong exactly!
> >
> >
> >
> > "William" wrote:
> >
> > > I have on occasion provided an "ALL" option to my parameter and set the
> > > default parameter to "ALL".
> > >
> > > In SQL, the parameter list is loaded from a dataset as
> > >
> > > SELECT <FieldName> AS OutName FROM <TableName>
> > > UNION ALL
> > > SELECT "All" AS OutName FROM <TableName>
> > >
> > > In my data dataset the code becomes:
> > >
> > > SELECT...
> > > FROM ...
> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> > >
> > >
> > >
> > > "SK" wrote:
> > >
> > > > Thank you for your reponse!
> > > >
> > > > I actually want the user to be able to see and choose the parameters, but I
> > > > want the default to be all possible values and not one value for the cases
> > > > where they don't want to limit the results.
> > > >
> > > > "toolman" wrote:
> > > >
> > > > > If what you want to do is use default parameter values and hide their
> > > > > values from the user then open the Report Parameters dialogue box in
> > > > > report designer and remove any values from the Prompt window. This
> > > > > will cause the parameters to not display.
> > > > >
> > > > >|||The reason it doesn't match is that the preview tab uses cached results
unless the value of the parameters you pick change. So if no parameters or
the parameters don't change then it will use the cached value. Look where
you rdl files are stored. You will see files called reportname.rdl.data,
these files have the cached data used by the preview tab. Delete the file
and it will requery the database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SK" <SK@.discussions.microsoft.com> wrote in message
news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> Yes, I tried it again and it gives me the correct number of records, but
> they
> all have the same values when "All" is chosen. The data tab gives perfect
> result, but as I found it often to be the case, the preview does not
> mirror
> the result in the data tab!
>
> "William" wrote:
>> Have you altered your detail dataset to test the parameter for the "All'
>> option?
>> SELECT...
>> FROM ...
>> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
>> AND ....
>>
>> "SK" wrote:
>> > Thank you very much William, for responding!
>> >
>> > I think this is exactly what I was looking for!
>> > I created a stored procedure in SQL Server and it works fine in the
>> > data
>> > tab. When I run it the "Define Query Parameters" dialog box comes up
>> > and I
>> > put 'All' and it works fine, giving me all the values. The problem is
>> > when I
>> > preview it, it gives me only one value. I've put 'All' as the default
>> > under
>> > the report parameter so I'm not sure what's wrong exactly!
>> >
>> >
>> >
>> > "William" wrote:
>> >
>> > > I have on occasion provided an "ALL" option to my parameter and set
>> > > the
>> > > default parameter to "ALL".
>> > >
>> > > In SQL, the parameter list is loaded from a dataset as
>> > >
>> > > SELECT <FieldName> AS OutName FROM <TableName>
>> > > UNION ALL
>> > > SELECT "All" AS OutName FROM <TableName>
>> > >
>> > > In my data dataset the code becomes:
>> > >
>> > > SELECT...
>> > > FROM ...
>> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
>> > >
>> > >
>> > >
>> > > "SK" wrote:
>> > >
>> > > > Thank you for your reponse!
>> > > >
>> > > > I actually want the user to be able to see and choose the
>> > > > parameters, but I
>> > > > want the default to be all possible values and not one value for
>> > > > the cases
>> > > > where they don't want to limit the results.
>> > > >
>> > > > "toolman" wrote:
>> > > >
>> > > > > If what you want to do is use default parameter values and hide
>> > > > > their
>> > > > > values from the user then open the Report Parameters dialogue box
>> > > > > in
>> > > > > report designer and remove any values from the Prompt window.
>> > > > > This
>> > > > > will cause the parameters to not display.
>> > > > >
>> > > > >|||That makes sense, thank you!
But, it still didn't seem to work in this case!
If I use the below in the dataset, I get the all the parameters right, excep
the "All" and
SELECT...
FROM ...
WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
AND ....
And if I call a stored procedure in the dataset, I get all the values and
not just the parameter value that I choose.
"Bruce L-C [MVP]" wrote:
> The reason it doesn't match is that the preview tab uses cached results
> unless the value of the parameters you pick change. So if no parameters or
> the parameters don't change then it will use the cached value. Look where
> you rdl files are stored. You will see files called reportname.rdl.data,
> these files have the cached data used by the preview tab. Delete the file
> and it will requery the database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> > Yes, I tried it again and it gives me the correct number of records, but
> > they
> > all have the same values when "All" is chosen. The data tab gives perfect
> > result, but as I found it often to be the case, the preview does not
> > mirror
> > the result in the data tab!
> >
> >
> >
> > "William" wrote:
> >
> >> Have you altered your detail dataset to test the parameter for the "All'
> >> option?
> >> SELECT...
> >> FROM ...
> >> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> >> AND ....
> >>
> >>
> >> "SK" wrote:
> >>
> >> > Thank you very much William, for responding!
> >> >
> >> > I think this is exactly what I was looking for!
> >> > I created a stored procedure in SQL Server and it works fine in the
> >> > data
> >> > tab. When I run it the "Define Query Parameters" dialog box comes up
> >> > and I
> >> > put 'All' and it works fine, giving me all the values. The problem is
> >> > when I
> >> > preview it, it gives me only one value. I've put 'All' as the default
> >> > under
> >> > the report parameter so I'm not sure what's wrong exactly!
> >> >
> >> >
> >> >
> >> > "William" wrote:
> >> >
> >> > > I have on occasion provided an "ALL" option to my parameter and set
> >> > > the
> >> > > default parameter to "ALL".
> >> > >
> >> > > In SQL, the parameter list is loaded from a dataset as
> >> > >
> >> > > SELECT <FieldName> AS OutName FROM <TableName>
> >> > > UNION ALL
> >> > > SELECT "All" AS OutName FROM <TableName>
> >> > >
> >> > > In my data dataset the code becomes:
> >> > >
> >> > > SELECT...
> >> > > FROM ...
> >> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >> > >
> >> > >
> >> > >
> >> > > "SK" wrote:
> >> > >
> >> > > > Thank you for your reponse!
> >> > > >
> >> > > > I actually want the user to be able to see and choose the
> >> > > > parameters, but I
> >> > > > want the default to be all possible values and not one value for
> >> > > > the cases
> >> > > > where they don't want to limit the results.
> >> > > >
> >> > > > "toolman" wrote:
> >> > > >
> >> > > > > If what you want to do is use default parameter values and hide
> >> > > > > their
> >> > > > > values from the user then open the Report Parameters dialogue box
> >> > > > > in
> >> > > > > report designer and remove any values from the Prompt window.
> >> > > > > This
> >> > > > > will cause the parameters to not display.
> >> > > > >
> >> > > > >
>
>|||I am trying the same thing the problem is that I need to do this for
several different fields. If I select "all", all the records show. But
when I change one to something other then "All" I get zero records
shown..
Randy
--
Bruce L-C [MVP] wrote:
> The reason it doesn't match is that the preview tab uses cached results
> unless the value of the parameters you pick change. So if no parameters or
> the parameters don't change then it will use the cached value. Look where
> you rdl files are stored. You will see files called reportname.rdl.data,
> these files have the cached data used by the preview tab. Delete the file
> and it will requery the database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:327904F9-F352-4BE7-9887-88AC6F522979@.microsoft.com...
> > Yes, I tried it again and it gives me the correct number of records, but
> > they
> > all have the same values when "All" is chosen. The data tab gives perfect
> > result, but as I found it often to be the case, the preview does not
> > mirror
> > the result in the data tab!
> >
> >
> >
> > "William" wrote:
> >
> >> Have you altered your detail dataset to test the parameter for the "All'
> >> option?
> >> SELECT...
> >> FROM ...
> >> WHERE (<DataFieldName> = @.param1 or @.param1 = 'All')
> >> AND ....
> >>
> >>
> >> "SK" wrote:
> >>
> >> > Thank you very much William, for responding!
> >> >
> >> > I think this is exactly what I was looking for!
> >> > I created a stored procedure in SQL Server and it works fine in the
> >> > data
> >> > tab. When I run it the "Define Query Parameters" dialog box comes up
> >> > and I
> >> > put 'All' and it works fine, giving me all the values. The problem is
> >> > when I
> >> > preview it, it gives me only one value. I've put 'All' as the default
> >> > under
> >> > the report parameter so I'm not sure what's wrong exactly!
> >> >
> >> >
> >> >
> >> > "William" wrote:
> >> >
> >> > > I have on occasion provided an "ALL" option to my parameter and set
> >> > > the
> >> > > default parameter to "ALL".
> >> > >
> >> > > In SQL, the parameter list is loaded from a dataset as
> >> > >
> >> > > SELECT <FieldName> AS OutName FROM <TableName>
> >> > > UNION ALL
> >> > > SELECT "All" AS OutName FROM <TableName>
> >> > >
> >> > > In my data dataset the code becomes:
> >> > >
> >> > > SELECT...
> >> > > FROM ...
> >> > > WHERE <DataFieldName> = @.param1 or @.param1 = 'All'
> >> > >
> >> > >
> >> > >
> >> > > "SK" wrote:
> >> > >
> >> > > > Thank you for your reponse!
> >> > > >
> >> > > > I actually want the user to be able to see and choose the
> >> > > > parameters, but I
> >> > > > want the default to be all possible values and not one value for
> >> > > > the cases
> >> > > > where they don't want to limit the results.
> >> > > >
> >> > > > "toolman" wrote:
> >> > > >
> >> > > > > If what you want to do is use default parameter values and hide
> >> > > > > their
> >> > > > > values from the user then open the Report Parameters dialogue box
> >> > > > > in
> >> > > > > report designer and remove any values from the Prompt window.
> >> > > > > This
> >> > > > > will cause the parameters to not display.
> >> > > > >
> >> > > > >