Wednesday, March 7, 2012
Cannot sync when Auto Identity Ranges Enabled?
I have a small db which I need to merge replicate with a number of PocketPC
devices. NT Authentication is used.
If I do not enable the automatic identity range option, the PocketPC clients
can connect without error. However, if I enable this (which I need to), the
PocketPC user gets a host of errors:
1. (Code 80004005) A call to SQL Server Reconciler failed.
2. (Code 80045007) The merge process could not retrieve article information
for publication 'Survey'
3. (Code 0) {call sp_MShelpmergearticles(?,8000000,?)}
4. (Code 4833) Could not connect to server 'SERVER1' because 'SQLAdmin' is
not defined as a remote login at the server.
SQLAdmin is our Service startup account. It is a member of the Administrator
group.
The user account being used to connect is a standard domain user account.
If I add the user to the Administrators group the process succeeds
everytime. I have checked permissions on the SQLCE folder, and snapshot
folders and AFAIK they are correct (i.e. the user has write permission).
It looks like a permission problem, but I dont know where. It seems the only
solution to this is to add the user to the Administrators group (totally
unacceptable!).
Any suggestions because I am at a complete loss?
Cheers,
James Goodman
Did you re-run your merge agent? I've had no problem using a domain user
account, particularly if set up for anonymous subscribers. Also, you should
be getting a 280xx error number back from the errors collection in the .Net
code. The MSDN site has a list that precisely defines what each of those
errors mean.
"James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:ufn1ZWObFHA.1404@.TK2MSFTNGP09.phx.gbl...
>I have been pulling my hair out with this for some time now:
> I have a small db which I need to merge replicate with a number of
> PocketPC devices. NT Authentication is used.
> If I do not enable the automatic identity range option, the PocketPC
> clients can connect without error. However, if I enable this (which I need
> to), the PocketPC user gets a host of errors:
> 1. (Code 80004005) A call to SQL Server Reconciler failed.
> 2. (Code 80045007) The merge process could not retrieve article
> information for publication 'Survey'
> 3. (Code 0) {call sp_MShelpmergearticles(?,8000000,?)}
> 4. (Code 4833) Could not connect to server 'SERVER1' because 'SQLAdmin' is
> not defined as a remote login at the server.
> SQLAdmin is our Service startup account. It is a member of the
> Administrator group.
> The user account being used to connect is a standard domain user account.
>
> If I add the user to the Administrators group the process succeeds
> everytime. I have checked permissions on the SQLCE folder, and snapshot
> folders and AFAIK they are correct (i.e. the user has write permission).
> It looks like a permission problem, but I dont know where. It seems the
> only solution to this is to add the user to the Administrators group
> (totally unacceptable!).
>
> Any suggestions because I am at a complete loss?
> --
> Cheers,
> James Goodman
>
Sunday, February 12, 2012
Cannot run SQL2005 stored procedure from excel 2003
Usually have no problems pulling data into excel via SQL stored procedures until now.
Created an sp that contains a
declare @.tbl table(
Customerno varchar(6)
,InvoiceDate varchar(25)
,CustomerType varchar(10)
,CustomerRegion varchar(50)
,CustomerTypeName varchar(50)
)
Followed by a insert then a select statement on this @.tbl variable.
whenever i try to call this sp i get an error message saying "The query did not run, or the database table could not be opened"
This is what i'm using to connect:
With Sheets(1).QueryTables.Add(connection:="OLEDB;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=UnityBI;" & _
"Data Source=SQLdatabase;Use Procedure for Prepare=1;Auto Translate=True;" & _
"Packet Size=4096;Workstation ID=ACER-DAVEW;Use Encryption for Data=False;" & _
"Tag with column collation when possible=False", Destination:=Sheets(1).Range("A1"))
.CommandType = xlCmdSql
.CommandText = "usp_gordonsnodrops "
'.Name = "proclarity UnityBI_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
The stored procedure runs fine from other applications, just excell seems to be the problem.
Any help would be greatly appreciated.
Ok, after a very frustating week with this problem the answer turns out to be very easy.
Kinda deflating really after the amount of hair i've pulled out.
The stored procedure needed the statement "Set NoCount On" adding.
Works like a charm now
eg
ALTERPROCEDURE [dbo].[usp_GordonsNoDrops]
@.MonthYear asvarchar(50)='march06/07'
--@.End as varchar(10)
as
begin
setnocounton
sql statements etc....
end