Question with SQL String

I have this code below to parse the string ''00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'' into Multiple columns.

Here is the code, but the code is splititng the columns incorrectly.

I need it to appear as

col1 col2 col3 col4 col5
00120212 pendin mod pen ria te 3/6/2007 3:51:49 pm
Can someone pl assist with this code below.

-

DECLARE @.str varchar(8000)
SET @.str = '00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'

DECLARE @.columns TABLE (
col1 varchar(8000)
,col2 varchar(8000)
,col3 varchar(8000)
)

SET @.str = LTrim(RTrim(@.str))

DECLARE @.col1 int
,@.col2 int
,@.col3 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)

INSERT INTO @.columns
VALUES (
SubString(@.str, 2, @.col1 - 2)
,SubString(@.str, @.col1 + 3, Len(@.str) - @.col2 - 4)
,SubString(@.str, @.col2 + 3, Len(@.str) - @.col3 - 1)
)

SELECT * FROM @.columns

You may find Jen Suessmeyer's Split function to be very useful for this situation.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

Please check out the link below:

http://www.sommarskog.se/arrays-in-sql.html

It contains few TVFs that can be used to split a string based on a delimiter. You can use it along with PIVOT for example to get the individual values easily like:

SELECT p.[1], p.[2], p.[3], p.[4], p.[5]

FROM split_str(@.str, '~') AS t

PIVOT (min(t.value) for t.idx in ([1], [2], [3], [4], [5])) as p

Alternatively, you can do these type of operations easily on the client side and send the values individually. This way you can use SQL for what it is supposed to do.

|||

This is by code below, Delimiter is ~. I need to split the values, into multilple field , based on the ~ being the delimiter. How do I loop throu this string, until the end of the string, and then split it one - by-one into multiple fileds? PL ADVISE?

Declare @.Str Varchar(1000),@.I Int

set @.str='0001232~PENDING~MOD PENDING~Trad Jane~3/29/2007 5:03:30 PM~0001232~PENDING~MODIFICATION PENDING~ Jane Delder~3/29/2007 5:05:06 PM~0001232~PENDING~Approved~ Mon Savy~3/29/2007 5:05:27 PM~0001232~PEND'
SET @.str = LTrim(RTrim(@.str))

DECLARE @.columns TABLE (
LoanNum varchar(8000)
,ConvertedFromStatus varchar(8000)
,ConvertedToStatus varchar(8000)
,ConvertedName varchar(8000)
,StatusChangedDate text
)

begin
Begin
DECLARE @.col1 int
,@.col2 int
,@.col3 int,
@.col4 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)
SET @.col4 = CharIndex('~', @.str, @.col3 + 1)
--print @.col1
--print @.col2
--print @.col3
--print @.col4

INSERT INTO @.columns
VALUES (
Left(@.Str, @.Col1 - 1),
SubString(@.Str, @.Col1 + 1, @.col2 - @.Col1 - 1),
SubString(@.Str, @.Col2 + 1, @.col3 - @.Col2 - 1),
SubString(@.Str, @.Col3 + 1, @.col4 - @.Col3 - 1),
SubString(@.Str, @.Col4 + 1, @.col4 )
)
End

--Select @.I = 0

End
SELECT * FROM @.columns

|||Does the Split function I previously posted for you not work properly?

Question Regarding Stored Procedure??OUTPUTS

I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure....

CREATE procedure dbo.IDXAppt_Settings_NET
(
@.SQLADD nvarchar(15)Output,
@.SQLDatabase nvarchar(20)Output,
@.SQLLogin nvarchar(20)Output,
@.SQLPass nvarchar(20)Output
)
as
select
@.SQLADD=SQLAddress,
@.SQLDatabase=SQLDatabase,
@.SQLLogin=SQLLogin,
@.SQLPass=SQLPassword

from
Clinic_Settings

GO

Here is the Vb.Net Code......
To retrieve the elements that does not give me an error just gives me no data...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand

Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter

Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String

cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@.SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@.SQLData").Value

parmLogin = cmdsql.Parameters.Add("@.SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@.SQLLogin").Value

parmSqlPass = cmdsql.Parameters.Add("@.SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@.SQLPass").Value

parmSQLAddress = cmdsql.Parameters.Add("@.SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@.SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()

Label1.Text = strtest
End SubYou may get the output value after the stored procedure executed.|||Doesn't look like you want to use OUTPUTs here. You're query will get every row back from the table, so only the last (or is it the first - last I think) row will go into your output params.

question on tasks

hi,

if a package fails on error, would i be able to know code wise which particular task failed?
also, if the task is using a connection manager, how will i be able to access that particular connection object?

If you turn on logging then you will know what failed. Does that not suffice?

-Jamie

|||

i'm not sure if this answers you questions, however...

you could execute the package programatically and trap for a DtsTaskException that may be generated. then, you can determine which task caused the error by checking the subcomponent property of the dtserror object.

i believe that you should already know which connection manager a task uses because (as far as i know) this property cannot be set dynamically.

|||

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

|||

Ranier wrote:

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

do you really want to do this? why don't you just execute the package within a transaction and then re-execute it if the package fails?|||

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

|||

Ranier wrote:

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

it may be possible, but unnecessary.

if i understand you correctly, you want to re-execute a task if it fails. why do you want to do this? wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it?

|||

the component that i want to make should be on a script task. i need to be able to reuse it on every package just drag and drop.

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

|||

Ranier wrote:

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it? you can use transactions and checkpoints to accomplish this.

Question on MTS and SQL Server

Hi
I have a stored procedure which gets executed from ASP code, the ASP in turn
uses MTS . the problem is, when the stored procedure encounters a rollback
statement, instead of just sending the error message and then rolling back
the transaction, the application aborts with a SQL Server error message and
the transaction gets rolled back. It does not send the user defined error
message
to the application. if i remove the rollback statement from the stored
procedure and then run the application, the proper error message is displaye
d
. Is there any ways i can skip the SQL Server error messages or any idea o
n
how MTS handles the rollbacks and transactions ?
Thanks
RodgerWhat are the error messages? Can you post the procedure?
ML
http://milambda.blogspot.com/|||These are sql server error messages. when the same stored proc is executed
from query analyser the proc works fine and even the rollback is ok.
8519 Current MSDTC transaction must be committed by remote client.
8525 Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
her's the code which i execute and the error message which i get. Remember
i get the error message on my front end which is ASP.NET. The stored
procedure gets executed from the ASp code
Let me know if you have any questions
First Step :
If i execute the first piece of code, the table nfi_stage always has a
value, so the commit tran is sucessfull. Now if i change the code and put a
rollback tran (second piece of code ) instead of a commit tran
the application aborts giving the following error
Error : 6401
Cannot roll back %.*ls. No transaction or savepoint of that name was found.
First Code
begin distributed tran tran_outer
if (select count(*) from nfi_stage ) > 0
begin
delete nfi_stage
if @.@.trancount = 2
begin
commit tran tran_outer
--set @.retcode = 10087
return 10087
end
end
Change code with rollback
begin distributed tran tran_outer
if (select count(*) from nfi_stage ) > 0
begin
delete nfi_stage
if @.@.trancount = 2
begin
rollback tran tran_outer
--set @.retcode = 10087
return 10087
end
end
"ML" wrote:

> What are the error messages? Can you post the procedure?
>
> ML
> --
> http://milambda.blogspot.com/|||Nested transactions can be a pain. Look at the example in Books Online on ho
w
to "append" transactions to existing transactions:
http://msdn2.microsoft.com/en-us/library/ms188378.aspx
A few pointers:
1) check in each procedure where you intend to use explicit transactions,
whether an outer transaction has already begun;
2) if already in transaction create a save point (SAVE TRAN <save point
name> );
3) on errors rollback to the save point (ROLLBACK TRAN <save point name> );
4) only commit a transaction if it was started in the current procedure.
ML
http://milambda.blogspot.com/|||Hi
I tried using the code for transaction existence, and it gives me a error
message no
627 which means i cannot save a distributed transaction. the fact is i do
not have a distributed transaction started. maybe MTS treats the transactio
n
as a distributed transaction. any other alternate to saving a transaction
"ML" wrote:

> Nested transactions can be a pain. Look at the example in Books Online on
how
> to "append" transactions to existing transactions:
> http://msdn2.microsoft.com/en-us/library/ms188378.aspx
> A few pointers:
> 1) check in each procedure where you intend to use explicit transactions,
> whether an outer transaction has already begun;
> 2) if already in transaction create a save point (SAVE TRAN <save point
> name> );
> 3) on errors rollback to the save point (ROLLBACK TRAN <save point name> );
> 4) only commit a transaction if it was started in the current procedure.
>
> ML
> --
> http://milambda.blogspot.com/|||In your example you've started a distributed transaction explicitly.
Maybe you should explain a bit more why you think you need nested
transactions.
ML
http://milambda.blogspot.com/|||Here's the problem
I execute a proc from my VB code, the app is a web base application using
VB, IIS and MTS. when the proc is executed from app and if the first
sstatement in the proc is
if @.@.trancount > 0
save transaction firsttran
the @.@.trancount value = 1
and it gives a sql server error message 627
if i replace the save transaction with a commit transaction
it gives a sql error message 8519
so the problem here is the moment i execute a stored proc there is already a
transaction present which is a distributed transaction, started by MTS. so
the counter is always 1. now if i start my transactions and give a rollback
the entire proc fails and just comes out. its a bit complicated to explain
but can send the stored proc its a big proc.
thanks
"ML" wrote:

