Distributed query

First question : what are the necessary step to allow remote access (linked
server) using Windows logging and no Active Directory available
I tried the following without success
EXEC sp_addlinkedsrvlogin 'sql2k5', 'false', 'Soline\myLogin',
'sql2k5\mylogin', 'remotepassword'
but when I map to sa remote server login it works fine, so my questions
A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able t
o
run distributed query. what I mean by that is there any good step by step
procedure to follow to implment this using min priviliges on both sides as o
n
Lcris blog regarding "Cross database access"Hi,
If you would like to use Windows login without AD to access linked servers,
the Windows Authentication is NTLM then.
It is required to create a same user with same password on each machine.
For your two questions:
> A - Does that mean that we can not map windows login as far as delegation
is not possible becaus of non existance of AD?
If there is no AD, please manually create the Windows user with the same
password on both machine.

> B - what are the min requirements (privilges) to be given to login rather
then "SA" login on the server leveland on the DB level in order to be able
to run distributed query.
> what I mean by that is there any good step by step procedure to follow to
implment this using min priviliges on both sides as on Lcris blog regarding
"Cross database access"
If you just want to query the database, the min requirements is that the
user corresponed to the login needs db_datareader permission.
First create a login on the remote server, double click the login, switch
to the User Mapping, map the login to a user of the database, and assign
db_datareader permission;
Then on your local server, execute the SPs as following:
sp_addlinkedserver 'mySqlServer-03'
sp_addlinkedsrvlogin 'mySqlServer-03',false,NULL,'s1','s1'
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Distinct

Hi, I've recently moved a site to MSSQL server from Access and I've hit a few problems as it implements SQL differently. I've had a lot of duplicate data on the site which I have partly remedied by using SELECT DISTINCT - though I don't know how to use this in conjunction with SELECT * to cure other parts of my site that return duplicate data. Does anyone know how to do this? Thanks!First let mi say that MSSQL Server supports most(if not all) of the sql u write in Ms Access; it however builds on this to provide proprietary utility aggregate functions using T-SQL...
So depending on ur calling application(front end)...use 'select distinct' in ur SQL cmd to get distinct record temporarily or u just go 2 d database to do dat - select distinct into a temporary table, delete d table n insert d distinct records into a new table dat bears d name of the old(deleted) table.|||ok thanks, i'll look into that.

Like above because of syntax differences, this no longer works and the page does not display:

"Select * from Events where Format(EventDate,'yyyymmdd') >= " & FormatDate(Now(),"YYYYMMDD") & " order by EventDate asc"

I'm pretty sure it has something to do with Format(), but I'm a little unsure. Any ideas?

Thanks!|||u cannot use an expression directly on the column name, so try:
"Select * from Events where EventDate >= " & FormatDate(Now(),"YYYYMMDD") & " order by EventDate asc"|||hmm still not working|||use convert function of sql serversql

Distant access to Excel file for import

Hi,

I'm developping a web application and I need to import a data from an excel file to sql server 2005.

It works very well locally (visual studio & sql server & the excel file on the same machine).

But it doesn't work when visual studio & excel file are in a machine and sql server is in another server.

I have always the same problem:

'C:\Documents and Settings\momo\Bureau\File.xls' n'est pas un chemin d'accès valide. Assurez-vous que le nom du chemin d'accès est correct et qu'une connexion est établie avec le serveur sur lequel réside le fichier.

Could any one help me please ?!

Thank you in advance.

It would help if you translated the French error message to English.

|||

Sorry to be late !

Here is the translation :

'C:\Documents and Settings\momo\Desktop\File.xls' isn't a correct access path. Be sure that the name of the access path is correct and a connexion is established with the server where is the file.

In fact, I'm using the FileUpload control as follows:

string filemap = Convert.ToString(fileupload.PostedFile.FileName);

string conString = @."Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=YES;"";Data Source=" + filemap;

Could you tell me, please, what do I have to modify in order to be able to import my file from any client machine (of course, each client machine has a copy of the same file locally).

Thanks in advance.

|||

You need to think of a different architecture. You should create a web service that writes to the SQL Server. On each client PC you need a windows application that can read the excel spreadsheet and write to the web service.

|||

Please, could I have more details on how to do it. I'm a begginer in ASP NET and so I do not know what a web service means exactely.

Thanks.

|||

Have a look at http://quickstarts.asp.net/QuickStartv20/webservices/default.aspx and work through the tutorial

"The ASP.NET Web Services QuickStart is a series of client and server samples along with supporting commentary designed to quickly acquaint developers with featuresin ASP.NET Web Services. The QuickStart samples are designed to be short, easy-to-understand illustrations of these features. By the time you finish reviewing these samples, you will be familiar with the majorfeature areas in ASP.NET Web Services. "

Displaying HTML within an Access Report

Is this possible? I have a column which has data with HTML formatting.
Is it possible to remove the tags within a Stored Procedure so that it
displays nicely in an Access Report?
This is my SP:
ALTER PROCEDURE dbo.sp_Phat_Beats
AS SELECT fldcat, flddescript, fldtracklisting, fldprice, fldCategory
FROM dbo.tblProducts
WHERE (fldprice <> 0) AND (fldCategory = 17)
ORDER BY fldcat
fldtracklisting is the field that is HTML formatted. Any help would be
relly appreciated.
SteveHi
You could use replace multiple times to remove each tag, but this would
require embeded calls to the procedure one call per string you wish to
replace which could prove tedious. Check out replace in Books online
John
"Dooza" wrote:

> Is this possible? I have a column which has data with HTML formatting.
> Is it possible to remove the tags within a Stored Procedure so that it
> displays nicely in an Access Report?
> This is my SP:
> ALTER PROCEDURE dbo.sp_Phat_Beats
> AS SELECT fldcat, flddescript, fldtracklisting, fldprice, fldCategory
> FROM dbo.tblProducts
> WHERE (fldprice <> 0) AND (fldCategory = 17)
> ORDER BY fldcat
> fldtracklisting is the field that is HTML formatted. Any help would be
> relly appreciated.
> Steve
>

Displaying a relational database as it is displayed in MS Access

Can this be done in ASP.Net, as it stands my database views in my ASP.Net application are just standard

Unlike the view in MS Access which shows the collapsable linked data below the data (from a different table)

Many thanks

Rich

Yes, it's possible to create a GUI similar to the one that comes with Access. It's not a trivial undertaking though. You can use CSS, javascript and Ajax to overcome the inherent limitations of html.|||If your tables/views are relatively narrow and have few rows (the table can be convenantly viewed on a screen without scrolling), then use a DataGrid or GridView, and set it to automatic columns - this will give you a read-only view of the database. Code behind can be written to allow updates, but you do need to enforce any business rules.|||Have a look at http://www.codeproject.com/cs/database/DBViewer.asp A simple database viewer to manipulate SQL Server data types (in particular: image, binary, varbinary and text).|||Also try http://www.codeproject.com/asp/ute.asp "Viewer and Editor for any table in any Database you can reach from your IIS/PWS."

Display picture in crystal report

i have a picture field in an ms access database and i have problem displaying the picture in crystal report. it shows the name of the file in the field instead of the picture itself.Did you store only filepath or the image itself?
If it is file path then it wont work. You need to save the image itself to the table