SQL Server 2005 Opening Script File Problem

[mollyf]

I just installed SQL Server 2005 on my PC (the developer's edition)
yesterday. I have some scripts written by one of my coworkers to
create some tables and stored procedures in a database that I've
already created on my computer. Whenever I go to open the script file
(whose icon shows that it's a SQL Server Query File and I've got the
file type set to open with SQLWB - SQL Server Management Studio), the
file opens in Notepad instead of appearing in the query window. My
coworker told me that he just dragged and dropped the file in the query
window and I tried that and got the same results (opening in Notepad).
I can copy and paste the cotents of the file into the query window and
run it but of course this gets cumbersome for long scripts.

Is there a setting or something that I need to change? I have SQL
Server 2000 installed on my computer too.

Thanks.

Molly J. Fagan

[mollyf]

Followup to this:

I copied and pasted a script and ran it and then saved it on my
machine. I was able to open that copy the way I want to (not in
Notepad).

So is there something differently that needs to be done when running a
script written by someone else?

Thanks.
[e-mail] wrote:

I just installed SQL Server 2005 on my PC (the developer's edition)
yesterday. I have some scripts written by one of my coworkers to
create some tables and stored procedures in a database that I've
already created on my computer. Whenever I go to open the script file
(whose icon shows that it's a SQL Server Query File and I've got the
file type set to open with SQLWB - SQL Server Management Studio), the
file opens in Notepad instead of appearing in the query window. My
coworker told me that he just dragged and dropped the file in the query
window and I tried that and got the same results (opening in Notepad).
I can copy and paste the cotents of the file into the query window and
run it but of course this gets cumbersome for long scripts.

Is there a setting or something that I need to change? I have SQL
Server 2000 installed on my computer too.

Thanks.

Molly J. Fagan

[Erland Sommarskog]

(mo****@hotmail.com) writes:

I just installed SQL Server 2005 on my PC (the developer's edition)
yesterday. I have some scripts written by one of my coworkers to
create some tables and stored procedures in a database that I've
already created on my computer. Whenever I go to open the script file
(whose icon shows that it's a SQL Server Query File and I've got the
file type set to open with SQLWB - SQL Server Management Studio), the
file opens in Notepad instead of appearing in the query window. My
coworker told me that he just dragged and dropped the file in the query
window and I tried that and got the same results (opening in Notepad).
I can copy and paste the cotents of the file into the query window and
run it but of course this gets cumbersome for long scripts.

Is there a setting or something that I need to change? I have SQL
Server 2000 installed on my computer too.

First thing is to go the Windows Explorer, and in the menu pick
Tools->Folder Options, and there to go the View tab. Make sure that
"Hide file extentions for known file types" is unchecked. (Overall, any
good programmer who gets a new box should always go this dialog and
change this setting and a few more which have stupid defaults.)

Now you can see which extensions, you coworker's file has. In the next
tab in Folder Options, you can check the association for that file type.
You can also change associations from Tools->Customise->Text Editor in
Mgmt Studio.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mollyf]

Erland Sommarskog wrote:
<snip>

First thing is to go the Windows Explorer, and in the menu pick
Tools->Folder Options, and there to go the View tab. Make sure that
"Hide file extentions for known file types" is unchecked. (Overall, any
good programmer who gets a new box should always go this dialog and
change this setting and a few more which have stupid defaults.)

Now you can see which extensions, you coworker's file has. In the next
tab in Folder Options, you can check the association for that file type.
You can also change associations from Tools->Customise->Text Editor in
Mgmt Studio.

Like I said in my original post--the application associated with the
file is SQL Server 2005 (has the new icon associated with it and has
the file extension of .sql). It was the very first thing I checked
when it happened the first time.

I went and did what you said in Mgmt Studio and still no change--the
file opened up in Notepad.

Another thing that I noticed is that any queries that I wrote for SQL
2000, I can open them in Mgmt Studio but not the ones created by my
coworker. The other gal who works in our department hasn't run into
the same problems that I have. I copied and pasted the scripts written
by my coworker into the query window and ran them and they ran perfect
so it would appear that it's not a permissions issue or anything like
that.

Thanks.

Molly J. Fagan

[Erland Sommarskog]

(mo****@hotmail.com) writes:

I went and did what you said in Mgmt Studio and still no change--the
file opened up in Notepad.

This happens when you double-click on the file in Explorer, I assume.
Or does happen when you open the file from withing Mgmt Studio as well?
That would be really wild.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mollyf]

Erland Sommarskog wrote:

(mo****@hotmail.com) writes:

I went and did what you said in Mgmt Studio and still no change--the
file opened up in Notepad.

This happens when you double-click on the file in Explorer, I assume.
Or does happen when you open the file from withing Mgmt Studio as well?
That would be really wild.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yes it happens when I double-click on the file in Explorer and within
Mgmt Studio. All of my .sql files have had the little icon thing
associated with them ever since I installed SQL Server 2005 but I can
only open the files I created myself in Mgmt Studio--not the ones that
my coworker gave me. On Monday I'm going to have my other coworker
create a script and put it on the network drive for me to access (my
other coworker e-mailed the others) and I'm going to have her try
opening a script coming from my machine.

Molly J. Fagan

[Erland Sommarskog]

(mo****@hotmail.com) writes:

Yes it happens when I double-click on the file in Explorer and within
Mgmt Studio. All of my .sql files have had the little icon thing
associated with them ever since I installed SQL Server 2005 but I can
only open the files I created myself in Mgmt Studio--not the ones that
my coworker gave me. On Monday I'm going to have my other coworker
create a script and put it on the network drive for me to access (my
other coworker e-mailed the others) and I'm going to have her try
opening a script coming from my machine.

Try this. From the menu in Mgmt Studio, select File->Open->File. Navigate
to the directory where you have one of these suspect files. Select such
a file, and then click the arrow on the left of the Open button and
Select Open With.

You now get a window that presents you with a whole range of editors,
and on the top one is listed by default. Which editor is this?

Interesting enough, Notedpad does appear in this list, although I
don't get it as the default for any file.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mollyf]

Erland Sommarskog wrote:

Try this. From the menu in Mgmt Studio, select File->Open->File. Navigate
to the directory where you have one of these suspect files. Select such
a file, and then click the arrow on the left of the Open button and
Select Open With.

You now get a window that presents you with a whole range of editors,
and on the top one is listed by default. Which editor is this?

Interesting enough, Notedpad does appear in this list, although I
don't get it as the default for any file.

I tried what you said to do. The SQL Server Query Editor was shown as
being the default editor and I clicked on the "Set as Default" just to
"make sure." Then I clicked on the OK button and got the following
error: "The file cannot be opened with the selected editor. Please
choose another editor."

I just checked with my other coworker and she has the same thing
happening on her machine--the scripts open up in Notepad if she
double-clicks on them or by going through the File, Open option.

Like I've said before, copying and pasting the scripts and running them
that way does work so it's not an issue with errors in the scripts.
Plus I've saved the script with a different name (after copying and
pasting) and have been able to open them up the way I want to. I'm
wondering if there's some unknown bug or some unknown issue about
permissions or something that could be causing this.

