How do I handle single quotes in data fields

I want to set company name in table company to "Don's Autoworks". When I run
a simple query:
update company
set company_name = 'Don''s Autoworks'
where id = 125
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 's'.
However, select queries with 2 single quotes like below work fine:
select * from company
where company_name = 'Mel''s Diner'
What should I do to insert or update data with single quotation marks in it.
Thanks,
Ashhad.>I want to set company name in table company to "Don's Autoworks". When I
>run
> a simple query:
> update company
> set company_name = 'Don''s Autoworks'
> where id = 125
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 's'.
This query should work. Where are you running it?
A|||Query Analyzer, SQL Server 2000, on Windows 2000
"Aaron Bertrand [SQL Server MVP]" wrote:

> This query should work. Where are you running it?
> A
>
>|||The query seems correct. Check it again
Madhivanansql

How do i getting many Rows from a single XML column?

using SQL 2005...

I have a table called training. It has 2 columns ID and data.
The ID column is a INT.
The data column is an XML data type that contains XML looking like the following:
<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005<ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>

if we asume that the table has 3 rows with the ID field being 1-3 and all the XML is the same as above.
I'm trying create an SQL statement that outputs the following results

id	ProgramName	UrgencyLevel
1	Learn SQL 2005	3
1	Learn To Walk	1
1	Learn To Eat	1
2	Learn SQL 2005	3
2	Learn To Walk	1
2	Learn To Eat	1
3	Learn SQL 2005	3
3	Learn To Walk	1
3	Learn To Eat	1

please can some one help me?

You will need to use the nodes() to break your xml into multiple rows then CROSS APPLY to that. For example:

Dan

CREATE TABLE #t1(
id int,
data xml
)

INSERT INTO #t1 SELECT 1, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

SELECT ID, t.c.value('ProgramName[1]', 'NVARCHAR(MAX)') + ' ' + t.c.value('UrgencyLevel[1]', 'NVARCHAR(MAX)')
FROM #t1
CROSS APPLY
#t1.data.nodes('TrainingRequests/TrainingRequest') AS t(c)

|||Thank you, you saved me ripping out the rest of my hair!!!

How do I get Management Studio to not reset the selected database to Master when I open a new qu

Hi there,

I often have to run multiple SQL scripts against a single database. SQL Server Management Studio always reverts the selected database to Master whenever I open a different SQL file. This requires me to re-select the correct database, and if I forget, I end up running the script against the Master database. SQL 2000's query analyzer "remembered" your selected database and did not force you to re-connect each time.

Is there any way to make SQL Server Management Studio act this way?

Thanks!

Kiron

Kiron,

You could set the default database for the sql login. so that the default DB will be selected automatically.

To set default databasefor a sql login, In Object Explorer, expand Security -> Logins

right click on login , properties, set the default database

After setting default DB for the SQL login, if you open up new query with the above mentioned login, you would see the default database selected

If you feel that the above solution does not meet your requirements, Please log a feature request in connect web site

https://connect.microsoft.com/SQLServer/Feedback

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Hi Sethu -

thanks for the response. Unfortunately, this will reset the default database, as opposed to having the new query session just "remember" the database that was selected for other open query sessions. Ideally, I would want the same behavior as in Query Analyzer from 2000 - you connect once to the database and from then on, all query windows are assumed to be related to the same database.

As part of my work, I often have to open multiple SQL scripts and execute them against the same database - the database is never the same, hence, the default database approach will merely shift the issue from "master" to some other database. What I would need is an option to tell Management Studio to pick the database that is used by all open query windows as the default when a new query window is opened vs. picking the user's default database.

I'll log a feature request as suggested!

Thanks again!

Kiron

How do I get Management Studio to not reset the selected database to Master when I open a ne

Hi there,

I often have to run multiple SQL scripts against a single database. SQL Server Management Studio always reverts the selected database to Master whenever I open a different SQL file. This requires me to re-select the correct database, and if I forget, I end up running the script against the Master database. SQL 2000's query analyzer "remembered" your selected database and did not force you to re-connect each time.

Is there any way to make SQL Server Management Studio act this way?

Thanks!

Kiron

Kiron,

You could set the default database for the sql login. so that the default DB will be selected automatically.

To set default databasefor a sql login, In Object Explorer, expand Security -> Logins

right click on login , properties, set the default database

After setting default DB for the SQL login, if you open up new query with the above mentioned login, you would see the default database selected

If you feel that the above solution does not meet your requirements, Please log a feature request in connect web site

https://connect.microsoft.com/SQLServer/Feedback

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Hi Sethu -

thanks for the response. Unfortunately, this will reset the default database, as opposed to having the new query session just "remember" the database that was selected for other open query sessions. Ideally, I would want the same behavior as in Query Analyzer from 2000 - you connect once to the database and from then on, all query windows are assumed to be related to the same database.

As part of my work, I often have to open multiple SQL scripts and execute them against the same database - the database is never the same, hence, the default database approach will merely shift the issue from "master" to some other database. What I would need is an option to tell Management Studio to pick the database that is used by all open query windows as the default when a new query window is opened vs. picking the user's default database.

I'll log a feature request as suggested!

Thanks again!

Kiron

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens