Sunday, February 26, 2012
help to define a search criteria with FTS
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 to Create job
i can't do this when my user do not use sa user but i don't want to give him the sa password so how can i do this
eytanb@.mekorot.co.ilBoth SPs execute permissions are default to the public role and ensure that user does have permission on MSDB database and required privilege on underlying user database.
Help to Configure SQL 2005 to listen on a specific Port
I look under the TCP/IP properties dialog box on the IP Address tab
in the see several IP addresses in format IP1,IP2,IP3 and finally
IPALL. Which one am I suppose to configure? When I look at the IP
addresses I don't see the virtual IP address for my SQL Server
Instance. Can anyone help me on this.
I looked at BOL and it says "Right-click each address, and then click
Properties to identify the IP address that you wish to configure." But
right cliking on the address does not give me the properties option.
The server that I am working with is in a Cluster.
ThanksThe multiple IP addresses indicate you have multiple network connections.
You can set a different port for each connection OR you can set the same
port on all connections. I would use the 'ALL' option on a cluster.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> The multiple IP addresses indicate you have multiple network connections.
> You can set a different port for each connection OR you can set the same
> port on all connections. I would use the 'ALL' option on a cluster.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "shub" <shubt...@.gmail.com> wrote in message
> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>
> >I am trying to configure my port for my SQL 2005 named instance. When
> > I look under the TCP/IP properties dialog box on the IP Address tab
> > in the see several IP addresses in format IP1,IP2,IP3 and finally
> > IPALL. Which one am I suppose to configure? When I look at the IP
> > addresses I don't see the virtual IP address for my SQL Server
> > Instance. Can anyone help me on this.
> > I looked at BOL and it says "Right-click each address, and then click
> > Properties to identify the IP address that you wish to configure." But
> > right cliking on the address does not give me the properties option.
> > The server that I am working with is in a Cluster.
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks for your help. As far as deciding the port number itself do you
follow any guidelines or is there any guidelines by Microsoft?|||For whatever reason, SQL2005 Configuration Manager does NOT list the virtual
server IP address used by the SQL instance. I have no idea why.
But if ListenAll is enabled, you can use Configuration Manager to set TCP
Port under IP All on the Ip Address tab to a port of your choice, and upon
restart the instance the port will be used. Also, you only need to do this on
one of the nodes, and the cluster service will replicate the setting to the
other nodes.
Linchi
"shub" wrote:
> I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||I either use the autoconfig port and lock it in or I pick something that
isn't in use.
I don't recall any guidelines for this from Microsoft.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193279519.381435.155240@.i13g2000prf.googlegroups.com...
> On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> The multiple IP addresses indicate you have multiple network connections.
>> You can set a different port for each connection OR you can set the same
>> port on all connections. I would use the 'ALL' option on a cluster.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>> "shub" <shubt...@.gmail.com> wrote in message
>> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>>
>> >I am trying to configure my port for my SQL 2005 named instance. When
>> > I look under the TCP/IP properties dialog box on the IP Address tab
>> > in the see several IP addresses in format IP1,IP2,IP3 and finally
>> > IPALL. Which one am I suppose to configure? When I look at the IP
>> > addresses I don't see the virtual IP address for my SQL Server
>> > Instance. Can anyone help me on this.
>> > I looked at BOL and it says "Right-click each address, and then click
>> > Properties to identify the IP address that you wish to configure." But
>> > right cliking on the address does not give me the properties option.
>> > The server that I am working with is in a Cluster.
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks for your help. As far as deciding the port number itself do you
> follow any guidelines or is there any guidelines by Microsoft?
>
Help to a query
Hi
In acces i use this:
[varebetegnelse] & " Med ordennummer:--" & [ordenummer] AS tekst
Where [varebetegnelse] and [ordennummer] is column in my query
How can i do this in a view in SQL ?
Second
also in acces i use a criteria based on a form like this
WHERE (((kategori.katId)=[Forms]![samlekursus]![kategorinr]))
How can i do this in a view in SQL
I work from access cnoocted to SQL sever
Regards
alvin
You will either have to do the filtering on the view (using the view as a mapped table and building a query in access on top of that)
Select SomeColumns
FROM
Viewname
WHERE (((kategori.katId)=[Forms]![samlekursus]![kategorinr]))
Or use a parametrized query, passing the parameter to the procedure.
EXEC SomeProcedure @.SomeVariablewithin = 'YourValue'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
help textcopy
is there has replace_tool in sql server 2005?
thanks
jason>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
SQL 2005, you can accomplish the same result in Transact-SQL using
OPENROWSET...BULK. The proc example below imports file contents into a temp
table and then updates the specified main table row with the blob data.
CREATE PROC dbo.UpdateMyTableImageDataFromFile
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(MAX))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
> thanks
>
> jason
>|||thanks!
but if i need export image/text data, how do?
SSIS or command?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> д?:uojyazm2GHA.4648@.TK2MSFTNGP
04.phx.gbl...
> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
> SQL 2005, you can accomplish the same result in Transact-SQL using
> OPENROWSET...BULK. The proc example below imports file contents into a
> temp table and then updates the specified main table row with the blob
> data.
>
> CREATE PROC dbo.UpdateMyTableImageDataFromFile
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(MAX))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "udb" <datacen@.vip.sina.com> wrote in message
> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>|||I would use SSIS. It's quite flexible and allows you to create any sort of
standard or custom file output.
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:Ow%23YLgz2GHA.1548@.TK2MSFTNGP02.phx.gbl...
> thanks!
> but if i need export image/text data, how do?
> SSIS or command?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> д?:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl...
>
help textcopy
is there has replace_tool in sql server 2005?
thanks
jason>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
SQL 2005, you can accomplish the same result in Transact-SQL using
OPENROWSET...BULK. The proc example below imports file contents into a temp
table and then updates the specified main table row with the blob data.
CREATE PROC dbo.UpdateMyTableImageDataFromFile
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(MAX))
--insert blob into temp table
SET @.SqlStatement = N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
> thanks
>
> jason
>|||thanks!
but if i need export image/text data, how do?
SSIS or command?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> дÈëÏûÏ¢ÐÂÎÅ:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl...
> >i like textcopy in sql server 2000, but i don't find it in sql server
> >2005. is there has replace_tool in sql server 2005?
> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
> SQL 2005, you can accomplish the same result in Transact-SQL using
> OPENROWSET...BULK. The proc example below imports file contents into a
> temp table and then updates the specified main table row with the blob
> data.
>
> CREATE PROC dbo.UpdateMyTableImageDataFromFile
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(MAX))
> --insert blob into temp table
> SET @.SqlStatement => N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "udb" <datacen@.vip.sina.com> wrote in message
> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>>i like textcopy in sql server 2000, but i don't find it in sql server
>>2005. is there has replace_tool in sql server 2005?
>> thanks
>>
>> jason
>|||I would use SSIS. It's quite flexible and allows you to create any sort of
standard or custom file output.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:Ow%23YLgz2GHA.1548@.TK2MSFTNGP02.phx.gbl...
> thanks!
> but if i need export image/text data, how do?
> SSIS or command?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> дÈëÏûÏ¢ÐÂÎÅ:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> >i like textcopy in sql server 2000, but i don't find it in sql server
>> >2005. is there has replace_tool in sql server 2005?
>> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
>> SQL 2005, you can accomplish the same result in Transact-SQL using
>> OPENROWSET...BULK. The proc example below imports file contents into a
>> temp table and then updates the specified main table row with the blob
>> data.
>>
>> CREATE PROC dbo.UpdateMyTableImageDataFromFile
>> @.MyPK int,
>> @.FileName varchar(255)
>> AS
>> DECLARE @.SqlStatement nvarchar(MAX)
>> CREATE TABLE #BlobData(BlobData varbinary(MAX))
>> --insert blob into temp table
>> SET @.SqlStatement =>> N'
>> INSERT INTO #BlobData
>> SELECT BlobData.*
>> FROM OPENROWSET
>> (BULK ''' + @.FileName + ''',
>> SINGLE_BLOB) BlobData'
>> EXEC sp_executesql @.SqlStatement
>> --update main table with blob data
>> UPDATE dbo.MyTable
>> SET MyBlob = (SELECT BlobData FROM #BlobData)
>> WHERE MyTable.MyPK = @.MyPK
>> DROP TABLE #BlobData
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "udb" <datacen@.vip.sina.com> wrote in message
>> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>>i like textcopy in sql server 2000, but i don't find it in sql server
>>2005. is there has replace_tool in sql server 2005?
>> thanks
>>
>> jason
>>
>
help textcopy
is there has replace_tool in sql server 2005?
thanks
jason
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
SQL 2005, you can accomplish the same result in Transact-SQL using
OPENROWSET...BULK. The proc example below imports file contents into a temp
table and then updates the specified main table row with the blob data.
CREATE PROC dbo.UpdateMyTableImageDataFromFile
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(MAX))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
> thanks
>
> jason
>
|||thanks!
but if i need export image/text data, how do?
SSIS or command?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> д?:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl ...
> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
> SQL 2005, you can accomplish the same result in Transact-SQL using
> OPENROWSET...BULK. The proc example below imports file contents into a
> temp table and then updates the specified main table row with the blob
> data.
>
> CREATE PROC dbo.UpdateMyTableImageDataFromFile
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(MAX))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "udb" <datacen@.vip.sina.com> wrote in message
> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>
|||I would use SSIS. It's quite flexible and allows you to create any sort of
standard or custom file output.
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:Ow%23YLgz2GHA.1548@.TK2MSFTNGP02.phx.gbl...
> thanks!
> but if i need export image/text data, how do?
> SSIS or command?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> д?:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl ...
>