|
Books Online[^] seems to indicate that you can grant rights to create tables and views separately. IIRC, you can only Drop an object if you're a member of the db_owner or db_ddladmin roles for the database, or if you own the object. These roles are fixed: you cannot alter the privileges of these roles and you cannot set permissions for user-defined roles.
You can use
GRANT CREATE TABLE TO public to allow all members of the public role to create tables.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mike Dimmick wrote:
you can only Drop an object if you're a member of the db_owner or db_ddladmin roles for the database
That's what I was afraid of. Thanks, anyway.
"if you vote me down, I shall become more powerful than you can possibly imagine" - Michael P. Butler.
Support Bone
It's a weird Life
|
|
|
|
|
Hi,
im not so good with SQL so pls bear with me if this is too easy...  anyways i'd like to display information from two tables "Diagnosis" and "DiagPhar" in a datagrid...my question is how do i code the SQL command text for this...i tried using ways i got from the net but its not working. also how do i setup the datagrid to display the information from two tables.
CODER
|
|
|
|
|
Hi
Try : Select * from Diagnosis,Diagnosis
this brings the two table in one result set and you can show the result set in one grid
|
|
|
|
|
Hi MasudM
thx for the hint. anyways how can i display the data using ONE datagrid?..i would appreciate it if u showed me how...
thx again!
CODER
|
|
|
|
|
Hi
As Mike Said below a full description if need join ! I do not exactly
know what are you after if these two tables have relation you probably need kind of join ! but I can give another hint if you don't know it before !
when you use SqlDataAdapter (or Command) it only turns SqlData to some format that visul Studio can show . then you fill the DataSet
SqlDataAdapter.Fill(DataSet,"TableName"); it brings the sql Data to a format Like Sql But Viewable with visual Sudio Tools !
so you can add two SqlTable to one Table in DataSet then can show that table with a grid .
But Mike's message is very good for joinning Two tables.it works at the SqlServer Level .
|
|
|
|
|
Sounds like you need to JOIN your tables together.
If you want only rows that appear in both tables, use an INNER JOIN. If you want all rows that appear on the left-hand side of the JOIN, with any data appearing on the right-hand side if available, use a LEFT JOIN. Vice-versa, use a RIGHT JOIN. If you want all rows from both tables, regardless of whether corresponding data appears on the other side, use a FULL OUTER JOIN. Finally, if you don't want to use a join condition (the ON clause) and want to output every row of your left-hand table joined to every row of your right-hand table, use a CROSS JOIN.
Example: you have tables OrderLine and Product. An OrderLine maps between an order and the products on that order, and includes the quantity ordered. You want to print an invoice, listing the product details and total prices. You might write something like:
SELECT
OrderLine.Sequence,
OrderLine.Quantity,
Product.ShortDesc,
Product.Price AS ItemPrice,
OrderLine.Quantity * Product.Price AS LinePrice
FROM
OrderLine INNER JOIN Product
ON OrderLine.ProductID = Product.ProductIDIf, somehow, we had OrderLine rows with ProductID not in the Product table, and we wanted to show those anyway, we would use a LEFT JOIN in the above query. Any fields we refer to from Product, for OrderLine rows with no matching ProductID, will be NULL (e.g. ShortDesc and Price will be NULL for the above query). The INNER JOIN omits these rows.
Note that if there is more than one row on each side that matches the join condition, all those rows will be output, joined to each other. For the sake of argument, let's say that we have two OrderLine rows which refer to ProductID 10, and there are two rows in Product where ProductID is 10 (you would try to avoid this in an ordering system!). Let's give them descriptions ProductA and ProductB. You might get the result
Sequence Quantity ShortDesc ItemPrice LinePrice
-----------------------------------------------
1 2 ProductA 5 10
1 2 ProductB 6 12
2 1 ProductA 5 5
2 1 ProductB 6 6
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thx Mike n Masud!
with your help, i was able to do what i wanted to. thx again!
CODER
|
|
|
|
|
Is it possible to have a DataView that do not contain all the columns of the corresponding DataTable? If so, how do I do it?
--------
"I say no to drugs, but they don't listen."
- Marilyn Manson
|
|
|
|
|
Hi ...
I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.
Any comments are appreciated,
Thanks,
Chris
<br />
<br />
void CTestApp::TestDatabaseUpdateBatch1a(void) <br />
{<br />
int nDataCount = 0; <br />
long nIndex = 0; <br />
long nIndex2 = 0; <br />
<br />
CString csMessage; <br />
CString csErrorMessage;<br />
CString csTemp; <br />
CString csSQL; <br />
<br />
BOOL bIsOpen; <br />
BOOL bIsEmpty; <br />
<br />
long nCount = 0; <br />
int nTemp = 0; <br />
int nLimit = 0; <br />
<br />
int nTempInt = 0; <br />
long nTempLong = 0; <br />
double nTempDouble = 0; <br />
<br />
HRESULT hResult; <br />
<br />
SYSTEMTIME st;<br />
<br />
<br />
int i = 0; <br />
<br />
string strTemp; <br />
<br />
_variant_t sval;<br />
<br />
_variant_t vNull;<br />
vNull.vt = VT_ERROR;<br />
vNull.scode = DISP_E_PARAMNOTFOUND;<br />
<br />
COleSafeArray colesaFieldList;<br />
COleSafeArray colesaDataList;<br />
<br />
vector<COleSafeArray> *pvecDataList;<br />
<br />
pvecDataList = new vector<COleSafeArray>; <br />
<br />
<br />
COleDateTime oledtCurrentDate = COleDateTime::GetCurrentTime();<br />
<br />
COleVariant vCurrentDateTime; <br />
<br />
CMxTextParse *pMxTextParse = NULL; <br />
<br />
CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd(); <br />
CFrameWnd* pChild = pMainFrame->GetActiveFrame();<br />
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView(); <br />
<br />
pView->WriteLog("Start TestDatabaseUpdateBatch1a."); <br />
pView->WriteLog("Load table using AddNew() and UpdateBatch()."); <br />
<br />
<br />
_ConnectionPtr pConnection = NULL;<br />
_RecordsetPtr pRecordset = NULL;<br />
<br />
try<br />
{<br />
pConnection.CreateInstance(__uuidof(Connection));<br />
<br />
bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';"<br />
"Initial Catalog='AlphaNumericData';"<br />
"User Id=cmacgowan;Password=cmacgowan");<br />
<br />
pConnection->Open(bstrConnect,"","",adConnectUnspecified);<br />
<br />
pRecordset.CreateInstance(__uuidof(Recordset));<br />
<br />
<br />
csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL";<br />
<br />
<br />
pRecordset->PutRefActiveConnection(pConnection);<br />
pRecordset->CursorLocation = adUseClient;<br />
<br />
<br />
pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);<br />
<br />
if(pRecordset->GetState() != adStateClosed)<br />
{<br />
<br />
if((pRecordset->BOF) && (pRecordset->GetadoEOF()))<br />
{<br />
bIsEmpty = false;<br />
}<br />
<br />
<br />
if(pRecordset->GetadoEOF())<br />
{<br />
bIsOpen = false;<br />
}<br />
else<br />
{ <br />
pRecordset->PutRefActiveConnection(NULL);<br />
}<br />
}<br />
<br />
<br />
<br />
pRecordset->PutRefActiveConnection(NULL);<br />
<br />
nCount = 1; <br />
<br />
while(nCount > 0) <br />
{ <br />
nCount--; <br />
<br />
nDataCount = 10; <br />
<br />
if (nDataCount >= 0) <br />
{ <br />
<br />
COleSafeArray warningList;<br />
<br />
VARIANT vFieldList[25]; <br />
VARIANT vValueList[25]; <br />
<br />
int nFieldIndex = 0; <br />
int nValueIndex = 0; <br />
<br />
<br />
vFieldList[nFieldIndex].vt = VT_BSTR;<br />
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Name");<br />
nFieldIndex++;<br />
<br />
vFieldList[nFieldIndex].vt = VT_BSTR;<br />
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Section");<br />
nFieldIndex++;<br />
<br />
vFieldList[nFieldIndex].vt = VT_BSTR;<br />
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Code");<br />
nFieldIndex++;<br />
<br />
vFieldList[nFieldIndex].vt = VT_BSTR;<br />
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Latitude");<br />
nFieldIndex++;<br />
<br />
vFieldList[nFieldIndex].vt = VT_BSTR;<br />
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Longitude");<br />
nFieldIndex++;<br />
<br />
<br />
pView->WriteLog("Set data using AddNew() ..."); <br />
<br />
COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();<br />
<br />
COleVariant vCurrentDateTime2(oledtCurrentDate2);<br />
<br />
memset(&st, 0, sizeof(SYSTEMTIME));<br />
st.wYear = 2000;<br />
st.wMonth = 1;<br />
st.wDay = 1;<br />
st.wHour = 12;<br />
<br />
for(i = 0; i < 10; i++)<br />
{<br />
<br />
nValueIndex = 0; <br />
vValueList[nValueIndex].vt = VT_BSTR;<br />
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE");<br />
nValueIndex++;<br />
<br />
vValueList[nValueIndex].vt = VT_BSTR;<br />
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");<br />
nValueIndex++;<br />
<br />
vValueList[nValueIndex].vt = VT_I4;<br />
vValueList[nValueIndex].dblVal = 100 + nFieldIndex; <br />
nValueIndex++;<br />
<br />
vValueList[nValueIndex].vt = VT_R8;<br />
vValueList[nValueIndex].dblVal = 11.11 + nFieldIndex; <br />
nValueIndex++;<br />
<br />
vValueList[nValueIndex].vt = VT_R8;<br />
vValueList[nValueIndex].dblVal = 22.22 + nFieldIndex; <br />
nValueIndex++;<br />
<br />
pRecordset->AddNew(vFieldList, vValueList);<br />
}<br />
<br />
<br />
<br />
pView->WriteLog("Call UpdateBatch()."); <br />
<br />
pRecordset->PutRefActiveConnection(pConnection);<br />
<br />
pRecordset->UpdateBatch(adAffectAll);<br />
<br />
pRecordset->Close();<br />
pConnection->Close();<br />
<br />
} <br />
}<br />
}<br />
catch(_com_error *e)<br />
{<br />
CString Error = e->ErrorMessage();<br />
AfxMessageBox(e->ErrorMessage());<br />
pView->WriteLog("Error processing TestDatabase()."); <br />
}<br />
catch(...)<br />
{<br />
csMessage = "Undefined exception handled. Error message details \n\n"; <br />
<br />
hResult = GetAdoErrorMessage(m_pConnection, <br />
&csErrorMessage);<br />
<br />
csMessage += csErrorMessage; <br />
csMessage += "\nmethod: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()"; <br />
<br />
AfxMessageBox(csMessage);<br />
<br />
}<br />
<br />
csTemp.Format("Last Row %03d DIcastId = %s ", nIndex, strTemp.c_str()); <br />
pView->WriteLog(csTemp); <br />
<br />
pView->WriteLog("End TestDatabaseUpdateBatch1."); <br />
<br />
}<br />
<br />
<br />
<br />
<br />
<br />
Thanks,
Chris
|
|
|
|
|
Hello to all of you, good code-time for all. 
Partners, I'm having a problem and would like to ask for help. Is the following:
I would like to create an App with some reports built-in (no external files, ok?); now, when I create a CrystalReport, assign (in my App) a viewer and later try to execute App, it requests for login, bla, bla, bla (all those things you know) but my database doesn't have any of these, so, what's the matter here? . What is more, have also created a DB with pass and all of these but when filled out what is needed it always says "Logon failed". Suggestions?
Thanx in advance. May schwartz be with U.
Note: If you consider this question should be asked somewhere else, let me know, I'm a newbie here, ok?
|
|
|
|
|
What is the datasource of your report if its dataset, then i think there's no problem.
Or.. Try this one
*************************
private void applyLoginInfo(ReportDocument report){
TableLogOnInfo loginfo = new TableLogOnInfo();
foreach (Table table in report.Database.Tables){
loginfo = table.LogOnInfo;
loginfo.ConnectionInfo.ServerName = database.Server; //Pass the server name
loginfo.ConnectionInfo.DatabaseName = database.Database; //pass the db name
loginfo.ConnectionInfo.UserID = database.UserID; //user id
loginfo.ConnectionInfo.Password = database.Password; //password
table.ApplyLogOnInfo(loginfo);
}
}
I hope this will help you
|
|
|
|
|
I need suggestions.
I am creating a new database for a referral system. I have my main table "tblProviders" along with other 15-20 child tables such as tblTypeFacility, tblLanguges, tblTypePrograms,tblEnvironment...etc.
So, the user will be able to select one or more type of facilities, languagess, etc for each Provider. How can I save this information on the database.
Do I need to normalize every child table.
tblProvider -> tblProviderTypeFacility <-> tblTypeFacility
How this is going to perform and how complicated will be when I create a search for one or more options (type facilities, languages, etc)using asp.
Some one mentioned to do my child table as a lookup table...I am not sure what is a lookup table.
Any suggestion will be great!!
|
|
|
|
|
If you normalise as you suggested then the following SQL-Select statement will find all of the providers who can speak English and French:
select P.ProviderId, P.ProviderName<br />
from Provider P<br />
where P.ProviderId in (select ProviderId from ProviderLangauge where LanguageCode = 'UK')<br />
and P.ProviderId in (select ProviderId from ProviderLanguage where LangauageCode = 'FR')
The performance would depend upon the database that you are using and how many records are in each table (make sure that your development database contains a similar number of records to what you would expect in your live database).
A composite unique index on "ProviderLanguage(LanguageCode, ProviderId)" would be useful for resolving the above query. I would expect a (possibly clustered) primary key on "ProviderLanguage(ProviderId, LanguageCode)" to help your provider-maintenance web page.
I would also strongly recommend that you get yourself a good book on SQL and database design (if you don't already have one). Good knowledge of these two areas tend to lead to much simpler ASP code and much faster performance.
Hope this helps.
Andy
|
|
|
|
|
Hi,
I kind of have a strange situation here. One of our customer needs a realtime notification when a particular action is taken on their data and want an xml file. So, I created a trigger and an extended stored procedure. My idea is to create the xml in the trigger by concatinating row values into xml using an NTEXT object.
So, my question is, can I create an NTEXT object, concatenate string data and then, pass that value to my extended stored procedure? Then, have the extended stored procedure create a text file somewhere on the hard drive and write out that NTEXT value to file?
I know this is very strange but I can't really figure out another, more efficient way of doing it.
Thanks in advance for any ideas,
Craig
|
|
|
|
|
In the Enterprise version of .NET, you can edit SQL scripts in the GUI and then run it against a database just as if you were working in Query Analyser. I have the Professional version, where thise feature is turned off (you can run simple queries in that version, but not complex scripts).
Does anyone know if there is a plugin or something similar, even from Microsoft, that will allow me to do this without purchasing the entire Enterprise version of .NET?
~Steve
|
|
|
|
|
When I try to create a database in VS.NET thru the Server Explorer window I get this error:
ADO Error:
'Could not obtain exclusive lock on database 'MODEL'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.'
I'm running VS.NET Professional with MSDE 2000, so this should work, I think.
Anybody else here seen this error? Is there anything I can do about it? I'm not seeing anything in the log files concering what's causing this...
Get that finger out of your ear! You don't know where that finger's been!
|
|
|
|
|
Hmmmm... "MODEL" is the database that is used as a template for all new databases, it shouldn't ordinarily be written to. The exclusive lock is probably requested to prevent others from updating MODEL while a new database is in the process of being created.
My suggestion is to check to see if any processes are accessing MODEL and stop them.
EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
|
|
|
|
|
Your suggestion worked - for some dumb reason I tried to do this while connected to the databases...all of them.  But when I tried to create the new db while only being connected to the instance, it worked. Yeah, I'm new at this...
Thanks for your help
Frederick S Jones
"Get that finger out of your ear! You don't know where that finger's been!"
|
|
|
|
|
I want to create a script the database that I will be deploying with my application. The problem is that when I generate a SQL script it only scripts the database schema/structure but not the data. There is some data in the database that I wish to include with my script.
How can I script the database so that it includes the entire database including the data?
Thanks.
|
|
|
|
|
Some suggestions:
You can write the INSERT statements in a script. It can be time consuming if there is a lot of data.
You can create an XML file that contains the data and your deployment package can load that into the database.
You can skip the scripting of the database schema and have the deployment install pre-filled database files and just script the connection of these files to the Master database (I'm assuming your using SQL Server - other databases may differ)
EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
|
|
|
|
|
Does anyone know what's the equivalent of SQL Server Notification Services in Oracle?
I'm primarily looking to maintain the consistency of the data caches obtained from reading the database to be sync'ed with any changes made to the database. i.e, need a way for apps to register interest & be notified when a row changes for e.g. Are there any classes/add-ons in the .NET framework that works with the Oracle database? Any pointers would be appreciated.
Chen Venkataraman
|
|
|
|
|
Hi,
I want to save Armenian UniCode Characters in my tables any body knows which collation should I choose for my DataBase ?!!
Thanks in advance .
|
|
|
|
|
Hi, everyone. I need help to SQL 2000 server in relation to obtain a backup for any database. I have some trouble using the backup of SQL 2000 server because the SQL agent must be stop first before start the backup. How I can automaticly start a backup without user interaction with the server?
Johnny Lizardo
|
|
|
|
|
BEGIN
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'MyDatabase backup1')
IF (@JobID IS NOT NULL) BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''MyDatabase backup1'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'MyDatabase backup1'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'MyDatabase backup1', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'sp_Hmx_BackupDb ''MyDatabase'',''D:\Database\DbBackups'',''MyDatabase1.BAK''', @database_name = N'MyDatabase', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @stDate as int -- default job start date is today
SET @stDate=0
DECLARE @today as datetime
set @today = DATEADD(day,0,GETDATE()) -- get start date
set @stDate = DATEPART(yy,@today)*10000 + DATEPART(mm,@today)*100 + DATEPART(day,@today)
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'backup1', @enabled = 1, @freq_type = 4, @active_start_date = @stDate, @active_start_time = 010000, @freq_interval = 14, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
GO
-- Job Script generated by Cruncher - job |MyDatabase backup2|
-- Do Not Edit
BEGIN
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'MyDatabase backup2')
IF (@JobID IS NOT NULL) BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''MyDatabase backup2'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'MyDatabase backup2'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'MyDatabase backup2', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'sp_Hmx_BackupDb ''MyDatabase'',''D:\Database\DbBackups'',''MyDatabase2.BAK''', @database_name = N'MyDatabase', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
DECLARE @stDate as int -- default job start date is today
SET @stDate=0
DECLARE @today as datetime
set @today = DATEADD(day,7,GETDATE()) -- get start date
set @stDate = DATEPART(yy,@today)*10000 + DATEPART(mm,@today)*100 + DATEPART(day,@today)
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'backup2', @enabled = 1, @freq_type = 4, @active_start_date = @stDate, @active_start_time = 010000, @freq_interval = 14, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
END
GO
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
