|
|
Can I use something like try/finally construction for guaranteed run script with -b option in sqlcmd? The task is:
turn on single user mode
run some scripts that may be errors
guaranteed turn off single user mode
|
|
|
|
|
Yes, even Sql has the famous try..catch[^] block. Your "finally" would be included at the end of the "try" part.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Your "finally" would be included at the end of the "try" part.
Whaaaa....? No. Shirley that wont work like a finally block.
|
|
|
|
|
Not guaranteed to execute indeed. Would it help to nest to try's, and check whether an error occured?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Not guaranteed to execute indeed
Well thats kinda the point of a finally block.
|
|
|
|
|
Hi All,
I have created two databases Test1 and Test2. For one database(Test1) I got the latest version of the code, created the deployement script and deployed the changes to the db.For the other database(Test2) I need to run the deployment script which got created form the Test1 DB.I tried to run the script but my db is not supporting..I get the following errors..
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ':'.
Msg 50000, Level 16, State 127, Line 4
You cannot deploy this update script to target INP-xx. The database for which this script was built, Test2, does not exist on this server.
Msg 911, Level 16, State 1, Line 1
Database '$(DatabaseName)' does not exist. Make sure that the name is entered correctly.
Can Some one please help me out
modified 27-Sep-12 3:06am.
|
|
|
|
|
sindhuan wrote: Can Some one please help me out
So there are errors for the first four lines of code? Please show us the first four or five lines of code. Otherwise it's hard to help you as we don't know what you do. 
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
ya..I have given the code below
/*
Deployment script for Test2
*/
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
:setvar DatabaseName "Test2"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
GO
:on error exit
GO
USE [master]
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
RETURN
END
GO
IF NOT EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)')
BEGIN
RAISERROR(N'You cannot deploy this update script to target INP-X. The database for which this script was built,Test2, does not exist on this server.', 16, 127) WITH NOWAIT
RETURN
END
GO
|
|
|
|
|
sindhuan wrote: :setvar DatabaseName "Test2"
Well you are not in SQL-Commandmode, I bet. Change it in the MS Sql Server Management Studio. Go to Query->SQLCMD-Mode
Better way to change (in my opinion):
SET @DatabaseName VARCHAR(32) = 'Test'
...
IF (DB_ID(N'$(@DatabaseName)') IS NOT NULL
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
You need to run this script in SQLCMD mode.
Goto the menu, click Query > SQLCMD Mode
Now you will be able to run your script.
|
|
|
|
|
What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??
|
|
|
|
|
sindhuan wrote: What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??
Well, give it a try! 
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
sindhuan wrote: What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??
You could ask Google what this "CMD MODE" is, what it does, and where it lives. Try to get to know the beast, as opposed to "try and see if it works".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
You don't need to copy/paste.
Just open the script in SSMS, then click Query>CMD Mode, then click run.
|
|
|
|
|
hi,
I have the following sql.
select
'00' + 'TESTIN' + '634' + SPACE(56) + '000001' + CHAR(13) + char(10) +
'1'
I need the 1 on the next line, as I have specified CHAR(13) + char(10). But it does show that way, it still appears on the same line.
Any help is appreciated.
|
|
|
|
|
So where exactly are you running this?
In other words where exactly do you expect this "next line" to show up?
|
|
|
|
|
Thank you.
Sql server 2005
|
|
|
|
|
vanikanc wrote: Sql server 2005
SQL Server is a database server.
The end of line character means nothing in the database.
There is no way to 'display' it.
So that is not how you are using it.
Best I can suppose is that you are running the Microsoft SQL Server Management Studio and it show 'rows' and columns and your value is thus in a row and in a column and it will not display it any other way.
|
|
|
|
|
Hi
char(10) and char(13) will work only when results to text..
it won't work if the results are displayed in grid
|
|
|
|
|
vanikanc wrote: select
'00' + 'TESTIN' + '634' + SPACE(56) + '000001' + CHAR(13) + char(10) +
'1'
In my eyes this is abuse of SQL. Such things should be done with the software not in a SQL-Script.
If you really need to, place to SELECT Statements:
SELECT '00' + 'TESTIN' + '634' + SPACE(56) + '000001';
SELECT 1;
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
|
Greetings,
I have 3 tables respectively called "Employee_Personal_Info", "Employee_Job_Info" and "Attend_and_Leave_Of_Staff". The Last table contains a log from the fingerprint device and its columns are [Record Number], [Machine ID], [Employee ID], [In/Out Mode] which contains only 0 for attend and 1 for leave, [Verify Mode] which contains only 1 for fingerprint and 15 for face, [Date] and [Time]
Columns concern me here within those tables are
1- Employee_Personal_Info: [Employee ID], [First Name], [Middle Name], [Last Name] and [Family Name]
2- Employee_Job_Info: [Job title], [Shift ID]
3- Attend_And_Leave_Of_Staff: [Date], [Time]
We have 2 work shifts one that starts and ends in the same date begin at 8:00 AM and ends at 4:00 PM and the other begin at 6:30 PM and ends on the next day at 8:00 AM.
I need to create a SQL query that returns me all employees registered on a specific work shift with their attend and leave time. That is it when selecting a specific type of shift the query returns me all the employees registered in such shift and beside each one his attend/leave date/time and returns null if one of the date/time is not found and null for both if there is no attendance data for him. I know it may be a complex query specially when dealing with employees registered in the second shift that starts at a day and ends on another one. I will be grateful for any ideas or any help
|
|
|
|
|
I don't know if I understand what output you wanted, but I tried 
I pretend that the ist the EmployeeID in every table, otherwise you can't join data (and you can't know who belongs to the data). There are other columns missing, too. Look at Attend_And_Leave_Of_Staff - where do you know, to which shiftId the date and time belongs? Just a date and a time column doesn't make sense to me.
SELECT a.FirstName, a.FamilyName, b.JobTitle, c.???
FROM Employee_Personal_Info a INNER JOIN Employee_Job_Info b ON a.EmployeeID = b.EmployeeID
LEFT JOIN Attend_And_Leave_Of_Staff c ON a.EmployeeID = c.EmployeeID AND b.ShiftId = c.ShiftId
WHERE b.ShiftId = @shiftIdToLookUp
I can't go further because of missing data, but that
I changed the second join to LEFT JOIN thanks to Andrei Straut.
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
modified 26-Sep-12 3:18am.
|
|
|
|
|
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
He said he wanted to see the null values for when records are not found, and your inner join will simply disregard (not display at all) the rows that have no correspondence.
EDIT: I've upvoted your solution, as it seemed to be what the OP wanted.
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
modified 26-Sep-12 3:14am.
|
|
|
|
|
Andrei Straut wrote: Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?
Of course! You are absolutely right! Thanks!
------------------------------
Author of Primary ROleplaying SysTem
How do I take my coffee? Black as midnight on a moonless night.
War doesn't determine who's right. War determines who's left.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
