BUG? Unable to Contact non default instance of SQL server

Hi,
think i have found a resolution to a problem which has been ocurring
intermittantly on new sql server installations for some time. The symptoms
are being unable to see or connect to a named instance of sql server when
there is a default instance running also. I have spotted this behaviour in
both workstation installed versions and windows 2000 server installations.
It appears the problem is due to the named instance using tcpip with a port
of 1433 the same as the default instance. One of my machines which has a
named instance which can be connected to, has a Tcpip port of 1104. So i
guess the installation procedure is supposed to assign an unused tcpip port
when creating new instances (but this isn't occurring sometimes).
I changed the port to 1104 and was then able to connect correctly. I also
tried removing tcpip and connecting only using named pipes and this worked
also.
The latest machine build (which had this issue) was installed by installing
the default instance then the named instance and then servicepacking the
default and then the named instance with service pack3.
Thanks
TimBwhat's your connecting client's mdac-level ?
jobi
"timb" <timb@.test.com> wrote in message
news:u8fAuakjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> think i have found a resolution to a problem which has been ocurring
> intermittantly on new sql server installations for some time. The
symptoms
> are being unable to see or connect to a named instance of sql server when
> there is a default instance running also. I have spotted this behaviour in
> both workstation installed versions and windows 2000 server installations.
> It appears the problem is due to the named instance using tcpip with a
port
> of 1433 the same as the default instance. One of my machines which has a
> named instance which can be connected to, has a Tcpip port of 1104. So i
> guess the installation procedure is supposed to assign an unused tcpip
port
> when creating new instances (but this isn't occurring sometimes).
> I changed the port to 1104 and was then able to connect correctly. I
also
> tried removing tcpip and connecting only using named pipes and this worked
> also.
> The latest machine build (which had this issue) was installed by
installing
> the default instance then the named instance and then servicepacking the
> default and then the named instance with service pack3.
>
> Thanks
> TimB
>|||What does MDAC have to do with it?
"jobi" <jobi@.reply2.group> wrote in message
news:bm3ccv$1si$1@.reader08.wxs.nl...
> what's your connecting client's mdac-level ?
> jobi
> "timb" <timb@.test.com> wrote in message
> news:u8fAuakjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > think i have found a resolution to a problem which has been ocurring
> > intermittantly on new sql server installations for some time. The
> symptoms
> > are being unable to see or connect to a named instance of sql server
when
> > there is a default instance running also. I have spotted this behaviour
in
> > both workstation installed versions and windows 2000 server
installations.
> > It appears the problem is due to the named instance using tcpip with a
> port
> > of 1433 the same as the default instance. One of my machines which has
a
> > named instance which can be connected to, has a Tcpip port of 1104. So
i
> > guess the installation procedure is supposed to assign an unused tcpip
> port
> > when creating new instances (but this isn't occurring sometimes).
> >
> > I changed the port to 1104 and was then able to connect correctly. I
> also
> > tried removing tcpip and connecting only using named pipes and this
worked
> > also.
> >
> > The latest machine build (which had this issue) was installed by
> installing
> > the default instance then the named instance and then servicepacking the
> > default and then the named instance with service pack3.
> >
> >
> > Thanks
> >
> > TimB
> >
> >
>|||You have to have at least mdac 2.6 to be able to detect virtual instances by
their instancename if they don't run on port 1433.
Search KB and BOL !
jobi
"timb" <timb@.test.com> wrote in message
news:#SLUYGojDHA.2232@.TK2MSFTNGP09.phx.gbl...
> What does MDAC have to do with it?
> "jobi" <jobi@.reply2.group> wrote in message
> news:bm3ccv$1si$1@.reader08.wxs.nl...
> > what's your connecting client's mdac-level ?
> >
> > jobi
> > "timb" <timb@.test.com> wrote in message
> > news:u8fAuakjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > think i have found a resolution to a problem which has been
ocurring
> > > intermittantly on new sql server installations for some time. The
> > symptoms
> > > are being unable to see or connect to a named instance of sql server
> when
> > > there is a default instance running also. I have spotted this
behaviour
> in
> > > both workstation installed versions and windows 2000 server
> installations.
> > > It appears the problem is due to the named instance using tcpip with a
> > port
> > > of 1433 the same as the default instance. One of my machines which
has
> a
> > > named instance which can be connected to, has a Tcpip port of 1104.
So
> i
> > > guess the installation procedure is supposed to assign an unused
tcpip
> > port
> > > when creating new instances (but this isn't occurring sometimes).
> > >
> > > I changed the port to 1104 and was then able to connect correctly.
I
> > also
> > > tried removing tcpip and connecting only using named pipes and this
> worked
> > > also.
> > >
> > > The latest machine build (which had this issue) was installed by
> > installing
> > > the default instance then the named instance and then servicepacking
the
> > > default and then the named instance with service pack3.
> > >
> > >
> > > Thanks
> > >
> > > TimB
> > >
> > >
> >
> >
>|||Hi Jobi,
no both machines are mdac 2.7. If you read my initial post you
can see that i changed the port to be different to the default port (1433)
and i could then see the instance! i.e. this is due to the sql server
installation for the named instance assigning it a port which is already in
use rather than using a new port.
Tim B
"jobi" <jobi@.reply2.group> wrote in message
news:bm5ke4$jrs$1@.reader08.wxs.nl...
> You have to have at least mdac 2.6 to be able to detect virtual instances
by
> their instancename if they don't run on port 1433.
> Search KB and BOL !
> jobi
> "timb" <timb@.test.com> wrote in message
> news:#SLUYGojDHA.2232@.TK2MSFTNGP09.phx.gbl...
> > What does MDAC have to do with it?
> >
> > "jobi" <jobi@.reply2.group> wrote in message
> > news:bm3ccv$1si$1@.reader08.wxs.nl...
> > > what's your connecting client's mdac-level ?
> > >
> > > jobi
> > > "timb" <timb@.test.com> wrote in message
> > > news:u8fAuakjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi,
> > > > think i have found a resolution to a problem which has been
> ocurring
> > > > intermittantly on new sql server installations for some time. The
> > > symptoms
> > > > are being unable to see or connect to a named instance of sql server
> > when
> > > > there is a default instance running also. I have spotted this
> behaviour
> > in
> > > > both workstation installed versions and windows 2000 server
> > installations.
> > > > It appears the problem is due to the named instance using tcpip with
a
> > > port
> > > > of 1433 the same as the default instance. One of my machines which
> has
> > a
> > > > named instance which can be connected to, has a Tcpip port of 1104.
> So
> > i
> > > > guess the installation procedure is supposed to assign an unused
> tcpip
> > > port
> > > > when creating new instances (but this isn't occurring sometimes).
> > > >
> > > > I changed the port to 1104 and was then able to connect correctly.
> I
> > > also
> > > > tried removing tcpip and connecting only using named pipes and this
> > worked
> > > > also.
> > > >
> > > > The latest machine build (which had this issue) was installed by
> > > installing
> > > > the default instance then the named instance and then servicepacking
> the
> > > > default and then the named instance with service pack3.
> > > >
> > > >
> > > > Thanks
> > > >
> > > > TimB
> > > >
> > > >
> > >
> > >
> >
> >
>

Bug? Column Default Disappears when Reinitializing Replication

Hi Gurus,
I have a default on a column that disappears every time I reinitialize
replication. This is transactional replication to an updatable subscriber on
SQL Server 2005. Yes, replication of defaults is turned on, and *most* of my
column defaults do, in fact, replicate to the subscriber. The ones that fail
are distinctive in that they reference a user-defined function. And yes,
that UDF and all its internal dependencies are present on the subscriber.
(All the defaults that successfully replicate are either literal values or
else reference *intrinsic* functions, e.g. GETDATE.)
Any idea whether this is a bug? Or what? Any help would be *VERY*
appreciated!
Thanks!
Jason Fisher
Did you replicate the UDFs referenced by the defaults as well, would be
great if you can post the history of the snapshot agent as well. Thanks
much.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uaU5n8waHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Hi Gurus,
> I have a default on a column that disappears every time I reinitialize
> replication. This is transactional replication to an updatable subscriber
> on SQL Server 2005. Yes, replication of defaults is turned on, and *most*
> of my column defaults do, in fact, replicate to the subscriber. The ones
> that fail are distinctive in that they reference a user-defined function.
> And yes, that UDF and all its internal dependencies are present on the
> subscriber. (All the defaults that successfully replicate are either
> literal values or else reference *intrinsic* functions, e.g. GETDATE.)
> Any idea whether this is a bug? Or what? Any help would be *VERY*
> appreciated!
> Thanks!
> Jason Fisher
>
|||Raymond,
Thanks for the reply. The UDF is not being *replicated* -- currently, we
aren't replicating any of the code objects (e.g., stored procedures,
functions, or triggers) -- but it was copied to the subscriber database
manually (ahead of time). It did exist there at the time replication was
initialized. But does it have to be explicitly *included* in the replication
articles? I wouldn't have thought so.
Jason
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:ew9MKFxaHHA.1400@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Did you replicate the UDFs referenced by the defaults as well, would be
> great if you can post the history of the snapshot agent as well. Thanks
> much.
> -Raymond
> "Jason Fisher" <j.fisher@.earthlink.net> wrote in message
> news:uaU5n8waHHA.4396@.TK2MSFTNGP06.phx.gbl...
|||The problem is that we can't just assume that you have the UDFs referenced
by the defaults\check constraints to be at the subscriber ahead of time so
you have two options:
1) Explicitly include the UDFs referenced by the defaults in your
publication
2) Put the defaults referencing the unpublished UDFs in a post-snapshot
script, this basically means you know better than we do what you have at
your subscriber (After all, you did manually create the UDFs at the
subscriber right?)
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:OsG3BaxaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Raymond,
> Thanks for the reply. The UDF is not being *replicated* -- currently, we
> aren't replicating any of the code objects (e.g., stored procedures,
> functions, or triggers) -- but it was copied to the subscriber database
> manually (ahead of time). It did exist there at the time replication was
> initialized. But does it have to be explicitly *included* in the
> replication articles? I wouldn't have thought so.
> Jason
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:ew9MKFxaHHA.1400@.TK2MSFTNGP06.phx.gbl...
>
|||Raymond,

> The problem is that we can't just assume that you have the UDFs referenced
> by the defaults\check constraints to be at the subscriber ahead of time so
> you have two options:
Hmm. But wouldn't it make more sense to replicate the default and then throw
an error later if the referenced UDF is not there on the subscriber, rather
than to simply fail silently during replication? I would always rather have
a noisy, trappable error later on than a silent, untrappable "error" earlier
on. Oh well ...
Regarding your recommendations:

> 1) Explicitly include the UDFs referenced by the defaults in your
> publication
I'm trying this now, but it doesn't look like a good solution in our
particular case because we have tables already in the subscriber database
(not being replicated from the publisher) that also reference the same UDF.
This means that what I'm seeing is error after error in the replication
monitor as it tries and fails to drop the UDF.

