how to select and display a string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to select and display a string?

Author

Topic

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-05-12 : 18:29:15

SELECT 'exec sp_addlogin @loginame = ''' + su.name + ''', @sid = ' + su.sid + ' go '
FROM sysusers su
LEFT OUTER JOIN master..syslogins sl ON su.sid = sl.sid
WHERE su.islogin = 1 --su.issqluser = 1 --AND sl.sid IS NULL
AND su.name NOT IN ('guest', 'INFORMATION_SCHEMA')
And su.name NOT IN ('dbo', 'INFORMATION_SCHEMA')

hi, I want the first sentence display after execution like that:

exec sp_addlogin @loginame = 'qqq', @sid= C34V4V490BK9KJI3F93KD go

but I couldn't make it work, Can someone help me out! Thanks

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-05-12 : 19:17:23

Sysusers sid is not a varchar, so you need to cast it. Otherwise you're trying to add two different types together in the one concatenation.

change
, @sid = ' + su.sid + ' go '
to
, @sid = ' + cast(su.sid as varchar) + ' go '

In any case though, I'm not sure what you're trying to get as sid will most likely look like garbage...

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 05/12/2003 19:19:26

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 07:25:14

I think he wants the little print line that comes back after running the stored proc saying it was successful or not...

- Jeff

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-05-13 : 10:55:40

the display results after running is like that:
exec sp_addlogin @loginame = 'BusLinkAdmin', @sid = Ê€š8Q/eAŒÝ.„�E™ go
exec sp_addlogin @loginame = 'COE\jocada', @sid =
exec sp_addlogin @loginame = 'COE\marric', @sid =
exec sp_addlogin @loginame = 'COE\ricmar1', @sid =
exec sp_addlogin @loginame = 'COE\rmar', @sid =
exec sp_addlogin @loginame = 'guest1', @sid = èÙ²°q¬ EŠ ª¬ÃÿRÁ go

some characters not valid, why??? how to solve it?
Thanks

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-13 : 11:50:48

master.dbo.fn_varbintohexstr(sid)

well,

UPPER(master.dbo.fn_varbintohexstr(sid))

if you want it to have the same case hex letters.

Edited by - Arnold Fribble on 05/13/2003 11:54:18

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-13 : 12:04:45

''' + su.name + '''
I don't understnad why this one need to use three colons( ''') instead of two.
somebody can explain?

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 12:13:23

I don't think it's three...but try the following and you'll see:

Dealing with quotes is a pain, so I try to keep the separate in the conct...

Did see the other day about using chr, but I forget what value it was...

Anyway:

SELECT ''+'A'+''
GO
SELECT ''''+'A'+''''
GO

See the difference?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 12:23:16

I don't know why this is so hard to find it BOL, but you can see all of the Characters this way:


SET NOCOUNT ON
DECLARE @y TABLE(col1 char(1))
DECLARE @x Int
SELECT @x=0
WHILE @X < 300 
	BEGIN
		INSERT INTO @y SELECT CHAR(@x)
	SELECT @X = @x +1 
	END
SELECT * FROM @y WHERE col1 IS NOT NULL
GO

Brett

8-)

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-05-13 : 12:55:00

Thanks everyone, especially Arnold Fribble
It works!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-13 : 14:54:09

Tally tables are so useful:


SELECT n, CHAR(n)
FROM Numbers
WHERE n BETWEEN 32 AND 255
  AND NOT(UNICODE(CHAR(n)) BETWEEN 127 AND 159)

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-13 : 18:02:54

Bret,

quote:
Did see the other day about using chr

No, I didn't see it. where can i find more information about using quotes?

Thanks
Jung

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-13 : 18:08:34

one more question,
what the differences between select '' + 'A' + '' and select 'A'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources