HELP with connection string PLEASEEEE!

hi, I'm kind of really new to this. Trying to learn asp.net 2.0. Have been designing a website, and, so far so good, I can deploy the files, but I can't get the synthax right for the connection string. in my web.config file the connection string works on the local computer, but I can't figure out how to change the data source part:
this is what I have in my web convig file
<add name="Database2ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Alexi\My Documents\Delivery\web\App_Data\Database2.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" /
the the wrox book says I have to have the|datadirectory|
does it mean I have to write the full http address on the server?
and I can't figure out what to substitute in for the ".\sqlexpress" part...

my host has ms sql server 2000 and my sql. I asked for their support and the gave me a weird looking connection string and claimed that it worked...

<%@. LANGUAGE = JScript %>
<% var oConn;
oConn = Server.CreateObject("ADODB.Connection");
oConn.Mode = 3
oConn.Open("Provider=SQLOLEDB;Server=203.89.181.78;Database=alexeyka_yah_1951com_;UID=support;PWD=test123;");
%>
'

How can I, and where, copy my existing databse, and change the connection string?

Would really appreciate any help, perhaps buy a new book from the programmers in gratitude.

there is nothing wrong with connection string - the problem is that you are using database file with SQL express and you host SQL 2000 - so they will not be able to attach database file the way SQL Express does...

few days ago i posted why not to use database file with project that will go to shared hosting here:
http://forums.asp.net/thread/1375347.aspx - in the future login to SQL (express is cool) and create database then backup it and restore on your web hosting's SQL server

More info about connection strings you will find here:
http://www.connectionstrings.com/

If the database you have is empty no real data, then copy databse structure to new server change database2ConnectionString and you are good to go

your connections strin g will look like this

<add name="Database2ConnectionString" connectionString="Data Source=203.89.181.78;Initial Catalog=alexeyka_yah_1951com_;User ID=support;Password=test123;"
providerName="System.Data.SqlClient" />

BTW if that is real user pwd - CHANGE IT

|||

Hey, thank you so much for you help. Sorry for the late reply - didn't

The database does connect now, so all I have to do now is to figure out how to actually copy my database file to server.

Alexi;)

|||

if they offer SQL 2005 you should not have any problems. If not and you have to use SQL 2000 read this:

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

|||

Hey, thanks again.

I was able to create an identical blank database table on the server in their asp.net enterprise manager. Yes, the do only have 2000 version.

Then, in VWD express I changed datasource control to point to the connection string that connects to the db on the server - it worked.

I'm struggling to contect to the database remotely, will hopefully figure something out.

Cheers again,

Alexi

|||

" I'm struggling to contect to the database remotely " ? - IF the hosting company accepts remote connections you can use free tool like SQL Server Managment Studio Express Edition to connect and work on you database. Keep in mind that many hoster block remote connections so you can only make changes to database design via online Enterprise manager or diffrent tool. If that is the case then simply change connection string in web config when you work on this application to you local machine, and before you do upload to server change it back to real SQL (the 2000 one)

|||

Hi Tom,

Thanks for you reply. I did download the sql server management studio, and asked whether my hosts allowed the remote connection - they don't. so I guess I have to do everything from scratch on the enterprise manager online. I've figured out normal tables, my main concern now will be to generate tables to store username/password info.

Cheers once again,

Alex

|||

one solution is to script everything

or the easy way will be :) :

http://forums.commercestarterkit.org/files/folders/sql_2000_upsize_scripts/entry471.aspx

|||

you can use this tool to add/edit/ delete users, create roles

http://peterkellner.net/2006/07/17/atlasjunectpsource/

make sure you secure it on the server, so u are the only one who can access it

|||

Hi Tom,

Thanks again for you input, have been real busy populating my datatables, so haven't had time to work on logins/users. If you like, you can check out the results of your efforts atwww.takeawaydelivery.co.nz

Will now be working my way into creating loging pages. By the way, the link that you gave me for some reason, it says there was a fata error and it couldn't be viewed? Anyway, thanks for all your help, and I'll let you know if I get around to creating users/etc

|||

the one with script or the one with tool to manage users ? i chcecked both links and are working just fine for me.

|||

Hi,

Seems to be working fine now, that was the tool to manage script. Downloaded it, and ran it. Works fine on my computer, though I can't understand though where are the usernames/passwords are stored. Also, with the tables, do I just run the whole script in the enterprise manager? and how do I link the two? Thanks again,

