How to show all aliases in SQL 7 ?

Does anybody know a command that shows ALL aliases on the database?
Or could I search for them in the system table and make a SELECT for them?
pls. helpRE: Q1 Does anybody know a command that shows ALL aliases on the database?
Or could I search for them in the system table and make a SELECT for them?

pls. help

A1 To get the alias [member] or user status for each login on a Sql Server run:

exec sp_helplogins

If run without specifing a login, the information will appear in the last column (UserOrAlias) of the second result set that the sp_helplogins stored proc returns.

How to setup SelectParameters programmatically?

Hi,

I am using Visual Web Developer 2005 Express Edition.

I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example).

How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @.resortid". How do I programatically set it up so that @.resortid contains the value that is associated with "Articleid"?

My code is below.

Thank you for your advise!

Regards
Jan

/******************************************************************************** RETRIEVE INFORMATION FROM DATABASE*******************************************************************************/// specify the data sourcestring connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString;SqlConnection myConn =new SqlConnection(connContStr);// define the command queryString query ="SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @.resortid)";SqlCommand myCommand =new SqlCommand(query, myConn);// open the connection and instantiate a datareadermyConn.Open();SqlDataReader myReader = myCommand.ExecuteReader();// loop thru the readerwhile (myReader.Read()){ Label5.Text = myReader.GetInt32(0).ToString(); Label6.Text = myReader.GetInt32(1).ToString(); Label7.Text = myReader.GetInt32(2).ToString();}// close the reader and the connectionmyReader.Close();myConn.Close();

You can try the following, but you may need to change the resotid if it is an integer type.

SqlCommand myCommand =new SqlCommand(query, myConn);

myCommand.Parameters.Add("@.resortid"

,SqlDbType.NVarChar, 10).Value =Request.QueryString("resortid");|||

Something like this: 

String ArticleID;if (Request.QueryString["ArticleID"] !=null) ArticleID = Request.QueryString["ArticleID"];else// handle bad parameterSqlParameter param =new SqlParameter();param.ParameterName ="@.resortId";param.Value = ArticleID.ToInt32();myConn.Parameters.Add(param);

|||

Hi Limno and SGWellens,

Thanks for your help - I managed to get it working like a charm!

Regards

Jan

How to set the width on the multi-select drop down (not the simple select)?

Hi,

How can I set the width on the multi-select parameter box? I checked the post about setting the SELECT, but that affects the single parameter box, not the multi-select.

I know there is an HtmlViewer.css file and have changed the rsReportServer.config file to use it, but I don't know what the tag is to set the width on the drop down parameter list box in the htmlViewer.css file. Or is it in another file?

Any ideas?

Thanks.

pl

The width of the MVP parameter box is not configurable sorry to say.

|||

Thank you for your response.

Is there any chance that the ability to set the width for the multi-select parameter box might be included in RS 2005 SP1?

Also, I read somewhere that the solution Report Manager is a project that is available as a download for Visual Studio 2005. If I were to download the project, could I set this feature up myself?

Thanks.

|||Has this bug been addressed in SP1. Users are gobsmacked when they hear that the size of the multi box cannot be set to the width of the widest entry.|||

This behavior will not changed in SP1. From our customers we have seen this as a good recommendation for the next release and it is on the list (with many other suggestions) as a possible feature improvement.

How to set permissions for objects quickly

After I create a user (ref to database), then I need to assign 'select' &
'exec' rights at permissions for all objects. But there are over 1000
objects. How can I set the permissions quickly? Can I do it at query
analyzer?
Alternatively, what is the best way to setup this if want to add / rename
database username? Thanks.
"Pleo" <rx8@.hotmail.com> bl news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
g...[vbcol=seagreen]
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> referring to the login name
change
> the name of a login or a
> though.
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>
SELECT permissions on all user tables and views can be assigned by adding
users to the db_datareader fixed database role. There is no such role for
executing procs but you can assign such permissions by creating your own
role and using a script like the one below to grant permissions on all
existing stored procedures:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:un0Y5Rw0FHA.1564@.tk2msftngp13.phx.gbl...
> After I create a user (ref to database), then I need to assign 'select' &
> 'exec' rights at permissions for all objects. But there are over 1000
> objects. How can I set the permissions quickly? Can I do it at query
> analyzer?
> Alternatively, what is the best way to setup this if want to add / rename
> database username? Thanks.
> "Pleo" <rx8@.hotmail.com> bl news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
> g...
> change
>

how to set multiple local variable from single select statement

