Showing posts with label query. Show all posts
Showing posts with label query. Show all posts

Friday, March 30, 2012

How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)


Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:

Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.



Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:

You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!

Wednesday, March 28, 2012

How to construct a like compariso when there are special characts

How do I construct a like compariso query when there are special characts
such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
--
bicbic,
use a scape character.
Example:
select *
from
(
select 'abc[Y]def' as c1
union all
select 'abc[Y]def' as c1
) as t1
where c1 like '%\[Y\]%' escape ''
go
AMB
"bic" wrote:

> How do I construct a like compariso query when there are special characts
> such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> --
> bic|||You can use the ESCAPE clause. In this example I'm using '!' as the escape
character:
DECLARE @.x VARCHAR(100)
SELECT @.x = '[Y]'
SELECT
CASE
WHEN @.x LIKE '![Y!]' ESCAPE '!' THEN 'Match'
ELSE 'No Match' END AS Answer
"bic" <bic@.discussions.microsoft.com> wrote in message
news:B548D713-0022-47D1-A0E4-47630561159C@.microsoft.com...
> How do I construct a like compariso query when there are special characts
> such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> --
> bic

How to consolidate multiple rows into a single column

Hello,
I would like some help on developing a SQL query.
I have a Team table and a Person Table. For simplicity sake, lets say
the Team has a key and team name. The Person has a person key, team
key, and person name.
I want to query for all team members, and store the results in a single
column. So, the resulting view would have three columns: team key, team
name, and a list of all people on the team.
Any pointers or tips appreciated,
J Wolfgang GoerlichSELECT T.team_key, T.team_name, P.person_name
FROM Team AS T
JOIN Person AS P
ON T.team_key = P.team_key ;
David Portas
SQL Server MVP
--|||Here is a sample:
=====
CREATE TABLE Team
(
TeamID INT,
TeamName VARCHAR(20)
)
GO
CREATE TABLE Person
(
PersonID INT,
TeamID INT,
PersonName VARCHAR(50)
)
GO
INSERT INTO Team VALUES (1, 'Development')
INSERT INTO Team VALUES (2, 'Release')
INSERT INTO Person VALUES (1, 1, 'Bob')
INSERT INTO Person VALUES (2, 1, 'Mason')
INSERT INTO Person VALUES (3, 2, 'Chris')
INSERT INTO Person VALUES (4, 2, 'Scott')
INSERT INTO Person VALUES (5, 2, 'Bruce')
GO
=====
The above just creates some sample tables and data. Now, we can define a
function that concatenates the list of team members as follows:
=====
IF (OBJECT_ID ('dbo.formTeamList') IS NOT NULL)
DROP FUNCTION dbo.formTeamList
GO
CREATE FUNCTION dbo.formTeamList (@.teamID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.teamList VARCHAR(8000); SET @.teamList = ''
SELECT
@.teamList = @.teamList + ', ' + ISNULL (PersonName, '')
FROM
Person
WHERE
TeamID = @.teamID
RETURN (STUFF (@.teamList, 1, 2, ''))
END
GO
=====
Once done, we can test this out as follows:
=====
SELECT
TeamID, TeamName, dbo.formTeamList (TeamID)
FROM
Team
=====
Although the method shown above works, it is not recommended as there are
limitations:
(1) The function is called for every row in the team table. This can cause
performance problems for large lists.
(2) The function can only concatenate a list of 8000 characters in length
(4000 if you are using UniCode).
Such logic is usually best handled in the application tier of your program,
sinnce you have good flexibility to rotate rows into columns.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<jwgoerlich@.gmail.com> wrote in message
news:1131967790.112616.168370@.g44g2000cwa.googlegroups.com...
> Hello,
> I would like some help on developing a SQL query.
> I have a Team table and a Person Table. For simplicity sake, lets say
> the Team has a key and team name. The Person has a person key, team
> key, and person name.
> I want to query for all team members, and store the results in a single
> column. So, the resulting view would have three columns: team key, team
> name, and a list of all people on the team.
> Any pointers or tips appreciated,
> J Wolfgang Goerlich
>

Friday, March 23, 2012

How To Connect Remote Server's DataBase from another's Server's QueryWindow

Please Help Me to do this...

Consider i hv already logged in

sql server 2005
ip no : 192.168.2.20
db name: framework

from this query window is it possible to access the objects(tables,stored porc's)
of another server's database

for example ,

sql server 2005
ip no : 192.168.2.21
db name: framework1

...................................................

Im able to access the same server's db objects
consider im frame work db
declare @.qry as nvarchar(max)
set @.qry= '
use [master]
select * from [Sales]
'
exec (@.qry)
... but when i use use statement like,(its not working)
use 192.168.2.21
use [master]

Please help me out .....

No it is not possible from that query window unless you use linked servers.

Take a look at: http://www.sqlservercentral.com/columnists/ksonti/anintroductiontolinkedservers.asp

Martin

|||If the servers are part of a replicated topology you will have to set the server option for data access to true,

is sp_serveroption 'ServerIWishToQuery','Data Access','True'

and then

select * from ServerIWishToQuery.Database.dbo.TableName|||Thank u Hilary...

It was really helpful...

as u said i executed

exec sys.sp_serveroption [192.168.2.20],'data access','true'

It is Giving this type of error ...

Msg 15015, Level 16, State 1, Procedure sp_serveroption, Line 101
The server '192.168.2.20' does not exist. Use sp_helpserver to show available servers.

I think we have to link the servers ..

can u please guide me how to do it.......

Monday, March 12, 2012

How to Config Virtual Directory for HTTP Access to Achieve XML Template Query in SQL Server2005?

Hi,

I have built a system with xml template query using SQL Server2000.

Now ,I want to update it with SQL Server2005.

The problem is how to config the virtual directory?

In SQL 2005 the SQLXML ISAPI virtual directories are no longer being shipped. Instead we have things like SOAP endpoints and ASP.Net that can provide you HTTP access to your data. If these do not work for you, your SQL 2000 virtual directories should work against SQL 2005, and in addition SQLXML 3 SP3 is available as a Web Release and works against SQL 2005:

http://www.microsoft.com/downloads/details.aspx?FamilyID=51d4a154-8e23-47d2-a033-764259cfb53b&DisplayLang=en

Friday, March 9, 2012

how to concatenate the xml returned in different rows, and different select statements

hi
How would I write my query to concatenate the xml segment returned from
different rows, and from different select statements
Look at FOR XML...
E.g.,
declare @.x1 xml, @.x2 xml;
set @.x1 = '<a/>';
set @.x2 = '<b/>';
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169742062.183408.76500@.j27g2000cwj.googlegro ups.com...
> hi
> How would I write my query to concatenate the xml segment returned from
> different rows, and from different select statements
>
|||I would recommend you go with FOR XML, as Michael Rys suggested. However, if for
some reason you're unable or not willing to use that method, you can always
convert your instances to a string type, concatenate them, and convert back to
XML
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169742062.183408.76500@.j27g2000cwj.googlegro ups.com...
> hi
> How would I write my query to concatenate the xml segment returned from
> different rows, and from different select statements
>

How to concate 2 ore more text fields into one field?

Hello,

I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.

Something like - Note: the codes below doesn't work!!

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA

FETCH NEXT FROM _rows INTO @.AccID

WHILE (@.@.fetch_status <> -1)
BEGIN

UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @.AccID)

WHERE AccID = @.AccID


FETCH NEXT FROM _rows INTO @.AccID

END

Thanks in advance

I tested something like this:(Assume you are using SQL Server 2000 text fields)

You can try it out in your code.

DECLARE @.ptr varbinary(50),@.ptr2 varbinary(50)

DECLARE @.len int, @.len2 int

update Table_B SET colB=(SELECT colA FROM Table_A where Accid=1) WHERE Accid=1

select @.ptr = TEXTPTR(colB), @.len=datalength(colB), @.ptr2 = TEXTPTR(report), @.len2=datalength(report) from Table_B WHERE Accid=1

UPDATETEXT Table_B.report @.ptr2 @.len2 0 Table_B.colB @.ptr

In SQL Server 2005, you can define Varchar(MAX) field, it will be a lot more easy to manipulate by using .WRITE function.

|||

Thanks Limno,

Sorry I could reply this sooner. This is one slick trick - appreciated.

However, I couldn't get this working as the way you have. Not sure what I am missing.

I kept having the error

Server: Msg 7116, Level 16, State 4, Line 13
Offset 17 is not in the range of available text, ntext, or image data.
The statement has been terminated.

DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE @.Heading varchar(255)
DECLARE @.ptrReport varbinary(16)
DECLARE @.ptrTmpField varbinary(16)
DECLARE @.Len1 int, @.Len2 int

SELECT @.ptrTmpField = TEXTPTR(tmpField), @.Len1 = Datalength(tmpField),
@.ptrReport = TEXTPTR(Report), @.len2 = Datalength(report)
FROM PHILTBL2 WHERE AccID = 1624728


UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 tmpField @.ptrTmpField

Thanks

|||

If I changed the updatetext statement as

UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 @.ptrTmpField

The error went away, but I get gebrish text in my report field.

Look like the textptr doesn't work

|||

Check this link that Jared Ko provided earlier today:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

|||

Thanks CetinBasoz, but the the problem I am dealing with is the text field and it's a different animal than varchar field. :-(

Check here for more info

http://msdn2.microsoft.com/en-us/library/ms189466.aspx

|||

Please see below scripts for some examples on how to use UPDATETEXT with TEXTPTR.

-- Script #1

/* To concatenate several binary values into one image field: */
create table #Bin ( SegmentID int IDENTITY ( 1 , 1 ) , Segment varbinary(2) )
insert #Bin values (0x11)
insert #Bin values (0x22)
select * from #Bin
go
declare @.imageptr varbinary(16), @.segmentid int, @.segment varbinary(16)
create table #AllBin ( Segments image null )
-- Get valid pointer first
insert #AllBin values ( 0x0 )
select @.imageptr = TEXTPTR( Segments ) from #AllBin
-- Set data to null
update #AllBin set Segments = null

select @.segmentid = -1
while(1=1)
begin
select @.segmentid = min(segmentid)
from #Bin
where segmentid > @.segmentid
if @.segmentid is null break

select @.segment = segment from #Bin where segmentid = @.segmentid

updatetext #AllBin.Segments @.imageptr null 0 @.segment
end
select * from #AllBin
go
drop table #Bin
drop table #AllBin
go

-- Script #2

create procedure #t (
@.t1 text , @.i1 image, @.t2 text, @.i2 image, @.t3 varchar(30), @.i3 varbinary(2)
)
as
declare @.tptr varbinary(16), @.iptr varbinary(16), @.tpos int, @.ipos int
create table #blob(id int identity, t text, i image)

