Access Denied txt file on other server

Hi and Help!!
I'm using Bulk Insert to read a text file that is on a different server. I
get an access denied message.
- If I change the name to a non-existant file,
I get an error that it can't find that file, so it can see it.
- I can successfully read a copy that I stored on the SQL server server.
I pretty much have whatever rights I need, personally, but I'm guessing I
need to set something up elsewhere.
Any suggestions?
Thanks,
ArtTry checking the account that the server is running as, for example if
LocalSystem is being used, it may not have the permissions to access the
file. I always use a service account and grant permissions to a specific
folder on the server to pass to.
"Art" wrote:

> Hi and Help!!
> I'm using Bulk Insert to read a text file that is on a different server.
I
> get an access denied message.
> - If I change the name to a non-existant file,
> I get an error that it can't find that file, so it can see it.
> - I can successfully read a copy that I stored on the SQL server server.
> I pretty much have whatever rights I need, personally, but I'm guessing I
> need to set something up elsewhere.
> Any suggestions?
> Thanks,
> Art|||Josh,
Thanks for the suggestion -- I won't be able to try it until Tuesday.
Unfortunatly I still don't know much about how to use SQL Server. I've
learned enough SQL and enough about Stored Procedures, but I don't actually
know too much about the system itself. I guess that's something that I had
better change.
Any suggestions on a good book? I'm currently using 2000, but in a few
months will upgrade to 2005.
Thanks again,
Art
"Josh Crosby" wrote:
> Try checking the account that the server is running as, for example if
> LocalSystem is being used, it may not have the permissions to access the
> file. I always use a service account and grant permissions to a specific
> folder on the server to pass to.
> "Art" wrote:
>|||sure, great book on SQL 2005 written by Michael Otey i'm writing a review
for, here's the info.
SQL Server 2005: Developer's Guide
ISBN: 0-07-226099-8
--
Josh Crosby
"Art" wrote:
> Josh,
> Thanks for the suggestion -- I won't be able to try it until Tuesday.
> Unfortunatly I still don't know much about how to use SQL Server. I've
> learned enough SQL and enough about Stored Procedures, but I don't actuall
y
> know too much about the system itself. I guess that's something that I ha
d
> better change.
> Any suggestions on a good book? I'm currently using 2000, but in a few
> months will upgrade to 2005.
> Thanks again,
> Art
> "Josh Crosby" wrote:
>

Access dabase linked to SQL Server

Hi,

I have an Access database (name "Shipping") linked to SQL Server. The Access database is not secured. My access file is located on the same machine as the instance of SQL Server, in a shared folder that can be accessed by any user. The file permissions are set to full control for Everyone. I linked Access with the following procedure:

EXEC sp_addlinkedserver
@.server = 'Shipping',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\Shipping\Shipping BackEnd.mdb'

then modified the linked server login mapping with:

EXEC sp_addlinkedsrvlogin 'Shipping','false',NULL,'Admin',NULL

Now, since I have domain administrator permissions, I can run a stored procedure in SQL Server that does a Select in the Access database without any problem. But when a normal user tries to run the procedure, he gets an error #7399. If I give this user domain administrator rights, he's able to run the procedure. There is something I don't get, with normal user permission, this user can open the Access database through the shared folder...

Thanks

-SteveStraight from BOL:

Error 7399
Severity Level 16
Message Text
OLE DB provider '%ls' reported an error. %ls

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Explanation

This error message returned by the Microsoft OLE DB Provider for Jet indicates one of the following:

The Microsoft Access database is not a secured database and the login and password specified was not Admin with no password.

The Access database is secured and the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Syst emDB registry key is not pointing to the correct Access workgroup file. Secured Access databases have a corresponding workgroup file, including the full path, which should be indicated by the above registry key.

Action

Verify that there is a login mapping for the current Microsoft SQL Server login to Admin with no password.

If the Access database being accessed is secured, make sure that the above registry key points to the full pathname of the Access workgroup file.|||Thanks, but I've already checked the BOL and it didn't help me:

1- The Access database is not secured so I shouldn't have to worry about the workgroup information file
2- I already made the login mapping to Access user 'Admin' with no password and it didn't fix the problem => EXEC sp_addlinkedsrvlogin 'Shipping','false',NULL,'Admin',NULL
3- Everything is working great with Windows users with Administrator rights. So it seems to be a Windows permission problem even though I gave Full control permission to everyone on the Access file. This file is in a Shared Folder on the server and anyone can open it...

Access 2007 linked tables (vs Access 2003)

We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.

Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.

Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?

Thanks!

I hope someone answers this. I would really like to see the answer...

Access 2000, SQL Server 2000, Bulk Insert

I am pulling information from a DB2 table through a pass-through query
in Access 2000. I am going to create a comma delimited file that I want
to use Bulk Insert to put into SQL Server 2000.
I have the query to pull the data, all is well. How do I program in VBA
(Acess 2000) to do a bulk insert into a SQL Server table. I have 100000
records that I do this to every day. Bulk Insert seems to be the fastest
way. I will bulk insert the comma delimited file.
I am on a PC, not the SQL Server for the bulk insert. I cannot access
the mainframe DB2 tables from the SQL Server. My PC is the middle man
right now. How do I effectively do the bulk insert.
Does anybody have any experience in doing this, if so, can you post some
code to assist.
Thanks, Lyners
--
Posted via http://dbforums.comOne more thing, I have the code written so that it pulls the data and
loads the records 1 by 1. It takes to long and I keep blowing up the
transaction file. That is why I want to do the bulk insert. 1 record for
the transaction file. (AT least that is what I have read).
Posted via http://dbforums.com

Access 2000 to Sql2000 : file size change?

I have an Access 2000 database with 640,000 records.
(growing weekly) File size is hovering around 245MB.
I plan to import/convert this database into a new database
on our SQL2000 server. (As soon as I finished that
chapter of the book!)
Implementation planning question: Will the database size
change significantly either way once it's imported in SQL?
TIA,
TeresaIt will probably me less than with Access.
When you import the access stuff, take a minute to check the column-type
definitions and optimize it for your needs.
You might also want to check indexing (wizard) when running on SQL2000 with
your applications, so you can provide propre and fast usage.
jobi
"teresa" <teresaj@.vvh.org> wrote in message
news:06c301c35550$497ded40$3501280a@.phx.gbl...
> I have an Access 2000 database with 640,000 records.
> (growing weekly) File size is hovering around 245MB.
> I plan to import/convert this database into a new database
> on our SQL2000 server. (As soon as I finished that
> chapter of the book!)
> Implementation planning question: Will the database size
> change significantly either way once it's imported in SQL?
> TIA,
> Teresa
>sql

Access

My question is Access related.
1) I have an access file with many tables.I want to replicate this access file but without data.Means I want to make a copy of the access file with the same tables with same design but without any data.How to do that?
2)In one of my DTS data goes from SQL to Access.I want to truncate few access tables before this DTS package is run.It should be done through SQL.How can I do that?replicate without data? what do you mean? any table change should result in a change on a table in sqlserver?

Access

I have an Access file with many tables and each table has got many constraints.I want to remove each and every constraints(including primary key,foreign relationship) for every table.How can I do it first?Delete the file

Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.

Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.

Accesing offline cubes with MS Excel 2003

Hi,

I created offline cube (.cub file) with SQL Server 2005 Analysis Services. Accessing the offline cube with MS Excel 2003 I get an error message: "Unable to obtain list of tables from the data source".

I can access the offline cube with MS Excel 2003 only when MS Excel 2007 is instaled. After uninstaling MS Excel 2007 the same error ocures.

I have MS Excel 2003 (11.8146.6568) SP2, Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider, Microsoft Core XML Services (MSXML) 6.0 instaled.

Thanks for help,

Martin

Hello! This is only from what I have read.

To create local cubes you can write XMLA commands in Management Studio or write code by using the SSAS2005 object model called AMO.

I do not think that these two possibilities are part of Excel 2003.

HTH

Thomas Ivarsson

Acces to a file that is locked by another process

I am writing a package to process perfmon logs. The issue I have come across is that the perfmon process holds onto the log file and SSIS fails because it wants to exclusive read access. Is there any way of getting SSIS to not take an exclusive read on the file.

I can read the file in notepad fine but not SSIS.

check by changing the isolation level of package by default its serialiazable

Absurdal affair in trigger file

Hi!

I found a bug in my trigger and i don't know why it is error.

See small report:

After executing following code's fragment in my trigger:

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL
SET @.orzecz_koszty_sadowe = 0
IF @.orzecz_mks_przyznane IS NULL
SET @.orzecz_mks_przyznane = 0

IF @.orzecz_oplaty_pozostale IS NULL
SET @.orzecz_oplaty_pozostale = 0

IF @.orzecz_zasadzona_zaliczka_na_koresp IS NULL
SET @.orzecz_zasadzona_zaliczka_na_koresp = 0

-- Koniec sprawdzanie czy sa NULL

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

we have on consolle following result:

************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43
************** koszt sadowe ***************** 0
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43

So, variable @.orzecz_koszty_sadowe was set to 0.
Previously it was 145, so why in the if statement it values to NULL?

After small change, code looks like this:

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL
SET @.orzecz_koszty_sadowe = 0
IF @.orzecz_mks_przyznane IS NULL
SET @.orzecz_mks_przyznane = 0

IF @.orzecz_oplaty_pozostale IS NULL
SET @.orzecz_oplaty_pozostale = 0

IF @.orzecz_zasadzona_zaliczka_na_koresp IS NULL
SET @.orzecz_zasadzona_zaliczka_na_koresp = 0

-- Koniec sprawdzanie czy sa NULL

print '************** koszt sadowe *****************' + str(@.orzecz_koszty_sadowe)
print '************** koszt mks *****************' + str(@.orzecz_mks_przyznane)
print '************** koszt korespondencji *****************' + str(@.orzecz_zasadzona_zaliczka_na_koresp)

and the result is following:

************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43
************** koszt sadowe ***************** 145
************** koszt mks ***************** 123
************** koszt korespondencji ***************** 43

So the result is proper. Now I explain the difference between
above listings. Second listing arise from first by following
transformation:

1. Place the cursor after last character of comment preceding
first IF statement (ie. after ....pozycji 'Koszty sadowe')
2. Press Enter key
3. Press Delete key (the line comes back, to initial position)

And that's all :) There's no optical difference between two
listings. However first works bad, and the second works good.

Any idea? Explanation?
I used SQL Server 2000.
Best regards
Walter

Could you please post a script that demonstrates the problem?|||Probably there was a Unix-style newline in your SQL. The query editor displays CHR(13) without CHR(10) as a new line, but the SQL processor considers the text before and after CHR(13) to be part of the same line if there is no CHAR(10).

So the SQL processor interpreted
-- Potrzebne do wstawienia pozycji 'Koszty sadowe'
IF @.orzecz_koszty_sadowe IS NULL

to mean the same thing as

-- Potrzebne do wstawienia pozycji 'Koszty sadowe' IF @.orzecz_koszty_sadowe IS NULL

The typing you did changed the CHR(13) to a valid 2-character Windows newline marker.

Steve Kass
Drew University

Abstracts for Full-Text Index entries