Thanks.

Molly J. Fagan

[mollyf]

mo****@hotmail.com wrote:
<snip>

I've been messing around with things off and on today and it's starting
to look like if the create date and modified date of the file do not
match, that's when the script opens in Notepad, unless I make any
changes to the script within Mgmt Studio. Is there some sort of
security options in SQL Server 2005 that checks for stuff like this?

Molly J. Fagan

[Erland Sommarskog]

(mo****@hotmail.com) writes:

I've been messing around with things off and on today and it's starting
to look like if the create date and modified date of the file do not
match, that's when the script opens in Notepad, unless I make any
changes to the script within Mgmt Studio. Is there some sort of
security options in SQL Server 2005 that checks for stuff like this?

To be perfectly honest, I have no idea. I will have to admit that at this
point, I'm just perplexed. I've posted a summary of the thread to our
internal MVP forum where also Microsoft people hang out. Let's see if that
brings anything up.

By the way, in that Open With dialog did you try any other editor like the
XML editor? Not that it is a very meaningful operation, but just out of
curiousity.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mollyf]

Erland Sommarskog wrote:

To be perfectly honest, I have no idea. I will have to admit that at this
point, I'm just perplexed. I've posted a summary of the thread to our
internal MVP forum where also Microsoft people hang out. Let's see if that
brings anything up.

By the way, in that Open With dialog did you try any other editor like the
XML editor? Not that it is a very meaningful operation, but just out of
curiousity.

I tried opening the file with an XML editor but got the parameter is
incorrect error message (it's not an XML file so I wasn't surprised).
I was able to open it with the various encoding editors--of course it
appeared as all garbage in the editor since it's a plain text file but
any other type of editor, I got the same error message ("The file could
not be opened with the selected editor. Please choose another
editor.")

I appreciate your trying to help me with this.

Thanks.

Molly J. Fagan

[Erland Sommarskog]

(mo****@hotmail.com) writes:

I tried opening the file with an XML editor but got the parameter is
incorrect error message (it's not an XML file so I wasn't surprised).
I was able to open it with the various encoding editors--of course it
appeared as all garbage in the editor since it's a plain text file but
any other type of editor, I got the same error message ("The file could
not be opened with the selected editor. Please choose another
editor.")

I appreciate your trying to help me with this.

OK, I think I have the answer: there is probably a NUL character in
the file. Or many of them. (NUL character the value of char(0).)

Two possibilities: 1) Someone actually entered a NUL character in it
at will. 2) The file was saved as Unicode file, but without any byte-
order mark. Unicode files usually have a byte-orde mark (BOM) their first
two bytes, so that programs that read the file can tell whether the high or
low bytes come first. Mgmt Studio appear to interpret the absesnce of
byte-order marks as if the file in an 8-bit character set. I produced
a Unicode file without BOM, and indeed the file opened in Notepad -
which displayed it correctly.