insert #blob values(@.t1, @.i1)
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

update #blob set t = @.t2, i = @.i2 where id = @.@.identity
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

select @.tptr = TEXTPTR(t), @.tpos = PATINDEX('%TEXT...%', t) - 1,
@.iptr = TEXTPTR(i), @.ipos = 2
from #blob

updatetext #blob.t @.tptr @.tpos 0 @.t3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob

updatetext #blob.i @.iptr @.ipos 1 @.i3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
go

exec #t 'SOME TEXT HERE...', 0x02498765bcde3,
'MODIFIED TEXT...', 0xab86ec64,
'(INSERT BEFORE TEXT) ', 0xcd -- replace 3rd byte

-- Inserted text & image value
/* id text_val image_val
-- -- --
1 SOME TEXT HERE... 0x002498765BCDE3
*/

-- Updated text & image value. This one replaces the existing values
/*
id text_val image_val
-- -- --
1 MODIFIED TEXT... 0xAB86EC64
*/

-- Modified text value only. This one inserts some text into the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86EC64
*/

-- Modified image value only. This one changes a byte in the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86CD64

*/
go
drop proc #t
go

Also, here is a link to a SP that show how to generate text value from multiple strings.

|||Sorry my bad. When I wrote it was around 3 AM here.

Wednesday, March 7, 2012