When using Microsoft Index Server file system catalogs, the Index Server
generates 'abstracts' which give the user an 'at a glance' indication of the
entry contents. Does Full-Text Indexing within SQL Server provide such
abstracts?
I am developing a search facility for a web site where I want to combine
search results for static web pages (managed by Index Server) with Full-Text
search results from SQL. I'll union the two results sets. I can get
abstracts for the Index Server entries, but can't find anything that will
give me a similar abstracts for the SQL entries. I could just use column
contents, but I'd rather have something more in keeping with the Index Server
abstracts.
Anyone got any good ideas?
Many thanks!
Indexing services abstracts are the first 320 bytes of textual data in
Office documents or the contents of the description metatag for html
documents. If the html document does not have a description metatag, its the
first 320 bytes of the html document.
For sql you have to do something like this
declare @.searchphrase varchar(20)
declare @.MarkedupSearchPhrase varchar(20)
set @.searchphrase='shadow'
set @.MarkedupSearchPhrase ='<B>shadow</B>'
select --1,firstword=len(@.searchphrase)-(len(substring(charcol,charindex(@.se
archphrase,
charcol)-50,100))-charindex('shadow',substring(charcol,charindex(@.se archphra
se, charcol)-50,100))),
--len(substring(charcol,charindex(@.searchphrase, charcol)-50,100)),
MarkedUpColumn = case when len(charcol) >100 and
len(@.searchphrase)-(len(substring(charcol,charindex(@.searchphrase,
charcol)-50,100))-charindex(@.SearchPhrase,substring(charcol,charinde x(@.searc
hphrase, charcol)-50,100))) <>-1 then
replace(ltrim(substring(replace(substring(charcol, charindex(@.searchphrase,
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase),1,len (replace(substrin
g(charcol,charindex(@.searchphrase,
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase))-
len(left(reverse(replace(substring(charcol,charind ex(@.searchphrase,
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase)),char index('
',reverse(replace(substring(charcol,charindex(@.sea rchphrase,
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase))))))) ,char(13)+char(10
),' ')
when len(charcol) >100 and
len(@.searchphrase)-(len(substring(charcol,charindex(@.searchphrase,
charcol)-50,100))-charindex(@.SearchPhrase,substring(charcol,charinde x(@.searc
hphrase, charcol)-50,100))) =-1 then
replace(substring(charcol,charindex(@.searchphrase,
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase)
else replace(replace(substring(charcol,charindex(@.searc hphrase,
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase),char( 13)+char(10),' ')
end
from texttable where FreeText(*,@.searchphrase)
this needs some more work as I have to correctly remove the first word or
word fragment in the markedupColumn (as long as its not the search phrase).
This also does inline hit highlighting.
For "true" abstracts like what indexing services delivers you should use the
left function.
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
"Dave Hickson" <DaveHickson@.discussions.microsoft.com> wrote in message
news:BEEC89DE-421F-4C0C-84A2-94ADA5B3B56C@.microsoft.com...
> When using Microsoft Index Server file system catalogs, the Index Server
> generates 'abstracts' which give the user an 'at a glance' indication of
the
> entry contents. Does Full-Text Indexing within SQL Server provide such
> abstracts?
> I am developing a search facility for a web site where I want to combine
> search results for static web pages (managed by Index Server) with
Full-Text
> search results from SQL. I'll union the two results sets. I can get
> abstracts for the Index Server entries, but can't find anything that will
> give me a similar abstracts for the SQL entries. I could just use column
> contents, but I'd rather have something more in keeping with the Index
Server
> abstracts.
> Anyone got any good ideas?
> Many thanks!
|||Thanks very much Hilary. I assume from your answer that there is therefore
no built-in support for such abstracts?
"Hilary Cotter" wrote:

> Indexing services abstracts are the first 320 bytes of textual data in
> Office documents or the contents of the description metatag for html
> documents. If the html document does not have a description metatag, its the
> first 320 bytes of the html document.
> For sql you have to do something like this
> declare @.searchphrase varchar(20)
> declare @.MarkedupSearchPhrase varchar(20)
> set @.searchphrase='shadow'
> set @.MarkedupSearchPhrase ='<B>shadow</B>'
> select --1,firstword=len(@.searchphrase)-(len(substring(charcol,charindex(@.se
> archphrase,
> charcol)-50,100))-charindex('shadow',substring(charcol,charindex(@.se archphra
> se, charcol)-50,100))),
> --len(substring(charcol,charindex(@.searchphrase, charcol)-50,100)),
> MarkedUpColumn = case when len(charcol) >100 and
> len(@.searchphrase)-(len(substring(charcol,charindex(@.searchphrase,
> charcol)-50,100))-charindex(@.SearchPhrase,substring(charcol,charinde x(@.searc
> hphrase, charcol)-50,100))) <>-1 then
> replace(ltrim(substring(replace(substring(charcol, charindex(@.searchphrase,
> charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase),1,len (replace(substrin
> g(charcol,charindex(@.searchphrase,
> charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase))-
> len(left(reverse(replace(substring(charcol,charind ex(@.searchphrase,
> charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase)),char index('
> ',reverse(replace(substring(charcol,charindex(@.sea rchphrase,
> charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase))))))) ,char(13)+char(10
> ),' ')
> when len(charcol) >100 and
> len(@.searchphrase)-(len(substring(charcol,charindex(@.searchphrase,
> charcol)-50,100))-charindex(@.SearchPhrase,substring(charcol,charinde x(@.searc
> hphrase, charcol)-50,100))) =-1 then
> replace(substring(charcol,charindex(@.searchphrase,
> charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase)
> else replace(replace(substring(charcol,charindex(@.searc hphrase,
> charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase),char( 13)+char(10),' ')
> end
> from texttable where FreeText(*,@.searchphrase)
>
> this needs some more work as I have to correctly remove the first word or
> word fragment in the markedupColumn (as long as its not the search phrase).
> This also does inline hit highlighting.
>
> For "true" abstracts like what indexing services delivers you should use the
> left function.
>
> --
> 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
> "Dave Hickson" <DaveHickson@.discussions.microsoft.com> wrote in message
> news:BEEC89DE-421F-4C0C-84A2-94ADA5B3B56C@.microsoft.com...
> the
> Full-Text
> Server
>
>
|||Not in this current version, or the next.
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
"Dave Hickson" <DaveHickson@.discussions.microsoft.com> wrote in message
news:71E03E56-85DE-45DC-A148-342756EC7CC9@.microsoft.com...
> Thanks very much Hilary. I assume from your answer that there is
therefore[vbcol=seagreen]
> no built-in support for such abstracts?
> "Hilary Cotter" wrote:
the[vbcol=seagreen]
select --1,firstword=len(@.searchphrase)-(len(substring(charcol,charindex(@.se[vbcol=seagreen]
charcol)-50,100))-charindex('shadow',substring(charcol,charindex(@.se archphra[vbcol=seagreen]
charcol)-50,100))-charindex(@.SearchPhrase,substring(charcol,charinde x(@.searc[vbcol=seagreen]
replace(ltrim(substring(replace(substring(charcol, charindex(@.searchphrase,[vbcol=seagreen]
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase),1,len (replace(substrin[vbcol=seagreen]
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase))))))) ,char(13)+char(10[vbcol=seagreen]
charcol)-50,100))-charindex(@.SearchPhrase,substring(charcol,charinde x(@.searc[vbcol=seagreen]
charcol)-50,100),@.searchphrase,@.MarkedupSearchPhrase),char( 13)+char(10),' ')[vbcol=seagreen]
or[vbcol=seagreen]
phrase).[vbcol=seagreen]
the[vbcol=seagreen]
Server[vbcol=seagreen]
of[vbcol=seagreen]
such[vbcol=seagreen]
combine[vbcol=seagreen]
will[vbcol=seagreen]
column[vbcol=seagreen]

about xp_cmdshell..

Hello there!
I'm using xp_cmdshell in a loop to write in a file...
do you know better way to improve effiently writing on disk in a loop?
or any recommandations on xp_cmdshell uses (could we switch off the
msg feedbacks ?)
thanks a lot
++
VinceDTS, BCP, COM or .NET all seem likely to be more efficient ways of
writing to a file. What type of file are you trying to write?
David Portas
SQL Server MVP
--|||On 31 Mar 2005 01:18:03 -0800, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>DTS, BCP, COM or .NET all seem likely to be more efficient ways of
>writing to a file. What type of file are you trying to write?
I'm trying to write csv files. this, in a loop cursor which 'travels'
over 4 millions records so far...I mean, I must write row after row
because some of them are being value-changed during looping ...So I
could not use of BCP...
but what about DTS? can we use it in a loop?
thanks
Vince|||I don't understand why you think you need to do this cursor-style. Why can't
you do a set-based UPDATE for whatever data changes you require and THEN use
DTS, BCP or whatever other method to do the export?
Yes, you can export data row-by-row using DTS, but that doesn't necessarily
mean that's the best way to do it. BCP is probably a better choice for
millions of rows of data.
Comma-separated files are a poor medium for data at the best of times. For 4
million rows it seems like there ought to be a better way. What is the targe
t
system for the exported data? Can't it support other more structured file
formats? If you must delimit, avoid commas and use TAB or pipe characters
instead.
David Portas
SQL Server MVP
--|||Hello,
xp_CmdShell is not an efficient way of writing to a file. Instead you
can try with some Active-X Dll which can be instantiated by SQL Server
using sp_OACreate call. This may be a better way of doing "writing to
an external file" especially when there are large number of rows.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D SQL Snippet
Exec @.hr =3D sp_OACreate @.tProgId, @.iobToken Output
If @.hr <> 0 Goto TrapOAErrors
Exec @.hr =3D sp_OASetProperty @.iobToken, N'FileName', @.strFileName
If @.hr <> 0 Goto TrapOAErrors
Exec @.hr =3D sp_OAMethod @.iobToken, N'OpenFileForWriting'
If @.hr <> 0 Goto TrapOAErrors
--
--
Exec @.hr =3D sp_OAMethod @.iobToken, N'WriteBufferToFile', @.bytesBuffer
If @.hr <> 0 Goto TrapOAErrors
--
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DSQL Snippet
Snippet VB Active-X DLL --
'--=3D=3DFunction for Opening file for Reading=3D=3D=BB
Public Function OpenFileForWriting()
hOrgFile =3D CreateFile(m_sFileName, GENERIC_READ, FILE_SHARE_READ Or
FILE_SHARE_WRITE, ByVal 0&, OPEN_EXISTING, 0, 0)
If hOrgFile <> 0 Then
SetFilePointer hOrgFile, 0, 0, FILE_BEGIN
End If
End Function
'--=3D=3DFunction for: Closing the File Handle=3D=3D=BB
Public Function CloseFile()
CloseHandle hOrgFile
'--=3D=3DClose the file=3D=3D=BB
End Function
'--=3D=3DFunction for Retrieving the FileReadBuffer=3D=3D=BB
Public Function WriteBufferToFile(tBuff() As Byte)
lRet As Long
Dim m_lOffset As Long
Err.Clear
'--=3D=3DClear the Error Device=3D=3D=
=BB
If hOrgFile =3D 0 Then
OpenFileForWriting
If hOrgFile =3D 0 Then
ReadBlockIntoBuffer =3D ""
Return
End If
End If
WriteFile hOrgFile, tBuff(1), UBound(tBuff), Ret, ByVal 0&
'--=3D=3DCheck for errors=3D=3D=BB
If lRet <> UBound(bBytes) Then 'Failed to READ the whole file
ErrorFlag =3D True
ErrorNumber =3D Err.Number
ErrorDesc =3D Err.Description
End If
End Function
Snippent Ends ---
The Above snippet is not complete and may not work :-)
Rgds ...
--Bju.|||If you have the option of including this in a .Net project, I would
recommend a nice normal select statement, using a sql reader, loop over
the reader and perform your row by row logic changes, and use my csv
parser/writer to create the file. The writer is not free however, but
is relatively cheap. At worst, I'd still recommend basically the same
way of doing it, using another way of creating the file in place of my
writer. The writer itself is going to create files at roughly 10 mb per
second.
http://www.geocities.com/shriop/index.html

About XML upload to database

HI

Can anyone tell me how to upload a xml file in a table.I have uploaed text,csv,xel file but never xml.

just want to know how to upload, and when,why should we use xml file for data

thanks

sandipan

You can use an XML Source to read XML data and import it into a table.

about the workload of DTA?

Why i could not supply a query as workload to tuning engine?

I found some session using query, but mine only have table and file?

many thanks

DTA allows you to 'tune' a single query, or a saved workload (saved in either a file or db table.)

See this article for working details:

http://download.microsoft.com/download/e/8/b/e8b42814-6a0c-40eb-911f-e7adec87f5d5/SQL2005DTA.doc

|||

You have read up on the documentation, correct?

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

About the location of database file.

I can install the SQL Server Express in a computer and locate the database files in another computer of the same local network?

No, you cannot.

The datafiles have to be on a local drive -NOT a mapped drive, or even an UNC share. (A properly configured NAS/SAN is different though.)

about the e-mail question of Sql Server 2005 notification services

Dear all

can notification services send a e-mail with a accessory of PDF file ?

Tanks a lot.

Out of the box, SSNS comes with a SMTP delivery channel that does not support attachments. However, you can easily create your own custom event provider for this.

Or, the notification can send a hyperlink to a PDF file that's available online.

HTH...

Joe|||Hi Joe

Thank you for your help.

about the e-mail question of Sql Server 2005 notification services

Dear all

can notification services send a e-mail with a accessory of PDF file ?

Tanks a lot.

Out of the box, SSNS comes with a SMTP delivery channel that does not support attachments. However, you can easily create your own custom event provider for this.

Or, the notification can send a hyperlink to a PDF file that's available online.

HTH...

Joe|||Hi Joe

Thank you for your help.

about SQL Server Express

i am current developing a project. in visual studio i can create a database file. if i use the database file in my app. does it require SQL Server Express install on a user pc in order to run my app?

thanks

If you can create a DB within Visual Studio, then you already have the SQL Express installed on your PC.|||

i meant when people using my app, will they have to install SQL Server Express?

thanks

|||If this is a web application, then only the web server requires SQL Express. Clients only require the browser. On the other hand, if you're building a Windows application that run locally on each PC, then yes, you do need local installation of SQL Express.