You can verify my theory with a hex viewer. If you don't have one,
go to www.textpad.com and download Textpad, a shareware editor. Open
the file and in the File Format box, select Binary. If my theory is
correct every second byte will read 00.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Steve Kass]

Molly and Erland,

I am able to reproduce this behavior. I think your coworker has somehow
saved his file as Unicode but without the Unicode byte order signifier
(0xFFFE for the more common little-endian ordering). I suggest you
check this by looking at one of the files as binary. Here's a way using
SQL Server 2005's new bulk rowset provider:

declare @s varbinary(max)
select @s = BulkColumn FROM OPENROWSET(BULK 'C:\MysteryQuery.sql', SINGLE_BLOB) AS t
select substring(@s,1,10)

To fix these files, you can open them in Notepad and Save As... on
top of the original file name, choosing Unicode in the Encoding
dropdown below the filename edit box. You could also insert the two
bytes FF FE into the beginning of the file with a hex editor.

Unfortunately, I can't speculate on what your coworking is doing to
cause this to happen, but I suspect he is using an alternative editor,
or perhaps he is transfering or copying the files in some way no one
else is.

Steve Kass
Drew University
www.stevekass.com

mo****@hotmail.com wrote:

I just installed SQL Server 2005 on my PC (the developer's edition)
yesterday. I have some scripts written by one of my coworkers to
create some tables and stored procedures in a database that I've
already created on my computer. Whenever I go to open the script file
(whose icon shows that it's a SQL Server Query File and I've got the
file type set to open with SQLWB - SQL Server Management Studio), the
file opens in Notepad instead of appearing in the query window. My
coworker told me that he just dragged and dropped the file in the query
window and I tried that and got the same results (opening in Notepad).
I can copy and paste the cotents of the file into the query window and
run it but of course this gets cumbersome for long scripts.

Is there a setting or something that I need to change? I have SQL
Server 2000 installed on my computer too.

Thanks.

Molly J. Fagan

[mollyf]

Steve Kass wrote:
<snip>

Thanks Steve and Erland. That was definitely what was going on. I
know my coworker uses Textpad and I know he generated the scripts
through Mgmt Studio and then went back and made some changes in Textpad
(that's what I had done myself with both Textpad and Notepad on my own
machine and realized that something was going on whenever the file was
edited).

Of course I'm wondering why this wasn't an issue in 2000 but now it's
an issue in 2005.

Molly J. Fagan

[Erland Sommarskog]

(mo****@hotmail.com) writes:

Thanks Steve and Erland. That was definitely what was going on. I
know my coworker uses Textpad and I know he generated the scripts
through Mgmt Studio and then went back and made some changes in Textpad
(that's what I had done myself with both Textpad and Notepad on my own
machine and realized that something was going on whenever the file was
edited).

Of course I'm wondering why this wasn't an issue in 2000 but now it's
an issue in 2005.

I guess it's more an issue of the tools, not the SQL Server version as
such.

The default format for saving in Mgmt Studio is Unicode, while the default
in Query Analyzer is ANSI I think. And with 8-bit chars you never run into
these sort of problems.

SQL Server Management Studio on the other hand saves in Unicode by default.
I have not experienced that Textpad thrashes the BOM, but then again I don't
use Unicode files in Textpad much. (Textpad only supports Unicode if chars
are in the ANSI set, so it's kind of useless.)

As for what happens when you open a BOM-less file, it appears that Notepad
and Textpad plays a guessing game and says "every second byte is NULL, it
must be Unicode". Guess what happens if there is some Chinese or Russian
text, so that not every second byte is 00 anymore.

Mgmt Studio refuses to open the file as soon there is a NUL character. But
guess what happens if the file has only Chinese text. The file will open,
but you will see something else.

Or you can do like Query Analyzer, which opened my BOM-less file as is.
That is, I see text like "S E L E C T "

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[mollyf]

Erland Sommarskog wrote:
<snip>
In SQL Server 2000, I could generate scripts in Enterprise Manager and
then open them up in Textpad or Notepad and make changes if I needed
to, then go and open the file in Query Analzyer and run it without a
problem--having to deal with Unicode was never an issue. I should have
phrased my question better to make it more along the lines of why
Microsoft changed the default setting, etc. the way that they did.
I'll resist answering my question with a sarcastic answer:-)

What books would you recommend for someone transitioning from 2000 to
2005? Back in 2000, I bought the book "Professional SQL Server 7.0
Programming" by Rob Vieira and used that to teach myself SQL Server 7
(I had never used it before prior to the year 2000 and never took a
training class for it) and that book has been a great reference book
through the years for my coworkers and me but from looking at Amazon,
the 2005 version of his book won't be released until November and I'm
needing to learn about the differences now. Not only with programming
but with some of the DBA tasks (I'm not a DBA but I do some of the DBA
stuff). Preferably a book not released by Microsoft Press:-)

Thanks.

Molly J. Fagan

[Steve Kass]

I'm not sure this kind of thing is going to show up in a typical
book. Depending on what you do in 2000 that needs to be done in
2005, monitoring this newsgroup and the .tools and .programming
ones within microsoft.public.sqlserver.* hierarchy isn't a bad
start. There may be good books out for what you want, too - browse
around.

SK

mo****@hotmail.com wrote:

Erland Sommarskog wrote:
<snip>
In SQL Server 2000, I could generate scripts in Enterprise Manager and
then open them up in Textpad or Notepad and make changes if I needed
to, then go and open the file in Query Analzyer and run it without a
problem--having to deal with Unicode was never an issue. I should have
phrased my question better to make it more along the lines of why
Microsoft changed the default setting, etc. the way that they did.
I'll resist answering my question with a sarcastic answer:-)

What books would you recommend for someone transitioning from 2000 to
2005? Back in 2000, I bought the book "Professional SQL Server 7.0
Programming" by Rob Vieira and used that to teach myself SQL Server 7
(I had never used it before prior to the year 2000 and never took a
training class for it) and that book has been a great reference book
through the years for my coworkers and me but from looking at Amazon,
the 2005 version of his book won't be released until November and I'm
needing to learn about the differences now. Not only with programming
but with some of the DBA tasks (I'm not a DBA but I do some of the DBA
stuff). Preferably a book not released by Microsoft Press:-)