is it possible to set the value of multiple local variables using just 1
statement. For example assume a simple table 'E' with 3 columns 'empID',
'empName', 'empToken'. I can get their values with
declare @.ENAME as char(30), @.ETOKEN as int
SET @.ENAME = (SELECT empName FROM E WHERE empID = 1)
SET @.ETOKEN = (SELECT empToken FROM E WHERE empID = 1)
What I want to do is compine the two assignments into 1 single statement so
that the db never has to be looked up more than once. e.g.
SET @.ENAME, @.ETOKEN = (SELECT empName, empToken FROM E WHERE empID = 1)
is this possible and if it is, what is the synthax?try this.. hope this helps.
SELECT @.ENAME = empName,
@.ETOKEN = empToken
FROM E WHERE empID = 1
--|||HI,
sure:
SELECT @.ENAME = empName , @.ETOKEN = emptoken FROM E WHERE empID = 1
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--

How to Set dynamic column name or Change column name dynamicly

How to Change column name dynamically
Declare @.Column_name Varchar(30)
Set @.Column_name = (Select Name from Customer where ...)
Create table #temp
(
Name Varchar(30)
Date datetime
Sales Money
)
EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
It failsThis only seems to work on physical tables, i.e. not temporary ones.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M" <mxchen@.hotvoice.com> wrote in message
news:upIZpUkFGHA.2212@.TK2MSFTNGP15.phx.gbl...
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
>
>|||M wrote:
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
Posting guidelines: http://vyaskn.tripod.com/posting.htm
The code you posted will work if you are in the context of tempdb (where the
temp table is located). You may be able to execute the change from the
context of another database using dynamic SQL (see EXEC and sp_executesql in
BOL for more information)
David Gugick
Quest Software|||Don't do that.
You are trying to mix data with metadata, which is a definite No-No
Roji. P. Thomas
http://toponewithties.blogspot.com
"M" <mxchen@.hotvoice.com> wrote in message
news:upIZpUkFGHA.2212@.TK2MSFTNGP15.phx.gbl...
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
>
>|||No one else asked this: why? Why so you think you need to change schema on
the fly?
ML
http://milambda.blogspot.com/|||I want a stored procudure to return a recordset which column name set on the
fly.
Declare @.Column_name1 Varchar(30)
Declare @.Column_name2 Varchar(30)
Set @.Column_name1 = (Select Name from Customer where ...)
Set @.Column_name2 = (Select Name from Customer where ...)
Create #Temp (
@.Column_name1 Varchar(30) -- Set @.Column_name1 as Column name as column
namename
@.Column_name2 Varchar(30) --will not work but I want actual customer
[Date] datetime
)
While
Begin
Select ... into #Temp from .. where
...
End
Select * from #Temp
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DFC9E35F-FEB7-4451-B78F-DF84D2B28951@.microsoft.com...
> No one else asked this: why? Why so you think you need to change schema on
> the fly?
>
> ML
> --
> http://milambda.blogspot.com/
>|||Holy guacamole! Have you ever read anything in Books Online? Read through
this, please:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
I see no reason for the temporary table. Never use "select *" in production
code!
You need to explicitly list all columns you want in the result-set and asign
an appropriate ALIAS to set specific column names. In your specific case thi
s
is best done on the client, since you'd have to use dynamic SQL on the
server. Anyway, here goes...
You'd need something like this:
declare @.statement nvarchar(4000)
declare @.Col1Name sysname
declare @.Col2Name sysname
set @.Col1Name = 'Jim'
set @.Col2Name = 'Bob'
set @.statement = 'select <col1> as ' + @.Col1Name + '
,<col2> as ' + @.Col2Name + '
from <table>
where <conditions>'
Which constitutes:
select <col1> as Jim
,<col2> as Bob
from <table>
where <conditions>
You then have to execute the statement using EXEC:
exec (@.statement)
ML
http://milambda.blogspot.com/|||M (mxchen@.hotvoice.com) writes:
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
Apart from the very dubious in this, this works:
Declare @.Column_name Varchar(30)
Set @.Column_name = 'nisse'
Create table #temp
(
Name Varchar(30),
Date datetime,
Sales Money
)
EXEC tempdb..sp_rename '#temp.Name', @.Column_name, 'COLUMN'
select *from #temp
You need to add tempdb.. to set the context for sp_rename.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to SET CONCAT_NULL_YIELDS_NULL OFF?

I'm writing a select query that's, in effect, like this: Select FirstName + ' ' + LastName FROM Users.

How do I get just the first name to display in my gridview if last name is null?

I'm familiar with aspx and aspx.cs files, but stored procedures are beyond me right now.

You need to use ISNULL or COALESCE.

Select FirstName + ' ' + ISNULL(LastName,'') FROM Users

How To Set a Variable During an Insert Into Select From

Hello.
I'm inserting rows into a table that I retrieve from another table.
There's a lot of data manipulation going on during this process.
For 10 columns in the Select From portion I'm using a CASE statement that
starts with CASE
WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
END,
Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
table!) I though of setting a variable: Set @.MyVar =
Left(Discount_Specification, 2) and then
saying WHEN @.MyVar = @.PF etc.
I just don't know where in the logic to place this Set @.MyVar so it works
for each row that's inserted.
TIA,
RitaHard to say without DDL, but perhaps something like:
INSERT INTO ...
SELECT ds, ds + 'a', col2
FROM
(
SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
) AS t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RitaG" <RitaG@.discussions.microsoft.com> wrote in message
news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
> Hello.
> I'm inserting rows into a table that I retrieve from another table.
> There's a lot of data manipulation going on during this process.
> For 10 columns in the Select From portion I'm using a CASE statement that
> starts with CASE
> WHEN Left(Discount_Specification, 2)= @.PF THEN etc.
> END,
> Instead of doing the "Left" 10 times (10 * 8 million rows in the "From"
> table!) I though of setting a variable: Set @.MyVar =
> Left(Discount_Specification, 2) and then
> saying WHEN @.MyVar = @.PF etc.
> I just don't know where in the logic to place this Set @.MyVar so it works
> for each row that's inserted.
> TIA,
> Rita
>|||Hi Tibor,
Thanks for your response.
I'm trying to figure out how to use it along with a CASE statement.
Here's my code:
INSERT INTO MyTable(
Col1,
Col2,
etc.)
SELECT
CASE
WHEN Left(SM.Discount_Specification, 2) IN (@.P, @.L) THEN
Something
ELSE 1
END,
CASE
WHEN Left(SM.Discount_Specification, 2) = @.K THEN
SomethingElse
ELSE 1
END,
Etc.
From MyTable
Thanks,
Rita
"Tibor Karaszi" wrote:

> Hard to say without DDL, but perhaps something like:
> INSERT INTO ...
> SELECT ds, ds + 'a', col2
> FROM
> (
> SELECT LEFT(Discout_Specification, 2) AS ds, col2 FROM tbl
> ) AS t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RitaG" <RitaG@.discussions.microsoft.com> wrote in message
> news:A07835AD-AAC2-4225-BA8C-FDA70C1C0631@.microsoft.com...
>|||Is this a "lazy programmer doesn't want to type all those keystrokes" issue
or something else? It is possible that "Left(SM.Discount_Specification, 2)"
indicates a schema issue. If so, you should consider a change to the schema
to unbind the two attributes currently stored in the Discount_Specification
column. This can be done permanently via the addition of another column
(and the movement of the associated information), via a view, or via a
computed column, via a udf, etc. You can also do this via a derived table
within this particular query.
insert ...
select case when derived_discount in (@.P, @.L) then x else y end,
...
from
(select Left(SM.Discount_Specification, 2) as derived_discount,
...
from MyTable ) as t1
where ...|||Hi Scott,
No, it's not a "lazy programmer"! :-)
I just thought there may be a more efficient way since I'm dealing with a
large volume of rows (up to 10 million).
Thanks for your reponse. That was what I was looking for.
Rita
"Scott Morris" wrote:

> Is this a "lazy programmer doesn't want to type all those keystrokes" issu
e
> or something else? It is possible that "Left(SM.Discount_Specification, 2
)"
> indicates a schema issue. If so, you should consider a change to the sche
ma
> to unbind the two attributes currently stored in the Discount_Specificatio
n
> column. This can be done permanently via the addition of another column
> (and the movement of the associated information), via a view, or via a
> computed column, via a udf, etc. You can also do this via a derived table
> within this particular query.
> insert ...
> select case when derived_discount in (@.P, @.L) then x else y end,
> ...
> from
> (select Left(SM.Discount_Specification, 2) as derived_discount,
> ...
> from MyTable ) as t1
> where ...
>
>

How to set a value to TEXT column

Hi, everybody,
I tried, to paste a clipboard content into an empty text column,
and then to show the content of the text column
by using a select command, but unfortunatelly the original text was
truncated.
does anyone know, how to correctly set a value into a text type column
using standard SQL Server tools such as Enterprise manager or Query
analyzer (but w i t h o u t writting an insert/update command)?
thanks
Libor
"Libor Forejtnik" <lforejtn@.seznam.cz> wrote in message
news:clqta15kb0graa8rt7ongplh6e5cekn0ok@.4ax.com...
> does anyone know, how to correctly set a value into a text type column
> using standard SQL Server tools such as Enterprise manager or Query
> analyzer (but w i t h o u t writting an insert/update command)?
Sorry, it's not possible. Those tools are not meant to be used for data
entry. You'll have to write INSERTs/UPDATEs.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net