Embedding multiple .sql files

How do I write a .sql script that executes a number of other scripts in .sql
files? This has to be easy. I just can't find it.
Thanks,
JerryRYou can use oSql. See if this helps:
http://www.sql-server-performance.com/rd_osql.asp
Andrew J. Kelly SQL MVP
"JerryR" <JerryR@.discussions.microsoft.com> wrote in message
news:76549937-4F1E-41B5-B148-A548F0B8CA95@.microsoft.com...
> How do I write a .sql script that executes a number of other scripts in
> .sql
> files? This has to be easy. I just can't find it.
> Thanks,
> JerryR|||There is no such feature in SQL like this in Oracle @.Somefile.sql. You
have to put in in a OS batch to execute it via a cmdshell program like
SQLCMD or OSQL or ISQL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes, that's what I was looking for.
Thanks
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1143927815.456011.236990@.v46g2000cwv.googlegroups.com...
> There is no such feature in SQL like this in Oracle @.Somefile.sql. You
> have to put in in a OS batch to execute it via a cmdshell program like
> SQLCMD or OSQL or ISQL.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

emailed subscription not including port number in servername URL link for report

I hope someone can help as I have searched for a solution but been unable to find one as yet.

My problem is that after installing SharePoint V3.0 and amending the Report Server to <ServerName>:8080, as I don't yet wish to use integrated mode, everything works fine except email delivered subscriptions. The URL in the email body referencing the report only puts in <ServerName> etc. omitting the port number :8080. I have tried to find where this URL link is built-up from but have had no luck in the report manager, report services configuration and the config files, does anyone know where I need to look and if possible what I need to amend to have the link include :8080 after the servername portion of the URL. I can copy the URL and manually insert the port address and it works but this defeats the object of automated subscriptions.

Sorry if this is a dumb question but it is giving me a headache at the moment.

All answers gratefully received,

Andy

You should update the URLRoot element in the rsreportserver.config file to include :8080 in it. This should get the URL to work.

-Lukasz

|||

Thanks Lukasz,

I thought I had looked at all the appropriate config files but obviously I missed this one. I found the entry and added the :8080 to the path and it all works fine now.

Much appreciated,

Andy

emailed subscription not including port number in servername URL link for report

I hope someone can help as I have searched for a solution but been unable to find one as yet.

My problem is that after installing SharePoint V3.0 and amending the Report Server to <ServerName>:8080, as I don't yet wish to use integrated mode, everything works fine except email delivered subscriptions. The URL in the email body referencing the report only puts in <ServerName> etc. omitting the port number :8080. I have tried to find where this URL link is built-up from but have had no luck in the report manager, report services configuration and the config files, does anyone know where I need to look and if possible what I need to amend to have the link include :8080 after the servername portion of the URL. I can copy the URL and manually insert the port address and it works but this defeats the object of automated subscriptions.

Sorry if this is a dumb question but it is giving me a headache at the moment.

All answers gratefully received,

Andy

You should update the URLRoot element in the rsreportserver.config file to include :8080 in it. This should get the URL to work.

-Lukasz

|||

Thanks Lukasz,

I thought I had looked at all the appropriate config files but obviously I missed this one. I found the entry and added the :8080 to the path and it all works fine now.

Much appreciated,

Andy

emailed subscription not including port number in servername URL link for report

I hope someone can help as I have searched for a solution but been unable to find one as yet.

My problem is that after installing SharePoint V3.0 and amending the Report Server to <ServerName>:8080, as I don't yet wish to use integrated mode, everything works fine except email delivered subscriptions. The URL in the email body referencing the report only puts in <ServerName> etc. omitting the port number :8080. I have tried to find where this URL link is built-up from but have had no luck in the report manager, report services configuration and the config files, does anyone know where I need to look and if possible what I need to amend to have the link include :8080 after the servername portion of the URL. I can copy the URL and manually insert the port address and it works but this defeats the object of automated subscriptions.

Sorry if this is a dumb question but it is giving me a headache at the moment.

All answers gratefully received,

Andy

You should update the URLRoot element in the rsreportserver.config file to include :8080 in it. This should get the URL to work.

-Lukasz

|||

Thanks Lukasz,

I thought I had looked at all the appropriate config files but obviously I missed this one. I found the entry and added the :8080 to the path and it all works fine now.

Much appreciated,

Andy

E-mail failure in data driven subscriptions

When using a data driven subscription to generate and send a number or
reports to different addresses I have only been able to find a number that
were successful or failed. Is there any way to tell specifically which
e-mails from the group failed?The ReportServerService<timestamp>.log file will contain the information.
There is no UI around this information.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"bwray" <bwray@.discussions.microsoft.com> wrote in message
news:021B7E1F-76F7-4932-9448-7B79EF561763@.microsoft.com...
> When using a data driven subscription to generate and send a number or
> reports to different addresses I have only been able to find a number that
> were successful or failed. Is there any way to tell specifically which
> e-mails from the group failed?|||Thanks. That helped to some degree. I still can't find which indivdual
e-mail within the batch failed. I set up a controlled report scenario in
which I knew two of the e-mails would fail. However, the indivdual entrees
in the execution log all had a 'rssuccess' status for the report. The 'last
run status' in the schedule table shows '2 of 3 comlete with 2 errors'. I
know which ones failed, I just can't seem to pinpoint them within the logs.
Am I on the right track or am I looking in the wrong place? Thanks.
"Daniel Reib [MSFT]" wrote:
> The ReportServerService<timestamp>.log file will contain the information.
> There is no UI around this information.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "bwray" <bwray@.discussions.microsoft.com> wrote in message
> news:021B7E1F-76F7-4932-9448-7B79EF561763@.microsoft.com...
> > When using a data driven subscription to generate and send a number or
> > reports to different addresses I have only been able to find a number that
> > were successful or failed. Is there any way to tell specifically which
> > e-mails from the group failed?
>
>|||You should look in the log files for something like the following:
Notification <guid> completed. Success: True, Status: Mail sent to
<address>, DeliveryExtension: Report Server Email, Report: <report name>,
Attempt 0
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"bwray" <bwray@.discussions.microsoft.com> wrote in message
news:C99702B8-05E3-46FC-B7F5-A450289EF8DF@.microsoft.com...
> Thanks. That helped to some degree. I still can't find which indivdual
> e-mail within the batch failed. I set up a controlled report scenario in
> which I knew two of the e-mails would fail. However, the indivdual
> entrees
> in the execution log all had a 'rssuccess' status for the report. The
> 'last
> run status' in the schedule table shows '2 of 3 comlete with 2 errors'.
> I
> know which ones failed, I just can't seem to pinpoint them within the
> logs.
> Am I on the right track or am I looking in the wrong place? Thanks.
> "Daniel Reib [MSFT]" wrote:
>> The ReportServerService<timestamp>.log file will contain the information.
>> There is no UI around this information.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "bwray" <bwray@.discussions.microsoft.com> wrote in message
>> news:021B7E1F-76F7-4932-9448-7B79EF561763@.microsoft.com...
>> > When using a data driven subscription to generate and send a number or
>> > reports to different addresses I have only been able to find a number
>> > that
>> > were successful or failed. Is there any way to tell specifically which
>> > e-mails from the group failed?
>>|||Thanks again. I think I'm getting closer to what I'm looking for but I'm
still not there. The 'To:' address for e-mails that fail is not available in
the log file. Now I can tell by recipient / time which ones were
successful. Now the problem is that if multiple copies of the same named
report go out in close proximity to each other (think invoices) I can't tell
which one fails.
"Daniel Reib [MSFT]" wrote:
> You should look in the log files for something like the following:
> Notification <guid> completed. Success: True, Status: Mail sent to
> <address>, DeliveryExtension: Report Server Email, Report: <report name>,
> Attempt 0
>
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "bwray" <bwray@.discussions.microsoft.com> wrote in message
> news:C99702B8-05E3-46FC-B7F5-A450289EF8DF@.microsoft.com...
> > Thanks. That helped to some degree. I still can't find which indivdual
> > e-mail within the batch failed. I set up a controlled report scenario in
> > which I knew two of the e-mails would fail. However, the indivdual
> > entrees
> > in the execution log all had a 'rssuccess' status for the report. The
> > 'last
> > run status' in the schedule table shows '2 of 3 comlete with 2 errors'.
> > I
> > know which ones failed, I just can't seem to pinpoint them within the
> > logs.
> > Am I on the right track or am I looking in the wrong place? Thanks.
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> The ReportServerService<timestamp>.log file will contain the information.
> >> There is no UI around this information.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "bwray" <bwray@.discussions.microsoft.com> wrote in message
> >> news:021B7E1F-76F7-4932-9448-7B79EF561763@.microsoft.com...
> >> > When using a data driven subscription to generate and send a number or
> >> > reports to different addresses I have only been able to find a number
> >> > that
> >> > were successful or failed. Is there any way to tell specifically which
> >> > e-mails from the group failed?
> >>
> >>
> >>
>
>|||I'm not sure what the issue might be. Each row from the DataDriven
subscription will create a new notification row. Each of these rows are
handled individually. I am not sure why you are not seeing the results.
You could send me the log file and I could take a look if you like. Just
remove the online from my address.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"bwray" <bwray@.discussions.microsoft.com> wrote in message
news:90179329-413F-425F-8D8F-FD97876F94FE@.microsoft.com...
> Thanks again. I think I'm getting closer to what I'm looking for but I'm
> still not there. The 'To:' address for e-mails that fail is not available
> in
> the log file. Now I can tell by recipient / time which ones were
> successful. Now the problem is that if multiple copies of the same named
> report go out in close proximity to each other (think invoices) I can't
> tell
> which one fails.
> "Daniel Reib [MSFT]" wrote:
>> You should look in the log files for something like the following:
>> Notification <guid> completed. Success: True, Status: Mail sent to
>> <address>, DeliveryExtension: Report Server Email, Report: <report name>,
>> Attempt 0
>>
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "bwray" <bwray@.discussions.microsoft.com> wrote in message
>> news:C99702B8-05E3-46FC-B7F5-A450289EF8DF@.microsoft.com...
>> > Thanks. That helped to some degree. I still can't find which indivdual
>> > e-mail within the batch failed. I set up a controlled report scenario
>> > in
>> > which I knew two of the e-mails would fail. However, the indivdual
>> > entrees
>> > in the execution log all had a 'rssuccess' status for the report. The
>> > 'last
>> > run status' in the schedule table shows '2 of 3 comlete with 2
>> > errors'.
>> > I
>> > know which ones failed, I just can't seem to pinpoint them within the
>> > logs.
>> > Am I on the right track or am I looking in the wrong place? Thanks.
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> The ReportServerService<timestamp>.log file will contain the
>> >> information.
>> >> There is no UI around this information.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "bwray" <bwray@.discussions.microsoft.com> wrote in message
>> >> news:021B7E1F-76F7-4932-9448-7B79EF561763@.microsoft.com...
>> >> > When using a data driven subscription to generate and send a number
>> >> > or
>> >> > reports to different addresses I have only been able to find a
>> >> > number
>> >> > that
>> >> > were successful or failed. Is there any way to tell specifically
>> >> > which
>> >> > e-mails from the group failed?
>> >>
>> >>
>> >>
>>

