CREATE FULLTEXT CATALOG Inside A User Transaction.
Jan 15, 2007
hi:
I try to create full text for new created tables.
Since all new created tables will have same columns with different table name.
After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.
But I got error like this:
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Any one has idea how to deal with it?
Thanks
View 3 Replies
ADVERTISEMENT
Mar 19, 2007
Hi Experts:
Do users will still have access to the FullText catalog while the catalog is optimized (not rebuilt)?. My assumption is that they should have the access. Can anyone please confirm?.
Thanks
AK
View 2 Replies
View Related
Nov 29, 2005
I use the context connection for the "normal" work in the CLR procedure.
View 7 Replies
View Related
Sep 18, 2015
I am getting an error about "Cannot perform a shrinkfile operation inside a user transaction", but I don't have a shrinkfile command in my procedure. Does SQL hang on to that command if it was received earlier in a different procedure?
View 5 Replies
View Related
Sep 1, 2007
Hi All,
I am using SQL SERVER 2005 Express. I am trying to set up CREATE FULLTEXT INDEX.
I have confirmed that the Fulltext is installed with:
SELECT
fulltextserviceproperty('IsFulltextInstalled')
So no I need to create a Full Text index:
CREATE FULLTEXT INDEX
ON E190MELCDL(xmldata)
KEY INDEX PK_E190MELCDL
But I get this error:
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search.
Any Ideas?
Thanks,
Bones
View 7 Replies
View Related
Jan 15, 2007
I've got fresh Installed SQLExpress 2005 Adv. with a following:
WinXP Pro, Developer PC, Admin/Full rights, Antivirus PC-Cilin was switched off for the installation.Enable User Instance - 0 (clear check box during installation)SQL Collation: Dictionary order case sensetive for1252 Char setSELECT @@language: us_englishcreate fulltext catalog testAPFullTextcatalog,
Sp_help_FullText_catalogs: 5 testAPFullTextcatalog C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData estAPFullTextcatalog 0 0create unique index indexName_Comment on dbo.FullTextSearchTestTable(RecordID)
sp_configure:
allow updates 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user instances enabled 0 1 0 0
user options 0 32767 0 0CREATE FULLTEXT INDEX ON dbo.FullTextSearchTestTable(Comment)
KEY INDEX indexName_Comment ON testAPFullTextcatalog
WITH CHANGE_TRACKING AUTO
Result:
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1Default full-text index language is not a language supported by full-text search.
I couldn't overcome the issue. Please help.
Alexei
P.S. May be Important: During installation (on Management Studio Express installation stage) a had to click twice the "Ignore" button for the following popUp message:
"Could not write value to key:
SoftwareClasses.xdropenWithProgIdsShared - verify, if you have sufficient access to hot key or contact your support.
View 10 Replies
View Related
May 1, 2007
I'm about ready to smash my computer. I've installed SQL Server 2005 Express Edition with Advanced Services on Win XP Pro SP2. I made sure to enable the Fulltext service at install. Installed to the default instance name, and made sure the fulltext service is running from the CM. I made sure to enable full text indexing on my database from the properties window. I can create the fulltext catalog, but not the index!
----
create fulltext index on [person.contact] (Lastname)
key index [PK_Person.Contact] on myfulltextcatalog
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search.
----------------------------------------------
select * from sys.fulltext_catalogs
returns the catalog
----------------------------------------------
sp_fulltext_service 'update_languages'
Informational: No full-text supported languages found.
----------------------------------------------
select @@language
us_english
----------------------------------------------
select * from sys.fulltext_languages
Returns an empty table
----------------------------------------------
SELECT * FROM sys.configurations
ORDER BY name ;
1126 default full-text language 1033 0 2147483647 1033 default full-text language 1 1
----------------------------------------------
Does anyone have any idea what's going on? I've already removed and re-installed a couple of times to different directories, used different instance names, and still no luck. Any help would be greatly appreciated.
Mike
View 6 Replies
View Related
Oct 30, 2007
Hello all,I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :SELECT ci.itemNameFROM Content_Items ciINNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemIdINNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :A unique column must be defined on this table/view.How do I create a unique column within a view?Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
View 1 Replies
View Related
Oct 30, 2007
Hello all,
I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :
SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]
However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.
So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.
It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :
A unique column must be defined on this table/view.
How do I create a unique column within a view?
Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
View 1 Replies
View Related
Oct 30, 2007
Hello all,
I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :
SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]
However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.
So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.
It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :
A unique column must be defined on this table/view.
How do I create a unique column within a view?
Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.
View 7 Replies
View Related
Dec 10, 2007
Hi,
Hope this is the right forum - apologies if its not. I'm a newbie. I'm at my wits end as I cant create a full-text catalog in SQL server 2000. Let me explain (I'll try and include as much info as I can):-
When I run the following command:
sp_fulltext_catalog 'Cat_Desc', 'create'
I get the following error mesaage:
Server: Msg 7619, Level 16, State 2, Procedure sp_fulltext_catalog, Line 64
The specified object cannot be found. Specify the name of an existing object.
I in as user sa. I determine this from running:
select suser_sname()
The SQL Server instance is running under user: LocalSystem
I determine this from the following command:
DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEMCurrentControlSetServicesMSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @Serviceaccount
The database is owned by: sa (determined by visual inspection).
Yes, full text indexing is enabled for this database as I ran the following command:
EXEC sp_fulltext_database 'enable'
and get the following:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Now I can't think of anything else. I'm at my wits end! Please help. Any comments/suggestions/ideas/diagnostics greatly appreciated.
Thank you,
Al.
PS: Apologies if I'm in the wrong forum!
View 19 Replies
View Related
Nov 2, 2007
When I try to create a full-text catalog on my local database I get an error that I can not find support information for.
Here is the command I run :CREATE FULLTEXT CATALOG asset_search_values_catalog on FILEGROUP ftFileGroup IN PATH 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData' as default
Here is the error message I get:
Msg 7689, Level 16, State 1, Line 1
Execution of a full-text operation failed. 'No such interface supported'
I check the properties of my database and Full-text is enabled.
I am running SQL Server 2005, on an XP Pro, SP2.
I had originially installed in side by side with SQL Server 2000.
I even tried uninstalling SQL Server 2005 (to try a re-install), I could not even uninstall the database.
What should I do.
View 1 Replies
View Related
Sep 7, 2006
Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik
View 1 Replies
View Related
Oct 1, 2007
I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line
quote:
exec %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0)
I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'"
Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs.
How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?
View 5 Replies
View Related
May 6, 2008
Hi,
When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.
Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest
Please let me know the reason for this.
Thanks in advance
Regards,
K. Manivannan
View 1 Replies
View Related
Aug 22, 2005
I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1). However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??
View 5 Replies
View Related
Feb 26, 2006
Is it normal practice to check for @@ERROR after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.
View 1 Replies
View Related
May 9, 2007
Please find my second post in this thread.
Can anyone help me in sorting out the problem and let me know what might be the reason.
Thanks & Regards
Pradeep M V
View 19 Replies
View Related
Oct 9, 2007
Hi,
Can we include a Send Mail Task inside a transaction?
We intend to callback a sent email in case there is an error in the subsequent task, if it is possible.
Thanks and Regards,
B@ns.
View 4 Replies
View Related
Oct 30, 2006
We are designing an ETL solution for a BI project using SSIS.
We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table.
The source table includes technical columns that indicate if each record was processed correctly by ETL procedures.
Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated.
Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code.
I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert.
The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine.
There is some way to group task in the data flow pane in a single transaction ?
There is a better way to do that ?
Cosimo
View 12 Replies
View Related
Apr 11, 2006
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everything works great.
Can you help me to find out the problem?
Thanks,
Laura
View 11 Replies
View Related
Jul 11, 2007
I want to list out the pending transaction for transaction replication by publication.
Help needed.
View 1 Replies
View Related
Nov 23, 2005
Hello, everyone:
I have a local transaction,
BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT
View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:
Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
It looks like remote server is not available inside the local transaction. How to handle that?
Thanks
ZYT
View 5 Replies
View Related
May 22, 2008
Hi All,
After detaching the database I placed the database files and log files in the D folder as C drive was full. When i try to attach the files I am getting an error Failed to attach database Microsoft SQL Error 7622 (QCFTCAT Full text catalog).
I checked there is a folder in C drive by name QCFTCAT and the C drive has no space. What does this folder means please let me know on the same.
Thanks and Best regards,
Raghavendra.
View 2 Replies
View Related
May 22, 2008
Hi All,
After detaching the database I placed the database files and log files in the D folder as C drive was full. When i try to attach the files I am getting an error Failed to attach database Microsoft SQL Error 7622 (QCFTCAT Full text catalog).
I checked there is a folder in C drive by name QCFTCAT and the C drive has no space. What does this folder means please let me know on the same.
Thanks and Best regards,
Raghavendra.
View 1 Replies
View Related
Oct 23, 2014
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
View 1 Replies
View Related
Jan 15, 2007
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
View 1 Replies
View Related
Jun 21, 2006
When the CLR function executes "CREATE ASSEMBLY" the following error is thrown.
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
View 3 Replies
View Related
May 16, 2008
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:
StudentInfo
StudentID StudentName DeptID
101 John 10
102 Alex 10
103 Beth 20
ClassInfo
ClassID DeptID
901 10
902 10
225 20
I want to create a Sproc which will retreive the student's classes in DeptID 10
Following is the Sproc and cursor:
use master
go
Create PROCEDURE [dbo].[getEnclishClasses]
@StudentID int
AS
Declare @printInsertStatement nvarchar(100)
ECLARE NewRowID int
Declare classCursor CURSOR FOR
SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID)
DECLARE @ClassID INT
DECLARE @DeptID INT
OPEN classCursor
FETCH NEXT FROM classCURSOR INTO
@ClassID, @DeptID
WHILE (@@FETCH_STATUs=0)
BEGIN
PRINT 'SET @newID = Scope_Identity()'
SET @printInsertStatement=
(Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values('
+CONVERT(NVARCHAR (10), @ClassID) + ','
+CONVERT(NVARCHAR (2), @DeptID)+')'
FROM [myTest].dbo.StudentInfo
WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
WHERE StudentID=@StudentID))
PRINT @printInsertStatement
END
CLOSE classCursor
DEALLOCATE classCursor
EXEC getEnclishClasses 101
Here is what I try to get (text with actual data from the table):
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10)
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)
Here is what I had got (returning multiple lines, more than number of records I have):
Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
View 8 Replies
View Related
Feb 28, 2005
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
(
@pageSize int,
@previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN
View 4 Replies
View Related
Jul 28, 2000
it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.
Any Help appreciated.
View 1 Replies
View Related
Jun 1, 2006
I've created an SSIS package that contains a Sequence Container with TransactionOption = Required. Within the container, there are a number of Execute Package Task components running in a serial fashion which are responsible for performing "Upserts" to dimension and fact tables on our production server. The destination db configuration is loaded into each of these packages using an XML configuration file. The structure of these "Upsert" packages are nearly identical, while some execute correctly and others fail. Those that fail all provide the same error messages.
These messages appear during Pre-Execute
[Insert new dimension record [1627]] Error: The AcquireConnection method call to the connection manager "DW" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Insert new dimension record" (1627) failed the pre-execute phase and returned error code 0xC020801C.
... which are followed by
[Connection manager "DW"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".
[Connection manager "DW"] Error: An OLE DB error has occurred. Error code: 0x8004D00A.
While still in debug mode, I can check the properties of the "DW" connection and successfully test the connection within the packages that fail.
The same packages run successfully when tested outside the container (i.e. no transaction) or when the configuration file is modified to point the "DW" connection to a development version of the db which is running on the same server as the source database.
I have successfully used DTCtester to verify that transactions from source to destination server are working correctly. Also tried setting DelayValidation = True with no change. I have opened a case with Microsoft and am awaiting a reply so I thought I'd throw a post out here to see if anyone else has encountered this and might have a resolution. Here's some more on the environment:
Source Server:
Windows Server 2003 Enterprise Edition SP1
SQL Server 2005 Enterprise Edition SP0
Destination Server:
Windows Server 2003 Enterprise Edition SP1
SQL Server 2000 Enterprise Edition SP3 (clustered)
Thank you in advance for any feedback you might be able to provide.
KS
View 4 Replies
View Related
May 10, 2007
Dear Friends... I'm having a problem...
I want to manually create the identity column for a table...
I have some dataflws, and in each dataflow I insert values in this table...
I need to start the controlflow in a SQL task to get the last ID and save it in a global variable with name D_INST_IDENTITY.
And in each dataflow I have a script component transform, to get the ID... using a local variable COUNTER! and for each row I increment this value...
Until this step there is no problem... the problem starts here...:
I need to refresh the global variable in the final of each dataflow in order that in the next sequence dataflow I have D_INST_IDENTITY refreshed......
D_INST_IDENTITY = D_INST_IDENTITY + COUNTER
How can I do it? I have a RowCount transform next the script component, but generates errors...
What do you think I can do it?
Thanks!!
View 8 Replies
View Related