Alexi

|||

Hi,

an update here - the admin tool works fine on the local computer. I also reconfigured the weg.config file to point towards a remote database for users/passwords:

<membershipdefaultProvider="CustomizedProvider">

<providers>

<addname="CustomizedProvider"

type="System.Web.Security.SqlMembershipProvider"

connectionStringName="MyDB"

minRequiredPasswordLength="5"

minRequiredNonalphanumericCharacters="0" />

</providers>

</membership>

(and added a corresponding connection string).

For some reason though, when I run the table building script it doesn't show that anything is happening? like tables do not appear? any ideas-

thanks

Alexi

|||

Server Error in '/' Application.

---

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Source Error:

Line 50: private void FindFirstUserName()Line 51: {Line 52: MembershipUserCollection muc = Membership.GetAllUsers();Line 53: foreach (MembershipUser mu in muc)Line 54: {

Source File:c:\Inetpub\vhosts\takeawaydelivery.co.nz\httpdocs\Default.aspx.cs Line:52

Stack Trace:

this is the erro when i go to the tool|||the database script (link i sent u) i used it few times and never had any problems with it - make sure that u can see all tables needed and stored proc.

Help with connection string

I have a connection string in my web.config file:

<add key="cnString" value="data source=ServerName;initial catalog=DatabaseName;password=Password;persist security info=True;user id=UserName;packet size=4096" />

and it works just fine with the SQL production server.
Recently the testing SQL server was created and I can login to it with Enterprise Manager.

But when I try to change the connection string just putting a new server name, database, user name, and password, it gives me the error:

Login failed for user 'UserName'

What should I look in the database or SQL server that is different from a production one? Or what should I ask since I don't have admin rights to both servers?

BTW, I've already tried to play with Trusted_Connection parameter (True/False) and it didn't help.

Thank you in advance for your help.You should check whether the user you are specifying in the connection string exists on the sql server and whether that user has access to the database that you are setting as "Initial catalog"

Hope that helps
Kashif|||Yes, the user exists and has an access to that database.

help with a User-Defined function to return a string from multiple records

I need some help with writing a User-Defined function in SQL Server 2000.
I would like to return a space-delimited string, which contains the column
data of several records from a table.
Here's an example:
table_fruit
id textid
-- --
1 APPLE
2 BANANA
4 ORANGE
8 PEAR
16 PLUM
My SQL query string uses a bitwise-AND (&) to determine which records to
return.
SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
So, for example, if I pass in the parameter @.param_fruits = 13, then I get
the following records back:
APPLE
ORANGE
PEAR
What I'd like to have is a User-Defined function that returns the data in a
concatenated space-delimited string like this:
APPLE ORANGE PEAR
I need help with writing this function. Thanks very much.To get a space delimited string, you should modify the statement as follows:
=====
-- Your function declarations etc
DECLARE @.returnString VARCHAR(8000)
SET @.returnString = ''
SELECT @.returnString = @.returnString + ' ' + textid
FROM table_fruit WHERE ([id] & @.param_fruits) > 0
RETURN (@.returnString)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I need some help with writing a User-Defined function in SQL Server 2000.
> I would like to return a space-delimited string, which contains the column
> data of several records from a table.
> Here's an example:
> table_fruit
> id textid
> -- --
> 1 APPLE
> 2 BANANA
> 4 ORANGE
> 8 PEAR
> 16 PLUM
>
> My SQL query string uses a bitwise-AND (&) to determine which records to
> return.
> SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> So, for example, if I pass in the parameter @.param_fruits = 13, then I get
> the following records back:
> APPLE
> ORANGE
> PEAR
> What I'd like to have is a User-Defined function that returns the data in
> a
> concatenated space-delimited string like this:
> APPLE ORANGE PEAR
> I need help with writing this function. Thanks very much.
>|||In addition, this method is unreliable and should be done on the client
side
As an alternative take a look at Erland's (if I remember well) example
CREATE PROCEDURE get_company_names_inline @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@.customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
CREATE FUNCTION inline_split_me (@.param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @.param + ',', Number + 1,
charindex(',', ',' + @.param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @.param + ',') - 1
AND substring(',' + @.param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
> follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Thanks very much! Worked great.
I had done the same query but had not declared the VARCHAR large enough, and
didn't use the SET statement to initialize the variable.
Thanks
Scott
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
column
get
in
>sql

Help with a list variable

I have made the following test stored proc that all it does right now
is accept a string variable that will be a list ie. :5,9,6,13. Right
now all the SP is doing is creating a temp table to store the values in
the list, then looping through the list and inserting the values into
the temp table, then selecting all the records from teh temp table.
It's WORKING however i must have done something wrong because it's not
inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
will get inserted, not the 13. if I pass in the list like this
"5,9,6,13," with an extra comma at the end, they all get inserted fine
but thats not how the strings will be coming in. Here's what I have so
far:

CREATE PROCEDURE sp_searchTaskTest

-- in params
@.strAssignedTovarchar (200) = NULL

AS

CREATE TABLE #tblAssignedTo (
strAssignedToIDvarchar(10)
)

-- initialize variables
DECLARE @.lengthOfString int
DECLARE @.startingPosition int
DECLARE @.parseString1 int
DECLARE @.strAssignedToID varchar(10)

SET @.startingPosition = 0
SELECT @.parseString1 = CHARINDEX (',', @.strAssignedTo,1)
WHILE ( @.parseString1 > 0 )
BEGIN
SELECT @.parseString1 = CHARINDEX (',',
@.strAssignedTo,@.startingPosition)
SET @.lengthOfString = @.parseString1 - @.startingPosition
IF @.lengthOfString > 0
BEGIN
SET @.strAssignedToID = SUBSTRING(@.strAssignedTo, @.startingPosition,
@.lengthOfString)
SET @.startingPosition = @.parseString1 + 1
END
ELSE
BEGIN
SET @.parseString1 = 0
SET @.strAssignedToID = ''
END
IF @.strAssignedToID != ''
BEGIN
INSERT #tblAssignedTo(strAssignedToID)
VALUES(@.strAssignedToID)
END
END

SELECT * FROM #tblAssignedTo
GOhi
looks like u need to make some changes in the code.

else what u can do is,

@.strAssignedTo = @.strAssignedTo + ','

in the first line of the SP. This is not correct but a quick fix

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||On 3 Aug 2005 10:38:43 -0700, Erich93063 wrote:

>I have made the following test stored proc that all it does right now
>is accept a string variable that will be a list ie. :5,9,6,13. Right
>now all the SP is doing is creating a temp table to store the values in
>the list, then looping through the list and inserting the values into
>the temp table, then selecting all the records from teh temp table.
>It's WORKING however i must have done something wrong because it's not
>inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
>will get inserted, not the 13. if I pass in the list like this
>"5,9,6,13," with an extra comma at the end, they all get inserted fine
>but thats not how the strings will be coming in. Here's what I have so
>far:

Hi Erich,

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

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with a connection string

I have a two computers, in one of them I install the SQL Server 2005 express with server name USER\SQLEXPRESS and ip 192.168.3.15.The data base name is Prueba.mdf

In the other computer I have VB 2005 express , and I want to connect with Prueba.mdf in the the other pc. Can you help me?

Hi

Try the below connection string settings

User ID=sa; ( if you configured your own, then replace user name and password acc)

password=sa;

Data Source=USER\SQLEXPRESS;

database=Prueba;

Connect Timeout=1800;

Regards,

Dhinesh Kumar

|||If you want to connect to a user instance, you should use one of the strings at www.connectionstrings.com , look for SQL Server 2005 and user instances, there are some strings which will help you.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Help with a CASE statement.

I am trying to get this case statement to work where it will
concatenate the values into a single string.
If I place anything with an equals sign such as (SELECT @.status_message
= @.status_message + 'variable') it give me an error. Any ideas. It
probably obvious but I am failing to see it.
DECLARE @.status_message varchar(100)
Set @.status_message = ''
SELECT * from master..sysdatabases
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
WHEN (status & 4194304) <> 0 THEN 'autoshrink'
WHEN (status & 32768) <> 0 THEN 'emergency mode'
WHEN (status & 4096) <> 0 THEN 'single user'
WHEN (status & 2048) <> 0 THEN 'dbo use only'
WHEN (status & 1024) <> 0 THEN 'read only'
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 256) <> 0 THEN 'not recovered'
WHEN (status & 128) <> 0 THEN 'recovering'
WHEN (status & 64) <> 0 THEN 'pre recovery'
WHEN (status & 32) <> 0 THEN 'loading'
WHEN (status & 16) <> 0 THEN 'torn page detection'
WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE 'Unknown'
end
from master..sysdatabasesYou cannot assign values to variables in the same select statement in which
you also return a result-set to the client.
E.g. this is not allowed:
select <column list>
,@.<variable> = <some column>
from <table>
Put the variable assignment ina separate query, then include it in the one
that returns the result to the client.
select @.<variable> = <some column>
from <table>
select <column list>
,@.<variable> as <variable name>
from <table>
Does that answer your question?
ML
http://milambda.blogspot.com/|||Sort of, I know what the problems is, I am looking for a work around,
or a different solution that might achieve the same results. someone
said that i sould thorugh it into a loop, so i might try that.|||Anyone. I must be totaly brainfried.|||>> I am trying to get this case statement to work where it will concatenate
Which values are you talking about? The string values in the THEN clause of
the CASE? Please elaborate on what you are trying to do here.
What is the error message? Which piece of code are you trying to run to
generate the error?
Anith|||I figured it out
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
****************************************
*******************/
SELECT @.@.SERVERNAME AS SERVER, VERSION, LEFT(name,30) AS [Databases],
DBID,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',Aautoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',Select Into / Bulk Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',Truncate Log on Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',Torn Page Detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',Cleanly Shutdown' END,
2,8000) AS OPTIONS_1,
SUBSTRING(CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default'
END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END
+
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor'
END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit'
END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS OPTIONS_2, CMPTLEVEL, FILENAME
FROM master..sysdatabases|||On 27 Feb 2006 14:48:32 -0800, Matthew wrote:

>I am trying to get this case statement to work where it will
>concatenate the values into a single string.
>If I place anything with an equals sign such as (SELECT @.status_message
>= @.status_message + 'variable') it give me an error. Any ideas. It
>probably obvious but I am failing to see it.
>DECLARE @.status_message varchar(100)
>Set @.status_message = ''
>SELECT * from master..sysdatabases
>SELECT name, dbid, status, cmptlevel, filename,
>CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
> WHEN (status & 4194304) <> 0 THEN 'autoshrink'
> WHEN (status & 32768) <> 0 THEN 'emergency mode'
> WHEN (status & 4096) <> 0 THEN 'single user'
> WHEN (status & 2048) <> 0 THEN 'dbo use only'
> WHEN (status & 1024) <> 0 THEN 'read only'
> WHEN (status & 512) <> 0 THEN 'offline'
> WHEN (status & 256) <> 0 THEN 'not recovered'
> WHEN (status & 128) <> 0 THEN 'recovering'
> WHEN (status & 64) <> 0 THEN 'pre recovery'
> WHEN (status & 32) <> 0 THEN 'loading'
> WHEN (status & 16) <> 0 THEN 'torn page detection'
> WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
> WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
> WHEN (status & 1) <> 0 THEN 'autoclose'
> ELSE 'Unknown'
>end
>from master..sysdatabases
Hi Matthew,
Though concatenating these in a single string is presentation and
shouyld therefor be handled in the presentation layer, I'll give you a
working SQL solution:
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
ELSE '' END
+ CASE WHEN (status & 4194304) <> 0 THEN 'autoshrink'
ELSE '' END
+ CASE WHEN (status & 32768) <> 0 THEN 'emergency mode'
ELSE '' END
(...)
+ CASE WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE '' END
from master..sysdatabases
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:81e902hircfvvlacle0bg16ois08j7kmoq@.
4ax.com...
>.
> Though concatenating these in a single string is presentation and
> shouyld therefor be handled in the presentation layer,
>.
Hmm where have I heard this same idea before?
Once upon a time many were saying the same thing about
row numbering or worse line numbering.Surely this was
something to be done on the client whereas it had no
place being done on the server.(Not to mention the situation
was further muddled when the same voices used the very same
constructs as intermediate results in a query).Now along comes
Sql 2005 with some Sql-99 analytic functions like row_number()
and viola row numbering is no longer *presentation* and it's
perfectly fine to do on the server.To keep the analytics
company, 2005 introduces some more xml.Now we learn that
concatenating over rows can be done explicitly with it.
Presentation now has now become transformed into a serve-ice.
Since the underlying nature of 2005 hasn't changed
save for the mechanisms to do these things, it is now
*expedient* to do them on the server.Of course the shift
over even includes the dreaded crosstab with the ingenius
implementation of PIVOT.One must marvel at the ease of how
MS can change presentation to serve-ice:) But this shift may
come with some head scratching.Least some users wonder why
they can't simulate the serve-ice of 2005 to overcome the
presentation inherent in 2000.But one of the great things
about expediency is that it takes so shallow an explanation.
Flip flopping is alive and well in the world of sql.And for those
who actually study the subject academically I would say
't'where ignorance is bliss,t'is folly to be wise':)
$.03 from
www.rac4sql.net|||Well I guess as "proof" in SQL 2005 running the Execution Plan, the
cost associated for running query is exactly the same for both. So I
really boils down, which way is the "more correct" way to code.
e.g. which is better in practice.|||'which is better in practice' is just another way of saying what
is the most expedient way.Whatever works best for you:)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1141230238.399541.6200@.z34g2000cwc.googlegroups.com...
> Well I guess as "proof" in SQL 2005 running the Execution Plan, the
> cost associated for running query is exactly the same for both. So I
> really boils down, which way is the "more correct" way to code.
> e.g. which is better in practice.
>

