The transaction log does not truncate despite all
attempts (no_log,truncate_only, etc...) This database is a subscriber
for replication. Tried the reconfigure with override.
There are no open transactions (dbcc opentran)
Need help fast !!!
Danke schoen
I have a DTS package in a QA server (MS SQL SERVER 2000). Now I want to implement that DTS package to the Production Server (MS SQL SERVER 2000) without any administrative effort.
I just wanted to create a script which can then execute to the production server to create that DTS package.
Till now what I have done to overcome this issues are..
I have save the DTS package in QA Server as Structured Storage File.
And then copied the Structured Storage file from the QA Server to the production Server
And then execute the package using dtsrun utilities.
But in that case it is not creating the package in the production server.So that one can update or view the package from SQL Server Enterprise Manager ->Data transformation Service->Locale Package.
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies View RelatedDoes anyone have a code snippet for dumping the actual sql a command would be running?
View 3 Replies View RelatedMy site is hosted with 1and1. They tell me I need to dump my SQL database before uploading it to their site. I have never done this before. I have looked in the online help and have asked them for help, but nothing seems to work. I've tried queries like the following:DUMP DATABASE dbname TO DISK = 'C:folderfilename'It tells me it doesn't recognize "dbname", which is the name of the MDF file (without the ".mdf" extension). So I'm not sure what I should be using for database name.I'm using Visual Studio 2005 Professional. Can someone help me with dumping my database? Thanks!
View 1 Replies View RelatedHi, is there a software that can dump the SQL result into a file or to another database? I am using the MSSQL Server and I am accessing it remotely. Is there a program where I can issue an SQL statement and then I can dump the result into a file (in SQL inserts or just plain CSV).
Is there a free version?
Thanks!
Hey Guys,
I do alot of development on my personal machine and have quite few databases in SQL Server. My PC is very prone to crashing :-p so Im writing a custom batch script to backup up all my data. How can I backup my SQL Server databses into a text file. I dont want to use the BAK file. In MYSQL this is a relatively simple process where i just have to use the mysqldump utility which scripts 'all' my databases (structure and data). Can I do the same thing in SQL Server? Please tell me a way. Its going to save me a lot of trouble.
Thanks in advance guys.
Hi - is it possible to dump the query text of every query runagainst a particular database, or all databases, in an SQL Server2000 installation? We've an issue whereby a 3rd. party applicationrunning queries over ODBC sometimes claims that gobbledegook isreturned, and it would be very useful to identify the query that'sbeing claimed is the problem. I suspect that it's an applicationissue rather than a SQL Server one.__________________________________________________ ___________Are you Catholic ?http://www.CatholicEmail.com100s of FREE email addresses --->http://www.UltimateEmail.comSend an Online Greeting Card http://www.UltimateEcards.com
View 1 Replies View RelatedDoes anyone know how I can dump the transaction log to a file
so I can read it?
Thanks,
Dianne
Using SQL version 6.5, I'm trying to dump our primary database to a backup server
with the following command:
DUMP DATABASE TMSEPRD TO Backup_Server WITH STATS = 10, NOINIT, NOSKIP
I created a backup device called Backup_Sever with the location of
stlcopadmbkackup. When I try the command I get the error
Can't open dump device 'stlcopadmbkackup', device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)
and in the eventlog it indicates 'access denied' as the cause for the failure.
I've looked in the Books Online and searched the technet database to find an
answer. The Books Online does say that in creating the backup device permissions
are not checked. I've checked and double checked the permissions on STLCOPADMBK
and there shouldn't be any reason why access is being denied.
I've installed the latest service releases for version 6.5 so that shouldn't be
an issue.
Help
I am having problems when trying to dump to a network share (NT Network) from SQL Server 6.5 and would appreciate any insight this group can lend.
To setup this dump, I followed the instructions provided in the SQL Server FAQ (www.swynk.com) and have been able to successfully complete this task on a different server (so I know I'm editing the registry correctly etc..etc..etc..)
none the less,
Here is an exerpt from NT's Event Viewer:
SQL Server Scheduled Task: 16, 'Network Dump - EIWDATA' -- Status: Failed
-- Task Invoked on: 7/23/99 1:45:18 PM -- Message: Can't open dump device 'crosrvdumpeiwdata.dmp', device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)
Thanks for any help :)
mtf8
I was wondering if there is a way to schedule a tast that will dump afixed width text file of all the new entries in a table. So if I hada table with likeusername - varchar(20)created - smalldatetimeI could get a weekly feed each week of all the new users in a textfile. I know I could write a script that would go through and do thisby looking at the time stamp, and the last time that the filepreviously ran and get the new dates but I was hoping there was abuilt way to do this. Or perhaps a more elegant solution.Thanks,Charlie
View 2 Replies View RelatedHi:)I need to dump data from database in to *.sql file. I try it using DUMPDATABASE but it not work that I need it.So, how can I dump data from database in to *.sql file and then run itusing: osql -E -i nameOfDumpedDatabase.sqlThanks for help, greetings, gregory :)
View 4 Replies View RelatedHi all,
While dumping several tables into the same Backup device, I got this error message in the Log :
dbsvolopen: Backup device 'E:BACKPANBackUp_Of_Focus_Tables.DAT' failed to open, operating system error = 32(The process cannot access the file because it is being used by another process.)
There is no other process using this device running at the same time.
Any idea ?
I have some "phantom" tasks running, specifically, DB dumps to a NULL Pipe device and they are not scheduled!!! These do not show up in the SYSTASKS table, where can I look to possibly find these un-scheduled dumps? Thank you....
View 1 Replies View RelatedWhile working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
<?xml version="1.0" encoding="utf-8"?><FormBuilder><ClientID>0</ClientID><SiteID>0</SiteID><IncidentType></IncidentType><IncidentCategory></IncidentCategory><IncidentSubCategory></IncidentSubCategory><CreatedBy>2</CreatedBy><CreatedOn>Wednesday, April 02, 2014</CreatedOn><ModifiedOn /><ModifiedBy /><Section SectionID="ASD" SectionDisplayName="ASD" ColumnType="1" IsDeleted="0" SectionPosition="1"><SectionField FieldName="Bro" Section="ASD" ModuleID="0" Length="" PickData="" ChkData="" RadioData="" ListData="" FieldType="Text" Checked="false" ColumnType="1" IsDeleted="0" CoulmnOrder="0" FieldID="1" IsPrimary="" IsMandatory="" SystemMandatory="" RowPosition="1" FullRow="" /></Section></FormBuilder>
Just want to know the comments how far is this design feasible.....
What are the pros and cons of such a design...
Where we should use such type of db design where are the fields are dumped in a single column...
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
i am having one ABC database in this a,b,c are the tables
i need to dump a,b,c tables data into z table in the other xyz database
Hi,
I have two table as follows
Tabel Cat
{
ID
Update Datetime
}
Table Master
{
UniqueId
Cat Datetime
}
I wanted to insert millions of rows from Cat to Master table and can be achieved from the following query
Code SnippetIINSERT INTO Master (UniqueId, Cat )
SELECT ID, Update from Cat
Question : Is there any better way do achieve the same operation with better performance mean to say that the execution should be less than the time it takes to execute the above statement
Thanks for your time
~Mohan
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies View Related
I want to list out the pending transaction for transaction replication by publication.
Help needed.
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?
I created a Calculated measure in cube something like this : ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount]). To get only spend transactions. Now, I want to slice this measure with same hierarchy to find the amount distribution across different transaction types under spend transaction. But this query behaving like the measure doesn't have relation with measure.
you can think this as below query:
WITH
MEMBER SPEND AS ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount])
SELECT NON EMPTY {SPEND} ON 0
,NON EMPTY ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent]) ON 1
FROM [CUBE]
Hi
How do I make use of begin transaction and commit transaction in SSIS.
As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:
Hresult:0x80004005
descriptionyntax error or access violation.
its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.
Thanks,
Prashant
Error returned when trying to commit the transaction to a database that is a replication distributor. (sql2005 ctp16)
View 10 Replies View Related
Hi,
I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented at http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp
. My workflow is as followed
*********************************
For Each ADO.Record in Oracle (transaction=not supported)
If (Certain_Field_Value = 'A')
Lookup Data in SQL DB with values from Oracle (transaction=not supported)
DO Sequence A (Start a Transaction , transaction=required)
INSERT/UPDATE some records in SQLDB(transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'B')
Lookup Data in SQL DB with values from Oracle (transaction=not supported)
DO Sequence B (Start a Transaction , transaction = required)
INSERT/UPDATE some records in SQLDB (transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'C')
------------
------------
End ForEach Loop
*************************************
My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.
But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".
What is it that I am doing wrong?
Regards
KyawAM
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
I'm getting this error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
http://support.microsoft.com/kb/839279 this didn't help
any suggestions?
I am executing a stored procedure something like this
Create Procedure TEST
@test1
@test2
AS
BeGIN TRANSACTION ONE
SELECT...
UPDATE..
....
....
....
TRUNCATE
etc and lot of Select,update and delete statements like this
.....
COMMIT TRANSACTION ONE
The Block of code which I have b/w BEGIN TRANSACTION AND COMMIT TRANSACTION ..Will it be rolled back
if it encounters any errors in the SELECT,UPPDATE,DELETE ..statements which I have with the transaction one.
IF not How do I roll back if it encounters any erros b/w the BEGIN TRANSACTION and END TRANSACTION.
Thanks
micky