Prompting for input from user using SQL Server 2000...

I know how to do it in Oracle.
ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
How do you do it in SQL Server 2000?SQL Server cannot do this, as SQL Server executes entirely at the server
side. (I take it that you don't want to prompt to pop up on the server
machine?)
So, this has to be done my the client application. None of the tools that
comes with SQL Server has that feature, as they aren't designed to be
end-user tools not development packages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||Hi,
You do not have that kind of facility in SQL Server.
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||I want to create a procedure the I can run from Query Analyzer that prompts
the user for input and supplies it to the procedure.
Not a big deal, but the client can do it in Oracle and wonders why SQL
can't.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
> SQL Server cannot do this, as SQL Server executes entirely at the server
> side. (I take it that you don't want to prompt to pop up on the server
> machine?)
> So, this has to be done my the client application. None of the tools that
> comes with SQL Server has that feature, as they aren't designed to be
> end-user tools not development packages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
> news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
>|||Hi,
In Query analyzer, Object browser window , right click above the procedure
name and select the debug option. This will ask you to enter the
input parameters of the procedure.
(It may not be same as you required, but allows you to take parameter
values.)
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:GaM2c.137196$%72.44266@.twister.nyroc.rr.com...
> I want to create a procedure the I can run from Query Analyzer that
prompts
> the user for input and supplies it to the procedure.
> Not a big deal, but the client can do it in Oracle and wonders why SQL
> can't.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
that
>

Prompting for input from user using SQL Server 2000...

I know how to do it in Oracle.
ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
How do you do it in SQL Server 2000?SQL Server cannot do this, as SQL Server executes entirely at the server
side. (I take it that you don't want to prompt to pop up on the server
machine?)
So, this has to be done my the client application. None of the tools that
comes with SQL Server has that feature, as they aren't designed to be
end-user tools not development packages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||Hi,
You do not have that kind of facility in SQL Server.
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> I know how to do it in Oracle.
> ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> How do you do it in SQL Server 2000?
>|||I want to create a procedure the I can run from Query Analyzer that prompts
the user for input and supplies it to the procedure.
Not a big deal, but the client can do it in Oracle and wonders why SQL
can't.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
> SQL Server cannot do this, as SQL Server executes entirely at the server
> side. (I take it that you don't want to prompt to pop up on the server
> machine?)
> So, this has to be done my the client application. None of the tools that
> comes with SQL Server has that feature, as they aren't designed to be
> end-user tools not development packages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
> news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> > I know how to do it in Oracle.
> >
> > ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> >
> > How do you do it in SQL Server 2000?
> >
> >
>|||Hi,
In Query analyzer, Object browser window , right click above the procedure
name and select the debug option. This will ask you to enter the
input parameters of the procedure.
(It may not be same as you required, but allows you to take parameter
values.)
Thanks
Hari
MCDBA
"Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
news:GaM2c.137196$%72.44266@.twister.nyroc.rr.com...
> I want to create a procedure the I can run from Query Analyzer that
prompts
> the user for input and supplies it to the procedure.
> Not a big deal, but the client can do it in Oracle and wonders why SQL
> can't.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OOR%23rXIBEHA.2628@.TK2MSFTNGP11.phx.gbl...
> > SQL Server cannot do this, as SQL Server executes entirely at the server
> > side. (I take it that you don't want to prompt to pop up on the server
> > machine?)
> > So, this has to be done my the client application. None of the tools
that
> > comes with SQL Server has that feature, as they aren't designed to be
> > end-user tools not development packages.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Jonathan McKinney" <jonmckin@.nycap.rr.com> wrote in message
> > news:TEL2c.137059$%72.48338@.twister.nyroc.rr.com...
> > > I know how to do it in Oracle.
> > >
> > > ACCEPT <<variable>> PROMPT 'ENTER VARIABLE: '
> > >
> > > How do you do it in SQL Server 2000?
> > >
> > >
> >
> >
>

Prompt for variable values in a SQL script

I am writing a SQL script and it needs some parameters to be entered by the user. Users will run this script from the SQL Query Analyzer. I trying to see if there is a way the script will prompt the user to enter values for the parameters. This is possible in Oracle. Is there any equivalent in SQL Server?

Thanks in advance for your timeNo, you can't prompt for variables in the SQL script.

You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).

That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.

Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?

blindman|||I realize that it is a bad and dangerous practice to let the users access the database from the query analyzer. We are redesigning the system from the scratch but till then, we have to support the existing system. Prevoius DBA let this hole into the system and we have to live with this till we finish our re-design. I was almost positive what I was looking for is not possible but I just wanted to make sure. Thanks a lot for your time blindman.

Originally posted by blindman
No, you can't prompt for variables in the SQL script.

You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).

That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.

Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?

blindman

Programming Field Lengths

Is it possible to tell sql server to cast to a datatype and set the
field length to a variable.

e.g. :-

declare @.flen int
set @.flen = 10

select (cast somefield as char(@.flen) newfield)
into newtable
from sometable

I have also tried :-
select (cast somefield as char(max(len(somefield))) newfield)
into newtable
from sometable

When I try the above examples I get error in @.flen; error in max
respectivly.

TIA

Simon<bozzzza@.lycos.co.uk> wrote in message
news:1119438419.547258.218740@.z14g2000cwz.googlegr oups.com...
> Is it possible to tell sql server to cast to a datatype and set the
> field length to a variable.
> e.g. :-
> declare @.flen int
> set @.flen = 10
> select (cast somefield as char(@.flen) newfield)
> into newtable
> from sometable
> I have also tried :-
> select (cast somefield as char(max(len(somefield))) newfield)
> into newtable
> from sometable
> When I try the above examples I get error in @.flen; error in max
> respectivly.
> TIA
> Simon

I don't believe there's any easy way to do this, but in most cases, it's
probably not necessary - instead of declaring char(10), why not just declare
varchar(1000), or whatever value is suitable for you? If you can explain why
you need to do this, someone may have a better solution. Depending on what
you need to achieve, you might be able to use dynamic SQL, but that has a
number of issues:

http://www.sommarskog.se/dynamic_sql.html

Simon|||(bozzzza@.lycos.co.uk) writes:
> Is it possible to tell sql server to cast to a datatype and set the
> field length to a variable.
> e.g. :-
> declare @.flen int
> set @.flen = 10
> select (cast somefield as char(@.flen) newfield)
> into newtable
> from sometable
> I have also tried :-
> select (cast somefield as char(max(len(somefield))) newfield)
> into newtable
> from sometable
> When I try the above examples I get error in @.flen; error in max
> respectivly.

No, you would have to use dynamic SQL for that. Seems easier to use
varchar.

What do you want to achieve, really?

--
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:
> (bozzzza@.lycos.co.uk) writes:
> > Is it possible to tell sql server to cast to a datatype and set the
> > field length to a variable.
> > e.g. :-
> > declare @.flen int
> > set @.flen = 10
> > select (cast somefield as char(@.flen) newfield)
> > into newtable
> > from sometable
> > I have also tried :-
> > select (cast somefield as char(max(len(somefield))) newfield)
> > into newtable
> > from sometable
> > When I try the above examples I get error in @.flen; error in max
> > respectivly.
> No, you would have to use dynamic SQL for that. Seems easier to use
> varchar.
> What do you want to achieve, really?

Yhe problem is we have had some data supplied and the all the fields
lengths are set to 255 (nvarchar), even though this is not good pratice
we could live with it until someone else wanted a fixed length export
of the data.

So my idea was to work out the length of the fields and insert them as
the maximum width into the new table. Then the fixed length file would
look a lot better and cleaner.

Thanks for the reply, I will look into Dynamic SQL.|||(bozzzza@.lycos.co.uk) writes:
> Yhe problem is we have had some data supplied and the all the fields
> lengths are set to 255 (nvarchar), even though this is not good pratice
> we could live with it until someone else wanted a fixed length export
> of the data.
> So my idea was to work out the length of the fields and insert them as
> the maximum width into the new table. Then the fixed length file would
> look a lot better and cleaner.

Maybe. But what if the max lengths you find do agree with the actual
business rules? Next time you get a refresh, you could get an error
because of truncation.

So I would suggest that either you find out the actual max lengths, or
you leave the table the way it is.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You have asked the same question in
microsoft.public.sqlserver.programming. Please don't post the same
question independently to diffferent groups. It's inconsiderate to
others who may waste time responding on something that has already been
answered elsewhere.

In your other thread you indicated that your intention is to
standardize the column sizes for reporting purposes. All the reporting
tools I know of allow you to specify a field width shorter than the
actual column width so I'm not sure why you would want to do this in
SQL. Keep it in the presentation tier is my suggestion.

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You have asked the same question in
> microsoft.public.sqlserver.programming. Please don't post the same
> question independently to diffferent groups. It's inconsiderate to
> others who may waste time responding on something that has already been
> answered elsewhere.

Sorry.

> In your other thread you indicated that your intention is to
> standardize the column sizes for reporting purposes. All the reporting
> tools I know of allow you to specify a field width shorter than the
> actual column width so I'm not sure why you would want to do this in
> SQL. Keep it in the presentation tier is my suggestion.

