|
There are SQL commands to BACKUP[^] and RESTORE[^] databases. You can find them in MSDN quite easily.
Next, all you need to do is create a string with the appropriate command in it and use SqlCommand. Like this:
SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand(myCommandString, myConnection);
myCommand.CommandTimeout = 3600;
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
It is as easy as that. You need to fill in the connection string and the command string with the appropriate values. If your backup or restore is likely to take more than one hour you might want to change the CommandTimeout as well.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thats a good solution. But if you want you can use SQL DMO to achieve this.
Just add a reference to sqldmo.dll and make method calls on it to backup and restore.
Public Function DB_Backup(ByVal nServer_Name As String, _
ByVal nDB_Name As String, _
ByVal nDB_Login As String, ByVal nDB_Password As String, _
ByVal nBack_Dev As String, ByVal nBack_Set As String, _
ByVal nBack_Desc As String) As Boolean
' nServer_Name = SQL server name
' nDB_Name = Database name
' nDB_Login = Login name
' nDB_Password = Password
' nBack_Dev =Backup device name
' nBack_Set = Backup set name
' nBack_Desc = Backup discription
Dim oBackup As SQLDMO.Backup
On Error GoTo ErrorHandler
Set oBackup = CreateObject("SQLDMO.Backup")
If Connect_SQLDB(nServer_Name, nDB_Login, nDB_Password) Then
oBackup.Devices = "[" & nBack_Dev & "]"
oBackup.Database = nDB_Name
oBackup.BackupSetName = nBack_Set
oBackup.BackupSetDescription = nBack_Desc
oBackup.SQLBackup oSQLServer
oSQLServer.DisConnect
DB_Backup = True
End If
Exit Function
ErrorHandler:
DB_Backup = False
End Function
Private Function Connect_SQLDB(ByVal nServer_Name As String, _
ByVal nDB_Login As String, _
ByVal nDB_Password As String) As Boolean
' nServer_Name = SQL server name
' nDB_Login = Login name
' nDB_Password = Password
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
On Error GoTo ErrorHandler
Connect_SQLDB = False
oSQLServer.Connect nServer_Name, nDB_Login, nDB_Password
Connect_SQLDB = True
Exit Function
ErrorHandler:
oSQLServer.DisConnect
Connect_SQLDB = False
End Function
Also check this
http://www.codersource.net/csharp_sqldmo_sqlserver.aspx
|
|
|
|
|
Also
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\unzip_sqldmo.exe
unzip archive and go to
E:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\vb\BackupDevice
or
E:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\vb\VerifyBackup
VB6,C#
|
|
|
|
|
My Environment: MySQL Database, ADO, MSVC6, MS DataGrid.
Datagrid is being used to display data from three tables, joined by their primary keys.
Problem: User wants to be able to edit the data in the grid itself. When he does, and tried to move to another record, the grid displays the message "Insufficient Key Column Information for updating or refreshing". User is not willing to have me bring up the row data in a dialog to edit.
Even Bigger Problem: I need to keep this user happy!
Schema:
Table_1: ID (AutoNumber, PK), Field_A (Varchar), Field_B (Varchar)
Table_2: ID (Autonumber, PK), Field_C (Varchar), Field_D (Integer)
Table_3: ID (Autonumber, PK), Field_E (Double)
SQL for Grid: SELECT Table_1.ID, Table_1.Field_A, Table_2.Field_C, Table_3.Field_E FROM (Table_1 INNER JOIN Table_2 ON Table_1.ID=Table_2.ID) INNER JOIN Table_3 ON Table_1.ID=Table_3.ID ORDER BY Table_1.Field_A
MS KB: There is no duplication of field names except ID. ID is always the primary key. The ID field is not being updated. Field_A is.
ADO: I have tried using both adUseClient and adUseServer as the cursor location, for both the connection object and the recordset object. Has not made any difference.
Can I set this up so that enough key column information is available for the update? Is this even possible?
|
|
|
|
|
From MSDN:
When updating an ActiveX Data Objects (ADO) recordset, an error occurs if the recordset, based on a SHAPE command, joins two tables where the primary key of one table has the same name as a column name of the other table. The following error appears:
Run-time error '-2147467259 (80004005)':
Insufficient key column information for updating or refreshing.
You can try renaming the primary keys.
|
|
|
|
|
Actually, I had tried that already, it did not work. I am beginning to think this is something so deep inside Microsoft Cursor engine, that there may not be an easy work-around. The SQL I used was not a SHAPE sql anyway.
My solution so far has been to switch to a disconnected recordset, and intercept update notifications from the grid. Then I have to write some code for a Lazy Writer to actually update the database with the changes from the grid.
|
|
|
|
|
I have a Access database that I wrote a VB.net program for. I want to set it up so I can seach it using the indexof method. I gotten as far as the listbox,inputbox and the button, but I'm pretty much running around in circles with the code. Anyone know how to do this?
BINARY
|
|
|
|
|
You don't search a database with the IndexOf() method, that is for arrays.
I suspect that what you are tying to do is to use the controls to select an item so that you can then select the appropriate data from the database. Is this the case? You need to describe what you are doing more clearly so we can help you better.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I want to be able to type a string into an input box or text box that would match itself with a field in the database then show me the results in a listbox. Then by clicking on the list box pull up the entire record.
BINARY
|
|
|
|
|
I am working on an asp script that pulls data from ms sql. In one part, it pulls the days of the week that an event occurs on. If I do something like this:
select dayofweek from schedule where id=5
I get:
---------
dayofweek
---------
M
---------
T
---------
W
---------
What I need is a sql statement that does something like this:
---------
dayofweek
---------
M,T,W
---------
Is there a way to select them into one record like this even though they are stored in multiple rows in the table?
Thanks,
Jim
|
|
|
|
|
Try this T SQL script (you ca make a SQL Server function)
--parameter @id
DECLARE @id INT
SET @id = 5
DECLARE @ssum NVARCHAR(255)
DECLARE @dayofweek NVARCHAR(25)
SET @ssum = ''
DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id
OPEN crs
FETCH NEXT FROM crs INTO @dayofweek
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @ssum = @ssum + ',' + @dayofweek
FETCH NEXT FROM crs INTO @dayofweek
END
CLOSE crs
DEALLOCATE crs
IF( LEN(@ssum) != 0 )
SET @ssum = SUBSTRING(@ssum,2,255)
PRINT @ssum
|
|
|
|
|
|
I need to do exactly the same thing, but my result needs to print in a textbox in a SQL Server 2000 Reporting Services Report. I created a function in the Custom Code area for the report, but I get the message: "Statement is not valid inside a method." Can you tell me how to make your code work in Reporting Services?
|
|
|
|
|
I want to say that
you must create a "user defined function" (T SQL function)
like this
CREATE FUNCTION SSum (@id INT)
RETURNS NVARCHAR(255) AS <br />
BEGIN <br />
<br />
DECLARE @ssum NVARCHAR(255)<br />
DECLARE @dayofweek NVARCHAR(25)<br />
SET @ssum = ''<br />
<br />
DECLARE crs CURSOR FOR SELECT DAYOFWEEK FROM schedule WHERE ID = @id<br />
OPEN crs<br />
<br />
FETCH NEXT FROM crs INTO @dayofweek<br />
WHILE( @@FETCH_STATUS = 0 )<br />
BEGIN<br />
SET @ssum = @ssum + ',' + @dayofweek<br />
FETCH NEXT FROM crs INTO @dayofweek<br />
END<br />
<br />
CLOSE crs<br />
DEALLOCATE crs<br />
<br />
IF( LEN(@ssum) != 0 )<br />
SET @ssum = SUBSTRING(@ssum,2,255)<br />
<br />
RETURN @ssum<br />
<br />
END
After, built your report on a SELECT query using SSum function like this:
<br />
SELECT DISTINCT ID , dbo.SSum(id) AS Days<br />
FROM schedule<br />
VB6,C#
-- modified at 17:46 Wednesday 31st August, 2005
|
|
|
|
|
I was able to put your code into a stored procedure, then call the string from there. It worked!! The user defined function would probably work as well. Your code has saved me hours of work!
|
|
|
|
|
I have found the xp_startmail, xp_sendmail, and the xp_stopmail. I have gotten it to run and send mail to myself. My problem is, is there anyway to get it to send from my current database. I have to be in the Master to get them to work. If I try to work out of my own DB than it says it cannot find the stored procedures. I want to know if there is anyway to map to my DB that I am using, or copy the mail stored procedures into my DB.
Thank You
Scott Moore
|
|
|
|
|
I solved my own problem... In the argument, you have to use "exec DBname.dbo.SP name". It is working for me!
|
|
|
|
|
Hello All!
I have some problem when i try Update record in opened Recordset in VB Script. Windows 2000Pro SP4,MDAC2.7SP1,VBS5.6,SQL2000SP3.
Table:
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) NULL ,
) ON [PRIMARY]
Table have an auto-incriment ID and 1 varchar field.
After execute Update method record must have filled ID field.
But in following example this did't appear:
....
Set Conn2 = CreateObject ("ADODB.Connection")<br />
Conn2.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=127.0.0.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=W2000;Use Encryption for Data=False;Tag with column collation when possible=False", "","" <br />
Set rs2 = CreateObject ("ADODB.Recordset")<br />
rs2.Open "Table1", Conn2,adOpenKeyset, 3, adCmdTable<br />
Rs2.AddNew<br />
Rs2("Title") = "Some text"<br />
Rs2.Update<br />
WScript.StdOut.WriteLine "Added New Record: " &Rs2("ID")& " " &Rs2("Title") ' ID = NULL!!!....
Somebody can me explain why this did not work in VBS and CORRECTLY executed and UPDATED in MCVC 6.0 SP4.
Thank for all who can help me understand this situation.
IFriendly
|
|
|
|
|
SELECT * INTO T2 FROM T1
fills T2 with T1 records.
How can I use an stored procedure that returns records , instead T1
?
thanks.
|
|
|
|
|
|
I want to create the table as a temporary table in a stored procedure.
What should i do ?
|
|
|
|
|
That was not the question you orginally asked. You asked how to replace T1 with a stored procedure.
To create a temporary table in a stored procedure do something like this
CREATE TABLE #MyTempTable(myKey int, someValue varchar(12))
INSERT INTO #MyTempTable ....
At the end of the stored procedure, SQL Server will drop the temp table for you, however you must remember to prefix the table name with '#' which marks it as temporary.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
use a UDF (user defined funcation) which can act just like a table. This is the MS recommended method.
select * from my_udf
|
|
|
|
|
I have a stored procedure to filtering data as :
CREATE PROCEDURE sp_getFilteredAdvertisement
@advertisementTypeID varchar(20),
@visible varchar(2),
@minDate varchar(40),
@maxDate varchar(40)
AS
declare @SQL as nvarchar(1000)
SET @SQL = 'SELECT [Advertisement].[advertisementID] , [Advertisement].[owner] , [Advertisement].[fileName] , [Advertisement].[link] , [Advertisement].[altText] , ABS([Advertisement].[visible]) AS visible , [Advertisement].[viewCounter] , [Advertisement].[clickCounter] , [AdvertisementType].[name] , [Advertisement].[startDate] FROM [Advertisement] INNER JOIN [AdvertisementType] ON [Advertisement].[AdvertisementTypeID]=[AdvertisementType].[AdvertisementTypeID] WHERE 1=1 '
IF ( LEN( @advertisementTypeID ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [Advertisement].[advertisementTypeID] = ' + @advertisementTypeID
END
IF ( LEN( @visible ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [visible] = ' + @visible
END
IF ( LEN( @minDate ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [startDate] >= ''' + @minDate + ''''
END
IF ( LEN( CAST( @maxDate AS VARCHAR(40) ) ) > 0 )
BEGIN
SET @SQL = @SQL + 'AND [startDate] <= ''' + @maxDate + ''''
END
exec (@SQL)
RETURN
I have a stored procedure to paging found from msdn :
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
AdvID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (AdvID)
SELECT
advertisementID
FROM
Advertisement
ORDER BY
advertisementID DESC
-- Return total count
SELECT COUNT(advertisementID) FROM Advertisement
----
select into
-- Return paged results
SELECT
O.*
FROM
Advertisement O,
#PageIndex PageIndex
WHERE
O.advertisementID = PageIndex.AdvID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
How i can put paging procedure in filtering procedure ?
|
|
|
|
|
Here is what you can do,
(1) Create this procedure
CREATE PROCEDURE GetDataByPage(
@SqlStr varchar(8000),
@PageSize int,
@CurrentPage int)
AS
DECLARE @FirstRec int, @LastRec int,@dt varchar(10)
BEGIN
-- Initialize variables.
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
SELECT @dt= substring(convert(varchar,rand()),3,10)
-- Create a temp table to hold the current page of data
SELECT @SqlStr = stuff(@SqlStr,charindex(' FROM ',upper(@SqlStr)),6,' INTO tempdb..Paging'+@dt+' FROM ')
EXECUTE (@SqlStr)
-- Add an ID column to count the records
SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD RID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
--Return the total number of records available as an output parameter
SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
EXECUTE (@SqlStr)
--Select one page of data based on the record numbers above
SELECT @SqlStr = 'SELECT * FROM tempdb..Paging'+@dt+' WHERE RID > '+convert(varchar,@FirstRec)+' and RID < '+convert(varchar,@LastRec)
EXECUTE (@SqlStr)
-- Drop the temporary table
SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
EXECUTE (@SqlStr)
END
(2) In sp_getFilteredAdvertisement add two more parameters @pageSize, @pageNo
instead of a call to exec(@sql) use
exec GetDataByPage @sql , @pageSize, @pageNo
Hope this helps.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