> In your example you've started a distributed transaction explicitly.
> Maybe you should explain a bit more why you think you need nested
> transactions.
>
> ML
> --
> http://milambda.blogspot.com/|||"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:7D6214FF-DBFE-4EE3-B5A9-CBA834A2376B@.microsoft.com...
> Here's the problem
> I execute a proc from my VB code, the app is a web base application using
> VB, IIS and MTS. when the proc is executed from app and if the first
> sstatement in the proc is
> if @.@.trancount > 0
> save transaction firsttran
> the @.@.trancount value = 1
> and it gives a sql server error message 627
> if i replace the save transaction with a commit transaction
> it gives a sql error message 8519
> so the problem here is the moment i execute a stored proc there is already
> a
> transaction present which is a distributed transaction, started by MTS. so
> the counter is always 1. now if i start my transactions and give a
> rollback
> the entire proc fails and just comes out. its a bit complicated to
> explain
> but can send the stored proc its a big proc.
> thanks
>
You do realise that ROLLBACK is not nestable, and ROLLBACK will roll back
all pending nested transactions?
David|||Can you just turn off the MTS transaction and control the transaction in
your stored procedure or do you have a need to coordinate two different
components in the same distributed transaction with MTS.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23812F0LZGHA.4144@.TK2MSFTNGP04.phx.gbl...
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:7D6214FF-DBFE-4EE3-B5A9-CBA834A2376B@.microsoft.com...
> You do realise that ROLLBACK is not nestable, and ROLLBACK will roll back
> all pending nested transactions?
> David
>

Question on IFilters

Hi,
Are the Ifilters COM/CORBA objects that I could call from my code? I'm trying to find out whether I could call them in my java code to extract text from various document formats such as PDF/MS Office etc before storing them to the database. The document
s that we are looking for full text search are on the average 100Mb in size and I'm looking at ways to cut down the size before storing them in the SQL server database.
Appreciate your reply,
Anantha
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Anantha,
The best information source for this is the MSDN Platform SDK "Using Custom
Filters with Indexing Service" at:
http://msdn.microsoft.com/library/de...ufilt_912d.asp
Specifically, click on "Filter Samples" -> HtmlProp Sample:
http://msdn.microsoft.com/library/de...ufilt_0lwl.asp
This provides examples of how to "extract value-type properties. It converts
HTML meta properties to data types other than strings as specified by a
configuration file."
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Anantha Padmanabhan" <ananthapus@.hotmail.com> wrote in message
news:#l$5EyZ$EHA.3472@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Are the Ifilters COM/CORBA objects that I could call from my code? I'm
trying to find out whether I could call them in my java code to extract text
from various document formats such as PDF/MS Office etc before storing them
to the database. The documents that we are looking for full text search are
on the average 100Mb in size and I'm looking at ways to cut down the size
before storing them in the SQL server database.
> Appreciate your reply,
> Anantha
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||They are com objects, you can call them from code. Here is an example of how
to call them.
http://sqljunkies.com/HowTo/C4AC6E97...E63EC99B6.scuk
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anantha Padmanabhan" <ananthapus@.hotmail.com> wrote in message
news:%23l$5EyZ$EHA.3472@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Are the Ifilters COM/CORBA objects that I could call from my code? I'm
trying to find out whether I could call them in my java code to extract text
from various document formats such as PDF/MS Office etc before storing them
to the database. The documents that we are looking for full text search are
on the average 100Mb in size and I'm looking at ways to cut down the size
before storing them in the SQL server database.
> Appreciate your reply,
> Anantha
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||Thanks John/Hilary for your reply and links.
I have another question though. If I store only txt documents in the SQL server (all other docuemnts are converted into txt documents before storing them in the database) does the indexing service still use the filter (in this case the standard filter) t
o extract textual data for indexing purposes?
Anantha
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||yes, it uses the default or null iFilter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anantha Padmanabhan" <ananthapus@.hotmail.com> wrote in message
news:%23rnRWam$EHA.208@.TK2MSFTNGP12.phx.gbl...
> Thanks John/Hilary for your reply and links.
> I have another question though. If I store only txt documents in the SQL
server (all other docuemnts are converted into txt documents before storing
them in the database) does the indexing service still use the filter (in
this case the standard filter) to extract textual data for indexing
purposes?
> Anantha
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||You're welcome, Anantha,
Yes, it does. However, keep in mind how you import or insert the text can be
important as well as where you store the row text. You may want to consider
using TextCopy.exe that ships with SQL Server 2000.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Anantha Padmanabhan" <ananthapus@.hotmail.com> wrote in message
news:#rnRWam$EHA.208@.TK2MSFTNGP12.phx.gbl...
> Thanks John/Hilary for your reply and links.
> I have another question though. If I store only txt documents in the SQL
server (all other docuemnts are converted into txt documents before storing
them in the database) does the indexing service still use the filter (in
this case the standard filter) to extract textual data for indexing
purposes?
> Anantha
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Question on deploying .SQL files

Hi..

I would not like my customers to have access to any SQL code that is
being packaged with our product and hence would like to encrypt it in
some fashion. What's your recommendation on packaging/deploying .sql
files?

Would appreciate any feedback.

Thanks,
Sandeep Madduri.Sandeep Madduri's group (sandeepmadduri@.gmail.com) writes:

Quote:

Originally Posted by

I would not like my customers to have access to any SQL code that is
being packaged with our product and hence would like to encrypt it in
some fashion. What's your recommendation on packaging/deploying .sql
files?

First of all, keep in mind that while you can create a stored procedure
WITH ENCRYPTION, it is not real encryption, but merely obfustication.
Any users who is dead set will be able to recover the original code.
(Can easily be found on Google.) That is not to say that WITH ENCRYPTION
is useless. It does act like a sign saying "NO TRESPASSING" and will
keep honest people out. But you still need a license agreement.

As for the setup, you could ship the files in some obfusticated
fashion, and then have a program that knows how to "decrypt" the files
and then pass them to SQL Server unecrypted.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Question on Custom code assembly

Hi,
I have to build a report whose content is very complex and comes from
multiple tables. I think the best possible way is to write custom
code(assembly). Is it possible to return a data set using custom code
assembly and assign it to report ?
Please help.
-- Thanks
RKOn Oct 19, 11:19 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have to build a report whose content is very complex and comes from
> multiple tables. I think the best possible way is to write custom
> code(assembly). Is it possible to return a data set using custom code
> assembly and assign it to report ?
> Please help.
> -- Thanks
> RK
Hi,
What you could do is write a storeproc for your dataset that handles
all (or most of)
your complex table handling.
V.|||Hi,
Thanks for the reply. Stored Procedure is a very good idea. I have a small
doubt. Is it possible at all to have a return type other than primitive
type(for example an array of integers ) for a method in a custom code
assembly in SSRS ?
--
RK
"Vinnie" <vsempoux@.gmail.com> wrote in message
news:1192786497.261274.131220@.e34g2000pro.googlegroups.com...
> On Oct 19, 11:19 am, "S V Ramakrishna"
> <ramakrishna.seeth...@.translogicsys.com> wrote:
>> Hi,
>> I have to build a report whose content is very complex and comes from
>> multiple tables. I think the best possible way is to write custom
>> code(assembly). Is it possible to return a data set using custom code
>> assembly and assign it to report ?
>> Please help.
>> -- Thanks
>> RK
> Hi,
> What you could do is write a storeproc for your dataset that handles
> all (or most of)
> your complex table handling.
> V.
>

question on AqcuireConnection

Hi,

I created a function that supposedly checks a connection manager if a connection can be established.
When I was testing the code, I found out that the following code always returns "Successful" for flat files and SMTP connection even if the flat file does not even exist or there are no connection parameters for SMTP.

Can you tell me what to use to check if a connection can be established for all types of connection managers?

Here is the function that I created which does not seem to work as I expected:

Public Function connect(ByVal connMgr As Object) As String
Dim connected As String
Try
Dts.Connections(connMgr.name).AcquireConnection(Nothing)
Return "Successful"
Catch ex As Exception
Return "Failure"
Finally
Dts.Connections(connMgr.name).ReleaseConnection(Nothing)
End Try
End Function

Thanks.

AcquireConnection returns an object that represents the connection. You need to check this object. It will be different for each connection manager.

An ADO.NET connection manager will return an IDbConnection object.

The SMTP Connection manager simply returns the connection string of the connection manager.

There isn't a generic way to determine if a connection can be established for all types of connection managers.
|||

A flat file does not need to exist for AcquireConnection to be successful. It can point to a destination file to be created at the runtime.

Thanks.