Actually I needed to create a fix length text file of the data, so a
pascal programmer could import it into a DOS application, and the
programmer wasn't happy that the fields were coming out at 255 each.

After reading Erland's post, I gave the programmer the export in Comma
delimited format instead, so a refresh of the data won't effect the
export.

But thanks to all the posts I now know dynamic sql exists (I thought
exec was just for stored procedures) and it has opened up a whole new
world for me.|||Yes it is possible!

Do it this way!

eg :-

declare @.flen int
set @.flen = 10

exec('select cast(somefield as char(' + @.flen + ')) as newfield into
newtable
from oldtable')

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||> I now know dynamic sql exists (I thought
> exec was just for stored procedures) and it has opened up a whole new
> world for me.

Make sure you understand the implications. Dynamic SQL should usually
be a last resort in production code. See:
http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--

Programmatically loop over variables in the variable dispenser?

In my custom task, I would like to loop over the variables in the variable dispenser, and only modify those that are of a certain type. Is this possible?

Thanks!

I think you can use Me.variables(x).getType to get the type of each variable. If that is not correct, you can define variables name sXXX for string, iXXX for integer, etc. Then parse the variable name, get the first character and determine the variable type.|||

That would work, but I should clarify my question:

I have the VariableDispenser object passed into my Execute method. I do not know ahead of time what variables will be accessible via this instance of the VariableDispenser object. I would like to loop over each variable that is accessible (again, without knowing its name), then perform an operation on it conditionally based on its type (which I will get from the getType method).

Sorry for not being clearer in my original post!

|||I know that we can say variables(0).xxxx to read the first variable. But I am not sure whether we can get the count of variables. If you can't get the count, what you can do is read variables(0), variables(1), etc like that, and put that code in a try..catch. If you get an exception as 'index out of range', come out of the loop.|||This would be a great way to loop over all the variables assuming I could get access to the entire variables collection to begin with. The problem is that all I have is the VariableDispenser object. The VariableDispenser object methods that interact with the Variables collection (LockForRead, LockOneForRead, etc.) require that I know the name of the variables I would like to access. In my component, I do not know this upfront. Unless I am missing something (and I hope I am), I think I am out of luck?|||

Hi David,

I am not sure whether I understood your problem correctly or not. If I am confusing you, I am sorry. So, what you want is variable name and its type before calling variableDispenser object method. If that is true, it is pretty much simple.

DTS.Variables collection has 'count', 'Name' and 'DataType' properties. Loop thru this collection to get the name of a variable and its datatype. If the datatype is what you are looking for, call the VariableDispenser method using the name that you got.

Hope it will work.

|||

Thiru,

No worries. I am probably not being as clear as I could be - I always find it difficult to describe technical problems on the first 3 or 4 tries. Thanks for the help thus far.

I should have clarified from the beginning that I writing my custom task in C#. All of my code exists within the Execute method of my task. I do not appear to have the equivalent of the DTS.Variables collection available to me. Instead, all I have is the VariableDispenser object. Again, I hope I am missing something simple here.

Thanks again for your help,

David

|||

Can I ask why you want to this? How does the type identify the variables you want to modify? I ask in case there is some better way which we could suggest to acheive your business case.

Donald

|||

The answer is kind of convoluted, but here goes:

I have a package that manages the execution of child packages. Exactly what child packages are to be executed in a given run of the parent package are store in a database table. The child packages stored in the database table are looped over in a ForEach loop, and executed sequentially.

Each child package also requires a set of variables for successful execution. The list of variables required differs for each child package. The value for each of the required variables is not going to be known until run-time, immediately before the child package executes. The easiest way for me to manage this is to have a database table that lists what variables are necessary for each child package, and what expression / database query should be executed at run-time to evaluate each variable.

I have found a number of limitations (perceived or real) within the current SSIS achitecture that makes what I am trying to accomplish difficult. To get around these limitations, I have created my own set of objects to interact with the variables stored in the database, evaluate them at run-time, and put them into a single custom "variable list" object (to which an SSIS variable is assigned) that is available to the child packages.

The problem I have is that my parent package contains the variable(s) assigned to the list object(s). For maximum portability, I do not want my child packages to have to know what the names of those variables are. Instead, I would like to write a custom task that loops over all variables accessible via the variable dispenser, identifies those of my custom type, iterates through all the custom variables in the list, and assigns those values to corresponding ssis child variables (of the same name).

I know - that is quite a mouthful, and likely is not as clear as it could be. I may be going down a path that is unnecessary as I have only been playing around with SSIS for about 3 weeks now. Any help / advice is much appreciated.

Thanks,

David