(NOLOCK) and Linked Servers

I know that you can't have an optimizer hint when running an ad-hoc query
from another server. My question is why? What prevents it from using the
hint?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C0F66FA391C@.droptable.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:[vbcol=seagreen]
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
Andrew J. Kelly SQL MVP
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

(newbie) Cant connect to msde Database,

Hi,
I am an ASP.net newbie learning thru databasing now.
When setting up and connectiong to Database, i am prompted at SQL Server
Authentication for a user name and password
When I installed, thru the CMD under Start: Run in Windows 2000, i believe i
specified "sa" and "password" for user name and password. but now i get an
error, trying to input that.
"unable to connect to database.
Login failed for user "sa" Reason : not associated with a trusted SQL Server
connection"

What am i doing wrong? if i mistyped the password info when setting up,
where can i check it? or reset it?

Please someone help me

RaphaelSo are you using a ASP.Net script to connect to the DB?

The SQL connection should look like:

server=localhost;Trusted_Connection=true;database=theDB;user id=sa;password=password

Does that make sense to you?

Adrian|||um,

No not at first site, I am a newbie, remember!
I am trying to run from the web matrix project, and i simply cannot create a new msde database.

Do you have any further advice?

Thanks

Raphael

(local) database wont start?

I dont know how this happened but my (local) SQL Server wont start. I cant access any of the databases. I just get this error:

'A connection could not be established to (local).

Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())

Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the (local) node) and try again.'

If I go to check the properties I get the same error. Im completely locked out. I looked at the folder and the database files are there, but I cant get to them through Enterprise Manager.

I have no idea how this problem could have happened. It just seemed to have stopped working. All my other databases can be reached (they are external). Could this be a virus or something? Can someone help me out on this one? I dont have a clue what the problem is, much less how to fix it.

Thanks.Are you CERTAIN that the MSSQLServer service is running? A virus is unlikely the cause.

(Cant get) Media for SQL Server 2005

We have a software assurance contract, and we get
our media from a Microsoft Certified Partner.
Our MCP hasn't been able to provide us with media for
SQL Server 2005.

It's been 7 weeks since the launch; I expected the product to
be in the channel by now.

Anybody else (especially software assurance customers) having
trouble getting the goods from MCPs and/or resellers?Media only started becoming available in the channel in the first week of
Dec. Patience.

-Euan

"Larry Bertolini" <bertolini.1@.osu.edu> wrote in message
news:dous86$sgm$1@.charm.magnus.acs.ohio-state.edu...
> We have a software assurance contract, and we get
> our media from a Microsoft Certified Partner.
> Our MCP hasn't been able to provide us with media for
> SQL Server 2005.
> It's been 7 weeks since the launch; I expected the product to
> be in the channel by now.
> Anybody else (especially software assurance customers) having
> trouble getting the goods from MCPs and/or resellers?

(Cancel this) Can’t paste into XML field in Management Studio.

Hi Folks,

It looks like Management Studio prevents pasting XML directly into a field of the type xml. Sometimes, it might be best to stay with varchar(max).

Original post below: >>

I want to paste some XML into an SQL Server 2005 xml type field, but the null is grayed out and paste simply does not work. What is going on?

Thanks,

Rob

Rob:

I see the same behavior. It appears to me that the COPY function is not active for the XML field and that you cannot directly modify it. You can copy from the XML field, but you cannot paste into it. I suggest that you use an XML query to update your field?

#Temp Tables

Why cant I use the same temptable name i a stored procedure after i have droped it?

I use the Pubs database for the test case.

CREATE PROCEDURE spFulltUttrekk AS

SELECT *
INTO #temp
FROM Jobs

SELECT *
FROM #temp

DROP TABLE #temp

SELECT *
INTO #temp
FROM Employee

SELECT *
FROM #temp[posted and mailed, vnligen svara i nys]

Per (per-eivind-greva.sivertsen@.cgey.com) writes:
> Why cant I use the same temptable name i a stored procedure after i have
> droped it?
> I use the Pubs database for the test case.
> CREATE PROCEDURE spFulltUttrekk AS
> SELECT *
> INTO #temp
> FROM Jobs
> SELECT *
> FROM #temp
> DROP TABLE #temp
> SELECT *
> INTO #temp
> FROM Employee
> SELECT *
> FROM #temp

When SQL Server builds the query plan for a procedure, it builds the
plan for the entire procedure in one go, with one exception. If a
statement refers to a non-existing table, that statement is deferred
until run-time.

So when you create the procedure, SQL Server defers the plan for the
two SELECT statements. When execution hits the deferred statement, SQL
Server recompiles the procedure. And the entire procedure. So when it
finds a SELECT * INTO #temp, it thinks that's bad, because #temp does
already exist. At this point, the DROP TABLE statement has not been
executed, so SQL Server does not know that the table will go away.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp