How Do I Implement ---prompt 'Enter 1 To Continue Or CTRL C To Abort
Jul 14, 2004
Hi Guys, I have a SQL block which I want to run based on some values in the DB
-----------------
I am implementing the check as follows..
---------------------
PRINT 'Checking the DATABASE name and Schema Version '
SET NOCOUNT ON
CREATE TABLE chkversion (table_name VARCHAR(30), id INT, reason varchar(200))
GO
DECLARE @MSG CHAR(1)
print @MSG
GO
/* Make sure user is running the right script and right db user */
declare
@ver varchar(100),
@username varchar(100),
@cnt int
begin
select @ver = value from propertytable where id = 33086
select @cnt = count(*) from propertytable where parentid=5001 and propertyid = 726;
select @username = db_name(dbid)from master.dbo.sysprocesses where status ='runnable'
insert into chkversion (table_name, id, reason) values ('', -1, 'You are logging on Database - ' + @username)
insert into error_report (table_name, id, reason) values ('', -1, 'Your agile database version is ' + @ver)
if (@ver != '9.0.144'or @cnt !=0) begin
insert into chkversion (table_name, id, reason) values ('', -1, 'STOP: Your Schema Version and/or database version are not at original 90SP2. Please abort!')
end
else
insert into chkversion (table_name, id, reason) values ('', -1, 'PROCEED: You can run this script to upgrade your db schema from 9.0.144 to 9.0.178')
end
GO
DECLARE @MSG CHAR(200)
SELECT @MSG = convert(varchar(255), reason) from chkversion where id = -1
print @MSG
GO
DECLARE @MSG CHAR(1)
print @MSG
GO
SET NOCOUNT OFF
----------------------------------------------------------
After this, I want user to press 1 to continue or CTRL +C to abort based on the output message from above.
How Do I implement this feature?
In Oracle, I use...
Accept agproceed number prompt 'Enter 1 to continue or CTRL C to abort.';
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ? Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
I need to be able to export the data from a table to a text file using sqlcmd. I need to be able to use Ctrl-A as a column separator. Is this doable, and if so, how? I am assuming the this is a value used by the -s option.I need to use a batch file to run the sqlcmd utility.
We have a suppliers application that runs a stored procedure (one ofmany). This stored procedure then calls various other ones etc... andthe final number of stored procedures run is 11.Now, some of our users have been having problems and deciding forthemselves to CTRL+ALT+DEL the application. This is the first thing wewill stop.However, we have had some error messages indicating that the previoustransaction is blocking them when they try it again. This leaves theuser blocking themself. Guess what they do next ? ******* ! :-)I'm assuming that SQL is stuck at some point in the 11 SP's used.When you cancel a query or stored procedure and SQL rolls back thetransaction, it may take a while to do this. Fair enough, but how doesit handle it if multiple transactions are to be rolled back ? Forexample.A calls B which calls CA gets cancelled. What happens to B and C ? (assuming A has finishedthe call to B but not finished the remainder of the SP i.e B and/or Care still running).Is there any way I can identify where it has failed (bit of a longshot I know)?Does SQL take the users permissions when running stored proceduresfrom within other stored procedures, or does it use their permissionsfor the first one and SQL Server handles the remainder ? I know thefirst one will use the users permissions, but does this carry onindefinately ?Any advice / help would be appreciated.ThanksRyanp.s. SQL 7 running on Windows 2000 Server / Windows 2000 PC's.
hey. I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible?
On several occasions I have experienced crashes while running an SSIS package in debug mode. It happens when I start process on Control Flow table and click on Data Flow tab to monitor progress. I have forwarded the error reports and have searched the boards for similar references. Is this problem going to be fixed?
SSIS Data Sources
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) ----------------------
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Visiual Studio 2005 Professional SP1 SP.050727-7600 .NET Framework 2.0.50727
We are using MSDTC to handle our transactions and some clients seem to be getting these errors more and more when we went to framework 2.0. Any ideas what could be causing this?
Before in framework 1.1 we would ask our clients to re-register their computer into the domain and/or rename their computer and this worked but it seems like in framework 2.0 it doesn't do the trick. Also, the wrong dns setup causes this too but after trying to fix both things it still doesn't work. I believe this error means either the server has finished the transaction and can't find the client to return the result or the active directory account/computer account is corrupted. Any ideas?
The transaction has already been implicitly or explicitly committed or aborted.
Type: TransactionException StackTrace: Server stack trace: at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException) at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts) at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction) at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open()
This morning I needed to update some bad address's in our production DB. I ran this query in ISQLW:
UPDATE mytable SET city = 'blah' WHERE city <> 'blah' GO
it should have been: UPDATE mytable SET city = 'blah' WHERE city <> 'blah' AND name = 'blah' GO
So I basically started updating my entire DB rather than just a handful of records. The query seemed to be running too long (30seconds) then I noticed and clicked the Cancel button. Does this transaction get rolled back when you click cancel? Im not sure where to look to see if the rollback occurred.?
I do have a way to re-update all the address's and a backup but Ive never restored and Im not sure I even need to. I could also run a query to group all the address's but it will be very difficult to tell what got updated. Where talking about 500k records.
Context: I have a web page that uplaods a file that is then imported to the database via an SSIS package.
Problem: If and when the package encounters an error, I want to surface that error to the user via the web page.
Approach: I derived my own class from the DefaultEvents class and then overrode the OnError event handler to catch any errors raised during package execution. I then pass my class to the package Execute method.
Results: The OnError is triggered, but I don't know how to abort package execution nor how to pass the error context to the user. I tried raising a custom exception in the OnError handler, but it failed to propogate to the calling code (ie the code that executes the package).
Another approach would be to write to a database table and then do a query from my web page after the package is done executing (alternatively, I could generate a log file). Seems like a lot of work though. All I want is to pass the error context back to my calling code so I can tell the user what happened.
I am using the sample SSIS Event Log reports provided by Microsoft: http://www.microsoft.com/downloads/details.aspx?familyid=526e1fce-7ad5-4a54-b62c-13ffcd114a73&displaylang=en
The Event Log Summary report is showing a negative value for #Abort. Why is the aborted count negative?
Hi all can you help me please, I need the users to be able to enter in a date for a self generating report. Usually I use the @Enter_Date but I need them to be able to enter a start date and a end date But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'Enter the Start Date'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'Enter the End Date'.
What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??
SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes FROM [Main Table] WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date])) ORDER BY [Main Table].[IR Number]
How do I replcae an 'enter' character with a space in a field? This extra spaces entered in the web form while populating the field is causing extra spaces in the field. What is the ASCII value for the 'enter' character? so that we can replace that with a space? Thanks. sa.
Hey in Access MDB you can create a query that when you run it, it will ask you to Enter the Paramer Value, User could enter in a year or Team Member Number or however you have the query set up. is this possible in SQL ADP???
I have a field in my table that is varchar:1500:null, but I cannot enter any more that 994 characters. any ideas why?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Quotes] GO
ALTER TABLE [dbo].[Quotes] ADD CONSTRAINT [DF_Quotes_Closed] DEFAULT ('N') FOR [Status] GO
CREATE INDEX [idx_RepID] ON [dbo].[Quotes]([RepID]) ON [PRIMARY] GO
CREATE INDEX [indx_CustomerID] ON [dbo].[Quotes]([CustomerID]) ON [PRIMARY] GO
CREATE INDEX [indx_QuoteID] ON [dbo].[Quotes]([QuoteID]) ON [PRIMARY] GO
CREATE INDEX [indx_Status] ON [dbo].[Quotes]([Status]) ON [PRIMARY] GO
[Comment] [varchar] (1500) NULL , is where I can't enter more than 994 characters.
note - I know very little about SQL, I just had the responsibility placed on me at my job. If this is not enough information to go on to throw an idea at me please let me know what else you need.
"Class" table have column names ClassID, SchoolID and FacultyID(FacultyID column have just created therefore there is no data in the column) "Faculty" table have column names FacultyID, SchoolID
In "class" table every SchoolID have 1 or 2 classID. Whereas in "Faculty" table every facultyID have more than 3 SchoolID.
Therefore when I update the table it only update the rows not entering new rows as per schoolID.
How can we add new rows in the class table according SchoolID, Because I want to get data in the newly generated column FacultyID in "CLASS" table.
Hi all, I need a bit of assistance with a piece of code I have written. I keep receiving a Syntax error "Incorrect syntax near END". The procedure is ~1000 line of code so I have just cut and paste the "problem" sql here and attached the full code to this message to see if anyone can help me out. Cheers :beer:
-- BEGIN CODE IF @NoOfChildren > 0 BEGIN DECLARE @childNo AS INTEGER DECLARE @SQL AS VARCHAR(8000) SET @childNo = 1
WHILE @childNo != @NoOfChildren BEGIN -- Perform Dynamic SQL check for children SET @SQL = 'IF @strChild'+ @childNo + 'Surname IS NOT NULL BEGIN SET @asciicounter = 1 SET @asciifound = 0
WHILE @asciicounter != 255 BEGIN IF CHARINDEX(char(@asciicounter),@strChild'+ @childNo +'Surname) != 0 BEGIN IF @asciicounter NOT IN (32,45,46) BEGIN SET @asciifound = 1 END END SET @asciicounter = @asciicounter + 1 CONTINUE END IF @asciifound = 1 BEGIN SET @strErrorBucket = @strErrorBucket + ''''45'''','' END END' EXECUTE @SQL
SET @SQL = ''
-- Perform Dynamic SQL check for Forename SET @SQL = 'IF @strChild'+ @childNo + 'Forename IS NOT NULL BEGIN SET @asciicounter = 1 SET @asciifound = 0
WHILE @asciicounter != 255 BEGIN IF CHARINDEX(char(@asciicounter),@strChild'+ @childNo +'Forename) != 0 BEGIN IF @asciicounter NOT IN (32,45,46) BEGIN SET @asciifound = 1 END END SET @asciicounter = @asciicounter + 1 CONTINUE END IF @asciifound = 1 BEGIN SET @strErrorBucket = @strErrorBucket + ''''46'''','' END END' EXECUTE @SQL
SET @SQL = ''
-- Perform Dynamic SQL check on DOB
SET @SQL = 'IF @dtChild'+@childNo+'Dob IS NULL BEGIN SET @strErrorBucket = @strErrorBucket + ''''047'','' END
IF ISDATE(@dtChild'+@childNo+'Dob) = 0 BEGIN SET @strErrorBucket = @strErrorBucket + ''''048'','' END
IF DATEDIFF(YEAR, @dtChild'+@childNo+'Dob, GETDATE()) NOT BETWEEN 0 AND 18 BEGIN IF DATEDIFF(YEAR, @dtChild'+@childNo+'Dob, GETDATE()) > 18 BEGIN SET @strErrorBucket = @strErrorBucket + ''''049'','' END ELSE BEGIN SET @strErrorBucket = @strErrorBucket + ''''050'','' END' EXECUTE @SQL
SET @SQL = ''
-- Perform dynamic SQL check on Gender
SET @SQL = 'IF @blnChild'+@childNo+'Gender IS NULL BEGIN SET @strErrorBucket = @strErrorBucket + ''''051'','' END'
How can I cause my insert statement to skip over (without failing) rowswhere there's a primary key constraint violation?I've got a case where daily I insert >500k rows of daily data, wherethe data is date and time stamped. So, for example, I have an insertstatement with constraint: WHERE date >= '5/20/05' AND date <'5/21/05'. That takes care of one day's data (5/20).However, the next day's data (5/21) will still have some time stampsfrom the previous day. Therefore the statement needs to be somethinglike WHERE date >= '5/20/05' AND date <= '5/21/05'. The 5/20 data isalready loaded but I need to take the 5/21 data which just happens tocontain just a few rows of data marked 5/20 and insert it withoutgenerating a primary key error from all the other 5/20 rows that arealready inserted.-Dave
In my package i have to loop through files and load the data from files into a table. but if a file has error i need to move it to a folder like errored and continue execution with other files.
I have an SSIS package that uses a for each loop to send an order confirmation e-mail. If it does not find an email I need the package to continue after the failure path. The package stops after the failure, but I need it to continue with the next iteration of the for each loop. How can I do this?
Hi i am trying to enter more than one value into my constructor field in my database, at the moment when the values are entered into the database through the front end, they appear in the database as seperate records however they have the same ID, the only thing that changes is are the data in the fields which i want in just one. This is the part of the stored procedure I am sure is causing this to happen; IF @Access_Right_ID=8 or @Access_Right_ID=3 or @Access_Right_ID=9 BEGIN DECLARE @Delimiter char(1) SET @Delimiter = ',' DECLARE @CommaPos int SET @CommaPos = Charindex(@Delimiter,@Constructor_IDs) DECLARE @Remaining_Constructor_IDs VARCHAR(8000) SET @Remaining_Constructor_IDs = @Constructor_IDs DECLARE @Constructor_ID int WHILE (@CommaPos>=0) BEGIN SET @CommaPos = Charindex(@Delimiter,@Remaining_Constructor_IDs) if @CommaPos >0 SET @Constructor_ID = ltrim(rtrim(Substring(@Remaining_Constructor_IDs,1,@CommaPos-1))) else SET @Constructor_ID = @Remaining_Constructor_IDs INSERT INTO tblUser_Constructor ( User_ID, Constructor_ID) VALUES (@User_ID, @Constructor_ID ) SET @Remaining_Constructor_IDs = SUBSTRING(@Remaining_Constructor_IDs,@CommaPos+1,LEN(@Remaining_Constructor_IDs)-@CommaPos) if @CommaPos=0 set @CommaPos = -1 END
(This is prob. a really dumb question but it's driving me mad!!...)
I am using the Debugger in SQL Query Analyzer & want to set the value of a datetime parameter prior to executing the stored proc. The "Debug procedure" window allows me to specify the parameter values - but I can't get it to accept a datetime. The language is us_english & I've tried most ways if specifying the date - 01/02/2004, with/out quotes, 02 Jan 2004, as a full datetime, swapping day/month values etc etc. The procedure always fails immediately with: Invalid character value for cast specification.
I have been asked to write a query on SBO.It requires (among other) a reference to a budget, which does not exist anywhere now.It is a yearly budget that changes quarterly.I suppose I could add a table and update the no. each quarter - but will the history be kept?What would you recommend?Also, they require a quarterly comparison (all in the same report) is it possible for the user to enter a range of date (for sales data, for example) and also receive the data for that range only last year's?
I want to, for each month of the year 2014 say, to create a loop that will enter data into a table.
Right now I have:
Select [Member Number], sum(case when [Receipt Date]='2014/01/01' then Amount else 0 end) as [Rec 2014/01/01] From [Receipts Table] Group by [Member Number] Insert into [Receipts 2014/01/01]
[Code] ....
Instead I would just like to do something like…
Declare i date For i=2014/01/01 to 2014/12/01
Select [Member Number], sum(case when [Receipt Date]=i then Amount else 0 end) as [Rec +i]
From [Receipts Table] Group by [Member Number] Insert into [Receipts + i]
I'm trying to connect to a database using my virtual machine, where I´m logged in as another user than the one that's granted acccess to the database. Can I enter the windows username and password used to connect to my SQL 2005 database, in Windows Authentication Mode?
I'm copying some of my development databases over to a new server and have noticed that even when I enable the acct the original password will no longer work. I have to then reenter each pwd for every acct that is in the database. I'm on sql server 2005 and I'm using the Task >> Copy Database.