How to remove 'Select a Value' from the drop down list.

I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in all
the drop down list.
I don't want 'Select a value' in the drop down list
Please advise how to remove it from the drop down listYour report has parameters and unless a default is provided you will get the
'Select a value message. If you want it to be blank, provide a blank as the
default value.|||you have to provide a default value for your lists.
without a default value, the Select a value text is displayed
"amitmittal77" <u17066@.uwe> wrote in message news:598ef6aff1ffa@.uwe...
>I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in
>all
> the drop down list.
> I don't want 'Select a value' in the drop down list
> Please advise how to remove it from the drop down list|||I am using dataset for the dropdown which have default default values
the default value setting in the report parameter Screen is
i have selected From query option in default value section
under From query folllowing values have been selected
Dataset - my dataset
Value field - Parameter value
Still i am getting 'Select a value'
Jéjé wrote:
>you have to provide a default value for your lists.
>without a default value, the Select a value text is displayed
>>I am using SQL Server 2005 RTM. In which i am getting 'Select a value' in
>>all
>> the drop down list.
>> I don't want 'Select a value' in the drop down list
>> Please advise how to remove it from the drop down list
--
Message posted via http://www.sqlmonster.comsql

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which referenced the table and
adjust the proc code for each to your liking. You can get some help in finding which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which refer
enced the table and
adjust the proc code for each to your liking. You can get some help in findi
ng which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...[v
bcol=seagreen]
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>[/vbcol]

how to reliably delete (or drop) tables

Hi folks,
I need to create three tables on the fly that change once a w (they are
frozen crosstabs) and I've decided to create them as report tables for sheer
speed.
I obviously want to create these tables in the database and not in temp and
I have complete permissions when running any code on the server
how can I reliably test for a table and either drop it or clear it only to
recreate it instantly.
I ask because I was getting plenty of errors when I did this the other day
'invalid object name etc..' 'table already exists'
should I use Object_id or the msys tables...
a little code snippet of how you do it would be very helpful
also, is it impossible to put GO between BEGIN and END or even in the body
of a sproc?
many thanks in andvance
CharlesAHere are 2 ways
if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND TABLE_NAME = ?)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TableName]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
http://sqlservercode.blogspot.com/|||DECLARE @.o INT;
SET @.o = OBJECT_ID('dbo.tablename');
IF @.o IS NOT NULL AND OBJECTPROPERTY(@.o, 'IsUserTable') = 1
BEGIN
PRINT 'dropping dbo.tablename';
DROP TABLE dbo.tablename;
END;
CREATE TABLE dbo.tablename
(
..
);
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:46529FC1-68AA-4425-A6F8-A84B79A5BFC5@.microsoft.com...
> Hi folks,
> I need to create three tables on the fly that change once a w (they are
> frozen crosstabs) and I've decided to create them as report tables for
> sheer
> speed.
> I obviously want to create these tables in the database and not in temp
> and
> I have complete permissions when running any code on the server
> how can I reliably test for a table and either drop it or clear it only to
> recreate it instantly.
> I ask because I was getting plenty of errors when I did this the other day
> 'invalid object name etc..' 'table already exists'
> should I use Object_id or the msys tables...
> a little code snippet of how you do it would be very helpful
>
> also, is it impossible to put GO between BEGIN and END or even in the body
> of a sproc?
> many thanks in andvance
> CharlesA|||Judging from your post you may have several issues but here goes:

> how can I reliably test for a table and either drop it or clear it only to
> recreate it instantly.
> I ask because I was getting plenty of errors when I did this the other day
> 'invalid object name etc..' 'table already exists'
Create a regular base table. A permanent one.

> should I use Object_id or the msys tables...
if object_id('<owner>.<table>') is not null
begin
print 'exists'
end
else
begin
print 'does not exist'
end

> also, is it impossible to put GO between BEGIN and END or even in the body
> of a sproc?
It's possible ;) but it would be syntactically incorrect.
Why would you think you need to create object dynamically? Please elaborate.
ML
http://milambda.blogspot.com/|||for ur first part let assume that the table name is --[mytable] ur
code should be...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo.mytable]
else
print 'Table not present'
u can remove else part to not print message.....
i think it will help..
ya i think it is not possible to put GO between BEGIN and END...|||hi ML,
I'm answering your question, 'why do you need to create the tables
dynamically'
because
it's a crosstab and it's a moving window on months of the year (12 at a
time) so the columns change once a month and I wish to automate the process
of creating them. It's also a read-only reporting table and not a
transactional or updatable table.
so my plan is to create the table dynamically and then to use dynamic SQL to
populate it, that bit is working already, I had incomprehensible (to me)
errors when I tried to delete and re-create these types of reporting tables
recently
Regards
CharlesA|||:-)
CharlesA|||If it's a 12 month window, then why not just do:
CREATE TABLE dbo.ReportingTable
(
DataPoint VARCHAR(32),
m1 INT,
m2 INT,
m3 INT,
m4 INT,
m5 INT,
m6 INT,
m7 INT,
m8 INT,
m9 INT,
m10 INT,
m11 INT,
m12 INT
)
GO
-- in other words, there is no reason to store '2005-01-01'
-- as a column name. This is data, not metadata.
CREATE TABLE dbo.ReportingStartMonth
(
dt SMALLDATETIME
)
GO
Now, have your starting month stored in the latter table, and the reporting
app can figure out column headers based on that (e.g. month1 =
DATEADD(MONTH, ReportingStartMonth.dt, 0), month2 = DATEADD(MONTH,
ReportingStartMonth.dt, 1), etc).
Or, better yet, store your data in a regular table, however this will
require more changes to the app:
CREATE PROCEDURE dbo.BetterReportingTable
(
Month SMALLDATETIME,
DataPoint1 INT,
DataPoint2 INT
-- , ... etc etc
)
Now you can delete data based on the time range, so you have 12 rows instead
of 12 columns, and also only process one month at a time (because it is
unlikely that data from 11 months ago really needs to be re-generated every
month). The query that retrieves this data can easily pivot it if need be,
but in my experience, most reports are going to show the data in that format
anyway, or can easily work with that format when developing the actual
display. Remember that the table structure does not have to imitate the
physical display for the end user...
Both of these solutions do not require dropping and re-creating the table;
the first is a mere truncate, the latter only requires deletes *if you need
them*... the latter also allows you to keep around years of history if you
need to, and re-create reports at will. The main reportng query can still
be rigged to only get the last 12 months...
A
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:FF70F337-5342-4182-AE7A-54E6B7A67B7D@.microsoft.com...
> hi ML,
> I'm answering your question, 'why do you need to create the tables
> dynamically'
> because
> it's a crosstab and it's a moving window on months of the year (12 at a
> time) so the columns change once a month and I wish to automate the
> process
> of creating them. It's also a read-only reporting table and not a
> transactional or updatable table.
> so my plan is to create the table dynamically and then to use dynamic SQL
> to
> populate it, that bit is working already, I had incomprehensible (to me)
> errors when I tried to delete and re-create these types of reporting
> tables
> recently
> Regards
> CharlesA
>|||I for one wouldn't mind seeing some DDL. It sounds like a job for an indexed
view. But I could be wrong.
ML
http://milambda.blogspot.com/|||Thanks Aaron,
your suggestions sound quite sane and well worth following up.
I suppose the purpose behind my hack, was the very underperformant Access
crosstab that was working with a smallish dataset (brought back via a
passthrough query), and I wanted to fix a problem under great deadline
pressure.
your suggestion that the table should not be dropped and recreated is of
course the one to follow
thanks again
regards
Charles Asql

How to regenerate a stored proc (drop & create or alter procedure) ??

Hi all.
Cf my other news: How to change or alter a user defined data type?
I need to regenerate (not only "sp_recompile") a list of stored procedures.
With the "SQL Server Enterprise Manager" it's very easy: just open each
Stored proc and save it. But how to do that by transact SQL script '
Thanks.
Lilian.What purpose does "regenerating" these serve? There are 2 ways to do this
depending on what your after. One way is to use the DROP PROCEDURE xxx
command and then issue the CREATE PROCEDURE xxx ... command. The other is
to use the ALTER PROCEDURE xxx.... command. Drop will not keep any
existing permissions and ALTER will.
--
Andrew J. Kelly
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u7Os%23CHQDHA.2228@.tk2msftngp13.phx.gbl...
> Hi all.
> Cf my other news: How to change or alter a user defined data type?
> I need to regenerate (not only "sp_recompile") a list of stored
procedures.
> With the "SQL Server Enterprise Manager" it's very easy: just open each
> Stored proc and save it. But how to do that by transact SQL script '
> Thanks.
>
> Lilian.
>sql