help to define a search criteria with FTS

Hi!
I'm using FTS in MSSQL2000.
1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
not "bcda" or "aabcd" (always from the second letter).
2. the search column is a long string. the begining of the string is
more important that the end. i want the results found in the begining
to get higher RANK so i can present them first.
Please help.
You can't do prefix based searches in SQL FTS. You might be able to store
your content in reverse and then do wildcard based searches, i.e.
select * from tablename where contains(ColumnName,'dcb*') which will return
hits to the reversed content of abcd, or 1bcd, but unfortunately also aabcd
reversed.
You may have to use like for this type of query.
Another option would be to use a thesaurus based search if you know in
advance all search tokens which you need to map to bdc, and expand then to
search on bcd,
so bcd will expand to a search on bcd and abcd, and 1bcd.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rom_ask_u" <nimrod4u@.gmail.com> wrote in message
news:1123138487.817248.51670@.g47g2000cwa.googlegro ups.com...
> Hi!
> I'm using FTS in MSSQL2000.
> 1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
> not "bcda" or "aabcd" (always from the second letter).
> 2. the search column is a long string. the begining of the string is
> more important that the end. i want the results found in the begining
> to get higher RANK so i can present them first.
> Please help.
>

Help required for Splitting up string variable using comma separator

I need a help in SQL Server 2000.

I am having a string variable in the format like -- (1,23,445,5,12)

I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.

Can anyone help me out in splitting the variable using the comma separator...

You can just use the split command e.g.

Dim sAs String ="1,23,445,5,12"Dim splitAs String() = s.Split(",")For Each itemAs String In split Response.Write("Item: " & item &"<br>")Next

|||

I doesnt want this in VB.NET.

I want the same using SQL query in SQL Server 2000.

|||You'll have to create a function to do this. Here's a starting point:http://www.madprops.org/cs/blogs/mabster/archive/2005/12/05/T_2D00_SQL-to-Split-a-varchar-into-Words.aspx|||

Oh, sorry. If you want to do this in SQL, it's a bit harder as it doesn't really have built in string manipulation functions. If you were using a later version of SQL Server you could have registered that .NET code as a CLR function but as you ar using SQL Server 2000, you will have to do something like this:

CREATE PROCEDURE SplitString
@.yourStringvarchar(100)
AS
BEGIN
DECLARE @.StringCountint, @.mycountint, @.mystrlenint
DECLARE @.myvalvarchar(100)
set @.StringCount=Len(@.yourString)
set @.mycount=1
if (CHARINDEX(',',@.yourString,1)=0)
print @.yourString
WHILE (CHARINDEX(',',@.yourString,1)<>0)
BEGIN
if @.mycount=1
set @.myval=substring(@.yourString,@.mycount,CHARINDEX('^',@.yourString,1)-1)
print @.myval
set @.yourString =substring(@.yourString,Len(@.myval)+2,Len(@.yourString))
set @.StringCount= @.StringCount -1
if (CHARINDEX(',',@.yourString,1)=0)
print ,@.yourString
END
end
GO

Help required

Psting For Charan,

Hi,

I am trying to connect MS SQL 2000 with C language using ODBC drivers in win 2k environment

i have a UTF 8 string stored in a nvarchar field of the MS SQL table and i am trying to retrieve this field from C and store it in a txt file
everythin is working perfectly fine the probs is that the UTF 8 string is not getting being retrieved correctly

Help Required Urgently

Tks in Adv
SsgI've replied to Charan when asked the same question via Private Message. Any scope of working on that topic.