Replication Not Working
Nov 16, 2000
I have eight SQL Servers 7.0 and transactional replication is working fine across these servers except one server. This one server (SERVERX) is not able to become subscriber. Tried everything even re-installed Windows 2000/SQL 7 (S.P2) but I am getting this error in msrepl_errors table "Login failed for user 'sa'". SERVERX is working fine as a publisher but not as a subscriber. The distribution agent displays an error "unable to connect to SERVERX"
Your help is greatly appreciated.
Thanks
Mike
View 3 Replies
ADVERTISEMENT
Apr 11, 2007
Hi,
I have the following setup Merge replication with Web Synchronization:
Server: SQL Server 2005
Subscriber: SQL Server Express edition
The system uses parametrized filters based on HOST_NAME and I have selected 'Automatically define a partition and generate a snapshot when a new subscriber tries to synchronize'
To reduce the size of the initial snapshot I have selected to 'compress' the snapshot files.
It works fine in most of the cases but sometimes when I initialize a new subscriber, replication process fails with the error:
Message
Replication-Replication Merge Subsystem: agent 25BCC8D2-968E-4CEE-B408-2DEF3210F682 failed. The Merge Agent failed to retrieve the snapshot schema script file '\db101BQ_REPL_SHAREuncDB101_BQDB_QA_BQ_WEB_REPL20070411090593dynsnapvalidation.tok'. Run the Snapshot Agent to regenerate the snapshot files for this publication.
If I regenerate the snapshot for the subscriber, and then replicate again it works fine. This is turning out to be an issue as I have to compress the snapshot files as they are huge.
And I cannot use 'Initializing a subscription without snapshot' due to this error: <http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1458147&siteid=1>
Help me
PS: (Applied SP2 Patch, but the problem still persists) and the process works fine if i use pusg subscription
For reference here is the t-sql subscription code, and the vb.net that initiates replication:
Code Snippet
use [BQ_DEV_CLIENT]
exec sp_addmergepullsubscription
@publisher = N'DB101-EQA-SCL',
@publication = N'BQ_PUB',
@publisher_db = N'BQDB',
@subscriber_type = N'Anonymous',
@subscription_priority = 0,
@description = N'',
@sync_type = N'Automatic'
GO
exec sp_addmergepullsubscription_agent
@publisher = N'DB101-EQA-SCL',
@publisher_db = N'BQDB',
@publication = N'BQ_PUB',
@distributor = N'DB101-EQA-SCL',
@distributor_security_mode = 1,
@distributor_login = N'user',
@distributor_password = N'user',
@enabled_for_syncmgr = N'False',
@alt_snapshot_folder = N'',
@working_directory = N'',
@use_ftp = N'False',
-- @job_login = null,
-- @job_password = null,
@publisher_security_mode = 1,
@publisher_login = N'user',
@publisher_password = N'user',
@use_interactive_resolver = N'False',
@dynamic_snapshot_location = N'',
@use_web_sync = 1,
@internet_url = N'https://company/BQREPL/replisapi.dll',
@internet_login = N'USIRU01',
@internet_password = N'pass',
@internet_security_mode = 0,
@internet_timeout = 1800,
@hostname = 'MY_MACHINE'
GO
VB.net code:
Dim subSqlCon As SqlConnection = New SqlConnection(m_subSqlConStr)
Dim pubSqlCon As SqlConnection = New SqlConnection(m_pubSqlConStr)
Dim subscriberConn As ServerConnection = New ServerConnection(subSqlCon)
Dim publisherConn As ServerConnection = New ServerConnection(pubSqlCon)
subscriberConn.Connect()
Dim subscription As MergePullSubscription = New MergePullSubscription(m_subscriberDBName, m_publisherMachineName, m_publisherDBName, m_publicationName, subscriberConn)
If subscription.LoadProperties() Then
If Not subscription.PublisherSecurity Is Nothing Then
Dim agent As MergeSynchronizationAgent = subscription.SynchronizationAgent
agent.DistributorSecurityMode = SecurityMode.Standard
agent.DistributorPassword = m_distributorPassword
agent.DistributorLogin = m_distributorUserName
agent.PublisherSecurityMode = SecurityMode.Standard
agent.PublisherPassword = m_publisherPassword
agent.PublisherLogin = m_publisherUserName
'Set the event handler to capture output messages
AddHandler agent.Status, New AgentCore.StatusEventHandler(AddressOf Me.agent_Status)
agent.Synchronize()
End if
View 10 Replies
View Related
Sep 18, 1998
I`m having a problem setting up replication on a newly installed 6.5 sp4 machine. When I try to create a publication, I get a message telling me that my chosen signon doesn`t have premissions to create a file in the working directory (though I know this isn`t the case). The publication does show up in the tree list in the Publications folder of the database, but when I try to subscribe to the publication, I get an Error 14096. Bummer. Anybody have any ideas? Thanks.
View 1 Replies
View Related
Aug 21, 2007
I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.
The actual command i am executing is a simple
sp_addpublication_snapshot
but it fails with
Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!
I am using SQL 2005 SP2 if that makes any difference.
Thanks
Andy
View 6 Replies
View Related
Apr 19, 2006
Hi,
I've been trying to get my merge replication to work with a sql ce 2.0 on
sql server 2005, but it keeps generating shapshot scripts my pocket pc can't
execute.
Example of my table.sch file in my snapshot folder:
drop Table [dbo].[Application]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Application](
[AppID] [nvarchar](20) NOT NULL,
[AddOnInfo] [nvarchar](50) NULL,
[MaxClients] [int] NULL,
[AppName] [nvarchar](255) NULL,
[NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory]
DEFAULT (10),
[NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture]
DEFAULT (10),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT
[DF__Applicati__rowgu__4C364F0E] DEFAULT (newid())
)
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
I've selected that I was setting up a merge replication that needs to be
compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains
80RTM...
I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY
CLUSTERED
(
[AppID]
)
GO
Even if I make a publication for sql mobile 2005 it generates the same
script.... It's almost as if SQL 2005 is ignoring my settings...
Thanks,
Desperate Natasja
View 12 Replies
View Related
Sep 18, 2015
I have a publisher, distributor and subscribed in transactional replication topology, each on a different MS Windows 2008 server. SQL server version is 2008. I changed the password of the service account used to the the sql server and sql server agent services on all 3 servers. But now replication stopped working (Distributor to publishers shows errors in replication monitor for all publications).
I restarted the services and also tried restarting the servers. I did "update replication passwords" and also used sp_changereplicationserverpasswords to change replication passwords. But it doesn't seem to work.
From [MSrepl_errors], i get the below errors:
The process could not connect to Subscriber 'XXX'.
Cannot generate SSPI context
SQL Server Network Interfaces: The target principal name is incorrect.
View 3 Replies
View Related
Oct 22, 2015
Replicate ddl setting is not working if multiple subscribers are used...
View 2 Replies
View Related
Sep 11, 2007
Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
View 5 Replies
View Related
Oct 12, 2015
Merge replication fails on one node with error. The merge process could not update the list of subscriptions.
(Source: MSSQL_REPL, Error number: MSSQL_ REPL-2147201010)
Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'.
The duplicate key value is . (Source: MSSQLServer, Error number: 2601)The subscription could not be created.
(Source: MSSQLServer, Error number: 14057) works fine on other subscribers.
View 4 Replies
View Related
May 14, 2015
How to check if web merge sync is working between a subscriber and publisher thru HTTPS ? SQL port 1433 at subscriber is blocked so no direct connection to subscriber.
View 2 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
Sep 13, 2007
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies
View Related
Jan 17, 2002
Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
View 1 Replies
View Related
Aug 22, 2007
Hello,
I have this problem on a Production database.
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Thank you.
View 4 Replies
View Related
Mar 6, 2007
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Jul 28, 2006
Hi,
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
Any advice appreciated !
Thanks
Ronan
View 2 Replies
View Related
Jun 28, 2007
Hi all,
I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????
Thanks in advance
Jacx
View 3 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Sep 2, 2015
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
View 6 Replies
View Related
Apr 17, 2007
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Nov 9, 2006
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies
View Related
Jan 30, 2007
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank
View 1 Replies
View Related
Apr 24, 2007
Hi,
I have a particularly difficult problem.
I have a SQL Server in an internal network and need to "replicate" to an identical server and database in a DMZ. The DMZ can only receive files sent by a custom component and no port(s) can be opened in the firewall or standard FTP used.
I also need to minimise traffic (i.e. sending whole tables is not an issue as some contain millions of rows), replicate approx once hourly and allow record locking only (as opposed to DB locking/exclsuive access or table locking).
Any thoughts / experiences greatly appreciated otherwise I'm looking at putting triggers on all the tables to monitor changes and generate SQL statements for execution against the DMZ server.
View 4 Replies
View Related
Feb 23, 2008
Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing?
using SQL 2000 server
SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE (LinksInfo.L_ID IN (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip
View 2 Replies
View Related
Mar 25, 2008
Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
View 1 Replies
View Related
Jun 17, 2004
Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciated
View 1 Replies
View Related
Apr 25, 2006
I am using the following C#...
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password";
loader.ErrorLogFile = "C:log.txt";
loader.SchemaGen = true;
loader.SGDropTables = true;
loader.CheckConstraints = true;
loader.Execute(
"C:xsd.xsd"/* this file below */,
"C:xml.xml"
);
To generate tables in the database using the following xml schema...
(file: xsd.xsd...)
My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.
Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.
Cheers,
Matt.
View 1 Replies
View Related
May 3, 2002
OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error
Syntax error converting character string to smalldatetime data type.
Any help is very much appreciated
Code follows
CREATE PROCEDURE spBattingAve1
@cid datetime,
@cid1 datetime,
@sid nvarchar(10)
AS
DECLARE @query nvarchar(4000)
SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns,
COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname,
t_batting.player_id,
Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games,
COUNT (t_batting.notout) as nout,
(Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun,
(COUNT(*)-COUNT(dnb)) AS Innings,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton
FROM t_batting
INNER JOIN t_game ON t_batting.game_id = t_game.game_id
INNER JOIN t_player ON t_batting.player_id = t_player.player_id
WHERE t_batting.player_id = t_player.player_id
AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'
GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id
HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0
order by '+@sid+'
DESC'
exec (@query)
View 1 Replies
View Related
Jun 21, 2001
In SQL 7.0, when i use the below query in query analyzer, I got an error message. why?
======================
Set Identity_insert on
UPDATE Table1
SET no = 3
WHERE no = 4
Set Identity_insert off
go
Error message
=============
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'off'.
View 2 Replies
View Related
