Hello,
I am attempting to backup a database that is receiving log shipments and is presently in standby / readonly mode. What is the best way to do this?
Thanks in advance,
Bob
I created a new column and modified my businesslogic file and the stored procedure. My program uses the SQL helpder class to execute the stored procedure which inserts the article into the database. All of the other previous columns are getting written to, however the new column I created is getting NULL when I open the database to inspect. Why does this happen? I made sure the db column name matches exactly with the params elsewhere.Did I miss something? It's not throwing an error anymore. it's not taking the data I enter into the form and putting it into the db column.
View 4 Replies View RelatedI have setup database mail account with the gmail smtp. But I m getting error (i.e. mail server failure) while sending or receiving database mails.
View 5 Replies View RelatedI have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows: -------------------------------------------------------------------------------------------------------
sw_calendar = calendarAdapter.GetEventByID(cid)
If sw_calendar.Rows.Count > 0 Then
lblStartDateText.Text = sw_calendar(0).eventStartDate
lblEndDateText.Text = sw_calendar(0).eventEndDate
lblTitleText.Text = sw_calendar(0).title
lblLocationText.Text = sw_calendar(0).location
lblDescriptionText.Text = sw_calendar(0).description
Else
lblStartDateText.Text = "*** Not Found ***"
lblEndDateText.Text = "*** Not Found ***"
lblTitleText.Text = "*** Not Found ***"
lblLocationText.Text = "*** Not Found ***"
lblDescriptionText.Text = "*** Not Found ***"
End If
-------------------------------------------------------------------------------------------------------
If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message:
Unable to cast object of type 'System.DBNull' to type 'System.String'.
I've tried a bunch of different things such as:
Adding ".ToString" to the database field,
Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value...
...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message.
Can anyone help me with the code required to trap the null within the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline )
Thanks,
Brad
Using the following code we are having a problem connecting a clients WIndows Mobile device to the SDF under IIS. We have success elsewhere. Does anyone have any clues ?
Cheers Al
Dim repl As New SqlCeReplication()
Try
repl.InternetUrl = InternetUrl
repl.Publisher = Repl_Publisher
repl.PublisherDatabase = Repl_PublisherDatabase
repl.PublisherSecurityMode = SecurityType.DBAuthentication
repl.Publication = Repl_Publication
repl.Subscriber = Repl_Subscriber
repl.SubscriberConnectionString = Repl_SubscriberConnectionString
repl.DistributorLogin = Handhelds_User
repl.DistributorPassword = Handhelds_Password
repl.AddSubscription(AddOption.ExistingDatabase)
repl.Synchronize()
MsgBox("Synched!")
Catch err As SqlCeException
MessageBox.Show(err.ToString, "Error")
Finally
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
End Try
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedI have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View RelatedBackup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies View RelatedI should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
Hi All,I am facing this issue very frist time. I add a logical device asfollowing:USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksData','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksData.bak'-- Create a logical backup device, AdvWorksLog.USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksLog','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksLog.bak'-- Back up the full AdventureWorks database.BACKUP DATABASE AdventureWorks TO AdvWorksData-- Back up the AdventureWorks log.BACKUP LOG AdventureWorksTO AdvWorksLogThe database backup is completed successfully but log's backup failswith the following messages:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current databasebackup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.We are moving from SQL 2000 to SQL 2005. I have ensured that databasehas full recovery mode on. We are using SQL 2005 on Windows 2003 ServerSP1.Further more, I also created a plan to test it, and plan also failswhen backing up the logs.Can anyone shed some light on this issue.Thanks in advance.Najm
View 1 Replies View RelatedHi,
I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.
The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:ackups.
The job executes the following steps:
Step 1: BackupFull:
Command:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:Backups' /* location of backup files */
Step 2: MarkAndBackupLog
Command:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:Backups' /* location of backup files */
My challenges are:
1. The job doesn't back up my databases(D:Backups is empty after executing the job)
2. I get every time the following error in error protocol of the job:
BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).
I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.
Any help would be appreciated.
Thanks in advance,
Greg
I am using sql server 2000 and windows server 2003 standard edition:
My database backup job is failing due to lack of disk space. I am taking the backup onto E drive and the
available free space on E drive is 6.85 GB and there are no other drives I can use for the database backups.
The size of mdf file is 21 GB and that of ldf file is 4.2 GB.
The transaction log back up job of that db ran fine.
This database recovery model is Full and
Auto shrink is not checked.
There is one primary filegroup for the database.
In this situation, I am thinking of the following option:
1. Backup the db and log files onto another network shared drive.
If I want to still use the same server E drive to backup the db and log files instead of using another network shared drives.
how can I do that.
Please let me know the best way of handling this issue.
Any help is greatly appreciated. Thanks!
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
Hi there,
I am writing an SQL stored procedure that returns a dataset as well as
a return value. When I execute the stored proc, all seems to
work, except the return value comes back as null.
Code:
// Create Instance of Connection and Command Object
SqlConnection
connection = new SqlConnection(ApplicationConfig.ConnectionString);
SqlCommand
command = new SqlCommand("SoundLeaf_GetPagedProductsByCategory",
connection);
// Mark the Command as a SPROC
command.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter
parameterID = new SqlParameter("@CategoryID", SqlDbType.Int);
parameterID.Value = categoryID;
command.Parameters.Add(parameterID);
SqlParameter
parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
parameterPageIndex.Value = pageIndex;
command.Parameters.Add(parameterPageIndex);
SqlParameter
parameterRowCount = new SqlParameter("@RowCount", SqlDbType.Int);
parameterRowCount.Value = rowCount;
command.Parameters.Add(parameterRowCount);
SqlParameter
parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int);
parameterPageCount.Direction = ParameterDirection.Output;
command.Parameters.Add(parameterPageCount);
// Open the connection and execute the Command
connection.Open();
SqlDataReader
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
pageCount =
(int)command.Parameters["@PageCount"].Value; // Null Reference Exception
}
SQL:
CREATE PROCEDURE GetPagedProductsByCategory]
(
@CategoryId int = 0,
@PageIndex int = 1,
@RowCount int = 10,
@PageCount int OUTPUT
)
AS
Declare @TotalRows int, @StartPosition int
Declare @PK int
DECLARE @tmpTable TABLE (
PK int NOT NULL PRIMARY KEY
)
SELECT @StartPosition = (((@PageIndex - 1) * @RowCount) + 1)
Set @TotalRows = @StartPosition + @RowCount
Set RowCount @TotalRows
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
select a.ProductId
from
products a,
categoryProducts b
where
b.categoryid = @CategoryID and
b.productid = a.productid
Order By a.productid
Open PagingCursor
Fetch Relative @StartPosition From PagingCursor Into @PK
while (@RowCount <> 0) And (@@Fetch_Status = 0)
begin
Insert Into @tmpTable (PK)
Values (@PK)
Fetch Next From PagingCursor Into @PK
Set @RowCount = @RowCount - 1
end
Close PagingCursor
Deallocate PagingCursor
Select Products.* From Products
Join @tmpTable temp ON Products.ProductID = temp.PK
Order By Products.ProductID
SELECT @PageCount = COUNT(*) / @RowCount FROM Products // value is never returned
Set RowCount 0
GO
Any thoughts?
JR
I have a scenario whereby I will have 200+ clients putting messages onto a service broker queue. This message will go through a pipes and filters based messaging system, and ultimately the message will pop out the other end.
Here's the question: what is a good way of making sure the same client gets the response to the message he received. Is there anyway I can selectively receive messages from a queue, i.e., pass a correlation id in with the message, and then filter messages based on that id.
Or if someone knows a better way to do it altogether i'd really appreciate it.
Many thanks,
Paul
Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.
The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.
Here is what I'm trying to do:
;RECEIVE TOP(1) @MsgBody = CAST(message_body as XML)
FROM ProcessingLetters
WHERE conversation_handle = @Conversation_Handle
It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.
I've even tried this:
where cast(Conversation_Handle as varchar(100)) = cast(@Conversation_Handle as varchar(100))
Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.
So is the WHERE conversation_handle = @Conversation_Handle supposed to work?
Thanks.
I am right now looking at the new functionality in SSIS to cover some of the ways we can streamline proceses in the company. I know that SSIS has SendMail tasks, however I was wondering if there is any functionality for going out and receiving mail and parsing those messages in? Would this have to be done as custom code within SSIS and if so can anyone direct me to what would be needed to do so? Thanks.
View 4 Replies View Relatedhi friends, i want to store an image in DB. but most of my friends told that, to store an image in web server then store tat location in a DB and access it anywhere. this is for asp.net in C#-code behind. how to do this? i've a table with a text field. then .......? waiting for ur reply............ note: i need coding. not by using controls. pls...
View 1 Replies View RelatedIs there a way for me to set up CDC so that all the processing (SQL Agent, etc) happens on the machine receiving the data? I'd like to move as much of the processing as possible to the destination.
View 3 Replies View Related
I have just had SQL Server 2005 installed on my machine. I did have SQL Server 2000 on my machine previously and can still access parts of 2000.
In 2005 I do not have the option schema option under security for any of the databases; therefore, I tried creating the schema manually as you will see below.
In 2005 I execute the following
CREATE SCHEMA TEST
GO
The message returned is "COMMAND COMPLETED SUCCESSFULLY"
I, then, execute the following
CREATE TABLE TEST.SalesPeople
(
SalesPersonId INT,
SalesPersonName VARCHAR(50)
)
GO
The message returned is
Msg 2760, Level 16, State 1, Line 1
Specified owner name 'TEST' either does not exist or you do not have permission to use it.
Can you give me the correct syntax on how to create the schema manually? Do you know why this is not showing up under security for the databases?
Thanks in advance for your help.
Tara
If the service is defined with multiple contracts is there a way to receive a message with a specific contract?
View 5 Replies View RelatedI've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.
I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.
Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.
Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?
Thanks,
Chris
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.
And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.
Thanks for help on this,
Peter
Hi All,
I was using VB6 to access a MS SQL Server database. The code worked and works fine. I then decided to migrate the code to C#.Net 2005 using ADO 2.8 (not ADO.Net). Doing that yields with the same exact code the error message, "Current Recordset does not support updating".
I did a whole bunch of Google searches and didn't see anything useful. Mainly the advice from Microsoft and others is to make sure the mode on the connection string is set to "ReadWrite", as the default is "Read Only" and to make sure to set the lock type to either optimistic or pessimistic. Still others said that the code should set the CursorLocation property of the recordset.
I can safely say that I have been setting the mode to "Read/Write" since the start and have played around with the lock type, cursor location, and open method. Nothing works on C#, BUT VB6 is so totally happy with everything.
The provider works fine, as VB6 works fine, and the lock type is also fine, so therefore the built in suggestions do not apply.
My code is:
// Connection string template. Filled in properly in real code.
strConnect = "Server={0};Database={1};"
// Set the connection properties.
this.SQLConnection.ConnectionString = strConnect;
this.SQLConnection.Provider = "SQLOLEDB";
this.SQLConnection.Mode = adModeReadWrite;
// Open the connection.
this.SQLConnection.Open(strConnect, strUserName, strPassword, -1);
=================
// Create the ADO objects needed.
dbRSAdd = new Recordset();
// Open the recordset.
dbRSAdd.Open(strTable, dbCatalog.ActiveConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, (int)CommandTypeEnum.adCmdTable);
// Cycle through each record to add.
dbRS.MoveFirst();
for (lRecord = 0; lRecord < dbRS.RecordCount; lRecord++)
{
// Add a new record.
dbRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
...
}
// NOTE: The code crashes with the call to 'AddNew'.
Any advice?
I have a table that records sales leads and I need to calculate the total number of leads per user and the total amount owed, then then show only the users who have recieved 3 leads. The UserID is an integer.
Here is what i have:
SELECT * FROM
(
SELECT DISTINCT UserID,
(SELECT COUNT(*)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalLeads,
(SELECT SUM(Price)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalAmount
FROM LeadMatches AS L
)
WHERE TotalLeads = 3
This should work but it produced the following result:
UserID | TotalLeads | TotalAmount
---------------------------------------------------------------
<Unsupported Data Type> | 3 | 75.00
<Unsupported Data Type> | 2 | 50.00
I have no idea why the result produceing <Unsupported Data Type> for the UserID and have been unable to find a way around as of yet. Any help would be appreciated.
I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.
The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.
I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.
Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.
Thanks for any help!
John
While attempting to configure DTC on a Windows xp Pro, SP 2 machine I receive the error message [DTC was installed by the SQL Server. Please reinstall!] Where do I find this installation file to complete the requested task -- any help would be awesome -- I have tried down loading hotfixes, and security updates that mention fixing problems with DTC -- but alas they did not fix my error ;-)
I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )
When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.
But when I run the same app against the local machine SQL Server, I receive the message "Options".
Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl]
@MtlTblMaterialType READONLY
AS
BEGIN
Set NOCOUNT ON;
[code]....