Thursday, March 29, 2012
Do not Display "NULL" in Results tab
Results tab for either Grid or Text view? I didn't see anything under
Options. I'm currently having to do search and replace to remove it
before passing on to users or using for imports to other apps. Third
party import programs typically treat this as text.
MarkChange the query to say COALESCE(stringValue, '')
"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
> Is there a setting in Management Studio to NOT display "NULL" in the
> Results tab for either Grid or Text view? I didn't see anything under
> Options. I'm currently having to do search and replace to remove it
> before passing on to users or using for imports to other apps. Third
> party import programs typically treat this as text.
>
> Mark|||This is what I was afraid of. It seems for every two steps forward in
Management Studio, you have to take one step back. So now if I have
100 varchar columns, I'll have to create 100 alias columns
(ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
carry over features in previous versions to the next. It seems they
could have added an option to "Query Results" to display NULL or blank
for the output. This wasn't a problem in QA.
On Fri, 30 Jun 2006 08:45:49 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>Change the query to say COALESCE(stringValue, '')
>"Mark" <mytrash9_NOSPAM@.bellsouth.net> wrote in message
> news:u86aa2hhs69fdhun44e1cd5ihrio5fne07@.
4ax.com...
>|||> (ISNULL(ColName, '') AS ColName). I just don't understand why MS can't
> carry over features in previous versions to the next. It seems they
> could have added an option to "Query Results" to display NULL or blank
> for the output. This wasn't a problem in QA.
It's quite possible that they got a lot of complaints about that
"feature"... to me, at least, there is a big difference between NULL and
blank. If I can't visibly tell the difference, I'm going to have to change
something.
It's kind of like a car manufacturer choosing to put the cup holder on the
dash or in the center console. No matter which option they choose, they're
going to piss someone off.
A
Wednesday, March 7, 2012
Distriubtion Error on Stored Procedure
Setting up Transactional Replication.
Log Reader Agent successfull.
Snapshot Agent successfull.
Distribution Agent Error (Replication is failing because of this error. I don't know what is wrong with the stored procedure that causes it not to replicate):
Date 1/19/2007 10:28:13 AM
Log Job History (IS36-MMS_20061213-MMSRepTest-IS4-30)
Step ID 2
Server IS4
Job Name IS36-MMS_20061213-MMSRepTest-IS4-30
Step Name Run agent.
Duration 00:00:11
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2007-01-19 16:28:24.700 Category:COMMAND
Source: Failed Command
Number:
Message: CREATE PROCEDURE "dbo"."spBTG_GetEventsSince"(@.EventMin datetime, @.BoatID int) AS
BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)
insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from
2007-01-19 16:28:24.700 Category:NULL
Source: Microsoft SQL Native Client
Number: 1018
Message: Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Here is the entire source of the sp:
GO
CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS
BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)
insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END
Linda, you can either change the stored procedure definition in the script generated by the snapshot agent to the following:
CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS
BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)
insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh with (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh with (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END
Or, you can change the compatibility level of your subscriber database to 80 or lower.
-Raymond
|||Raymond,
This sp currently runs on SQL2000. However, before we implement replication, it will be running on 2005. Is the change "adding with" something that will be required for SQL 2005 or does it have to do with replication?
Linda
|||The use of the "with" keyword with query hints is a SQL2005 requirement that is not directly related to replication. As I mentioned before, you can always change the dbcmptlevel of your subscriber database to 80 or lower so the old syntax in your procedure can be accepted.
-Raymond
|||Raymond,
On the subscriber database, do I run this command:
EXEC sp_dbcmptlevel MMS_20061213, 80;
to change the cmptlevel?
I did that and now I am getting this error:
Command attempted:
/* ============================================================ */
/* View: vMcsHeaders */
/* ============================================================ */
CREATE VIEW "dbo"."vMcsHeaders" AS
/* VIEW: vMcsHeaders
ABSTRACT: Used by the Contract System to work with contract headers
AUTHOR DATE
EJB 3/31/98 Created
SRM 4/8/98 - Added Customer_Code
EJB 5/1/98 - added filter for only contracts that are not deleteme = 1
EJB 6/10/98 - Ad
(Transaction sequence number: 0x00046CEA0000F21D005600000000, Command ID: 3385)
Error messages:
Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208
Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208
I notice the McsDemurrageSetUp is it referencing is spelled McsDemurrageSetup. How do I track down what kind of error this is? (Perhaps, I did not get the compatibility set correctly to 80?)
|||It would appear that you are replicating from a case-insensitive database to a case-sensitive database, this is not a supported scenario.
|||Raymond,
If I run the command: sp_server_info, both databases say "MIXED" under identifier case. However, I did figure out the problem. The storedprocedure is referencing a table that was not replicated because it did not have a primary key. I did not notice that initially when I noticed the case was different. The database I am working with needs a lot of work before it can successfully be replicated!
Thanks,
Linda
Distributor cannot connect to subscriber
I am setting up my 2005 Replication system...
publisher = 2005 sp1
Subscriber = 2005 sp1
I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.
Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.
I am not sure where to look at next... I never had this issue in 2000.
Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.
I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.
Could it be that my pub/dist is using the wrong client files?
|||Hi William,
Are you using merge or transactional replication?
Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.
In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?
Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.
This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hey Tom,
I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.
The subscription is a push subscription. I will take a look at the link you sent.
=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.
Is there anything I can do to ensure it is working?
|||update
Since my subscriber is a dev server and if I mess it up it does not matter I did the following...
I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.
No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...
Any idea? or is this a Bug?
|||Can you try the following connectivity test from command prompt?
at your dev machine, try to connect to the other box
osql -S<publisher_server> -U<user id> -P<password>
and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.
Gary
|||Ok... I did as suggested and the publisher has no problem connecting to the subscriber...
So what does that mean?
I can connect via command line,SMS but not replication.
|||Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....
So when I execute from 80/tools/binn I can connect without issue.
From 90/tools/binn I get this error
[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]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.
Can you check the protocal interface are enabled for TCP and name pipe?
You can find the instruction on the following posting from Mahesh,
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1
Gary
|||I double checked and I already have that set. I cycled the server anyway... Still no luck.
What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...
Is there anyway to check what libraries each client is using?
|||What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights.
|||OS is Windows 2003 SP1...
I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...
I
|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...
So I am asking my boss for other options...
Thanks for all the help.
Saturday, February 25, 2012
Distribution Question
I'm just starting to look at replication for a job I have and I have a
question about setting up a distributor.
1. Can i have multiple distributors?
2. Is it possible to define which distributor is used for a certain
publication?
3. Can i set up the distributor to also be on the subscriber? (external to
the publisher)
Thanks for any and all help!
One distributor per publisher is what we can use.
The distributor can indeed be on the subscriber or the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Friday, February 17, 2012
distributed transaction w 2 servers in different domains
domain is no problem,
but when I try to do it with 2 servers from different domains i get error:
Server: Msg 7391, Level 16, State 1, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Situation: both servers Windows 2000 Server SP4, SQL Server 2000 SP4
Updating data remotely works fine only when distributed transaction is not
used.
I believe that the problem is with security.
Should MSDTC and MSSQLServer start under some special accounts with special
rights?
Maybe the domains should trust one another?
Please help.
Tom.Hello Tom,
You may want to use dtcping to test dtc communication between servers and
make sure they are not blocked by firewalls
How to troubleshoot MS DTC firewall issues
http://support.microsoft.com/defaul...kb;EN-US;306843
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||That was it.
Name resolution did not funtion correctly in both directions.
THANK YOU VERY MUCH.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:lEqqlwRiGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hello Tom,
> You may want to use dtcping to test dtc communication between servers and
> make sure they are not blocked by firewalls
> How to troubleshoot MS DTC firewall issues
> http://support.microsoft.com/defaul...kb;EN-US;306843
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Welcome!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support