2005 xml equality

There is not equality comparison for xml data type. You cannot cast xml to
text or ntext, but you can cast to varbinary(max), varchar(max), and
nvarchar(max).
A couple of routes you can take (I'm sure there are other ways as well):
1. Assuming the incoming xml is identical in structure (whitespace doesn't
count) to the existing xml value , you could cast the incoming value and
existing value to varbinary(max) and compare.
2. You could iterate through the xml and compare, although this is more
complicated to implement and slower as the size of the xml value increases.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:e$kf%23YvwHHA.5008@.TK2MSFTNGP05.phx.gbl...
> Is there any (perhaps roundabout) mechanism for testing xml columns
> for equality?
> Specifically, as part of a bulk update (i.e. into a staging table that
> is then migrated) I want to short-circuit any records whose xml hasn't
> actually changed, since I have audit requirements and I don't want to
> fill in the audit every time a record is present in an extract - only
> when it has actually changed.
> Any ideas? I have tried a few approaches like extracting as ntext or
> some of the checksum functions, but not with any success. CLR perhaps?
> Marc
>
>
Thanks Peter - I'll give the varbinary(max)/varchar(max)/nvarchar(max)
route a go in the morning. I'm confident that this will make
everything work - much appreciated.
Marc

2005 Schemas

Does anyone know if you can rename a schema in 2k5?
I checked out sp_rename, but it doesn't appear to be a supported object type
from the documentation. Unless I'm reading it wrong.
Thanks."sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
>
You cannot rename a schema.
David|||Cool, thanks.
"David Browne" wrote:
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> > Does anyone know if you can rename a schema in 2k5?
> > I checked out sp_rename, but it doesn't appear to be a supported object
> > type
> > from the documentation. Unless I'm reading it wrong.
> >
>
> You cannot rename a schema.
> David
>
>|||"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:C14095AC-5051-4256-ADBD-C330B819FAC3@.microsoft.com...
> Does anyone know if you can rename a schema in 2k5?
> I checked out sp_rename, but it doesn't appear to be a supported object
> type
> from the documentation. Unless I'm reading it wrong.
> Thanks.
>
I think you'll have to create a new schema then move existing objects to it
using ALTER SCHEMA:
ALTER SCHEMA new_schema_name TRANSFER old_schema_name.object_name
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

2005 Management Studio Reformats my source!

Minor issues that are driving me crazy about this tool:
1. I type in a new view and format it the way it makes sense to me. I hit
the Save button, and SqlMS decides it wants to format it a completely
different way. How can I turn this feature off, or tell the program how I
want it to format my code? Older versions of EM did not reformat the code.
2. When I create a new view, the Diagram, Results and Criteria panes
automatically display. I have to click the buttons to hide them each time.
I don't need them or want them taking up editing space. How can I make the
program remember that I don't want it to display these "helpful" windows
each time?
Please advise
no help or resolution here?
"ZippyThePinhead" wrote:

> Minor issues that are driving me crazy about this tool:
> 1. I type in a new view and format it the way it makes sense to me. I hit
> the Save button, and SqlMS decides it wants to format it a completely
> different way. How can I turn this feature off, or tell the program how I
> want it to format my code? Older versions of EM did not reformat the code.
> 2. When I create a new view, the Diagram, Results and Criteria panes
> automatically display. I have to click the buttons to hide them each time.
> I don't need them or want them taking up editing space. How can I make the
> program remember that I don't want it to display these "helpful" windows
> each time?
> Please advise
>
|||Why not create the view as a CREATE VIEW statement inside of a Query window? I
don't seem to have any issues when I do that. Of course, you will need to
choose ALTER - Script to New Window/Clipboard in order to modify the code
instead of choosing Modify as Modify defaults to that view.
-Pete Schott
ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> no help or resolution here?
> "ZippyThePinhead" wrote:
|||Thanks Peter -- yes that certainly does work, but it's a pain in the rear to
have to manage this myself..which is what I have been doing lately.
There simply needs to be some options available in the program to stop the
auto-formatting of our code. I've noticed that sometimes it even messes
things up by adding "as exp1" expressions on columns the parser thinks are
misnamed!
"Peter A. Schott" wrote:

> Why not create the view as a CREATE VIEW statement inside of a Query window? I
> don't seem to have any issues when I do that. Of course, you will need to
> choose ALTER - Script to New Window/Clipboard in order to modify the code
> instead of choosing Modify as Modify defaults to that view.
> -Pete Schott
> ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
>

2005 Express Error: Could not open a connection to SQL Server

Hi, I can't connect to my remote SQL Server 2005 Express - can anyone
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.
Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.
|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
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
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegrou ps.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>

2005 Express Error: Could not open a connection to SQL Server

Hi, I can't connect to my remote SQL Server 2005 Express - can anyone
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
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
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegroups.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>