Thanks.

Molly J. Fagan

[Steve Kass]

Meaningful Chinese files are likely to have some newlines that contain
0x00 bytes.

I removed the BOM from

空山�見人，
返景入深林，
但�人語響。
復照�苔上。

But perhaps because of the 0x00's remaining in the newlines,
Notepad again took over. However, it this case, notepad displayed
garbage:

zzq\
N媺篘

詮ofeQ駇梘

FO^€篘瀶?0

gqR椩?N0
SK
Erland Sommarskog wrote:

(mo****@hotmail.com) writes:

>>Thanks Steve and Erland. That was definitely what was going on. I
know my coworker uses Textpad and I know he generated the scripts
through Mgmt Studio and then went back and made some changes in Textpad
(that's what I had done myself with both Textpad and Notepad on my own
machine and realized that something was going on whenever the file was
edited).

Of course I'm wondering why this wasn't an issue in 2000 but now it's
an issue in 2005.

I guess it's more an issue of the tools, not the SQL Server version as
such.

The default format for saving in Mgmt Studio is Unicode, while the default
in Query Analyzer is ANSI I think. And with 8-bit chars you never run into
these sort of problems.

SQL Server Management Studio on the other hand saves in Unicode by default.
I have not experienced that Textpad thrashes the BOM, but then again I don't
use Unicode files in Textpad much. (Textpad only supports Unicode if chars
are in the ANSI set, so it's kind of useless.)

As for what happens when you open a BOM-less file, it appears that Notepad
and Textpad plays a guessing game and says "every second byte is NULL, it
must be Unicode". Guess what happens if there is some Chinese or Russian
text, so that not every second byte is 00 anymore.

Mgmt Studio refuses to open the file as soon there is a NUL character. But
guess what happens if the file has only Chinese text. The file will open,
but you will see something else.

Or you can do like Query Analyzer, which opened my BOM-less file as is.
That is, I see text like "S E L E C T "

[Erland Sommarskog]

(mo****@hotmail.com) writes:

In SQL Server 2000, I could generate scripts in Enterprise Manager and
then open them up in Textpad or Notepad and make changes if I needed
to, then go and open the file in Query Analzyer and run it without a
problem--having to deal with Unicode was never an issue. I should have
phrased my question better to make it more along the lines of why
Microsoft changed the default setting, etc. the way that they did.

There is no reason to blame Microsoft for third-party product throwing
away the byte-order mark. Least of all third-party products with only
a half-hearted Unicode support like Textpad.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx