How to change license type from Per Proc to CAL ?

We had a production server installed with a Per Processor license by mistake
and need to change it to 'per seat' ie CAL. Is there a way to do this without
reinstalling SQL on the server (as it is a production server now) ? The
'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
and only allows to change the number of processor licenses.
Thanks.Hi
"Pranil" wrote:
> We had a production server installed with a Per Processor license by mistake
> and need to change it to 'per seat' ie CAL. Is there a way to do this without
> reinstalling SQL on the server (as it is a production server now) ? The
> 'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
> and only allows to change the number of processor licenses.
> Thanks.
With a production server it is better to be safe as downtime could be
costly, but you could try http://tinyurl.com/25rvyo
John|||Awesome - thanks!
"John Bell" wrote:
> Hi
> "Pranil" wrote:
> > We had a production server installed with a Per Processor license by mistake
> > and need to change it to 'per seat' ie CAL. Is there a way to do this without
> > reinstalling SQL on the server (as it is a production server now) ? The
> > 'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
> > and only allows to change the number of processor licenses.
> >
> > Thanks.
> With a production server it is better to be safe as downtime could be
> costly, but you could try http://tinyurl.com/25rvyo
> John

How to change license type from Per Proc to CAL ?

We had a production server installed with a Per Processor license by mistake
and need to change it to 'per seat' ie CAL. Is there a way to do this withou
t
reinstalling SQL on the server (as it is a production server now) ? The
'Choose Licensing Mode' in control panel has the 'per seat' option grayed ou
t
and only allows to change the number of processor licenses.
Thanks.Hi
"Pranil" wrote:

> We had a production server installed with a Per Processor license by mista
ke
> and need to change it to 'per seat' ie CAL. Is there a way to do this with
out
> reinstalling SQL on the server (as it is a production server now) ? The
> 'Choose Licensing Mode' in control panel has the 'per seat' option grayed
out
> and only allows to change the number of processor licenses.
> Thanks.
With a production server it is better to be safe as downtime could be
costly, but you could try http://tinyurl.com/25rvyo
John|||Awesome - thanks!
"John Bell" wrote:

> Hi
> "Pranil" wrote:
>
> With a production server it is better to be safe as downtime could be
> costly, but you could try http://tinyurl.com/25rvyo
> John

How to change license type from Per Proc to CAL ?

We had a production server installed with a Per Processor license by mistake
and need to change it to 'per seat' ie CAL. Is there a way to do this without
reinstalling SQL on the server (as it is a production server now) ? The
'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
and only allows to change the number of processor licenses.
Thanks.
Hi
"Pranil" wrote:

> We had a production server installed with a Per Processor license by mistake
> and need to change it to 'per seat' ie CAL. Is there a way to do this without
> reinstalling SQL on the server (as it is a production server now) ? The
> 'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
> and only allows to change the number of processor licenses.
> Thanks.
With a production server it is better to be safe as downtime could be
costly, but you could try http://tinyurl.com/25rvyo
John
|||Awesome - thanks!
"John Bell" wrote:

> Hi
> "Pranil" wrote:
>
> With a production server it is better to be safe as downtime could be
> costly, but you could try http://tinyurl.com/25rvyo
> John
sql

how to call webservice from tsql?

Hello!

From an stored proc I want to call an existing web-service.

How to do that?

Must it be done using CLR-procs or is there some way in TSQL?

Greetings

Bjorn

Are you really need this...

CLR is one of the best way to invoke your web service. But i never tested it.

If you use sql server 2000 then the MSXML2.XMLHTTP object & sp_OACreate sp is one of the solution for this requirement.

|||

Here the sample code,

Declare @.Object as Int;

Declare @.ResponseText as Varchar(8000);

Code Snippet

Exec sp_OACreate 'MSXML2.XMLHTTP', @.Object OUT;

Exec sp_OAMethod @.Object, 'open', NULL, 'get',

'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)

'false'

Exec sp_OAMethod @.Object, 'send'

Exec sp_OAMethod @.Object, 'responseText', @.ResponseText OUTPUT

Select @.ResponseText

Exec sp_OADestroy @.Object

|||

Hi!

Sorry not writing, that I'm using SS2005...

Makes this some difference?

/Bjorn

|||

The Query (given below) can be used in both versions...

For SQL Server 2005 you have to enable the "OLE Automation" option.

Start-> Programs - > Microsoft SQL Server 2005 -> Configuration Tools ->SQL Server Surface Area Configuration

Click the link Surface Area Configuration for Features

Expand the DB -> Database Engine

Select OLE Automation & Check the checkbox Enable OLE Automation..