> 2) Put the defaults referencing the unpublished UDFs in a post-snapshot
> script, this basically means you know better than we do what you have at
> your subscriber (After all, you did manually create the UDFs at the
> subscriber right?)
I will think about trying this next, although it's not ideal because a) it's
manual, b) the tables in the script have to be updated / maintained as / if
our business needs change. In other words: again, it's manual.
Any other ideas? And thanks very much for the assistance, too, by the way!

Jason
|||> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
I believe the snapshot agent did print out messages explaining that a
default\check constraint is not scripted because it references udf not in
the publication; and believe me when I say some folks will complain the
other way if the snapshot fails to apply because we "should know better" not
to script a default constraint that is referencing something that they
"explicitly" exclude from the publication. The perfect solution will be for
us to divine what the user really wants but we are not there yet and will
not likely be there in the near future.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uobv$TyaHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Raymond,
>
> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
> Regarding your recommendations:
>
> I'm trying this now, but it doesn't look like a good solution in our
> particular case because we have tables already in the subscriber database
> (not being replicated from the publisher) that also reference the same
> UDF. This means that what I'm seeing is error after error in the
> replication monitor as it tries and fails to drop the UDF.
>
> I will think about trying this next, although it's not ideal because a)
> it's manual, b) the tables in the script have to be updated / maintained
> as / if our business needs change. In other words: again, it's manual.
> Any other ideas? And thanks very much for the assistance, too, by the way!
>
> Jason
>
|||Now that I have thought about your case some more, you can publish the UDFs
referenced by the default constraint while making sure that the pre-creation
command for the UDF articles is 'none' instead of 'drop'. This will avoid
having the snapshot delivery process from trying to drop the UDFs at your
subscriber which is most likely the main source of problem for replicating
the UDFs to the subscriber.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uobv$TyaHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Raymond,
>
> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
> Regarding your recommendations:
>
> I'm trying this now, but it doesn't look like a good solution in our
> particular case because we have tables already in the subscriber database
> (not being replicated from the publisher) that also reference the same
> UDF. This means that what I'm seeing is error after error in the
> replication monitor as it tries and fails to drop the UDF.
>
> I will think about trying this next, although it's not ideal because a)
> it's manual, b) the tables in the script have to be updated / maintained
> as / if our business needs change. In other words: again, it's manual.
> Any other ideas? And thanks very much for the assistance, too, by the way!
>
> Jason
>

