How To Make Fast Forwad Only Cursor Updatable
Sep 15, 2007
In the msdn SQL server document(http://msdn2.microsoft.com/en-us/library/aa172573(SQL.80).aspx), it states:
Transact-SQL cursors can specify the FAST_FORWARD clause on DECLARE CURSOR. This opens an optimized forward-only, read-only cursor.
But in the following section 'Implicit Conversion of Fast Forward-only Cursors', it states:
If a fast forward-only cursor is not read-only, it is converted to a dynamic cursor.
Now I am confused: is it possible to make a fast forward-only cursor 'not read-only'(updatable)?
A fast forward-only cursor by definition is read-only. Am I wrong?
Thanks,
Baihao Yuan
View 1 Replies
ADVERTISEMENT
Feb 3, 2015
i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .
create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp
[code]....
how to include cursor on if part and else part
View 2 Replies
View Related
Jul 9, 2006
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
View 1 Replies
View Related
May 16, 2008
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
DT
View 1 Replies
View Related
Dec 12, 2007
Hello everyone,
How can i create updatable views in sql 2005. Any article or link can be useful.
Thanks.
View 1 Replies
View Related
May 17, 2001
I have a fairly complicated data import that needs to be done, but the table structures of the two databases don't match up too neatly. I thought I'd be clever, and create a view in the target database, and import into that view -- only to find out (after reading the manual) that multi-table views are not updatable and cannot be used with BULK INSERT or bcp.
I'm not a database pro, I'm a programmer thrust into this role. Needless to say, I'm a bit of a newbie on these things!
Anyway, the view's code:
<pre>
CREATE VIEW dbo.RJ2_V_Client_Import
AS
SELECT Clients. IDENTITYCOL, Clients.CompanyName,
Clients.Address1, Clients.Address2, Clients.StateID,
Clients.City, Clients.Zip, Clients.CountryID, Clients.PhoneMain,
Clients.PhoneFax, Clients.WebAddress,
Clients.ParentCompany, Clients.DivisionDepartment,
People.NameFirst, People.NameLast,
People.AddressPrimary1, People.AddressPrimary2,
People.CityPrimary, People.StatePrimaryID,
People.ZipPrimary, People.CountryPrimaryID,
People.PhoneWork, People.PhoneWorkExtension,
Clients.EnteredDate, Clients.UserLevelID, Clients.Revenue,
Clients.NumberOfContractors, Clients.SICCode,
Clients.Industry, Clients.ReceptionistName
FROM Clients INNER JOIN
People ON Clients.BillingPersonID = People.PID
</pre>
The reason there is a "People" table is because several (SEVERAL) tables in this database contain records for people, and we felt that this was a better design because of it.
The data we're trying to import contains records for billing contacts, which are the People.* fields referenced. It's extremely important that we get this data in the system.
Since BULK INSERT is out, are there any workarounds? There's got to be some kind of non-manual solution, since I know other people have to do imports that start as one table and end up in two ...
For this particular set, I could do it manually -- only two records are affected. BUT, for future sets, I can't do it manually -- literally thousands of records will be affected, and I just don't have the time (or inclination) to do the entry ... Plus, think of the potential error rate!
Thanks in advance
John
View 1 Replies
View Related
Jun 2, 2000
I am trying to update the current row of a cursor that I have declared from Query Analyzer and I keep getting an error message saying the cursor is Read only. This doesn't make sense because BOL says that cursor's are updatable by default. Anyone had this problem?
Ex: This is the statement I am using to update the current row in the cursor:
UPDATE TableName SET Field1 = 'D'
WHERE CURRENT OF CursorName
jg
View 1 Replies
View Related
Apr 12, 2000
I have created a view ('Data_test') based on Table1 and Table2 joined in the following manner:
Table1.PrimaryKey <-----> Table2.ForeignKey /* same as Primary Key */
When trying to insert data into the tables through the view, I get the following error:
Server: Msg 4405, Level 16, State 2, Line 1
View 'Data_test' is not updatable because the FROM clause names multiple tables.
Any ideas of how to make the view updatable?
View 1 Replies
View Related
Jul 20, 2005
I am trying to write updatable cursors in a stored procedure? Can I getany help to write updatable cursors?Any help would be appreciated very much.Thanks for your help in advance.Rajah V.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
Hello:I've just converted my Access 2K db to SQL Server 2K and the conversion wentsmoothly. I created users, roles, views and forms and gave the userspermissions to use them. I "THINK" I have done everything, but......youcannto update the form, view, or table. I get "Recordset Not Updatabale"when I modify a field in either.Assuming I set the permissions correctly on the db, table, view, and userscorrectly, is there something else I need to do to make the table and viewupdatable?My front end is Access 2K.Thanks for any advise.....Richard H
View 1 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
           for select name, salary from ag
 open ll
            fetch from ll
Â
             while @@FETCH_STATUS=0
              fetch from ll
               update ag set salary=200 where 1=1
Â
  close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
Oct 6, 2004
I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.
Thanks in advance!
View 4 Replies
View Related
Nov 1, 2006
Hi all,
I have a problem with "Updatable Subscriptions" under concurrent transactions on both pub and sub effecting same data. At high level of concurrency it Subscriber starts giving error msg "Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber.".Please tell me any way arround for this.
View 4 Replies
View Related
Jun 13, 2007
I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions.
On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER
epl_snapshot, PUBLISHERSERVER
epl_logreader and PUBLISHERSERVER
epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER
epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER
epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
View 3 Replies
View Related
Dec 1, 2006
Hello, There are 2 questions that I interested in:Q1: We are planning to scale out SQL Server 2005 with Transactional Replication and use the following topology:NodeA: PublisherNodeB: Publishing Subscriber of NodeANodeC: Subscriber of NodeBIf it is necessary to allow update on NodeC, is it possible to set NodeC as an updatable subscriber? BOL states that Updatable Subscriptions do not support republishing data. But I'm not sure whether it is applicable to these case. Please advice.Q2:In SQL Server 2005 BOL, in claim that "Standard transactional replication assumes read-only Subscribers and is
hierarchical in structure: typically a single Publisher publishes data
to one or more Subscribers. Standard transactional replication also
supports a republishing hierarchy: updates are delivered from a
Publisher to a set of republishing Subscribers, who in turn deliver
updates to a final set of leaf-node Subscribers. Updating
subscriptions offer the ability for Subscribers to push changes back to
the Publisher, but the arrangement is still hierarchical because
changes follow the hierarchical structure when moving between
Subscribers and Publishers. ..." in the topic "Peer-to-Peer Transactional Replication" Could somebody describe how Subscribers push back changes to Publisher in hierarchical structure? Could Republisher setup in the "hierarchical" structure or topology as a middle-node (Not a leaf node nor root node)?Thanks a lot.Terence
View 1 Replies
View Related
Sep 28, 2007
Hi,
In the SQL92 Specifications i read the foloowingf statement...
"All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are
permitted for updatable tables, subject to constraining Access
Rules. The operations of insert, update, and delete are not allowed
for read-only tables."
But i am concentrating on the below line from the above written lines,
"Derived tables are either updatable or read-only."
I want to ask that is derived tables are updatable or not??? if yes then how,???i tried the following querry but its not working...
Code Block
Update (select * from test1) AS de SET id=0
????
View 10 Replies
View Related
Apr 11, 2006
Hi all,
I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.
My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)
I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.
Any help would be much appreciated!
Thanks,
Faraz
View 2 Replies
View Related
Feb 3, 2004
Hi. I receive the SQLException:
[Microsoft][SQLServer 2000 Driver for JDBC]Can not update, the specified column is not writable.
when I attempt to call updateString() on a RecordSet column. I have set the Statement object on which I execute the query to obtain the RecordSet to be scrollable and updatable.
My goal is to replace the ID value contained in a particular column of each row with a longer text string that includes the ID value. If I can't alter the column values via the RecordSet, can I write SQL as part of the query to generate the text string?
Thank you.
Raj
View 2 Replies
View Related
Mar 7, 2008
Hi All,
I have a problem replicating data from Subscriber to Publisher.
I configured Queued updatable subscriptions in transactional replication. Publisher is on SQL Server Standard edition and Subscriber is on SQL Server Express edition, both on Windows XP.
When I insert/update data in publisher, the data is replicated to subscriber. If I insert/update data on subscriber the data is not replicated. When I see the replication monitor for errors, it shows an error "Cannot load a DLL xprepl.dll or one of its resources".
Could any one give me a solution or please tell me if I missing something?
Thanks you all,
Ravi.
View 6 Replies
View Related
Dec 3, 2007
Hello,We are researching whether the following scenario would be possible:In an upcoming application release, we have to move some tables (Logtables, look up tables, and a couple of secure tables) from database Ato database B.Rather than wait and do everything all at once, and have no roll-backplan should it fail, we'd like to create database B now, and startmoving those tables one by one over to it.To ensure compatibility with the existing code-base, we'd like todetermine whether we can use updatable views to allow the current codeto continue to run against the existing DB.Essentially, we would do this:Given a table named LogTable In database A, we'd copy all ofLogTable's data to database B. (We'd look at the transaction log tocopy any changes made on rows modified after copying started.)Then, we'd turn off the site for a few moments, and:In database A, we'd rename LogTable to LogTable-Old, and create a viewcalled LogTable which points to DatabaseB.dbo.LogTable.When we turn the site back on, updates and selects to LogTable wouldphysically pull from database B from now on.I have already verified that performing selects and updates against aview that refers to another physical database actually does work inSQL 2K5.My question is are there any pitfalls or things we should be aware ofthat anyone else has experienced trying to do something like this?Does it sound feasible?Thank you,Josh
View 3 Replies
View Related
Apr 24, 2007
Hi to evebody.
I'm working with the transactional publication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher.
When I do an update in a subscriptor's table, the database engine shows the following error:
21064 - 16 - The subscription is unavailable for immediate updating because it is marked for reinitialization. Try again after the reinitialization completes.
And rollbacks the transaction.
Does anybody knows what to do to solve this problems.
The publisher is a Windows XP with the SQL Server 2005 Developer edition with SP2
The subscriber is a Windows 2003 Server with SQL Server 2005 Developer edition without SP2
I'm using also the inmediate updating subscriptions. Both operative systems have the MSDTC runing.
Thank you in advance.
Sebastian.-
PS: Sorry about my english, it's been a long time without using it.
View 1 Replies
View Related
Apr 11, 2007
This may be a simple answer, but I was wondering when setting up transactional replication with updatable subscriptions why does this error occur?
Error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK " was unable to begin a distributed transaction.
Changed database context to 'MyDB'.
OLE DB provider "SQLNCLI" for linked server "REPLLINK" returned message "No transaction is active.". (.Net SqlClient Data Provider)
I've looked for MSDTC security documents and checked every box in the security dialog and selected the 'No Authentication Required€™. MSDTC and SQL Agent are running as NETWORKSERVICE user. I also setup permissions on each individual DCOM Objects for SQL Server. And MSDTC service is running. Both Servers are SQL Server 2005 Standard and Windows 2003 R2. The publisher is on the same box as the distributor. No domains or trusts setup on either. This error occurs when changing data on the subscriber.
Thanks,
Patrick
View 6 Replies
View Related
Oct 17, 2006
Hi,
I have created a publication for sql server 2005 Mobile Edition subscriber,
I need to add another Publication/merge replication on the same tables,
Scenario:
-Server A and mobile devices (publication for sql server 2005 Mobile Edition subscriber)
-The same Server A send data "1 way direction" to Server B by merge replication publication.
NB: My Tables are updatble in both publications.
Any idea?
Thanks,
Tarek Ghazali
SQL Server MVP
View 1 Replies
View Related
May 24, 2012
So, Microsoft decided that they were deprecating Transactional Replication with Updatable subscriptions. In that case, you have 2 options (if I am correct): Pay for Enterprise (if you are already not) and use peer-to-peer or use bidirectional transactional replication which is basically setting up a transactional from db1 to db2 and also transactional from db2 to db1.
The issue I see in both cases is conflict resolution. With updatable subscriptions, you could specify how to handle the conflict. With either of these 2 options (from what I can tell) you cannot allow the engine to handle this for you.
Any thoughts? Seems like a slap in the face to those who have been using MS for years and a damn good reason for companies that rely on updatable subscriptions to not upgrade to 2012.
View 7 Replies
View Related
Jun 7, 2006
Hi
This is for Sql Server 2000.
I get the following error :
View or function 'PS_EMPLOYMENT' is not updatable because the modification affects multiple base tables.
Sql is as follows:
INSERT INTO PS_EMPLOYMENT(EMPLID,EMPL_RCD,PER_ORG,BENEFIT_RCD_NBR,HOME_HOST_CLASS,OWN_5PERCENT_CO,
SECURITY_CLEARANCE,NEE_PROVIDER_ID,POSITION_PHONE,BUSINESS_TITLE,REPORTS_TO,
SUPERVISOR_ID)
VALUES('000004', 0, ' ', 0, 'M', 'N', ' ', ' ', ' ', ' ', ' ', ' ')
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Searching the KB i only found links to SP1 and SP3, the server is already at SP3.
BOL says the following:
SQL Server 6.x :
Updatable views were restricted to modifications that affected only one table
SQL Server 2000:
Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
So why can this view not be updated in SS2000 SP3 ?
Here is a copy of the view defintion:
ALTER VIEW PS_EMPLOYMENT
(EMPLID, EMPL_RCD, PER_ORG, BENEFIT_RCD_NBR, HOME_HOST_CLASS, LAST_DATE_WORKED, HIRE_DT, REHIRE_DT,
TERMINATION_DT, SENIORITY_PAY_DT, CMPNY_SENIORITY_DT, SERVICE_DT, PROF_EXPERIENCE_DT,
LAST_VERIFICATN_DT, EXPECTED_RETURN_DT, LAST_INCREASE_DT, OWN_5PERCENT_CO, PROBATION_DT,
SECURITY_CLEARANCE, NEE_PROVIDER_ID, POSITION_PHONE, BUSINESS_TITLE, REPORTS_TO, SUPERVISOR_ID)
AS
SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,
D.HIRE_DT ,D.LAST_HIRE_DT ,D.TERMINATION_DT ,A.SENIORITY_PAY_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,
A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,D.EXPECTED_RETURN_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,
A.PROBATION_DT ,A.SECURITY_CLEARANCE ,C.NEE_PROVIDER_ID ,A.POSITION_PHONE ,A.BUSINESS_TITLE ,D.REPORTS_TO ,
D.SUPERVISOR_ID
FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D
WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )
View 3 Replies
View Related
Jun 15, 2007
I keep getting this following error when trying to set-up an updateable subscription on SQL 2005 (latest service packs). Server is set for mixed mode auth.
At the final stage of the new subscription wizard I get:
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
The user is not associated with a trusted SQL Server connection
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Error Number: 18456, Severity: 14, State: 1,Line Number: 1
Can anyone recommend a possible way of debugging this? Is it some issue with RPC security?
View 1 Replies
View Related
Feb 26, 2008
i have result set from such query :
resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable;
queryText =
SELECT agvb_id as ID
,agvb_cat_id as Category
,agv_brn_id as Brand
,agvb_brn_name as BrandName
,agv_brn_area as Area
,agv_brn_sku_qty as SkuQuantity
,agv_brn_qty as Quantity
,agv_orderliness as Orderliness
,agvb_importance as Importance
FROM AGENTS_VISITS_BRANDS
WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id)
AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id)
ORDER BY agvb_brn_name
if i open resultSet with such qury it looses its updatable option.
If i remove from where 1 param (no mater which) everything runs ok.
View 1 Replies
View Related
Jul 3, 2007
I have just started getting the following error from the queue reader but I can't see why.
The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''Database'' on ''ServerName''. Ensure that the publication and subscription are defined properly and that both servers are running.
Does anybody know what it means / how I can fix the problem?
Thanks
View 11 Replies
View Related
May 7, 2015
I have setup Transnational replication with updatable subscription.
There were around 7000 rows update at subscriber from one query which was executed at subscriber side.
But this update has not taken place at publisher yet and so to other subscribers.
Is there any way i can monitor pending log or where is the issue?
I find so many ways to see pending logs or statistics for - "Publisher to Subscriber" but not for "subscriber to publisher"
What is the way to see subscriber to publisher pending and issues?
View 0 Replies
View Related
Sep 28, 2007
We are currently testing a Replication topology as follows:
SQL Server 2005 Developer as publisher and distributor of a push updatable transaction replication
2 SQL Server 2005 Express clients as subscribers.
The replication is working great. If the connection is lost, the subscribers switch from Immediate to Queued Updating, and seem to shift back to Immediate when they regain the connection.
My question is to determine if there is a way that I can ask SQL Express on the client which mode is it currently running, or ask SQL Express if it has a current live connection to the distributor.
I know that they are usually mutliple ways to accomplish tasks, and I would be open any suggestions, preferrable programming in VB (such as with SMO or RMO) or using stored procedures or functions.
I hope this give some people something to chew on during the weekend and thank you to all that have taking the time to read this.
View 5 Replies
View Related