Email

Hi all me again I gotta question, the Sr executive asst has a number of reports that gets emailed to different department, only problem is those departments are on a totally different server. I am guessing that we need to start SQL AGENT Emailing MAPI to do this for us, of course I could be wrong. This is one of the stored procedures that creates one of the reports

CREATE PROCEDURE createSecurityListtobeEmailed
AS
SELECT SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type INTO SecurityList
FROM SecurityListX
GROUP BY SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type
HAVING (((SecurityListX."Last Name") Is Not Null))
GOIt appears that this just does a simple aggregation from SecurityListX into SecurityList. I can't see that it has any return set, or that it emails anything anywhere.

Are you sure that you aren't missing a few pieces to this puzzle ?

-PatP|||yes pat there are a few pieces of the puzzle missing, this will create a list which will be made into a report that a webpage on the casinos server, that the security department can view. Now I guess I have inherited this db and I need to include in the stored procedures using Xp_sendmail but I am unsure how to do it, and what I am doing is returning nothing but errors. So if you please sir....HELP???

Goodmorning Pat :)|||This is actually way too complex for me to even hazard a guess about what you really need to do, based on what I know so far. A lot depends on your mail configuration, security requirements, etc., so there isn'ta "one size fits most" kind of answer.

If all you want to do is run that statement, then send someone an email to tell them it ran (or failed), you can do that with just SQL Agent. If you want to extract the rows from the table and incorporate them into a mail message, then I'd suggest xp_sendmail.

At the moment I'm hampered by my rather limited understanding of what you want!

-PatP|||At the moment I'm hampered by my rather limited understanding of what you want!

And just when did that start to be a problem?

Sorry...I have Notus Lotes...(piece of garbage)|||And just when did that start to be a problem?

Sorry...I have Notus Lotes...(piece of garbage)I'm hampered by Bloats too. It does provide a great deal of casual entertainment as I'm trying to get some work done!

-PatP|||Nevermind Pat sorry to have bothered you|||Huh ?!?! Does that mean that you've solved the problem, or that you are going to look for another solution? Now you've given us enough to work on (via the snippets of VBA code) and you're going to take your ball and go home ?!?! Piddle! :rolleyes:

-PatP|||Misunderstood thought you were still unclear as was getting frustrated. I'm sure you have better things to do then to Mind read all day, since I couldnt get my point across. Had to retrieve more information was turning into a futile attempt...everyone was giving me bits and pieces.
XP_sendmail is new to me not sure how to do it|||That is actually one of the biggest hurdles to taking responsibility for a new (to you) system. You have to gather all of the stray bits of knowledge tucked into nooks and crannies throughout the systems affected, and if you have any pity on the next poor soul to come along you'll document the living bejesus out of what confused you.

If the next person has at least a few breadcrumbs to start from, they'll be a million miles further along than where you had to start from, and will hopefully utter prayers for you for the rest of their lives! Keep in mind that almost all systems are living things, they either grow or die, so the next person will have the chaos that you inherited plus everything that gets added during your tenure too. I've got a few systems that are exceptions that make great stories, but those are exceptions, not the rule by any means!

-PatP

EM connects to server without knowing port

I'm confused. When I create an ODBC connection to a remote SQL server 2000
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David WalkerHi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:

> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
>|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:

> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>

EM connects to server without knowing port

I'm confused. When I create an ODBC connection to a remote SQL server 2000
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David Walker
Hi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker
|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:

> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>
|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:

> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker
|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David
|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:

> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:
> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.