Bug: default value showing as query based when it is not (report m

I have a report which uses a query to populate a parameter.
The default value is not queried, this is just set to "=1".
When the report is deployed, the parameter in report manager is showing as
'query based' which is wrong.
This is a pain in the ass as when I try linking to this report, I am not
able to set the value to something else.If you use a query to specify a range of parameters then it seems to show
default as 'query based' in the report manager.
What I have discovered is that if you change something to do with the
parameters on the report and redeploy, you can pick up the changes in the
linked report by repointing the linked report at the same report, this forces
some kind of refresh to occur and although very annoying, is easier than
recreating the linked report.
<despair>Thanks to MS for providing a UI of such high quality.</despair>
"adolf garlic" wrote:
> I have a report which uses a query to populate a parameter.
> The default value is not queried, this is just set to "=1".
> When the report is deployed, the parameter in report manager is showing as
> 'query based' which is wrong.
> This is a pain in the ass as when I try linking to this report, I am not
> able to set the value to something else.

Bug when changing the default data and log directories

We have seen this problem on several SQL 2000 Standard Servers and was wonde
ring if anyone else had encountered it:
We started having problems restoring databases. We were receving errors tha
t the physical files paths were incorrect. When we looked at the file paths
for the databases we noticed that they looked like this "e:\mssql\data\\fil
e.mdf" and "F:\mssql\logs\\
logfile.ldf"" Note the double slashes.
What we found that is we changed the default data and log paths through ente
rprise manager. When we did it, we went to EM, clicked the browse button an
d then selected the target directories. This left the settings looking like
this:
"E:\mssql\data\"
"F:\mssql\logs\"
When you create a database through EM, everything works fine. When you crea
te a database using the create database command, it gets created with the do
uble backslashes in the file path for the data and log files.
We have been able to re-create this on several servers. If you take out the
trailing backslashes and stop and start the MSSQL Service (it appears chang
es to this setting only takes affect after a service stop and start even tho
ugh the running value would
indicate otherwise) then the 'create database' function work fine and only i
nserts single slashes from then on.
We are running SQL 2000 SP3 and we haven't been able to find any other docum
ented instance of this. If anyone else can re-create this issue we would be
very grateful to hear that it isn't something we are doing wrong.
ThanksThis is a known issue but I don't think there is a KB article. As you have
found, the default data and log directory strings should not have a trailing
backslash or you get the \\ in the filepath. Whilst it may be fixed in a
future service pack, it seems unlikely as it has been round for a while, is
fairly benign and has a simple workaround.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mike Holleran" <mholleran@.infodir.com> wrote in message
news:ED046519-1519-4579-A6FE-F5EE63C18B05@.microsoft.com...
> We have seen this problem on several SQL 2000 Standard Servers and was
wondering if anyone else had encountered it:
> We started having problems restoring databases. We were receving errors
that the physical files paths were incorrect. When we looked at the file
paths for the databases we noticed that they looked like this
"e:\mssql\data\\file.mdf" and "F:\mssql\logs\\logfile.ldf"" Note the double
slashes.
> What we found that is we changed the default data and log paths through
enterprise manager. When we did it, we went to EM, clicked the browse
button and then selected the target directories. This left the settings
looking like this:
> "E:\mssql\data\"
> "F:\mssql\logs\"
> When you create a database through EM, everything works fine. When you
create a database using the create database command, it gets created with
the double backslashes in the file path for the data and log files.
> We have been able to re-create this on several servers. If you take out
the trailing backslashes and stop and start the MSSQL Service (it appears
changes to this setting only takes affect after a service stop and start
even though the running value would indicate otherwise) then the 'create
database' function work fine and only inserts single slashes from then on.
> We are running SQL 2000 SP3 and we haven't been able to find any other
documented instance of this. If anyone else can re-create this issue we
would be very grateful to hear that it isn't something we are doing wrong.
> Thanks
>
>
>

BUG in SQL Server Manager - Default Schema

Hallo,
June CTP of SQL Server 2005 (NOT SQL Express)
########
Everything deleted from this post by Alle since it has no relevance to the RTM.
Unfortunately I can not delete the thread.
###########
Alle

There is now an invisible comment that has been added by a moderator (I hate that too, why isn't the comment / edit visible in the thread?) :

=== Edited by Paul Mestemaker - MSFT @. 28 Oct 2006 1:59 AM UTC===
This is a comment not a question. This bug has been fixed.

Paul,

you are absolutely right, it was fixed ages ago and in addition we are well over the CTP stage of SQL Server 2005 which was RTM one year ago.

Wouldn't it make much more sense to delete this entire thread since it now longer has ANY revelance?

I can't although I am the author (I tried).

Thanks,
Alle

BUG in SQL Server Manager - Default Schema

Hallo,
June CTP of SQL Server 2005 (NOT SQL Express)
########
Everything deleted from this post by Alle since it has no relevance to the RTM.
Unfortunately I can not delete the thread.
###########
Alle

There is now an invisible comment that has been added by a moderator (I hate that too, why isn't the comment / edit visible in the thread?) :

=== Edited by Paul Mestemaker - MSFT @. 28 Oct 2006 1:59 AM UTC===
This is a comment not a question. This bug has been fixed.

Paul,

you are absolutely right, it was fixed ages ago and in addition we are well over the CTP stage of SQL Server 2005 which was RTM one year ago.

Wouldn't it make much more sense to delete this entire thread since it now longer has ANY revelance?

I can't although I am the author (I tried).

Thanks,
Alle

Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why??
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny Tordrup
Benny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why??
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
David G.
|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why??
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>
|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
>

Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why''
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny TordrupBenny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
--
David G.|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
--
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > Hi
> >
> > I have a table defined as
> >
> > create table Project
> > (
> > Project nvarchar(20) collate database_default not null constraint
> > DF_Project_Project default '',
> > /* some other irrelevant fields */
> >
> > CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> > )
> >
> > If I insert a long numeric value into Project (3123456789), I get some
> > troubles:
> >
> > insert Project (Project) values ('3123456789')
> >
> >
> > If i execute the statement
> >
> > select Project from Project
> >
> > in Query Analyzer, I get the expected result (the record shown in the
> > result
> > grid)
> >
> > If i execute the statement
> >
> > select * from Project
> >
> > I get the following error (and no record shown in the result grid)
> >
> > Server: Msg 248, Level 16, State 1, Line 1
> > The conversion of the nvarchar value '3123456789' overflowed an int
> > column.
> > Maximum integer value exceeded.
> >
> >
> > Why''
> >
> > My SQL Server version is (according to Select @.@.Version)
> >
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > IMO, using Select * should be functionally equal to a Select statement
> > listing all fields in the table, but it is not.
> >
> > Best regards,
> >
> > Benny Tordrup
> >
> >
>

Box name and default SQL Server name

I had created SQL 2000 on Win 2000 with the default box
name. What happens to SQL 2000's default name if the
box's name gets changed in future ? Will it also take up
the new name of the box?
ThanksHi,
No, You have drop and create the server name.
Look into sp_dropserver ,sp_addserver in Bol.
Thanks
Hari
MCDBA
"kanthi" <anonymous@.discussions.microsoft.com> wrote in message
news:023701c3b103$22e83f50$a001280a@.phx.gbl...
> I had created SQL 2000 on Win 2000 with the default box
> name. What happens to SQL 2000's default name if the
> box's name gets changed in future ? Will it also take up
> the new name of the box?
> Thanks|||I had a problem because of this and got help off some forum It caused me
problems with an existing replication subscription here is what had to do
to fix it.
In QA use SELECT @.@.ServerName
This will show you the stored SQL servername (It will be your old one)
Then use sp_dropserver 'YourOldServerName', 'droplogins'
Stop and re-start SQL Server
Then do SELECT @.@.ServerNAme again in QA
This should return a null
Then use sp_addserver 'NewServerName', 'local'
Make sure the local parameter is there !
Stop and re-start SQL Server again
Then use SELECT @.@.ServerName again in QA
This should now be your new server name
Hope this helps
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ODplQtQsDHA.572@.TK2MSFTNGP11.phx.gbl...
> Hi,
> No, You have drop and create the server name.
> Look into sp_dropserver ,sp_addserver in Bol.
> Thanks
> Hari
> MCDBA
>
> "kanthi" <anonymous@.discussions.microsoft.com> wrote in message
> news:023701c3b103$22e83f50$a001280a@.phx.gbl...
> > I had created SQL 2000 on Win 2000 with the default box
> > name. What happens to SQL 2000's default name if the
> > box's name gets changed in future ? Will it also take up
> > the new name of the box?
> >
> > Thanks
>

Both versions of SQL Server on one server

Can I install SQL Server 7.0 SP2 as a Default version and
SQL Server 2000 SP3 as a named version on a WIndows 2000
SP3 server ?
We will run Connect-Care (Customer-First) Application
against SQL Server 7.0 version and Solomon 5.5 against SQL
Server 2000 version.
Anybody see any problem with this ?
Thanks for any help.
Rick,
Yep, should work fine. I have found it preferable to install SQL Server
7.0 first, then install SQL 2000 as a named instance. Remember to
configure your memory settings so that they aren't competing with each
other for RAM.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> Can I install SQL Server 7.0 SP2 as a Default version and
> SQL Server 2000 SP3 as a named version on a WIndows 2000
> SP3 server ?
> We will run Connect-Care (Customer-First) Application
> against SQL Server 7.0 version and Solomon 5.5 against SQL
> Server 2000 version.
> Anybody see any problem with this ?
> Thanks for any help.
|||I hope that installing service packs should not be a
problem....

>--Original Message--
>Rick,
>Yep, should work fine. I have found it preferable to
install SQL Server
>7.0 first, then install SQL 2000 as a named instance.
Remember to
>configure your memory settings so that they aren't
competing with each[vbcol=seagreen]
>other for RAM.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Rick wrote:
and[vbcol=seagreen]
2000[vbcol=seagreen]
SQL
>.
>
|||Rick,
No problem! You can even have two instances of SQL Server 2000 running
at different service pack levels. (Not that you'd want to though).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:[vbcol=seagreen]
> I hope that installing service packs should not be a
> problem....
>
>
> install SQL Server
>
> Remember to
>
> competing with each
>
> and
>
> 2000
>
> SQL

Both versions of SQL Server on one server

Can I install SQL Server 7.0 SP2 as a Default version and
SQL Server 2000 SP3 as a named version on a WIndows 2000
SP3 server '
We will run Connect-Care (Customer-First) Application
against SQL Server 7.0 version and Solomon 5.5 against SQL
Server 2000 version.
Anybody see any problem with this '
Thanks for any help.Check out
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/instsql/in_runsetup_0svo.asp
Peter
"Government does not solve problems; it subsidizes them."
Ronald Reagan
>--Original Message--
>Can I install SQL Server 7.0 SP2 as a Default version and
>SQL Server 2000 SP3 as a named version on a WIndows 2000
>SP3 server '
>We will run Connect-Care (Customer-First) Application
>against SQL Server 7.0 version and Solomon 5.5 against
SQL
>Server 2000 version.
>Anybody see any problem with this '
>Thanks for any help.
>.
>|||Rick,
Yep, should work fine. I have found it preferable to install SQL Server
7.0 first, then install SQL 2000 as a named instance. Remember to
configure your memory settings so that they aren't competing with each
other for RAM.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> Can I install SQL Server 7.0 SP2 as a Default version and
> SQL Server 2000 SP3 as a named version on a WIndows 2000
> SP3 server '
> We will run Connect-Care (Customer-First) Application
> against SQL Server 7.0 version and Solomon 5.5 against SQL
> Server 2000 version.
> Anybody see any problem with this '
> Thanks for any help.|||I hope that installing service packs should not be a
problem....
>--Original Message--
>Rick,
>Yep, should work fine. I have found it preferable to
install SQL Server
>7.0 first, then install SQL 2000 as a named instance.
Remember to
>configure your memory settings so that they aren't
competing with each
>other for RAM.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Rick wrote:
>> Can I install SQL Server 7.0 SP2 as a Default version
and
>> SQL Server 2000 SP3 as a named version on a WIndows
2000
>> SP3 server '
>> We will run Connect-Care (Customer-First) Application
>> against SQL Server 7.0 version and Solomon 5.5 against
SQL
>> Server 2000 version.
>> Anybody see any problem with this '
>> Thanks for any help.
>.
>|||Rick,
No problem! You can even have two instances of SQL Server 2000 running
at different service pack levels. (Not that you'd want to though).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rick wrote:
> I hope that installing service packs should not be a
> problem....
>
>>--Original Message--
>>Rick,
>>Yep, should work fine. I have found it preferable to
> install SQL Server
>>7.0 first, then install SQL 2000 as a named instance.
> Remember to
>>configure your memory settings so that they aren't
> competing with each
>>other for RAM.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Rick wrote:
>>Can I install SQL Server 7.0 SP2 as a Default version
> and
>>SQL Server 2000 SP3 as a named version on a WIndows
> 2000
>>SP3 server '
>>We will run Connect-Care (Customer-First) Application
>>against SQL Server 7.0 version and Solomon 5.5 against
> SQL
>>Server 2000 version.
>>Anybody see any problem with this '
>>Thanks for any help.
>>.

BorderStyle bug??

In RS 2005, I cannot hide a particular border by setting it to 'None' if the
Default border setting is 'Solid'.
I can get the desired result by setting the Default border to 'None' and
then setting only the borders I want to display to 'Solid', however, this is
not convenient to me as I have a large amount of reports where the Default is
solid and the borders I want hidden are set to 'None', which used to work
fine in RS 2000. Is this a bug? The control I am dealing with is a textbox,
but I have also tested a rectangle and the body of the report itself, and the
borderstyle is acting the same with all 3 objects. Also, let me add that in
layout mode while I am designing the report the borders appear to be
displaying/hiding as I would expect, but when the report is actually ran or
previewed is when the borders do not behave appropriately.
To be absolutely clear, here is an example:
The following does NOT hide the bottom border in RS 2005 like it did in RS
2000...
<BorderStyle>
<Bottom>None</Bottom>
<Default>Solid</Default>
</BorderStyle>
This does, however, work to hide the bottom border.. but very time-consuming
to try and change... (the default setting for the borderstyle in RS 2005 is
'None')
<BorderStyle>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
I appreciate your help in advance!On Mar 21, 4:13 pm, DBS5150 <DBS5...@.discussions.microsoft.com> wrote:
> In RS 2005, I cannot hide a particular border by setting it to 'None' if the
> Default border setting is 'Solid'.
> I can get the desired result by setting the Default border to 'None' and
> then setting only the borders I want to display to 'Solid', however, this is
> not convenient to me as I have a large amount of reports where the Default is
> solid and the borders I want hidden are set to 'None', which used to work
> fine in RS 2000. Is this a bug? The control I am dealing with is a textbox,
> but I have also tested a rectangle and the body of the report itself, and the
> borderstyle is acting the same with all 3 objects. Also, let me add that in
> layout mode while I am designing the report the borders appear to be
> displaying/hiding as I would expect, but when the report is actually ran or
> previewed is when the borders do not behave appropriately.
> To be absolutely clear, here is an example:
> The following does NOT hide the bottom border in RS 2005 like it did in RS
> 2000...
> <BorderStyle>
> <Bottom>None</Bottom>
> <Default>Solid</Default>
> </BorderStyle>
> This does, however, work to hide the bottom border.. but very time-consuming
> to try and change... (the default setting for the borderstyle in RS 2005 is
> 'None')
> <BorderStyle>
> <Top>Solid</Top>
> <Left>Solid</Left>
> <Right>Solid</Right>
> </BorderStyle>
> I appreciate your help in advance!
As far as I know, this is the only way to get the results you are
looking for; however, I can't say if it is a flaw or not: I guess we
would have to know MS's desired outcome to know. Sorry to not be of
more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer