Database

Ingo26-Sep-12 21:10

26-Sep-12 21:10

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. Roll eyes | :rolleyes:

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

sindhuan26-Sep-12 21:21

sindhuan

26-Sep-12 21:21

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

Ingo26-Sep-12 21:44

26-Sep-12 21:44

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

SQL

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.

J4amieC26-Sep-12 21:46

J4amieC

26-Sep-12 21:46

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.

sindhuan26-Sep-12 23:30

sindhuan

26-Sep-12 23:30

What am I supposed to do...go to Query->SQLCMD MODE then paste the script and click on execute??

Ingo27-Sep-12 0:07

27-Sep-12 0:07

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! $Shucks | :-\$

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

Eddy Vluggen27-Sep-12 0:22

Eddy Vluggen

27-Sep-12 0:22

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 Suspicious | :suss:

if you can't read my code, try converting it here[^]

Line line is a select statement.

J4amieC27-Sep-12 1:06

J4amieC

27-Sep-12 1:06

You don't need to copy/paste.

Just open the script in SSMS, then click Query>CMD Mode, then click run.

vanikanc26-Sep-12 8:22

vanikanc

26-Sep-12 8:22

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.

jschell26-Sep-12 8:37

jschell

26-Sep-12 8:37

So where exactly are you running this?

In other words where exactly do you expect this "next line" to show up?

vanikanc26-Sep-12 8:39

vanikanc

26-Sep-12 8:39

Thank you.

Sql server 2005

jschell27-Sep-12 9:57

jschell

27-Sep-12 9:57

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.

Niju126-Sep-12 22:28

Niju1

26-Sep-12 22:28

Hi
char(10) and char(13) will work only when results to text..

it won't work if the results are displayed in grid

Ingo26-Sep-12 23:04

Attendance and leave of employees

26-Sep-12 23:04

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:

SQL

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.

Amr Muhammed25-Sep-12 13:06

Amr Muhammed

25-Sep-12 13:06

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

Ingo25-Sep-12 20:50

25-Sep-12 20:50

I don't know if I understand what output you wanted, but I tried Smile | :)

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.

Andrei Straut25-Sep-12 21:03

Andrei Straut

25-Sep-12 21:03

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.

Ingo25-Sep-12 21:19

25-Sep-12 21:19

Andrei Straut wrote:
Maybe you should go with a LEFT JOIN on Attend_And_Leave_Of_Staff?

Of course! You are absolutely right! Thanks! Roll eyes | :rolleyes:

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

Convert datetime to certain format

Andrei Straut25-Sep-12 21:20

Andrei Straut

25-Sep-12 21:20

You're welcome Smile | :)

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.

vanikanc25-Sep-12 9:09

vanikanc

25-Sep-12 9:09

Hello All,

I need to convert this value of datetime
2012-09-16T20:08:30Z to this format "mmddyy".

I tried
select convert(char(10),'2012-09-16T20:08:30Z',101) - but this did not work.

Is there any other method to try before trying to write a whole bunch of casts, replaces!

Re: Convert datetime to certain format

Ingo25-Sep-12 20:58