Count CHAR(11) in a string

Hi
I need to cound the number of CHAR(11) charactors in a string.
I am currently attempting to use: len(string) - len(replace(string,
CHAR(11), ''))
But it seems to return far too many
Any help would be much appreciated
Thanks
B> But it seems to return far too many
Can you give an example?
SELECT LEN('foo') - REPLACE(LEN('foo'), CHAR(11), '')
returns 0...|||select dbo.OCCURS2 (string, CHAR(11))
CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
@.cExpressionSearched nvarchar(4000))
returns smallint
as
begin
return
case
when datalength(@.cSearchExpression) > 0
then ( datalength(@.cExpressionSearched)
- datalength(replace(cast(@.cExpressionSear
ched as
nvarchar(4000)) COLLATE Latin1_General_BIN,
cast(@.cSearchExpression
as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
/ datalength(@.cSearchExpression)
else 0
end
end
GO
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wcon...e~2,54,33,27115
Please, download the file
http://www.universalthread.com/wcon...treme~2,2,27115
With the best regards,
Igor.
"Ben" wrote:

> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||hi
just try this:
its same as ur implementation:
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'
select len(@.ch) - len (replace(@.ch,char(11),''))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Ben" wrote:

> Hi
> I need to cound the number of CHAR(11) charactors in a string.
> I am currently attempting to use: len(string) - len(replace(string,
> CHAR(11), ''))
> But it seems to return far too many
> Any help would be much appreciated
> Thanks
> B
>
>|||Chandra,
Please correct me if I am wrong but your solution may only work for one
occurrence of char(11).
I tried the following and still got 1 instead of 2.
declare
@.ch varchar(10)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select len(@.ch) - len (replace(@.ch,char(11),''))
http://zulfiqar.typepad.com
BSEE, MCP
"Chandra" wrote:
> hi
> just try this:
> its same as ur implementation:
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'
> select len(@.ch) - len (replace(@.ch,char(11),''))
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Ben" wrote:
>|||Zulfiqar,
You've declared @.ch so that it can only hold 10 characters.
So the value you assigned to @.ch is truncated to
'ABC'+ CHAR(11) + 'DEFjh'
and this does in fact have only one char(11) value.
If you change the varchar(10) declaration to varchar(20) or larger,
you will get the result 2.
Steve Kass
Drew University
ZULFIQAR SYED wrote:

>Chandra,
>Please correct me if I am wrong but your solution may only work for one
>occurrence of char(11).
>I tried the following and still got 1 instead of 2.
>declare
>@.ch varchar(10)
>set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
>select len(@.ch) - len (replace(@.ch,char(11),''))
>
>|||Try:
declare
@.ch varchar(20)
set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
select '*' + @.ch + '*', len(@.ch), len (replace(@.ch,char(11),''))
Since @.ch was varchar(10):
ABCEFjhi
123456790
The char(11) fell off of the end.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ZULFIQAR SYED" <DRSQLnospam2005@.hotmail.com> wrote in message
news:C0F8C537-9633-4880-831F-1497766F6870@.microsoft.com...
> Chandra,
> Please correct me if I am wrong but your solution may only work for one
> occurrence of char(11).
> I tried the following and still got 1 instead of 2.
> declare
> @.ch varchar(10)
> set @.ch = 'ABC' + char(11) + 'DEF'+'jhi'+char(11)+'klm'
> select len(@.ch) - len (replace(@.ch,char(11),''))
> --
> http://zulfiqar.typepad.com
> BSEE, MCP
>
> "Chandra" wrote:
>|||Sounds like another case for repeating the "string or binary data would be
truncated" error on invalid variable assignments. I wonder how often this
happens in the real world and people have no idea they're losing data.
"Steve Kass" <skass@.drew.edu> wrote in message
news:u$k1WGfqFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Zulfiqar,
> You've declared @.ch so that it can only hold 10 characters.
> So the value you assigned to @.ch is truncated to
> 'ABC'+ CHAR(11) + 'DEFjh'
> and this does in fact have only one char(11) value.
> If you change the varchar(10) declaration to varchar(20) or larger,
> you will get the result 2.
> Steve Kass
> Drew University
> ZULFIQAR SYED wrote:
>|||Thank you everyone
Igor2004's solution worked perfectly.
Thanks again
B
"Igor2004" <Igor2004@.discussions.microsoft.com> wrote in message
news:D82A4BEE-4EC9-44DF-BD41-1A0E3D061EC3@.microsoft.com...
> select dbo.OCCURS2 (string, CHAR(11))
> CREATE function OCCURS2 (@.cSearchExpression nvarchar(4000),
> @.cExpressionSearched nvarchar(4000))
> returns smallint
> as
> begin
> return
> case
> when datalength(@.cSearchExpression) > 0
> then ( datalength(@.cExpressionSearched)
> - datalength(replace(cast(@.cExpressionSear
ched as
> nvarchar(4000)) COLLATE Latin1_General_BIN,
> cast(@.cSearchExpression
> as nvarchar(4000)) COLLATE Latin1_General_BIN, '')))
> / datalength(@.cSearchExpression)
> else 0
> end
> end
> GO
> For more information about string UDFs Transact-SQL please visit the
> http://www.universalthread.com/wcon...e~2,54,33,27115
> Please, download the file
> http://www.universalthread.com/wcon...treme~2,2,27115
> With the best regards,
> Igor.
>
> "Ben" wrote:
>

Could not obtain a DataReader object from the specified data flow component.

I am getting the following exception when attempting to read from a DataReaderDestination:

System.Exception was unhandled
Message="Could not obtain a DataReader object from the specified data flow component."
Source="Microsoft.SqlServer.Dts.DtsClient"
StackTrace:
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.internalPrepare(Boolean fReaderRequired)
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread()
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior)
at CA3DataImportTool.ViewSSISOutput.btnRun_Click(Object sender, EventArgs e) in C:\Documents and Settings\rhein\My Documents\Visual Studio 2005\Projects\CA3DataImportTool\CA3DataImportTool\ViewSSISOutput.cs:line 35
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at CA3DataImportTool.Program.Main() in C:\Documents and Settings\rhein\My Documents\Visual Studio 2005\Projects\CA3DataImportTool\CA3DataImportTool\Program.cs:line 18
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

If I use the example from SQL Server BOL (http://msdn2.microsoft.com/en-us/library/ms135917.aspx), and make a new package for the sample in the same project, the sample works. The only thing that I can see that is significantly different between my code and the sample is that my DataReaderDestination has a lot more data in it, but here's the relevant code:

string dtexecArgs;

string dataReaderName;

DtsConnection dtsConnection;

DtsCommand dtsCommand; //IDbCommand

IDataReader dtsDataReader;

DataTable dtsTable;

dtexecArgs = @."/FILE ""C:\Documents and Settings\rhein\My Documents\Visual Studio 2005\Projects\CA3DataImportTool\ML3000_IntegrationProject\Package.dtsx"" ";

dataReaderName = "DataReaderDest";

dtsConnection = new DtsConnection();

dtsConnection.ConnectionString = dtexecArgs;

dtsConnection.Open();

dtsCommand = new DtsCommand(dtsConnection);

dtsCommand.CommandText = dataReaderName;

dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default); // EXCEPTION HERE

Please help!

Richard Hein

I get this when I have the worng name, check the name of your data reader destination really is DataReaderDest.|||

Quadruple checked ... I tried changing the name, increasing the DtsCommand timeout to 30 seconds, and I added another Data Flow Task to the same package with a DataReaderDestination and it worked. Very strange ... I must be missing something!

|||I looked at the difference between the Data Flow Tasks, and after some tests, I found that Delay Validation = true causes this error. Changing it to false solved the problem.|||Ahh yes, that one is fun too. For completeness, the real error you get when you get the data reader name (CommandText) wrong is, "The specified data flow component was not found in the package."

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexes
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
Bob Sullentrup
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is from
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexe
s
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
--
Bob Sullentrup> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is fr
om
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in messag
e
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>