如何在vb6中使用SQL Server 2005存储过程进行备份 [英] How to take backup using SQL server 2005 stored procedure in vb6
问题描述
请帮助我
如何使用 sql server 2005 备份存储过程 in vb6
我尝试过:
这是我的sql存储过程
设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
go
创建程序[dbo]。[GetBackup]
@SQLSTATEMENT VARCHAR (2000)
As
设置Nocount;
SET @ SQLSTATEMENT ='f:\Print_Branch \Print_Branch'+ CONVERT(nvarchar(30),Getdate(),112)+'。bak'
备份数据库Print_Branch到磁盘= @SQLSTATEMENT WITH NOFORMAT,INIT,NAME = N'Print_Branch-完整数据库备份',SKIP,NOREWIND,NOUNLOAD,STATS = 10
由于之前的解决方案是在VB.NET中你在你的帖子中提到了三次VB6 ,我猜测你可以用一个链接来帮助你写一个......呃... VB6解决方案。
这篇CP文章应该有帮助如何在VB6中使用存储过程 [ ^ ]
以下内容可以为您提供帮助。
私人 cnstr 作为 字符串 = 提供商= SQLOLEDB.1;密码= MYDATABASE; Persist Security Info = True;用户ID = xxxx;初始目录= xxxx;数据源= 192.168.1。##
Dim cn 作为 新 OleDb.OleDbConnection(cnstr)
Sub Main()
尝试
OpenConnection()
Dim sql As 新 OleDb.OleDbCommand( SELECT'['+ SPECIFIC_SCHEMA +']。['+ SPECIFIC_NAME +']'AS StoredProcedure,SPECIFIC_NAME as SPName FROM INFORMATION_SCHEMA.ROUTINES ,cn)
Dim Da As New OleDb.OleDbDataAdapter(sql)
Dim StoredProcedures 作为 新 DataTable( StoredProcedures)
Dim SP As DataRow
Dim OutputDirectory As String = CurDir() &安培; \
Dim OutputFile As String
Da.Fill(StoredProcedures)
对于 每个 SP 在 StoredProcedures.Rows
OutputFile = OutputDirectory& SanitizeFileName(SP( SPName))& 。sql
呼叫 SaveSPToFile(OutputFile,SP( StoredProcedure),SP( SPName))
下一步
CloseConnection()
Catch ex As 异常
CloseConnection()
Debug.Print(ex.ToString)
结束 尝试
结束 Sub
私有 Sub SaveSPToFile( ByVal FilePath As < span class =code-keyword>字符串 , ByVal StoredProcedure As String , ByVal StoredProcedureName As String )
< span class =code-keyword>尝试
如果 System.IO.File.Exists(FilePath)然后
Kill(FilePath)
结束 如果
System.IO.File.Create(FilePath)
Dim sql As OleDb.OleDbCommand = cn.CreateCommand()
sql.CommandType = CommandType.StoredProcedure
sql.CommandText = sp_HelpText'& StoredProcedure& '
Dim sqlrdr 作为 OleDb.OleDbDataReader = sql.ExecuteReader()' 这是错误的地方.....
Dim wrtr As 新 System.IO.StreamWriter(FilePath)
执行 while sqlrdr.Read
wrtr.WriteLine(sqlrdr.GetString( 0 ))
循环
sqlrdr.Close()
wrtr.Dispose()
Catch ex < span class =code-keyword> As Exception
CloseConnection()
Debug.Print(ex.ToString)
结束 尝试
结束 子跨度>
Help me please
How to take backup using sql server 2005 stored procedure in vb6
What I have tried:
this is my sql stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[GetBackup]
@SQLSTATEMENT VARCHAR (2000)
As
Set Nocount on;
SET @SQLSTATEMENT='f:\Print_Branch\Print_Branch' + CONVERT (nvarchar (30), Getdate(),112)+ '.bak'
Backup database Print_Branch to disk= @SQLSTATEMENT WITH NOFORMAT, INIT, NAME = N'Print_Branch-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
As the previous solution is in VB.NET and you have mentioned VB6 three times in your post, I'm guessing you could do with a link that helps you write a ... er ... VB6 solution.
This CP article should help How to Use Stored Procedures in VB6[^]
The following may help you.
Private cnstr As String = "Provider=SQLOLEDB.1;Password=MyDataBase; Persist Security Info=True;User ID=xxxx;Initial Catalog=xxxx;Data Source=192.168.1.##" Dim cn As New OleDb.OleDbConnection(cnstr) Sub Main() Try OpenConnection() Dim sql As New OleDb.OleDbCommand("SELECT '['+ SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']' AS StoredProcedure, SPECIFIC_NAME as SPName FROM INFORMATION_SCHEMA.ROUTINES", cn) Dim Da As New OleDb.OleDbDataAdapter(sql) Dim StoredProcedures As New DataTable("StoredProcedures") Dim SP As DataRow Dim OutputDirectory As String = CurDir() & "\" Dim OutputFile As String Da.Fill(StoredProcedures) For Each SP In StoredProcedures.Rows OutputFile = OutputDirectory & SanitizeFileName(SP("SPName")) & ".sql" Call SaveSPToFile(OutputFile, SP("StoredProcedure"), SP("SPName")) Next CloseConnection() Catch ex As Exception CloseConnection() Debug.Print(ex.ToString) End Try End Sub Private Sub SaveSPToFile(ByVal FilePath As String, ByVal StoredProcedure As String, ByVal StoredProcedureName As String) Try If System.IO.File.Exists(FilePath) Then Kill(FilePath) End If System.IO.File.Create(FilePath) Dim sql As OleDb.OleDbCommand = cn.CreateCommand() sql.CommandType = CommandType.StoredProcedure sql.CommandText = "sp_HelpText '" & StoredProcedure & "'" Dim sqlrdr As OleDb.OleDbDataReader = sql.ExecuteReader() 'This is where it errors..... Dim wrtr As New System.IO.StreamWriter(FilePath) Do While sqlrdr.Read wrtr.WriteLine(sqlrdr.GetString(0)) Loop sqlrdr.Close() wrtr.Dispose() Catch ex As Exception CloseConnection() Debug.Print(ex.ToString) End Try End Sub
这篇关于如何在vb6中使用SQL Server 2005存储过程进行备份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!