Database

29-Sep-12 2:38

SQL Ed wrote:
I keep getting a syntax error

On which line?

I suggest you comment out blocks of sql, until you have a working version.

Bastard Programmer from Hell Suspicious | :suss:

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

Removing the updated row

sindhuan28-Sep-12 1:48

MySQL sql statement question

28-Sep-12 1:48

Hi All
I have merged data into a new table say table1 from table2(need to do this every time data comes into table2..Both the tables have following columns Id,Period(which includes date and time)Constraint(flag is only for table1)
Once the data is merged into table1 the data in table2 gets deleted automatically.when a new data comes into table2 it checks whether the period(only date not the time) in table1 and table2 matches..If it gets matched then we update the constraint flag of table1 to "1". then we add this new data from table2 to table1..Then I should remove the old data...what I wanted to do now is remove the data whose contraint flag is 1 from table1

I have given the code what I have done..Can some one help how to proceed

MERGE INTO table1 AS t1
USING table2 AS t2
ON t1.Id=t2.UsagePointId

WHEN MATCHED THEN
UPDATE SET
AND t1.period=t2.period
t1.constraint=0

WHEN NOT MATCHED THEN
INSERT (Id, period,constraint)
VALUES (t2.Id,t2.period, 0);

WITH x AS
(
SELECT t1.constraint
FROM table1 AS t1
JOIN table2 AS t2
ON t1.Id=t2.Id
AND t1.period =t2.period
)
UPDATE x
SET constraint=1;
WHERE t1.period AS DATE =upi.t2.period AS DATE

Jassim Rahma27-Sep-12 9:28

Jassim Rahma

27-Sep-12 9:28

I have the following statment:

SQL

SELECT * FROM member_channels WHERE member_channel_address = param_member_channel_address;

that's member_channels where I also have member_channel_id (primary key)

In member_posts, I have member_channel_id..

I want to count total posts linked to member_channel_id and I want to display it as count field in the above statement..

Re: MySQL sql statement question

jschell27-Sep-12 10:12

jschell

27-Sep-12 10:12

Something like the following

SELECT *, count(p.member_channel_id) FROM member_channels c, member_posts p
WHERE c.member_channel_address = param_member_channel_address
and c.id = p.member_channel_id

Re: MySQL sql statement question

Jassim Rahma27-Sep-12 11:08

Jassim Rahma

27-Sep-12 11:08

thanks

but there is one problem.

when member_post has no rows linked to the same member_channel_id, it will not return anything but I want it to return the details with zero count

Re: MySQL sql statement question

Eddy Vluggen29-Sep-12 2:42

Is there a try/finally construct in sqlcmd with -b option, guaranteed to execute the finally block?

29-Sep-12 2:42

jrahma wrote:
but there is one problem.

We call it a JOIN. Try Visual Representation of SQL Joins[^] Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

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

muhalet27-Sep-12 0:56

muhalet

27-Sep-12 0:56

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

Re: Is there a try/finally construct in sqlcmd with -b option, guaranteed to execute the finally block?

Eddy Vluggen27-Sep-12 1:42

Re: Is there a try/finally construct in sqlcmd with -b option, guaranteed to execute the finally block?

27-Sep-12 1:42

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

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

J4amieC27-Sep-12 2:51

Re: Is there a try/finally construct in sqlcmd with -b option, guaranteed to execute the finally block?

27-Sep-12 2:51

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.

Eddy Vluggen27-Sep-12 4:27

Re: Is there a try/finally construct in sqlcmd with -b option, guaranteed to execute the finally block?

27-Sep-12 4:27

Not guaranteed to execute indeed. Would it help to nest to try's, and check whether an error occured?

Bastard Programmer from Hell Suspicious | :suss:

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

J4amieC27-Sep-12 21:29

27-Sep-12 21:29

Eddy Vluggen wrote:
Not guaranteed to execute indeed

Well thats kinda the point of a finally block.

sql script

sindhuan26-Sep-12 20:08

26-Sep-12 20:08

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.

Ingo26-Sep-12 21:10

Ingo

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

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

Ingo

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

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

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

Ingo

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

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[^]

J4amieC27-Sep-12 1:06

Line line is a select statement.

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.