how to compare 2 date by "month"?

I'm doing DTS, Here is one of my Sql Query in DTS

Select * From ZT_DailyRpt_Detail
Where
isNumeric(TenDayDate) = 1 And TenDayDate <
Convert(varchar(10),DateAdd(Month,-CAST
((SELECT keepmonth
FROM zt_databackup a, zt_biller b
WHERE a.companycode = b.companycode AND
zt_DailyRpt_Detail.biller_code = b.billercode) AS int),GetDate()),112)

the sub query 【SELECT keepmonth
FROM zt_databackup a, zt_biller b
WHERE a.companycode = b.companycode AND
zt_DailyRpt_Detail.biller_code = b.billercode) AS int】will return a value 6 or 12

for the reason, every month have differnet days, ( some have 31 days , some are 30 days ) I don't want to check the day of date , only to compare month

if sub query return "6" and I do DTS on 2007/07/29 , will select date whichTenDayDate< 2007/01 not TenDayDate<2007/01/29 ( don't want to check the day of date)

does my query correct? if not can you correct it for me? thank you very much

Not sure I understood your problem, but maybe what you're looking for isDATEDIFFsql function?

|||

sorry for not describe my question clearly

my problem is

if I want to compare 2 date for example one is 2007/01/22 another is 2007/07/30

2007/07/30 - 2007/01/22 = 2007/06/08 right? that is not what I need

I need the result is 2007/06

I want to do DTS

if user run DTS in 2007/07/28 , want to select the data < 2007/07/28- 6 months , so it would be where data < 2007/01/28

I am not select Data < 2007/01/28 , I want to select Data < 2007/01 ( need not to compare the Day )

thank you

how to comment out a block of codes in SQL Query Analyzer?

Hello,
I know '--' can be used to comment out a single line. I tried /* and */ but
got the follow errors.
/*use master
go
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '*'.
Thanks,
Bing
bing wrote on Tue, 29 Nov 2005 09:02:15 -0800:

> Hello,
> I know '--' can be used to comment out a single line. I tried /* and */
> but got the follow errors.
> /*use master
> go
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
> Server: Msg 113, Level 15, State 1, Line 1
> Missing end comment mark '*/'.
> Server: Msg 170, Level 15, State 1, Line 8
> Line 8: Incorrect syntax near '*'.
> Thanks,
> Bing
The GO is being interpreted as a batch delimiter. Just add some additional
markers around the GO.
/*
use master
*/
go
/*
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Dan
|||"Daniel Crichton" wrote:

> bing wrote on Tue, 29 Nov 2005 09:02:15 -0800:
>
> The GO is being interpreted as a batch delimiter. Just add some additional
> markers around the GO.
> /*
> use master
> */
> go
> /*
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
>
Ah, ok, thanks much for the heads-up.
Bing

how to comment out a block of codes in SQL Query Analyzer?

Hello,
I know '--' can be used to comment out a single line. I tried /* and */ but
got the follow errors.
/*use master
go
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '*'.
Thanks,
Bingbing wrote on Tue, 29 Nov 2005 09:02:15 -0800:

> Hello,
> I know '--' can be used to comment out a single line. I tried /* and */
> but got the follow errors.
> /*use master
> go
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
> Server: Msg 113, Level 15, State 1, Line 1
> Missing end comment mark '*/'.
> Server: Msg 170, Level 15, State 1, Line 8
> Line 8: Incorrect syntax near '*'.
> Thanks,
> Bing
The GO is being interpreted as a batch delimiter. Just add some additional
markers around the GO.
/*
use master
*/
go
/*
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Dan|||"Daniel Crichton" wrote:

> bing wrote on Tue, 29 Nov 2005 09:02:15 -0800:
>
>
> The GO is being interpreted as a batch delimiter. Just add some additional
> markers around the GO.
> /*
> use master
> */
> go
> /*
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
>
Ah, ok, thanks much for the heads-up.
Bing

Friday, February 24, 2012

how to comment out a block of codes in SQL Query Analyzer?

Hello,
I know '--' can be used to comment out a single line. I tried /* and */ but
got the follow errors.
/*use master
go
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '*'.
Thanks,
Bingbing wrote on Tue, 29 Nov 2005 09:02:15 -0800:
> Hello,
> I know '--' can be used to comment out a single line. I tried /* and */
> but got the follow errors.
> /*use master
> go
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
> Server: Msg 113, Level 15, State 1, Line 1
> Missing end comment mark '*/'.
> Server: Msg 170, Level 15, State 1, Line 8
> Line 8: Incorrect syntax near '*'.
> Thanks,
> Bing
The GO is being interpreted as a batch delimiter. Just add some additional
markers around the GO.
/*
use master
*/
go
/*
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'access',
@.useself = false,
@.locallogin = 'administrator',
@.rmtuser = 'admin',
@.rmtpassword = NULL
*/
Dan|||"Daniel Crichton" wrote:
> bing wrote on Tue, 29 Nov 2005 09:02:15 -0800:
> > Hello,
> >
> > I know '--' can be used to comment out a single line. I tried /* and */
> > but got the follow errors.
> >
> > /*use master
> > go
> >
> > exec sp_addlinkedsrvlogin
> > @.rmtsrvname = 'access',
> > @.useself = false,
> > @.locallogin = 'administrator',
> > @.rmtuser = 'admin',
> > @.rmtpassword = NULL
> > */
> >
> > Server: Msg 113, Level 15, State 1, Line 1
> > Missing end comment mark '*/'.
> > Server: Msg 170, Level 15, State 1, Line 8
> > Line 8: Incorrect syntax near '*'.
> >
> > Thanks,
> >
> > Bing
>
> The GO is being interpreted as a batch delimiter. Just add some additional
> markers around the GO.
> /*
> use master
> */
> go
> /*
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'access',
> @.useself = false,
> @.locallogin = 'administrator',
> @.rmtuser = 'admin',
> @.rmtpassword = NULL
> */
>
Ah, ok, thanks much for the heads-up.
Bing

How to Combine IN & LIKE

Hai All,
Could some one help me how to combine IN & LIKE in the query selection,
i'v try many syntac and i just have no find the way out Sad

this is what I would like to write:

Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and DSCRIPTN Like in '%'+(select distinct rtrim(ORMSTRID) ORMSTRID from L10001)+'%'

hi,

you can not combine the two keywords... as you can see from BOL synopsis, LIKE expects an expression and not a multiple reusult.. and you can not combine the expression as you require..

you probably have to rewrite your subquery to retun something comparable with the main query result's primary key.. the LIKE operator should probably go inside the subquery..

regards

|||

Something like:

Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and EXISTS
(

select * FROM L10001 WHERE DSCRIPTN LIKE '%' + ORMSTRID +'%'

)


If you have multiple entries in the L10001 table you should use a subquery which create the DISTINCT values on the fly.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to code sql comments within sql statement


I want to write a query like this:

select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...

The result should looks like this:

--
select count(*) /* - emp_name */
FROM emp_name

query result:

COUNT(*)-EMP_NAME
-
3

-
But, now I can only got

COUNT(*)
-
3

What's wrong with my original code?Actually, I am getting


- as the result, I think I just need to find out how to turn headings on.
3

Thanks|||

Well... you are comment out whatever is written. Comment out means that it will be ignored, it will not show up in the results, etc.

It looks like you want to create column headers for aggregrated columns. Fortunately, you can use an ALIAS for that task.

For example:

Code Snippet


SELECT
count(*) AS 'count(*) --Emp_Name'
FROM MyTable

Of course, whatever is typed as the ALIAS has to be know in advance, it will not dynamically put in the column or table name.

|||
Thanks, Arnie:

I got it now.

how to close results window in 2005 query analyzer?

Other than ctr-R, is there a way to close the results window after running a
query in 2005's query analyzer? In 2000 there was a "Show results Pane"
button. I can't find this button in 2005.
Thanks so much!
WalterHi,
It's there in 2005 also, In ToolBar under Window's option the 3rd one is
"Show result Pane"
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Walter Mallon" wrote:

> Other than ctr-R, is there a way to close the results window after running
a
> query in 2005's query analyzer? In 2000 there was a "Show results Pane"
> button. I can't find this button in 2005.
> Thanks so much!
> Walter
>
>|||Thanks!!!
Walter
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:5B8747C2-FD80-4B83-B7A2-6D1E01D4C029@.microsoft.com...[vbcol=seagreen]
> Hi,
> It's there in 2005 also, In ToolBar under Window's option the 3rd one is
> "Show result Pane"
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d
> time
> asking back if its 2000 or 2005]
>
> "Walter Mallon" wrote:
>

how to close results window in 2005 query analyzer?

Other than ctr-R, is there a way to close the results window after running a
query in 2005's query analyzer? In 2000 there was a "Show results Pane"
button. I can't find this button in 2005.
Thanks so much!
WalterHi,
It's there in 2005 also, In ToolBar under Window's option the 3rd one is
"Show result Pane"
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Walter Mallon" wrote:
> Other than ctr-R, is there a way to close the results window after running a
> query in 2005's query analyzer? In 2000 there was a "Show results Pane"
> button. I can't find this button in 2005.
> Thanks so much!
> Walter
>
>|||Thanks!!!
Walter
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:5B8747C2-FD80-4B83-B7A2-6D1E01D4C029@.microsoft.com...
> Hi,
> It's there in 2005 also, In ToolBar under Window's option the 3rd one is
> "Show result Pane"
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Walter Mallon" wrote:
>> Other than ctr-R, is there a way to close the results window after
>> running a
>> query in 2005's query analyzer? In 2000 there was a "Show results Pane"
>> button. I can't find this button in 2005.
>> Thanks so much!
>> Walter
>>