For a daily backup job,
Is there a way to "Backup Datbase dbXYZ to backup_dbXYZ with init", go, then next statment of master..xp_cmdshell to zip the backup_dbXYZ.bak to backup_dbXYZ.zip file?
Also, this may need to be done via remote server..
thanks
David
Good morning.
We are running SQL Server 2000 with SP3. I have recently created a DB Maintenance Plan to backup all of our databases nightly to a local tape drive, and verify the backup of those databases. The backup plan itself runs fine.
The first time it ran, it ran in a little over an hour. Since then, I have noticed the time increasing nightly to the point that last night's backup took 8 hours. Our databases are not that big and do not have enough transactions to cause such a drastic change in a matter of weeks.
Investigating the problem, I have found that the issue lies in the fact that the tape is not initializing each night, and thus each night's backup is appending to an increasingly more full tape.
My question is this: Is there a way to force a DB Maintenance Plan to initialize the tape each night? I know that I can probably add a "with init" command to the T-SQL statement created, but I am not sure exactly where to fit it in. Does anyone have any suggestions?
Thank you for your time.
Michael
I have a biztalk db server where I have few job steps. The log bkp job steps which the below error but when I go to the location of backup/checking the msdb backupset table I can see the backup file.
[SQLSTATE 01000] (Error 4035) BACKUP LOG successfully processed 1 pages in 0.010 seconds (0.146 MB/sec). [SQLSTATE 01000] (Error 3014)
I have inherited a job that is calling a maintenance plan. The maintenace plan makes a back-up of various databases.I have to add a step to add to encrypt the backup. I have the exe already. Its called "EncryptBackup.exe".My difficulty is I do not know how to add the step to the job exactly. The command syntax eludes me a bit. The syntax for the encrypter exe is:
C:exesEncryptBackup.exe C:DBBackupFolderA Encypt filename.bak
The output is "filename_encrypt.bak". It should be noted that there exists several sub-folder off: C:DBBackup like FolderA, FolderB, etc...each one holds a database.
Hi All,
Please let me know where can i find the sample files and rs2005sbsDW database in msdn library as i dont have CD provided along with the book.
Please give me the link of the sample files so that i can download it for testing the sample application.
Regards,
Prabhanjana
is there such a paper? if so, can you pls point me to it? thx in advance
View 4 Replies View Relatedhye everyone,
after finished do the report and deploy at IIS/report manager in local pc..
i want to know , what is the step /how to deploy the report project at the user's pc/ client pc /other server..
thanks in advance
thank you very much
I want to convert .rdl to .rdc need full steps.Actually i created .rdl report using sp sucessfully.Now i want to convert it to rdlc while doing it iam getting some authentication error and some thing else.I created rdl in 2008 and i want to change it to rdlc 2010.
View 5 Replies View RelatedI have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.
Here's the rub:
If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).
I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.
Any help is greatly appreciated.
I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)
Thanks
B.
My question is concerning whether or not to use INIT or NOINIT with RETAINDAYS. I know that if the backup has not expired, the backup will fail if you use INIT. But will the backups write over expired backups using the NOINIT or will they just keep appending to the end of the file? Also is there a way to view a backup and find the expiration date? I have looked in Enterprise manager and havent found it. One last question, can you only have backups expire using days? Or can you set the expirations to be a smaller value? Thanks in advance for any help.
View 1 Replies View RelatedI started getting this error when I tried to start my local SQL Server yesterday and nothing I do seems to help:
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2, status code 0x35.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2, status code 0x1.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
The only thing I did recently was move the My Documents folder from my local machine to a folder on a network share (to make sure my docs are backed up) via a right click on the My Documents folder in the Start menu, then pick the new location. I've since moved it back, but the above error persists.
Also, when I try to configure the server's protocols, I'm not able to enable any of the protocols. I can right click the protocol and click enable, then I get the error that says it'll take effect when i restart the service, but then in the display, it still says disabled. Refreshing doesn't change the status. The client protocols are enabled though.
Any help would be greatly appreciated (since the service never successfully starts)...
Hi!
I hope the answer is as simple as the question -- but after reading all the documentation I could find (understand?) and a lot of posts here, I'm no closer to achieving the goal.
I have a Visual C# app, DAYTRACKER, developed in VS2005. It uses a database with several tables constructed using SQL Server 2005 Developer Edition.
I want to deploy the app plus the database plus SQL Express to another machine, to be used by a single user (the administrator) with no need for network connectivity of any kind.
What I have so far is:
1. The application is successfully deployed from a CD-ROM, having used the Publish process within VS2005, and opens on the new machine -- without database connectivity, however.
2. SQL Express is successfully deployed (it deployed as a 'prerequisite' when I went through the Publish process in VS2005)
3. I manually copied the database's .mdf and .mdl files, using SQL Server Managers 'Copy Database' function, then transferred the copies to the new machine into the ..MSSQL.1MSSQLdata folder (where they appear along with the master.mdg, mastlog.ldf etc files)
Now, the DAYTRACKER application's DAYTRACKERConnectionString under 'Settings' in the VS2005 studio reads 'Data Source=DELL3;Initial Catalog=DayTracker;Integrated Security=True' (which are the appropriate parameters for the machine, DELL3, on which I wrote the program.)
The problem, of course, is that SQL Express on the new machine doesn't connect the application to the database. When I go to the 'SQL Server Configuration Manager' and go to the 'SQL Server 2005 Services' and double-click on the 'SQL Server (SQLEXPRESS)' icon (the service is running) and the user is logged on using 'Local System Account'. Under the 'Service' tab the Host Name is 'MUSIC' (which is the name of the new machine I've installed the app onto -- which of course is not the name - DELL3 - that the app's connection string is expecting). Under the 'Advanced' tab, I've tried correcting the name of the Startup Parameters default .mdf and .mdl entries to ..DayTracker.mdf and ..DayTracker_log.mdl, but the server won't start up after I make the changes.
What I'm hoping for: a step-by-step way of doing this type of deployment, preferable getting it all onto one CD-ROM, and installing it on the new machine so that it all works seamlessly from the start, not requiring any 'tweaking' of the SQLServer Express settings by the end-user.
But I'll take pretty much anything that fixes the specific db connectivity problem I've described.
Thank you very much.
John F.
Hi,
I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory.
Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.
Regards,
harry
Connecting to a networked SQL Server Box from my local machine
Open Query Analyzer from Start menu, logging in using sa account.
from the object browser i select my stored procedure (WEA_InsertClaim) - right click and select Debug.
i am prompted to enter the parameter values, which i do, auto rollback checkbox is checked - click Execute.
T-SQL Debugger opens and runs through the stored procedure.
but only buttons enabled are the "Go", "Toggle Breakpoint", "Clear All Breakpoints"
so i can set breakpoints etc. but when i select Go it will not stop at the breakpoints it just runs through the stored procedure from start to finish. giving the correct return code as its output
is there something i need to enable in order to make it stop at breakpoints??
Cheers,
Craig
hi,
i'm running win2k sp4 on a 2.4Ghz P4 with 512M ram and 1.5 gigs freespace on my harddrive (more than enough for the sql server client).
i insert the cd and start up the installer. i then get the splash screen, click on "SQL Server 2000 Components", then on "Install Database Server". Then the splash screen disappears and nothing happens for a while (sometimes a really long while). Then an error message pops up saying:
Setup initialization error.
<some message>
Source: 'E:<some source>'
Target: 'C:WINNTTEMPSqlSetup<some target>'
where <some message> is a differing message. sometimes it's "The data is invalid", sometimes a cycle redundancy check error, sometimes a inpage error...
<some source> is one of the files on the sql server cd, and this also differs most of the time.
<some target> is the corresponding <some source> in the sql server setup's temp dir.
as well, if i restart my computer and try starting the installer after windows has started up (well, at least all of the sys tray icons have loaded) i usually get an error message saying something about "msetup" having an error and being shut down. it also mentions that there is some log file which is created, but i'm not sure where it's located or what it's called.
i have also noticed that some process called " wowexec.exe" is starts up when the installer does, and keeps running after the installer (setupsql.exe) crashes. i am also unable to end the " wowexec.exe" process and process tree.
i don't think there's anything wrong with the cd, since it looks like it works file on other pc's. any idea what may be wrong?
i would really appreciate any help in fixing this. i really need to get the sql server 2k client installed (particularly the enterprise manager).
tia
Is there a good step by step guide to setting up an indirect configuration? I've followed the steps in Kirk Haselden's 'Keep your packages in the dark' article 3 or 4 times this afternoon and nothing seems to work. I cannot even get the XML configuration file to work by accessing it directly. I'm using the wizard to create the file.
Just wondering if there was something else out there....
Thanks in advance for any help.
MarkA
I am trying to get the CLR user-defined aggregate functions sample working (VB version).
I've saved the sample (from Invoking CLR User-Defined Aggregate Functions ) to a .vb file and compiled it like this:
C:dataVStudioprojectsSQLutilsSQLutils>c:WINDOWSMicrosoft.NETFrameworkv2
.0.50727vbc /t:library /out: SQLutils.dll Class1.vb
Microsoft (R) Visual Basic Compiler versie 8.0.50727.42
voor Microsoft (R) .NET Framework versie 2.0.50727.42
Copyright (c) Microsoft Corporation. Alle rechten voorbehouden.
The SQLutils.dll file is created without warnings or errors.
I've created the assembly
create assembly sqlutildll from 'D:SQLdatadllSQLutils.dll'
and then the function:
CREATE AGGREGATE Concat (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME sqlutildll.Concatenate
and then I got this error:
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
What is this UDAGG specification ?
How can I make this function working?
The VB code:
Code Snippet
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Text
<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> <Microsoft.VisualBasic.ComClass()> _
Public Class Concatenate
Implements IBinarySerialize
''' <summary>
''' The variable that holds the intermediate result of the concatenation
''' </summary>
Private intermediateResult As StringBuilder
''' <summary>
''' Initialize the internal data structures
''' </summary>
Public Sub Init()
Me.intermediateResult = New StringBuilder()
End Sub
''' <summary>
''' Accumulate the next value, not if the value is null
''' </summary>
''' <param name="value"></param>
Public Sub Accumulate(ByVal value As SqlString)
If value.IsNull Then
Return
End If
Me.intermediateResult.Append(value.Value).Append(","c)
End Sub
''' <summary>
''' Merge the partially computed aggregate with this aggregate.
''' </summary>
''' <param name="other"></param>
Public Sub Merge(ByVal other As Concatenate)
Me.intermediateResult.Append(other.intermediateResult)
End Sub
''' <summary>
''' Called at the end of aggregation, to return the results of the aggregation.
''' </summary>
''' <returns></returns>
Public Function Terminate() As SqlString
Dim output As String = String.Empty
'delete the trailing comma, if any
If Not (Me.intermediateResult Is Nothing) AndAlso Me.intermediateResult.Length > 0 Then
output = Me.intermediateResult.ToString(0, Me.intermediateResult.Length - 1)
End If
Return New SqlString(output)
End Function
Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
intermediateResult = New StringBuilder(r.ReadString())
End Sub
Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
w.Write(Me.intermediateResult.ToString())
End Sub
End Class
Hi
I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)
This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"
I have made the following configurations:
The user group (windows group) that the user belongs has the following roles in msdb :
db_dtsadmin
db_dtsltuser
db_dtsoperator
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
i have made a proxy to sql server agent which has the following subsystems :
"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.
Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.
Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.
Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.
I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:
select * from vProductProfitability
where year = 2003 and
MonthNumberOfYear = 1
The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.
I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?
BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.
This humble grasshopper seeks wisdom.
Respectfully Submitted,
Dave Matthews
Atlanta, GA
aka FlooseMan Dave
Dear Experts,
please guide me for replcation.....
i'm using sql server 2005 server tools developer edition. my OS is professional 2000.
and i've sqlserver 2000 client tools also
i've two databases in my machine. publisher is in different instance, and the transactions might be maximum 50 per day. my aim is when ever developer enters the data into the main database, automatically it should be updated on the my two databases also.
i mean the server is sysA and the database is srtp.
my machine is sys20 and the databases are srtp1, srtp2.
how should i make sysA as publisher?
i've right click on the server databases, but it is showing newsubscriptions option only.
Vinod
Even you learn 1%, Learn it with 100% confidence.
Visual C# 2005 Step by Step €“ John Sharp
Intel D975XBX €“ 930 €“ 2GB ram €“ HD 300GB - MatroxVD
Windows XP Sp2 + updates
Visual Studio 2005 Professional edition
SQL Sever 2005 Standard Edition
20070203
Install Practice Files at D:Program FilesMicrosoft PressVisual C Sharp Step by Step.
Configuring SQL Sever Express Edition
Hostname - xxxxxxxxx..
At sqlcmd €“s xxxxx...SQLExpress €“E got - Pipes error.
Open Microsoft SQL Sever 2005 - Configuration Tools €“ SQL Configuration Manager €“ select Protcols for SQLEXPRESS €“ select Named Pipes €“ enable €“ close.
At sqlcmd €“s xxxxx..SQLExpress €“E get Sqlcmd: €˜ €˜ : Unknown Option. Enter €˜ -? €˜ for help.
Chdir C:Documents and SettingsAll UsersStart MenuProgramsMicrosoft Server 2005
Sqlcmd €“s xxxxxx..SqlExpress €“E
Hresult 0x2, Lvel 16, State 1
Named Pipes Provider: Couldnot open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
How do I fix this?
Need to be able to to use the Northwind Traders data base
Thanks
Friends -
Could any one of you provide steps for upgrading SQL Server 2000 cluster to SQL server 2005 cluster.
My environment is Windows 2003 server.
Appreciate your support.
Cheers
VSH
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
I am trying to run the code below as a step in a job, but I cannot get this much code in the step. Is there another way of running this code? I tried putting the code inside of an SP, but I got errors there too.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_importthis
(
Confirmation varchar(50) NULL,
StartDate datetime NULL,
C1_1 char(3) NULL,
C1_2 char(6) NULL,
C1_3 datetime NULL,
C1_4 datetime NULL,
C1_5 varchar(55) NULL,
C1_6 money NULL,
C1_7 int NULL,
C1_8 char(9) NULL,
C1_9 varchar(5) NULL,
PRDept char(2) NULL,
TypeVisit int NULL,
C2_1 int NULL,
C2_2 int NULL,
C2_3 bit NULL,
C2_4 int NULL,
C2_5 bit NULL,
C2_6 varchar(50) NULL,
C2_7 char(1) NULL,
C2_8 char(1) NULL,
C2_9 char(1) NULL,
C2_10 char(1) NULL,
C2_11 char(1) NULL,
C2_12 char(1) NULL,
C2_13 char(1) NULL,
C2_14 int NULL,
C2_15 bit NULL,
C2_16 bit NULL,
C2_17 bit NULL,
C2_18 bit NULL,
C3_1 int NULL,
C3_2 int NULL,
C3_3 bit NULL,
C3_4 bit NULL,
C3_5 bit NULL,
C3_6 bit NULL,
C3_7 bit NULL,
C3_8 bit NULL,
C3_9 bit NULL,
C5_1 bit NULL,
C5_2 bit NULL,
C5_3 bit NULL,
C5_4 bit NULL,
C5_5 bit NULL,
C6_1 bit NULL,
C6_2 text NULL,
C6_3 bit NULL,
C6_4 text NULL,
C6_5 bit NULL,
C6_6 text NULL,
C6_7 bit NULL,
C6_8 bit NULL,
C6_9 bit NULL,
C6_10 text NULL,
C7_1 bit NULL,
C7_2 bit NULL,
C7_3 bit NULL,
C7_4 bit NULL,
C8_1 int NULL,
C8_2 int NULL,
C8_3 bit NULL,
C8_4 bit NULL,
C8_5 bit NULL,
C9_1 bit NULL,
C9_2 bit NULL,
C10_1 bit NULL,
C11_1 bit NULL,
C13_1 bit NULL,
C13_2 bit NULL,
C13_3 bit NULL,
C13_4 bit NULL,
C13_5 bit NULL,
LODept char(1) NULL,
C15_1 int NULL,
C15_2 int NULL,
C15_3 bit NULL,
C15_4 int NULL,
C15_5 bit NULL,
C15_6 varchar(50) NULL,
C15_7 char(2) NULL,
C15_8 char(2) NULL,
C15_9 char(2) NULL,
C15_10 char(2) NULL,
C15_11 char(2) NULL,
C15_12 char(2) NULL,
C15_13 char(2) NULL,
C15_14 int NULL,
C15_15 bit NULL,
C15_16 bit NULL,
C15_17 bit NULL,
C15_18 bit NULL,
C15_19 varchar(255) NULL,
C16_1 bit NULL,
C16_2 text NULL,
C16_3 bit NULL,
C16_4 text NULL,
C16_5 bit NULL,
C16_6 text NULL,
C16_7 bit NULL,
C16_8 bit NULL,
C16_9 bit NULL,
C16_10 text NULL,
C16_11 bit NULL,
C16_12 char(10) NULL,
C16_13 text NULL,
C16_14 bit NULL,
C16_15 bit NULL,
C16_16 text NULL,
Comments text NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.importthis)
EXEC('INSERT INTO dbo.Tmp_importthis(Confirmation, StartDate, C1_1, C1_2, C1_3, C1_4, C1_5, C1_6, C1_7, C1_8, C1_9, PRDept, TypeVisit, C2_1, C2_2, C2_3, C2_4, C2_5, C2_6, C2_7, C2_8, C2_9, C2_10, C2_11, C2_12, C2_13, C2_14, C2_15, C2_16, C2_17, C2_18, C3_1, C3_2, C3_3, C3_4, C3_5, C3_6, C3_7, C3_8, C3_9, C5_1, C5_2, C5_3, C5_4, C5_5, C6_1, C6_2, C6_3, C6_4, C6_5, C6_6, C6_7, C6_8, C6_9, C6_10, C7_1, C7_2, C7_3, C7_4, C8_1, C8_2, C8_3, C8_4, C8_5, C9_1, C9_2, C10_1, C11_1, C13_1, C13_2, C13_3, C13_4, C13_5, LODept, C15_1, C15_2, C15_3, C15_4, C15_5, C15_6, C15_7, C15_8, C15_9, C15_10, C15_11, C15_12, C15_13, C15_14, C15_15, C15_16, C15_17, C15_18, C15_19, C16_1, C16_2, C16_3, C16_4, C16_5, C16_6, C16_7, C16_8, C16_9, C16_10, C16_11, C16_12, C16_13, C16_14, C16_15, C16_16, Comments)
SELECT CONVERT(varchar(50), Confirmation), CONVERT(datetime, StartDate), CONVERT(char(3), [1-1]), CONVERT(char(6), [1-2]), CONVERT(datetime, [1-3]), CONVERT(datetime, [1-4]), CONVERT(varchar(55), [1-5]), CONVERT(money, [1-6]), CONVERT(int, [1-7]), CONVERT(char(9), [1-8]), CONVERT(varchar(5), [1-9]), CONVERT(char(2), [Purchase/ReturnDept]), CONVERT(int, TypeVisit), CONVERT(int, [2-1]), CONVERT(int, [2-2]), CONVERT(bit, [2-3]), CONVERT(int, [2-4]), CONVERT(bit, [2-5]), CONVERT(varchar(50), [2-6]), CONVERT(char(1), [2-7]), CONVERT(char(1), [2-8]), CONVERT(char(1), [2-9]), CONVERT(char(1), [2-10]), CONVERT(char(1), [2-11]), CONVERT(char(1), [2-12]), CONVERT(char(1), [2-13]), CONVERT(int, [2-14]), CONVERT(bit, [2-15]), CONVERT(bit, [2-16]), CONVERT(bit, [2-17]), CONVERT(bit, [2-18]), CONVERT(int, [3-1]), CONVERT(int, [3-2]), CONVERT(bit, [3-3]), CONVERT(bit, [3-4]), CONVERT(bit, [3-5]), CONVERT(bit, [3-6]), CONVERT(bit, [3-7]), CONVERT(bit, [3-8]), CONVERT(bit, [3-9]), CONVERT(bit, [5-1]), CONVERT(bit, [5-2]), CONVERT(bit, [5-3]), CONVERT(bit, [5-4]), CONVERT(bit, [5-5]), CONVERT(bit, [6-1]), CONVERT(text, [6-2]), CONVERT(bit, [6-3]), CONVERT(text, [6-4]), CONVERT(bit, [6-5]), CONVERT(text, [6-6]), CONVERT(bit, [6-7]), CONVERT(bit, [6-8]), CONVERT(bit, [6-9]), CONVERT(text, [6-10]), CONVERT(bit, [7-1]), CONVERT(bit, [7-2]), CONVERT(bit, [7-3]), CONVERT(bit, [7-4]), CONVERT(int, [8-1]), CONVERT(int, [8-2]), CONVERT(bit, [8-3]), CONVERT(bit, [8-4]), CONVERT(bit, [8-5]), CONVERT(bit, [9-1]), CONVERT(bit, [9-2]), CONVERT(bit, [10-1]), CONVERT(bit, [11-1]), CONVERT(bit, [13-1]), CONVERT(bit, [13-2]), CONVERT(bit, [13-3]), CONVERT(bit, [13-4]), CONVERT(bit, [13-5]), CONVERT(char(1), LookOnlyDept), CONVERT(int, [15-1]), CONVERT(int, [15-2]), CONVERT(bit, [15-3]), CONVERT(int, [15-4]), CONVERT(bit, [15-5]), CONVERT(varchar(50), [15-6]), CONVERT(char(2), [15-7]), CONVERT(char(2), [15-8]), CONVERT(char(2), [15-9]), CONVERT(char(2), [15-10]), CONVERT(char(2), [15-11]), CONVERT(char(2), [15-12]), CONVERT(char(2), [15-13]), CONVERT(int, [15-14]), CONVERT(bit, [15-15]), CONVERT(bit, [15-16]), CONVERT(bit, [15-17]), CONVERT(bit, [15-18]), [15-19], CONVERT(bit, [16-1]), CONVERT(text, [16-2]), CONVERT(bit, [16-3]), CONVERT(text, [16-4]), CONVERT(bit, [16-5]), CONVERT(text, [16-6]), CONVERT(bit, [16-7]), CONVERT(bit, [16-8]), CONVERT(bit, [16-9]), CONVERT(text, [16-10]), CONVERT(bit, [16-11]), CONVERT(char(10), [16-12]), CONVERT(text, [16-13]), CONVERT(bit, [16-14]), CONVERT(bit, [16-15]), CONVERT(text, [16-16]), CONVERT(text, Comments) FROM dbo.importthis TABLOCKX')
GO
DROP TABLE dbo.importthis
GO
EXECUTE sp_rename 'dbo.Tmp_importthis', 'importthis'
GO
COMMIT
I am trying to create an FTP process using DTS as one of the steps in my package, but the received file gets deleted as soon as it's received from the remote site(as soon as the FTP step completes)! Any idea on what needs to be done?
Thanks.
Sheila.
Hi All,
I am executing a stored procedure within a job step. This stored procedure executes the xp_sqlmaint stored procedure with some parameters. I run this job as 'sa'.
The job fails with the following error message:
sqlmaint.exe failed. Step failed.
What could cause this error? Please help. Thanks.
Hello,
I am wondering if anyone else has run into problems step debugging using vs 2005 t-sql stored procedures and functions. I have two issues.
1. sometimes my breakpoinst show up as hollow and the step debugger just runs thought
2. other times, I get in and the step debugger works for 4 -10 steps and then locks up.
Any ideas are appreciated. If there is a better place for me to post this problem, let me know. I have searched the web on this problem extensively and have not found a solution that fixes my issue. I am logged in with sysadmin which is the most common problem I could find online.
Thanks!
hi..
i have one job has 50 steps. every step check one database. so is not relate it. if the step one success go to next one. if fail still go to next one.. so if one of them fail. you cant see it in enterprise manager.. my question is... is there a way that i can get a warning if one of them are fail? anything beside modify the sp i am running.....
basically all the step are same.. (exec sp_xxxx 'database name')
just alot of them.
thanks
I'm trying to initiate an executable from a job step. This executable functions normally from a command prompt on the server and will initiate properly from a DTS package that is executed locally by a logged in user. It won't run from Query Analyzer, an Operating System Command jobstep, or a scheduled DTS package. The executable creates a report that gets written to disk and a window shows briefly during execution.
MS SQL 2000 on Win2K.
Any clues as to how to run this executable from a scheduled jobstep?
Thanks if you can help,
Mack
Is it possible to set up some kind of error handler in VB using the DTS DOM that will enable a retry of a step/task within a package? This is a DataPump task with a Custom Task. I am transferring 16 tables within each of the packages, 1 package for each of the 50 states, and executing them 1 at a time from a VB app. I can retry a package from the package error handler but can't find an equivalent for a step or task. I'd rather not have to resend tables, but restart from the table with the problem.
It is possible to write code to recreate an abbreviated form of the package but I'd like to avoid that, too.
Better yet, how can I avoid this error even appearing:
"Execution canceled (sic) by user".
I'm not cancelling anything, but about every 4th package this error comes up, not even while transferring the same table. If I retry the package, it almost always flies.
Hi, I've been writing simple database queries for a long time, but I've come to a point where I need to do more work with fewer queries, and I've not been able to make a dent in it. Please excuse my ignorance.
I have a relational database of prices that holds the following info:
Currencies (yen, euro, dollar)
Industries (chemistry, biology, etc)
Company/Institute size (<500, 500-1000, +1000)
Main Products (lab equipment, classroom equipment, etc.)
Series (subproducts) (100ml beaker, chalk, etc)
I want to build a form where my customers can select their currency, their industry and their company size, and then display prices for a specific product (series).
My problem is on the way to the product select. I don't want to display a huge list of EVERYTHING--I want the customer to be able to first select a Main Product, and then populate a list with the relevant sub product for them to select.
However my database is set up with three tables for the individual products: a MainProducts table with main products and ids, a Series table with sub products (we call them series) and ids, and a third table Products that combines the mainproductid with the subproductid to create a unique Productid that is used throughout the rest of the database to get the prices.
(see the relationships here: http://magneticmirror.com/db.gif. The reason for this is that Series in two different MainProducts can have the same name, and so constitute different Products)
The only way I know how to display the name of the relevant series (sub products) is to get the user to:
*select a MainProduct from a list;
*then select all ProductIDs from the the Products table with that MainProductID;
*then for each row from the Products table, take the SeriesID (sub product) and run that against the Series table to get the name of the series corresponding to that ID
if there are 50 series in a product line, that would be 50 individual queries just to populate the list.
There must be a better way. Is this what joining tables is about?
Thanks for any help.
Luud
Hi guys. I set up a maintenance job on my SQL 2000 server to back up a database. Before the job kicks in to back up the database, I would like to set up a step to check for a file first on another box. If the file exists, continues; otherwise, terminate the job. When I create a step, how can I check whether a file from \testserver empflag.log is there? What kind of method I can use?
I tried to paste some vbscript codes in the step; however, I don't know how to use vbscript to execute the next step. Below is the vbscript codes - please see bolds.
Option Explicit
On Error Resume Next
Dim objFso, LogFile, dteStart, sFolder, WshShell
sFolder = "\botest3ackupflag.log"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set WshShell = WScript.CreateObject("WScript.Shell")
set LogFile = objfso.getfile(sFolder)
dteStart = Now()
Do Until DateDiff("n", dteStart, Now()) > 5 OR Not objFSO.FileExists(sFolder)
Wscript.Sleep 10000
Loop
If objFSO.FileExists(sFolder) Then
<don't know what to put here to execute the next step>
Else
WshShell.logevent 1, "Time Expired - Cannot Find Flag.log"
<also don't know what to put here to stop the job>
End if
Wscript.Quit
Set objFso = Nothing
Set sFolder = Nothing
Set dteStart = Nothing
Set dteStart = Nothing
Set WshShell = Nothing