After the settings completed execute the code...

|||

Hi!

I have to use post, but how do I get it to work?

I was told I should look at this:

POST /xyzproxy/alarminsert.asmx HTTP/1.1
Host: 192.168.1.111
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/TestWebService"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<TestWebService xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>

How do I send a request like that using TSQL?

/Bjorn

How to call proc on linked Oracle server

Sorry if this is very stupid question, but i've spent too long searching for the answer:

I have a linked Oracle server set up for RPC in SQL server 9 db. How do I call it? I've tried this, but gives Unspecified error:

"

OLE DB provider "OraOLEDB.Oracle" for linked server "SANSORA1" returned message "Unspecified error".

"

From this:

declare @.UserName char(20) -- Current Username

declare @.Password varchar(20) -- Current Password

declare @.PasswordNew varchar(20) -- New Password

declare @.PasswordCfm varchar(20) -- Confirm New Password

set @.UserName = '900878'

set @.Password = '900878'

set @.PasswordNew = '777'

set @.PasswordCfm = '777'

declare @.return int

exec SANSORA1..PCG.USR_CHANGEPASSWORD @.UserName ,@.Password ,@.PasswordNew ,@.PasswordCfm;

--Exec ( 'SANSORA1..PCG.USR_CHANGEPASSWORD (' + @.UserName + ' ,' + @.Password + ' ,' + @.PasswordNew + ' ,' + @.PasswordCfm + ')')

You would need to create a package wrapper and use openquery().

Courtesy of Umachandar:
http://tinyurl.com/7dxrr|||

Thanks, found a solution:

declare @.sql varchar(3000)

set @.sql = 'CALL PCG.USR_CHANGEPASSWORD(''' + @.UserName + ''', ''' + @.Password + ''', ''' + @.PasswordNew + ''', ''' + @.PasswordCfm + ''')'

Exec (@.sql) at SANSORA1

How to call proc on linked Oracle server

Sorry if this is very stupid question, but i've spent too long searching for the answer:

I have a linked Oracle server set up for RPC in SQL server 9 db. How do I call it? I've tried this, but gives Unspecified error:

"

OLE DB provider "OraOLEDB.Oracle" for linked server "SANSORA1" returned message "Unspecified error".

"

From this:

declare @.UserName char(20) -- Current Username

declare @.Password varchar(20) -- Current Password

declare @.PasswordNew varchar(20) -- New Password

declare @.PasswordCfm varchar(20) -- Confirm New Password

set @.UserName = '900878'

set @.Password = '900878'

set @.PasswordNew = '777'

set @.PasswordCfm = '777'

declare @.return int

exec SANSORA1..PCG.USR_CHANGEPASSWORD @.UserName ,@.Password ,@.PasswordNew ,@.PasswordCfm;

--Exec ( 'SANSORA1..PCG.USR_CHANGEPASSWORD (' + @.UserName + ' ,' + @.Password + ' ,' + @.PasswordNew + ' ,' + @.PasswordCfm + ')')

Never mind, figured it out:

declare @.sql varchar(3000)

set @.sql = 'CALL PCG.USR_CHANGEPASSWORD(''' + @.UserName + ''', ''' + @.Password + ''', ''' + @.PasswordNew + ''', ''' + @.PasswordCfm + ''')'

Exec (@.sql) at SANSORA1

How to call Oracle Stored Procedure which has an output parameter from SSIS?

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

PROCEDURE Interface_Begin

(p_from_dttmOUT varchar2,

p_error_codeOUT number,

p_error_textOUTvarchar2,

p_proc_nameOUT varchar2);

please check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1711335&SiteID=1

http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html

-Nikul

|||

Could you please be more specific? I tried the following:

1) The stored proc spec is as follows:

Procedure testing(myDate OUT varchar2);

2) Created a Data Flow Task.

3) Created 2 variables called myStoredProc & myDate at the package level.

4) In the value for myDate variable i set it to myDate.

5) In the properties for myStoredProc variable i changed the EvaluateAsExpression property to True.

6) In the expression for myStoredProc variable i have the following:

"declare myDate varchar2(50); begin sa.testing(" + @.[User::myDate] + "); end;"

7) Now inside my Data Flow Task i have a Ole Db DataSource connection set to Native OLE DB\Microsoft Ole Db

Provider for Oracle.

8) Data Access Mode set to SQL Command from Variable.

9) The value of the Variable Name is User::myStoredProc.

10) Now when i hit preview i get the following error Message.

No disconnected record set is available for the specified SQL statement.

I am not sure what's wrong here. Could someone help me?

|||Anyone?|||Try doing this from a Execute SQL task, not a Source component. Source components expect a recordset, not an output parameter.|||

Hi there,

As Jwelch said use Execute SQL Task and select Single Row in Recordset option.

I think this will iron out your issue.

Please specify if this does not work.

Thanks

|||

Thanks guys. Now i am able to execute it successfully. But i am getting some junk characters in the Output parameters. Here is the stored proc definition:

create or replace procedure testing(myDate OUT varchar2)
IS
BEGIN
myDate := 'hey';
return;
END;

This should return 'hey' but i am getting this:


)

Any ideas?

How to call a stored proc?

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).
What would be the syntax for doing this within a UDF?
Thanks for any help!Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.
hope it was helpful
Shlomi
"megabyte" wrote:
> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
> expression that calls the stored proc (which would return a single
> value from a single field - a sum in this case).
> What would be the syntax for doing this within a UDF?
> Thanks for any help!
>|||Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).
Shlomi wrote:
> Hi
> it very similar with sending an sql statment.
> from the new dataset window choose command type=storsd procedure, and press
> OK.
> now you will get the stored procedures list. select the wanted SP and run
> the query.
> now, go back to the dataset window, you will find there your SP parameters.
> you can give some values to those parameters.
>
> hope it was helpful
> Shlomi
>
> "megabyte" wrote:
> > What is the syntax to call a SQL server stored procedure from a report?
> > Say if I wanted to create a custom field that would be based on an
> > expression that calls the stored proc (which would return a single
> > value from a single field - a sum in this case).
> >
> > What would be the syntax for doing this within a UDF?
> >
> > Thanks for any help!
> >
> >|||Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.
2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).
if you need the exact solution, i'll try to hel you.
Shlomi
"megabyte" wrote:
> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
> building the original datasets for the report - this stored proc needs
> to run during the execution of the report, for each record returned by
> one of the datasets - so really the best place for me to call it would
> be in the custom "code" section of the report, in a
> User-Defined-Function - do you know what the syntax would be for that?
> (this stored proc would return one single value, a sum).
>
> Shlomi wrote:
> > Hi
> > it very similar with sending an sql statment.
> > from the new dataset window choose command type=storsd procedure, and press
> > OK.
> > now you will get the stored procedures list. select the wanted SP and run
> > the query.
> > now, go back to the dataset window, you will find there your SP parameters.
> > you can give some values to those parameters.
> >
> >
> > hope it was helpful
> >
> > Shlomi
> >
> >
> > "megabyte" wrote:
> >
> > > What is the syntax to call a SQL server stored procedure from a report?
> > > Say if I wanted to create a custom field that would be based on an
> > > expression that calls the stored proc (which would return a single
> > > value from a single field - a sum in this case).
> > >
> > > What would be the syntax for doing this within a UDF?
> > >
> > > Thanks for any help!
> > >
> > >
>|||Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?
Shlomi wrote:
> Hi
> I can think about two ways to do this:
> 1. you can write a custom code for reading from DB, and call it from the
> development envionment.
> 2. you can retrive all data and write client function for dealing the
> aggregations. (i did dit once by retrive an XML and write some XML function
> to deal with the aggregation).
> if you need the exact solution, i'll try to hel you.
> Shlomi
> "megabyte" wrote:
> > Thanks for your reply - I think maybe I did not explain this well
> > enough: The place where I need to call the stored proc is not while
> > building the original datasets for the report - this stored proc needs
> > to run during the execution of the report, for each record returned by
> > one of the datasets - so really the best place for me to call it would
> > be in the custom "code" section of the report, in a
> > User-Defined-Function - do you know what the syntax would be for that?
> > (this stored proc would return one single value, a sum).
> >
> >
> > Shlomi wrote:
> > > Hi
> > > it very similar with sending an sql statment.
> > > from the new dataset window choose command type=storsd procedure, and press
> > > OK.
> > > now you will get the stored procedures list. select the wanted SP and run
> > > the query.
> > > now, go back to the dataset window, you will find there your SP parameters.
> > > you can give some values to those parameters.
> > >
> > >
> > > hope it was helpful
> > >
> > > Shlomi
> > >
> > >
> > > "megabyte" wrote:
> > >
> > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > Say if I wanted to create a custom field that would be based on an
> > > > expression that calls the stored proc (which would return a single
> > > > value from a single field - a sum in this case).
> > > >
> > > > What would be the syntax for doing this within a UDF?
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> >
> >|||Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.
"megabyte" wrote:
> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
> what I don't know how to do - it's VB.Net language, right? I have no
> idea how to do that - do you? Do you know of sample code out there?
>
> Shlomi wrote:
> > Hi
> > I can think about two ways to do this:
> > 1. you can write a custom code for reading from DB, and call it from the
> > development envionment.
> >
> > 2. you can retrive all data and write client function for dealing the
> > aggregations. (i did dit once by retrive an XML and write some XML function
> > to deal with the aggregation).
> >
> > if you need the exact solution, i'll try to hel you.
> >
> > Shlomi
> >
> > "megabyte" wrote:
> >
> > > Thanks for your reply - I think maybe I did not explain this well
> > > enough: The place where I need to call the stored proc is not while
> > > building the original datasets for the report - this stored proc needs
> > > to run during the execution of the report, for each record returned by
> > > one of the datasets - so really the best place for me to call it would
> > > be in the custom "code" section of the report, in a
> > > User-Defined-Function - do you know what the syntax would be for that?
> > > (this stored proc would return one single value, a sum).
> > >
> > >
> > > Shlomi wrote:
> > > > Hi
> > > > it very similar with sending an sql statment.
> > > > from the new dataset window choose command type=storsd procedure, and press
> > > > OK.
> > > > now you will get the stored procedures list. select the wanted SP and run
> > > > the query.
> > > > now, go back to the dataset window, you will find there your SP parameters.
> > > > you can give some values to those parameters.
> > > >
> > > >
> > > > hope it was helpful
> > > >
> > > > Shlomi
> > > >
> > > >
> > > > "megabyte" wrote:
> > > >
> > > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > > Say if I wanted to create a custom field that would be based on an
> > > > > expression that calls the stored proc (which would return a single
> > > > > value from a single field - a sum in this case).
> > > > >
> > > > > What would be the syntax for doing this within a UDF?
> > > > >
> > > > > Thanks for any help!
> > > > >
> > > > >
> > >
> > >
>

How to call a Store proc inside a view

Can anyone tell me how to call a sp inside a view.I dont think you can do that
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
> Can anyone tell me how to call a sp inside a view.
>
>|||You will need to set-up a linked server (LOCALHOST), and then...
CREATE VIEW vw_Test
AS
SELECT *
FROM OPENQUERY(LOCALHOST, 'SET FMTONLY OFF; EXEC pubs..Your_SP')
However, the SP is actually executed twice, so there is a performance
cost. If the SP performs updates/inserts, these are also performed
twice which is probably not what you want.|||You are right.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Pradeep Kutty" wrote:

> I dont think you can do that
> "Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
> news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
>
>|||However, if your SP doesn't create temp tables, you can do this (only
called once):
CREATE VIEW vw_Test
AS
SELECT *
FROM OPENQUERY(LOCALHOST, 'EXEC pubs..Your_SP')|||Not easily. You are asking the wrong question really. Call your view
from a proc if you need to, not the other way around. Maybe you could
explain just why you want to do this.
David Portas
SQL Server MVP
--|||I have 10 tables with common fields(splitted 1 into 10 due to easy access of
data). I want to make a view of it by combining all the tables using union
all.
From that view I will make a self join and do my operations. I am ok with
all these tasks. But my worry is if a new table is being added tomorrow I
have to hard code the 11th table in my view. I also have a log of how many
tables are added in a common table. So I want the view to autmatically add
the new table to the view with the help of the log table , which I will be
able to do it in an SP.
"David Portas" wrote:

> Not easily. You are asking the wrong question really. Call your view
> from a proc if you need to, not the other way around. Maybe you could
> explain just why you want to do this.
> --
> David Portas
> SQL Server MVP
> --
>|||Surely, you would know when the table is being added. You could do the
ALTER VIEW at that time and nothing else is required.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:19BD2C7D-D08C-4A5B-ABBE-472651BBE8B2@.microsoft.com...
I have 10 tables with common fields(splitted 1 into 10 due to easy access of
data). I want to make a view of it by combining all the tables using union
all.
From that view I will make a self join and do my operations. I am ok with
all these tasks. But my worry is if a new table is being added tomorrow I
have to hard code the 11th table in my view. I also have a log of how many
tables are added in a common table. So I want the view to autmatically add
the new table to the view with the help of the log table , which I will be
able to do it in an SP.
"David Portas" wrote:

> Not easily. You are asking the wrong question really. Call your view
> from a proc if you need to, not the other way around. Maybe you could
> explain just why you want to do this.
> --
> David Portas
> SQL Server MVP
> --
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:543962

> I have 10 tables with common fields(splitted 1 into 10 due to easy access
of
> data).
Your problem makes one thing clear: splitting tables with common
columns makes data HARDER to access not easier. By doing this you force
youself either to make lots of messy UNIONs or (worse) to use dynamic
SQL everywhere.
Splitting data across multiple identical tables is commonly understood
to be a big design error. In SQL2000 it makes sense in certain fairly
limited circumstances in support of partitioned views. If you are stuck
with this design for now then look up Partitioned Views in Books
Online. Basically you can create the view once and then reference it
everywhere as if it were a single table. Don't reference the base
tables.
David Portas
SQL Server MVP
--|||loopback query (though not really recommended due to perf penalty) would
allow you to..
e.g.
exec sp_serveroption 'srv','data access','true'
go
if object_id('_v','v') is not null
drop view _v
go
create view _v
as
select *
from openquery(srv,'set fmtonly off; exec sp_lock')x
go
select * from _v
go
-oj
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:05347F6A-25D7-44B0-BA89-F22495D1C417@.microsoft.com...
> Can anyone tell me how to call a sp inside a view.
>
>

how to calculate the least anmongst a list of Quarters

Hi,

i get a list of Quarters that look like following as an input to my proc.

'Q306/Q106/Q406/Q405'

I need to find out whihc one of the above is the least Quarter value.

In the above example it is 'Q405'

The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...

Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...

Can some one please help on this....

Hello,

There is no "quarter" datatype in sql server.

For your example, the parameter string will need to first be parsed (to split the quarters - you can use a recursive cte for this) then ordered. If you are using SQL 2005, you can also roll your own "quarter" datatype that could include ranking functions such as MIN/MAX etc.

What version of sql are you using?

Cheers

Rob

|||

Thanks for the reply...

I am using SQL Server2000. Can you please be more elaborate on this as I am a novice.what exactly is recursive cte...

Can you please send some sample code if possible...

Thanks...

|||

This function will help split up string with a delimited character. It was written in SQL 2005, but you should be able to change MAX to a length less than 8000 to make it work.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[SplitData] (@.Data VARCHAR(MAX), @.delimeter CHAR(1))

RETURNS @.v_Values TABLE ([Value] VARCHAR(MAX), [Position] BIGINT IDENTITY(1,1))

AS

BEGIN

DECLARE @.v_value VARCHAR(MAX),

@.v_pos BIGINT

SET @.v_Pos = 1

WHILE @.data > '' AND @.v_Pos > 0

BEGIN

SELECT @.v_pos = CHARINDEX(@.delimeter, @.data,1),

@.v_Value = CASE @.v_Pos

WHEN 0 THEN

@.data

ELSE

LEFT(@.data, @.v_pos-1)

END,

@.data = CASE

WHEN @.v_Value = @.data THEN

NULL

ELSE

SUBSTRING(@.data, @.v_pos + 1, LEN(@.data))

END

INSERT INTO @.v_Values ([Value])

VALUES (@.v_Value)

END

return

END

|||

I couldnt really understand how to use the above function...

Is it possible to get the seperate values by splitting the following data

'Q106/Q206/Q306 Version1/Q406 Version2/Current'

I need to be able to spilt the above Data...the delimiter is '/'...

is there something like a spilt function in Sql Server2000....

Please get back.

Thanks

Swapna

|||

The split function is the function I posted. The first parameter is the value and the second parameter is the delimiter.

SELECT Value

FROM dbo.SplitData('Q106/Q206/Q306 Version1/Q406 Version2/Current','/')

Hope this helps...

|||

Here is the version for 2000.

Run it with this:

SELECT * FROM dbo.SplitData('Q106/Q206/Q306 Version1/Q406 Version2/Current','/')

/*

Results:

Value Position

Q106 1

Q206 2

Q306 Version1 3

Q406 Version2 4

Current 5

*/

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[SplitData] (@.Data VARCHAR(8000), @.delimeter CHAR(1))

RETURNS @.v_Values TABLE ([Value] VARCHAR(1000), [Position] BIGINT IDENTITY(1,1))

AS

BEGIN

DECLARE @.v_value VARCHAR(1000),

@.v_pos BIGINT

SET @.v_Pos = 1

WHILE @.data > '' AND @.v_Pos > 0

BEGIN

SELECT @.v_pos = CHARINDEX(@.delimeter, @.data,1),

@.v_Value = CASE @.v_Pos

WHEN 0 THEN

@.data

ELSE

LEFT(@.data, @.v_pos-1)

END,

@.data = CASE

WHEN @.v_Value = @.data THEN

NULL

ELSE

SUBSTRING(@.data, @.v_pos + 1, LEN(@.data))

END

INSERT INTO @.v_Values ([Value])

VALUES (@.v_Value)

END

return

END

GO

|||

Thanks a lot....this almost saved my project...

Thanks