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.';

View 1 Replies


ADVERTISEMENT

How To Implement Alter Database Implement Restrictions On SQL2K ?

Dec 28, 2007

Hi Guyz

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."

thanks in advance.

View 4 Replies View Related

Ctrl + Alt + Some Key

Mar 11, 2004

I accidently pressed some combination of keys and my sql query analyzer has stopped recognising keywords,comments etc.

Has anybody faced this problem ...

have reset all the options availible in QA ... no results ...
tried restarting the comp .... no results ....

any ideas ???

View 14 Replies View Related

Use Ctrl-A As A Column Separator

Oct 2, 2013

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.

View 6 Replies View Related

Stored Procedures And CTRL+ALT+DEL

Jul 20, 2005

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.

View 1 Replies View Related

CTRL-Z Usage In SSRS

Sep 24, 2007



I have opened a connect issue for the CTRL Z handling in SSRS.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299431

Please vote on it so it get's fixed in 2008, I am tired of losing random changes because I hit CTRL-Z.

Thanks
BobP

View 4 Replies View Related

Abort Insertcommand

Apr 1, 2007

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? 

View 1 Replies View Related

BIDS Abort

Aug 1, 2007

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

View 1 Replies View Related

DTC Transaction Abort Error

Feb 14, 2006

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()



Previously

View 1 Replies View Related

How To Abort From A Replication Process

Nov 25, 2005

Hi guys.

View 1 Replies View Related

Rollback When Query Abort Thru ISQLW ?

May 28, 2002

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.

Thanks!

View 1 Replies View Related

Connection Faileed Package Abort

Feb 13, 2007

Hello,

I am connecting with various sqlservers in a package to get the information. If connection failed with any sqlserver, package failed after 3 tries.

I would like that if connection failed with any sqlserver, package should not end rather move ahead and connecto next sqlserver.

I already tried to increase the number of error from 3 to 1000 but still package failed as soon sqlserver connection failed.

Any help is appreciated.

Thanks.

--

Farhan

View 3 Replies View Related

How To Abort Package Execution Programmatically OnError

Aug 8, 2006

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.

Thanks in advance for your help.

View 1 Replies View Related

Negative #Abort From SSIS Event Log Report

Aug 28, 2007

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?

View 1 Replies View Related

Between [Enter The Start Date] And [Enter The End Date])

Mar 28, 2007

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]

View 4 Replies View Related

&#39;enter&#39; Character.

Aug 8, 2002

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.

View 2 Replies View Related

Enter Parameter Value

Mar 29, 2006

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???

View 10 Replies View Related

You Must Enter A Subject

Oct 8, 2004

Question: Why is Graz a moderator on this forum? And on nothing else?



Brett

8-)

View 4 Replies View Related

Can't Enter Data In Field

May 30, 2007

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

CREATE TABLE [dbo].[Quotes] (
[QuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[DateAdded] [datetime] NULL ,
[CustomerID] [int] NOT NULL ,
[ProductName] [varchar] (100) NULL ,
[RepID] [int] NULL ,
[QuoteNumber] [varchar] (30) NULL ,
[QuoteDate] [datetime] NULL ,
[QuoteTerm] [varchar] (10) NULL ,
[QuoteFOB] [varchar] (15) NULL ,
[QuoteNAIRep] [varchar] (30) NULL ,
[QuoteExpiration] [datetime] NULL ,
[Note] [varchar] (700) NULL ,
[Comment] [varchar] (1500) NULL ,
[OrderRequirement] [varchar] (1000) NULL ,
[Status] [varchar] (1) NULL ,
[DateClosed] [datetime] NULL ,
[ProductType] [varchar] (30) NULL ,
[ImageID] [int] NULL ,
[CloseMonth] [int] NULL ,
[CloseYear] [int] NULL ,
[ClosePercent] [int] NULL ,
[Segment] [varchar] (50) NULL ,
[AccountID] [uniqueidentifier] NULL ,
[ReplacedQuoteID] [int] NULL ,
[Lead] [varchar] (80) NULL
) ON [PRIMARY]
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.

Thanks in advance

View 5 Replies View Related

Enter Record (Sancronized)

Jan 28, 2008

Hi,

I have two tables. Class & Faculty

"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.

Thanks,



Navi

View 5 Replies View Related

WHILE..CONTINUE Problem

Aug 25, 2006

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'

EXECUTE @SQL

SET @childNo = @childNo + 1
CONTINUE
END

-- END CODE

View 8 Replies View Related

How To Continue On Error

Jul 23, 2005

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

View 7 Replies View Related

CONTINUE In A Cursor

Nov 29, 2007

Will using continue cause an infinite loop in a cursor or will the cursor know to go to the next record?


Begin Tran



exec @rtn = spDoSomething



select @err = @@error







If @err <> 0

Begin

rollback

continue

End

Commit Tran

View 1 Replies View Related

Continue Execution

Apr 15, 2008

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.

View 2 Replies View Related

Continue After Error

Aug 27, 2007

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?

View 4 Replies View Related

Trying To Enter More Than One Selected Value Into One Column In The Database.

Feb 1, 2008

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

View 2 Replies View Related

How To Enter A Date Parameter In Debugger

Jun 22, 2004

(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.

Thanks.

View 1 Replies View Related

Forms To Enter Data Into SSMS?

Oct 1, 2015

giving a use access to enter data into one database table.

I know Access is an option, but I was wondering if there was another, like MS Word or Adobe.

This is a SSMS database, 2008.

View 3 Replies View Related

Possible For User To Enter A Range Of Date

Nov 24, 2014

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?

View 5 Replies View Related

For / While Loop - Enter Data Into A Table

Feb 19, 2015

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]

Don’t know if this is at all possible?

View 2 Replies View Related

How To Enter Single Quote Character

Sep 22, 2006

Hi all

How to enter single quote character in a sting column for eg: Channel's

Thanks

View 6 Replies View Related

Enter Windows Authentication On Connect?

Nov 30, 2007

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?

Jakob

View 4 Replies View Related

Copying Database Disables Pwd And Must Enter New

Jan 24, 2008

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved