Having problems with Typed Xml

Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
1- I've created a "Business" Database
USE [Business]
GO
2- I've defined an Xml Schema Collection
create xml schema collection dbo.CustomerFile as
'<xs:schema xmlns="http://XmlSpaces.Tests"
targetNamespace="http://XmlSpaces.Tests"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="EntityType">
<xs:sequence />
<xs:attribute name="key" type="xs:string" />
<xs:attribute name="reference" type="xs:string" />
<xs:attribute name="creationDate" type="xs:dateTime" />
</xs:complexType>
<xs:simpleType name="CivilityCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Mr" />
<xs:enumeration value="Mm" />
<xs:enumeration value="Ml" />
</xs:restriction>
</xs:simpleType>
<xs:complexType name="CivilityType">
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="code" type="CivilityCodification" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:complexType name="NameType">
<xs:sequence>
<xs:element name="Civility" type="CivilityType" />
<xs:element name="First" type="xs:string" />
<xs:element name="Last" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="AddressType">
<xs:sequence>
<xs:element name="Street" type="xs:string" />
<xs:element name="Zip" type="xs:string" />
<xs:element name="Town" type="xs:string" />
</xs:sequence>
<xs:attribute name="code" type="AddressCodification" />
</xs:complexType>
<xs:simpleType name="AddressCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Main" />
<xs:enumeration value="Office" />
<xs:enumeration value="Home" />
</xs:restriction>
</xs:simpleType>
<xs:element name="Address" type="AddressType" />
<xs:complexType name="CustomerType">
<xs:complexContent mixed="false">
<xs:extension base="EntityType">
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="1" name="Name"
type="NameType" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:element name="Customer" type="CustomerType" />
</xs:schema>'
3- I've build a table that use this Schema definition
CREATE TABLE [dbo].[Customers]
(
[OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
[Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
(
[OID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
4- From what i understood the following statement should work but it doensn't
INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
'<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
xmlns="http://XmlSpaces.Tests">
<Name>
<Civility code="Mr">Mister</Civility>
<First>Brice</First>
<Last>Prunier</Last>
</Name>
<Address code="Main">
<Street>66 street A</Street>
<Zip>11111</Zip>
<Town>Seattle</Town>
</Address>
<Address code="Office">
<Street>33 Avenue B</Street>
<Zip>22222</Zip>
<Town>Los Angeles</Town>
</Address>
</Customer>' )
Trying the here above context raised the followings error
1- invalid format for Customer/@.creationDate
After switching xs:dateTime to xs:string i get :
Expected element : Name, http://XmlSpaces.Tests:Address where element
http://XmlSpaces.Tests:Name was specified
thanks for help
First issue:
In SQL Server 2005, we require a timezone on the value. So you either need
to change the type in the schema to xs:string (as you did) or add a timezone
to the value (e.g., append a Z). Also, in the April CTP (and June CTP) we do
not yet round to the millisecond precision but raise an error (we are
looking into changing that, although at this stage any functional change
needs lots of justification). So you should reduce the microseconds to
milliseconds.
Second issue:
You need to add
elementFormDefault="qualified"
to the xs:schema element.
Best regards
Michael
"Brice Prunier" <Brice Prunier@.discussions.microsoft.com> wrote in message
news:E28FD8F9-50CD-446D-8A0A-BB2775114B64@.microsoft.com...
> Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
> 1- I've created a "Business" Database
> USE [Business]
> GO
> 2- I've defined an Xml Schema Collection
> create xml schema collection dbo.CustomerFile as
> '<xs:schema xmlns="http://XmlSpaces.Tests"
> targetNamespace="http://XmlSpaces.Tests"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="EntityType">
> <xs:sequence />
> <xs:attribute name="key" type="xs:string" />
> <xs:attribute name="reference" type="xs:string" />
> <xs:attribute name="creationDate" type="xs:dateTime" />
> </xs:complexType>
> <xs:simpleType name="CivilityCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Mr" />
> <xs:enumeration value="Mm" />
> <xs:enumeration value="Ml" />
> </xs:restriction>
> </xs:simpleType>
> <xs:complexType name="CivilityType">
> <xs:simpleContent>
> <xs:extension base="xs:string">
> <xs:attribute name="code" type="CivilityCodification" />
> </xs:extension>
> </xs:simpleContent>
> </xs:complexType>
> <xs:complexType name="NameType">
> <xs:sequence>
> <xs:element name="Civility" type="CivilityType" />
> <xs:element name="First" type="xs:string" />
> <xs:element name="Last" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="AddressType">
> <xs:sequence>
> <xs:element name="Street" type="xs:string" />
> <xs:element name="Zip" type="xs:string" />
> <xs:element name="Town" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="code" type="AddressCodification" />
> </xs:complexType>
> <xs:simpleType name="AddressCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Main" />
> <xs:enumeration value="Office" />
> <xs:enumeration value="Home" />
> </xs:restriction>
> </xs:simpleType>
> <xs:element name="Address" type="AddressType" />
> <xs:complexType name="CustomerType">
> <xs:complexContent mixed="false">
> <xs:extension base="EntityType">
> <xs:sequence>
> <xs:element minOccurs="0" maxOccurs="1" name="Name"
> type="NameType" />
> <xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
> </xs:sequence>
> </xs:extension>
> </xs:complexContent>
> </xs:complexType>
> <xs:element name="Customer" type="CustomerType" />
> </xs:schema>'
> 3- I've build a table that use this Schema definition
> CREATE TABLE [dbo].[Customers]
> (
> [OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
> [Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> [XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
> CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
> (
> [OID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> 4- From what i understood the following statement should work but it
> doensn't
> INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
> '<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
> xmlns="http://XmlSpaces.Tests">
> <Name>
> <Civility code="Mr">Mister</Civility>
> <First>Brice</First>
> <Last>Prunier</Last>
> </Name>
> <Address code="Main">
> <Street>66 street A</Street>
> <Zip>11111</Zip>
> <Town>Seattle</Town>
> </Address>
> <Address code="Office">
> <Street>33 Avenue B</Street>
> <Zip>22222</Zip>
> <Town>Los Angeles</Town>
> </Address>
> </Customer>' )
> Trying the here above context raised the followings error
> 1- invalid format for Customer/@.creationDate
> After switching xs:dateTime to xs:string i get :
> Expected element : Name, http://XmlSpaces.Tests:Address where element
> http://XmlSpaces.Tests:Name was specified
> thanks for help
>

Having problems finding Sql server with VS 2005

I have upgraded to VS 2005 and using SQL server 2005 express. I have the SQL server up and running using the managment tools and have created a small simple database. I want to connect to it using the Server Explorer window in visual studio but it does not appear? I have had it sometimes but when i do I am unable to attach or select any databases that are there?

Can anyone tell me were i am going wrong? Is it because i have upgraded from an older version?
The server create and lets me add record to the tables i make, visual studio loads and i can create pages but making them talk is proving hard when it shouldnt. any help appreciated.

Nick

PS

the error i get is:

Could not find row in sysindexes for database ID 5, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'Friends_Data'. CREATE DATABASE is aborted. Could not attach database 'Friends_Data' to file 'c:\Documents and Settings\NBannister\My Documents\Visual Studio 2005\WebSites\Friends.MDF'.

The following article describes the procedure for attaching AW. You can use it as the template for attaching other databases.

http://msdn2.microsoft.com/en-us/library/ms310325.aspx

having difficulty inserting into the database table

I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code:
Dim Message As String
Dim connStr As String
Dim myConnection As SqlConnection
Dim mySqlCommand As SqlCommand
connStr = "server=SIMI\VSdotNET;Trusted_Connection=yes;database=AeroSea"
myConnection = New SqlConnection(connStr)
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)

If I execute the above code, then nothing gets updated. When I change the insert staement into the following,
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then,
value 1 for customerID field and myname in the namefield is added.
I know I am doing something stupid, but can't figure it out.
I am confused. please help me.One possibility is this (presuming textbox is name):

mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'" + name.text + "')", myConnection)

Better,use parameters, as the code above is subject to SQL Injection attacks, and as written will fail if the name.Text is "O'Reilly".|||Thanks, I used the parameters and it works fine.

Having a stored procedure copy tables & also preserve indexing/sch

Hello,
I created a stored procedure that renames a table to OLD_xxxxx and replaces
that table with another (copy) that resides on a different database. I pull
the
tablename names through the use of a cursor table and construct a SELECT INTO
statement as follows
'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
It works great especially since there are 80+ tables that need to be copied
from one database to another. The drawback is that it doesn't preserve the
indexing/foriegn key constraints. Is there a way to do this without having to
deal with DTS or creating additional scripts? Ideally I would like to
replace
the "SELECT * INTO" statement with something that not only does a copy but
also preserves the indexing! Does such a command exist? Any help from the
Microsoft guru's would be greatly appreciated!!!!
SELECT ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE,
FOREIGN KEY, CHECK, NOT NULL constraints and doesn't define DEFAULT and
IDENTITY column properties for the new table.
You will have to write seperate statements into your stored procedure to
create them.
--Vishal.
"Peter S." wrote:

> Hello,
> I created a stored procedure that renames a table to OLD_xxxxx and replaces
> that table with another (copy) that resides on a different database. I pull
> the
> tablename names through the use of a cursor table and construct a SELECT INTO
> statement as follows
> 'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
> It works great especially since there are 80+ tables that need to be copied
> from one database to another. The drawback is that it doesn't preserve the
> indexing/foriegn key constraints. Is there a way to do this without having to
> deal with DTS or creating additional scripts? Ideally I would like to
> replace
> the "SELECT * INTO" statement with something that not only does a copy but
> also preserves the indexing! Does such a command exist? Any help from the
> Microsoft guru's would be greatly appreciated!!!!
>

Havent been able to execute a package on the server

Hello, I created a package on my machine, it deletes some files, then delete some rows, then copy files from a destination to a source and then process all those files and insert rows in a table, really simple.

When I click execute in VS 2005 it executes perfectly. (it took about 45 seconds because there are many files)

I connected to integration services and imported the package then I did the two following things.

1-Right click run package and it executes normally but it took less than 1 second, and when I saw the destination folder there were no files in there so it didnt do anything)

2. I created a job and on the first step I put to execute that package. I then executed the job and the same thing happens, it executed without errors but it took less than 1 send and when I saw the destination folder there were no files in there so it didnt do anything).

I noticed that the Integration services project has a property for creating a deployment utility, I changed this property to true but I dont know how to make the deployment utility.

Maybe the problem was that when I Imported the package,, the package was on another machine on my LAN?

Can the package on the server even see the files? The directories have to be the same, and the user account for the SQL Server service must have rights to that directory as well.

When you execute it on your machine, it's using your user account and currently accessible folders. When the package is promoted to the server, you will be using a different account.|||enable package logging to get more details of the execution. The package may be failing...|||

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

|||

Luis Esteban Valencia Mu?oz wrote:

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Try using SQL Server Agent CmdExec job step sub-system so that Dtexec can be employed. This will cause more detailed error messages to be stored in the job history. It is also useful to implement the job step log.|||

Hi Luis,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey may be flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that is simplest to implement is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well. But this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

Have a query run at specific times

Please be patient I am a noobie
We have a Query created in Query Analyzer that we would like to run nightly.
How do we get this query to run on a schedule and update tables?
Hi
Wrong newsgroup. This is the group for data replication.
Look in BOL for "jobs".
If you want to use Enterprise Manager, you can create and schedule a job, it
is under SQL Server Agent.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mueller" <Mueller@.discussions.microsoft.com> wrote in message
news:BA6E0722-D733-4708-B7D6-A972125F3AB9@.microsoft.com...
> Please be patient I am a noobie
> We have a Query created in Query Analyzer that we would like to run
nightly.
> How do we get this query to run on a schedule and update tables?
|||What you need to create is a 'Job'. This is available under Management, SQL
Server Agent, Jobs and there are plenty of details n Books-On-Line (BOL).
Basically right click the jobs node, select new job and the rest is pretty
intuitive as long as you ensure the SQL Server Agent is started and
scheduled to restart on bootup. Please post back after giving it a go.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Handling wildcard characters in query string

Hi

First interaction to the forum.
My Query is :

I had a User Management module in my application where I created a user with name

`~!@.#$@.%^&*()[_]+|}{":?><-=\[[]];',./

Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.

How do I go about it as the SP i created for it gives correct results except the following

1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record

I handeled a few issues

1. replaced [ by [[]
2. replaced _ by [_]

So issues 2 & 3 are resolved.

Tried replacing % by [%] but did not work

Could someone plz help

Thanks in advance
AshutoshYou could search the string with CHARINDEX function instead of using LIKE.

HTH|||You could search the string with CHARINDEX function instead of using LIKE.

HTH

Sorry but did not get your point as how this will help me get through

Ashutosh|||What is your current where predicate?|||What is your current where predicate?

WHERE FName like '%`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./%'

In the front end code I handle it as input params come with %...% qualifiers and ' is replaced by ''

On the sql side i replace _ by [_] and [ by [[]

Ashutosh|||Why use LIKE? Is there a reason you can't search for the literal value?

WHERE FName = '`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./'
Otherwise, if you are searching for a string portion, use charindex
WHERE CHARINDEX('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', FName) >0
HTH

EDIT - you would need to remove the code adding the extra square brackets as you are searching for literals now.|||Why use LIKE? Is there a reason you can't search for the literal value?

WHERE FName = '`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./'
Otherwise, if you are searching for a string portion, use charindex
WHERE CHARINDEX('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', FName) >0
HTH

EDIT - you would need to remove the code adding the extra square brackets as you are searching for literals now.

well i am pretty much reluctant to change the sp and UDF for that

if i could somehow get around it just by some sort of replace statement etc, that would be nice :)

Ashutosh|||well i am pretty much reluctant to change the sp and UDF for that

if i could somehow get around it just by some sort of replace statement etc, that would be nice :)


AshutoshThere's another reason - sargability. LIKE '%something%' isn't an efficient search if you are looking for the exact string match rather than a portion. It appears that you are looking for one bodge to correct for another.|||There's another reason - sargability. LIKE '%something%' isn't an efficient search if you are looking for the exact string match rather than a portion. It appears that you are looking for one bodge to correct for another.

Probably i have gone the harder way

I created the function to check the same. If there is an exact match, it passes the parameter as string only else it pads the % characters into it

Ashutosh|||well i am pretty much reluctant to change the sp and UDF for that

if i could somehow get around it just by some sort of replace statement etc, that would be nice :)


AshutoshIf you aren't keen on CHARINDEX and you need to search for portions then perhaps:

WHERE REPLACE(REPLACE(FName, '%', 'Wild1'), '_', 'Wild2') LIKE REPLACE(REPLACE('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', '%', 'Wild1'), '_', 'Wild2')
It ain't exactly pretty though.|||If you aren't keen on CHARINDEX and you need to search for portions then perhaps:

WHERE REPLACE(REPLACE(FName, '%', 'Wild1'), '_', 'Wild2') LIKE REPLACE(REPLACE('`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./', '%', 'Wild1'), '_', 'Wild2')
It ain't exactly pretty though.

I di the same but in that case it also replaces the leading and trailing % which I have from the code itself and hense the criteria changes.

%%% --> should come as %[%]%

but it comes as [%][%][%]

Ashutosh|||This is an SP yes? So '%`~!@.#$%^&*()[_]+|}{":?><-=\[[]];'',./%' is actually passed as a parameter - like @.myParam?