only those jobs that run in the databases to which they have access

In the book <Inside SQL Server 2005 Tools>,

there is a paragraph as below:

=====================

Warning

In an ideal world, members of SQLAgentReaderRole and SQLAgentOperatorRole would be restricted to viewing and acting upon only those jobs that run in the databases to which they have access. Unfortunately, the SQL Server Agent security model does not allow this finer-granularity filtering. Therefore, when you make a user a member of these roles you must realize the implied security consequences to avoid unintentional information disclosure.

=====================

What is meaning of the sentence in red above?

Members of these database roles can view and execute jobs that they own, and create job steps that run as an existing proxy account, if you use.

HLEP..Sql Server UPDATE INNER JOIN QUERY ..

Im using an ADP to connect to a SQL Sqever DB.
In access it was really easy to say

Inner join on table1 and table2 and update columnA from table1 with
columnC from table2 where table1.key = table2.key and table2 columnB =
1 and table2 columnD = 4

I have tried all manner of beasts to get this thing to work..

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))

This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0' part which is really important

Any Help would be great...This way looks like it should work also but I get an error 'ADO Error:
HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
can only show one table in an update qurey?

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0))
WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)|||SAME ADO ERROR WHEN I USE THIS ???

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
FROM GIS_EVENTS_TEMP G INNER JOIN
HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
WHERE (H.UNITID = 1 AND H.LISTORDER = 0)|||(meyvn77@.yahoo.com) writes:
> Im using an ADP to connect to a SQL Sqever DB.
> In access it was really easy to say
> Inner join on table1 and table2 and update columnA from table1 with
> columnC from table2 where table1.key = table2.key and table2 columnB =
> 1 and table2 columnD = 4
>
> I have tried all manner of beasts to get this thing to work..
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
> AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))
> This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0' part which is really important
> Any Help would be great...

Unfortunately, it's not very easy to help if we don't know what
tables you have. The standard recommendation for this type of
problem is to post:

o CREATE TABLE statements of the tables nvolved. (Preferrably
cut down to the columns relevant to the problem.(
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the business problem.

The two first points makes it easy to copy and paste into Query Analyzer,
so a tested solution can be developed. The third point makes it possible
to verify that the solution is correct. And the fourth point gives some
extra information which helps to understand the general problem.

> This way looks like it should work also but I get an error 'ADO Error:
> HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
> can only show one table in an update qurey?
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0))
> WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)

No, but because you are referring to HARMFULEVENT outside the subquery.

> AME ADO ERROR WHEN I USE THIS ???
>
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
> FROM GIS_EVENTS_TEMP G INNER JOIN
> HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
> WHERE (H.UNITID = 1 AND H.LISTORDER = 0)

Here you are mixing use of aliases and table name. Once you have
introduced an alias, you can not refer to the full table name in
the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT

connect to distribution server

Need to be able to add data to a mobile with SQL2K
desktop edition and then manually replicate to SQL2K on
server. Can't access the distribution server from the
mobile.
Thoughts suggestions etc?
thanks
Sorry to ask the obvious, but is this when you are connected to the network?
Assuming it is, please can you clarify a few things:
the complete text of the error message,
the type of security configuration of your agents - are you logged onto the
domain, using pass-through security etc
can you 'see' the distribution server - pinging it by name will test this
Regards,
Paul Ibison
|||The distribution server is running on a SQLServer2K SE
machine running W2K OS. The remote is a laptop with
SQLServer2K PE. Both the remote and the server are
registered in EM respectively. They are both part of the
same workgroup but not a domain.
The problem arises when I attempt to use the Configure
Publishing, Subsribers and Distribution Wizard for the
laptop instance (SQLServer2K PE), The Select Distributor
dialogue has the "Use the following server..."
option "grayed-out" as is the "Add Server" button.
The only choice is to use the local machine as the
distributor which I don't want to do.
Is this a limitation of SQLServer2K PE or am I doing
something wrong with my configuration?
In the final anaylsis I need to be able to add data on
the laptop when not conncected to the server and then
manually synchronize when connected.
Thanks for your help
Thanks
>--Original Message--
>Sorry to ask the obvious, but is this when you are
connected to the network?
>Assuming it is, please can you clarify a few things:
>the complete text of the error message,
>the type of security configuration of your agents - are
you logged onto the
>domain, using pass-through security etc
>can you 'see' the distribution server - pinging it by
name will test this
>Regards,
>Paul Ibison
>
>.
>
|||The only restriction I was previously aware of is that SQL Server 2000
Personal Edition cannot be used as publisher for transactional replication.
However if you have a look at this link in BOL :
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\rep
wizrd.chm::/repwizrd_97oi.htm
it seems you are correct. This is a limitation of Personal Edition.
However,
if you are running merge replication, the distinction between publisher and
subscriber in this regard is not so big, so having the mobile as a
subscriber is a posibility.
HTH,
Paul Ibison
|||Thanks. I suppose I will have to work with merge
replication.
>--Original Message--
>The only restriction I was previously aware of is that
SQL Server 2000
>Personal Edition cannot be used as publisher for
transactional replication.
>However if you have a look at this link in BOL :
>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\rep
>wizrd.chm::/repwizrd_97oi.htm
>it seems you are correct. This is a limitation of
Personal Edition.
>However,
>if you are running merge replication, the distinction
between publisher and
>subscriber in this regard is not so big, so having the
mobile as a
>subscriber is a posibility.
>HTH,
>Paul Ibison
>
>.
>

.sdf databasse and programming

i need to access an .sdf database using microsfot visual forpro

what drivers/connection strings do i need?

If Foxpro supports ADO you can use the oledb provider, for a sample see http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/08/691894.aspx

.NET Windows Forms Application VS MS Access client Application

SQL Server will be used as the back-end database to a non trivial client
application.
In question is the choice of client application:
I need to be able to speak intelligently about when one client (MS Access vs
.NET Windows Forms) would be preferred over the other. While I have some
good arguments on both sides, I would appreciate your points of view on the
topic.
For the sake of this discussion, please assume a *non trivial* client
application with, say 120 forms, secure data processing, hundreds of
reports, and a clear need for a rich UI experience (MDI, a variety of rich
UI controls, non trivial printing requirements, etc).
I would appreciate help in compiling arguments both for and against each
technology (MS Access and .NET Windows Forms) as a client application.
So far I have this (in no particular order):
BENEFITS OF a .NET Windows Forms Application:
1. Client can be MDI (whereas Access only SDI)
2. Much richer UI with .NET (vs MS Access UI controls)
3. Easier deployment (with ClickOnce, XCopy, and similar .NET technologies
or methods). The client already has the CLR installed as part of their
standard desktop image - so I need to put nothing more than "XCopy" the
application files onto the local machine.
4. .NET requires a smaller footprint on the client with respect to the use
of 3rd party UI controls. MS Access is a COM-based technology and therefore
requires that 3rd party controls be COM controls. These require installation
to Windows\System32 and associated updates to the Registry (whereas .NET 3rd
party controls require only XCopy deployment to the application folder)
5. 3rd party UI controls for .NET are more prevalent, capable, and rich than
3rd party COM controls. Plus support for COM controls (i.e. number of 3rd
party companies making and supporting them) is expected to only decrease,
not increase, during the coming years - with the exact opposite trend
expected for 3rd party .NET controls.
6. .NET Windows Forms applications can take full advantage of OOP constructs
and patterns - thereby enabling the developers to create applications that
are easier to maintain, more easily extensible, and better architected than
the "equivalent" functionality provided in an MS Access application.
7. Visual Studio .NET significantly increases developer productivity (vs MS
Access support for application development)
8. The .NET base classes significantly increase developer productivity by
pre-packing substantial functionality that would have to be coded from
scratch in MS Access.
9. Runtime performance of a .NET application would likely be faster than MS
Access because MS Access (really Jet) necessarily entails a file server
architecture, while ADO.NET necessarily entails a distributed (and
disconnected) architecture.
10. ADO.NET takes care of connection pooling automatically and provides a
huge amount of built-in functionality that substantially increases developer
productivity and increases programmer control over database communications
and updates (as compared to JET and DAO).
DOWNSIDE OF a .NET Windows Forms Application:
1. Increased expertise required for .NET development - vs. MS Access (at
least that's the perception of the client)
2. Requires the target version of the CLR to be installed on the client
machines (leading possibly to multiple versions of the .NET Framework
installed simultaneously. Not that I have a problem with it, but their
desktop support folks might).
BENEFITS OF a .MS Access Client Application:
1. Less expertise required on the part of the developers (at least that's
the perception of the client).
2. Out of the box it includes many useful UI controls and a first-class
report writer.
DOWNSIDE OF a .MS Access Client Application:
1. Insert right here THE EXACT OPPOSITE of all of the benefits of a .NET
Windows Forms application, then:
2. The client machine must have the correct version of MS Access installed
(i.e. they're stuck with a particular version, or all must upgrade
simultaneously).
3. Passthrough queries would be required in order to get the query
processing to happen on the server. Passthrough queries ential some
additional complexity than non passthrough queries.
4. Without the use of passthrough queries, SQL Server's locking mechanisms
can behave unexpectedly (e.g., locks can unexpectedly be placed on tables
rather than pages or rows) - thereby resulting in slower performance.
5. Date processing logic is less secure because users can, relatively
easily, view and modify the client-side code and/or queries.
Any additions to these lists are greatly appreciated!
JordanThat's amusing. I won't attempt to correct your opinions,
but note that you haven't addressed reporting yet. I've
used Crystal, Access, and Report Services, and my opinion
is that you really need to accommodate the skill set of your
developers.
(david)
"Jordan S." <A@.B.COM> wrote in message
news:%23iVVsYoXGHA.196@.TK2MSFTNGP04.phx.gbl...
> SQL Server will be used as the back-end database to a non trivial client
> application.
> In question is the choice of client application:
> I need to be able to speak intelligently about when one client (MS Access
> vs .NET Windows Forms) would be preferred over the other. While I have
> some good arguments on both sides, I would appreciate your points of view
> on the topic.
> For the sake of this discussion, please assume a *non trivial* client
> application with, say 120 forms, secure data processing, hundreds of
> reports, and a clear need for a rich UI experience (MDI, a variety of rich
> UI controls, non trivial printing requirements, etc).
> I would appreciate help in compiling arguments both for and against each
> technology (MS Access and .NET Windows Forms) as a client application.
> So far I have this (in no particular order):
> BENEFITS OF a .NET Windows Forms Application:
> 1. Client can be MDI (whereas Access only SDI)
> 2. Much richer UI with .NET (vs MS Access UI controls)
> 3. Easier deployment (with ClickOnce, XCopy, and similar .NET technologies
> or methods). The client already has the CLR installed as part of their
> standard desktop image - so I need to put nothing more than "XCopy" the
> application files onto the local machine.
> 4. .NET requires a smaller footprint on the client with respect to the use
> of 3rd party UI controls. MS Access is a COM-based technology and
> therefore requires that 3rd party controls be COM controls. These require
> installation to Windows\System32 and associated updates to the Registry
> (whereas .NET 3rd party controls require only XCopy deployment to the
> application folder)
> 5. 3rd party UI controls for .NET are more prevalent, capable, and rich
> than 3rd party COM controls. Plus support for COM controls (i.e. number of
> 3rd party companies making and supporting them) is expected to only
> decrease, not increase, during the coming years - with the exact opposite
> trend expected for 3rd party .NET controls.
> 6. .NET Windows Forms applications can take full advantage of OOP
> constructs and patterns - thereby enabling the developers to create
> applications that are easier to maintain, more easily extensible, and
> better architected than the "equivalent" functionality provided in an MS
> Access application.
> 7. Visual Studio .NET significantly increases developer productivity (vs
> MS Access support for application development)
> 8. The .NET base classes significantly increase developer productivity by
> pre-packing substantial functionality that would have to be coded from
> scratch in MS Access.
> 9. Runtime performance of a .NET application would likely be faster than
> MS Access because MS Access (really Jet) necessarily entails a file server
> architecture, while ADO.NET necessarily entails a distributed (and
> disconnected) architecture.
> 10. ADO.NET takes care of connection pooling automatically and provides a
> huge amount of built-in functionality that substantially increases
> developer productivity and increases programmer control over database
> communications and updates (as compared to JET and DAO).
> DOWNSIDE OF a .NET Windows Forms Application:
> 1. Increased expertise required for .NET development - vs. MS Access (at
> least that's the perception of the client)
> 2. Requires the target version of the CLR to be installed on the client
> machines (leading possibly to multiple versions of the .NET Framework
> installed simultaneously. Not that I have a problem with it, but their
> desktop support folks might).
> BENEFITS OF a .MS Access Client Application:
> 1. Less expertise required on the part of the developers (at least that's
> the perception of the client).
> 2. Out of the box it includes many useful UI controls and a first-class
> report writer.
> DOWNSIDE OF a .MS Access Client Application:
> 1. Insert right here THE EXACT OPPOSITE of all of the benefits of a .NET
> Windows Forms application, then:
> 2. The client machine must have the correct version of MS Access installed
> (i.e. they're stuck with a particular version, or all must upgrade
> simultaneously).
> 3. Passthrough queries would be required in order to get the query
> processing to happen on the server. Passthrough queries ential some
> additional complexity than non passthrough queries.
> 4. Without the use of passthrough queries, SQL Server's locking mechanisms
> can behave unexpectedly (e.g., locks can unexpectedly be placed on tables
> rather than pages or rows) - thereby resulting in slower performance.
> 5. Date processing logic is less secure because users can, relatively
> easily, view and modify the client-side code and/or queries.
> Any additions to these lists are greatly appreciated!
> Jordan
>|||Thanks for your perspective. Skill set of the developers is very important
as you mentioned, and a shorter learning curve on Access may be relevant to
the final decision.
Please feel free to add to the lists - *all* sides must be represented.|||Why do you say that Access is only SDI? You can open multiple forms,
each one individually resizable & repositionable within the Access
application window. Sounds like MDI, to me!
One of the primary advantages of Access, IMO, is its data-bound
controls. You don't need any code at all, to bind a control to a field
in the data source of a form or report.
As for the rest, IMHO you are asking too much from a newsgroup staffed
by volunteers. It would be a non-trivial consulting task to provide the
detailed comparative report that you want. And you'd really want it to
be done by someone who was expert in both technologies (winforms and
Access). Otherwise, it's too easy for the winforms person to slag
Access (through lack of knowledge of the product), and vice versa.
HTH,
TC (MVP Access)
http://tc2.atspace.com|||It just frightens me that anyone would even consider using MS Access as
anything other than a torture device. Your point #6 for .NET benefits
is very important, and could easily be split out into about 20.
When the application needs to change to do some extra functionality
like access a web service, or perform complex operations on your data
then you would start crying if you were using access. An application
with 120 forms is probably going to have some seriously complex
requirement changes coming out that you won't find out about until mid
way through developing it (usually when the client actually sees a
screen working then says "oh, but if it's a saturday and it's raining
we don't do it like that"). You'll need the ability to put in some
serious design patterns that permit you to change this without having
to re-structure everything.|||Nonsense. A well designed & written systems can generally be enhanced
without much trouble. A badly designed & written system can't. The
workman has much more effect on this, than the tool. You could easily
have a well designed & written Access system, that was easy to enhance,
and a badly designed & written .NET system, that was a nightmare to
enhance.
TC (MVP Access)
http://tc2.atspace.com|||"Jordan S." <A@.B.COM> wrote in message
news:%23iVVsYoXGHA.196@.TK2MSFTNGP04.phx.gbl...

> 2. Much richer UI with .NET (vs MS Access UI controls)
While it is true that there is a wider range of controls available in .NET,
Access provides all the controls that a typical data-centric application
really needs.

> 4. .NET requires a smaller footprint on the client with respect to the use
> of 3rd party UI controls. MS Access is a COM-based technology and
> therefore requires that 3rd party controls be COM controls. These require
> installation to Windows\System32 and associated updates to the Registry
> (whereas .NET 3rd party controls require only XCopy deployment to the
> application folder)
See my answer to point 2 above. The availability or otherwise of third-party
controls isn't an issue when you don't need any third-party controls.

> 5. 3rd party UI controls for .NET are more prevalent, capable, and rich
> than 3rd party COM controls. Plus support for COM controls (i.e. number of
> 3rd party companies making and supporting them) is expected to only
> decrease, not increase, during the coming years - with the exact opposite
> trend expected for 3rd party .NET controls.
See answers to 2 and 4 above.

> 7. Visual Studio .NET significantly increases developer productivity (vs
> MS Access support for application development)
This has not been my experience.

> 8. The .NET base classes significantly increase developer productivity by
> pre-packing substantial functionality that would have to be coded from
> scratch in MS Access.
This has not been my experience.

> 9. Runtime performance of a .NET application would likely be faster than
> MS Access because MS Access (really Jet) necessarily entails a file server
> architecture, while ADO.NET necessarily entails a distributed (and
> disconnected) architecture.
I can't say for sure whether a .NET app is likely to be faster, but I can
say from experience that a well-designed Access app can perform more than
satisfactorily on a LAN.

> 10. ADO.NET takes care of connection pooling automatically and provides a
> huge amount of built-in functionality that substantially increases
> developer productivity and increases programmer control over database
> communications and updates (as compared to JET and DAO).
See answers to 7 and 8 above regarding developer productivity.

> 2. The client machine must have the correct version of MS Access installed
> (i.e. they're stuck with a particular version, or all must upgrade
> simultaneously).
Not necesarily. Access 2002 and 2003 use the same file format by default as
Access 2000. You can run the same MDB under the last three versions of
Access, provided you are careful not to use any new features that were not
supported in Access 2000.

> Any additions to these lists are greatly appreciated!
You haven't mentioned what you're going to use for reporting in .NET. SQL
Server Reporting Services is in many respects, but I miss the tight
integration of the Access report designer and engine, and SQL Server
Reporting Services requires additional installation and configuration on the
server.
Generally speaking, my experience so far is that ASP.NET has been a great
leap forward for Web-based applications, but I remain to be convinced about
the benefits of using Windows Forms for typical data-centric desktop
applications.
Brendan Reynolds
Access MVP|||Fair point that the developer skill is the key factor.
But do you really see Access as a scalable solution for a 120 form
application? I do admit that I've only had a few frustrating encounters
with access forms applications, however it seemed to me that while
their databound controls are their strength for a simple application,
they lack the expressiveness available to .NET controls. I can see the
advantage of access forms if it's managing the database as well, but
when you need to have very particular formatting of your controls, and
tie in to a large variety of events .NET is definitely easier / more
obvious in how to do this.
Secondly, while I do accept that a .NET application can be written
badly, if written well then it will provide a more scalable and
maintainable architecture. I don't believe that Access was designed to
develop complicated middle tier logic, and while it may be possible to
do it in Access, .NET was built with this in mind.
I do agree we shouldn't start bashing other technologies though, so
please ignore my "torture device" comment posted previously|||Will wrote:

> Fair point that the developer skill is the key factor.

> But do you really see Access as a scalable solution for a 120 form application?[/c
olor]
Access can easily handle 120 forms. There'd be squintillions of working
Access databases around the world with that number. However, you're
certainly right, that the number can not grow arbitrarily. So, winforms
might be more scalable here - I don't know.
But it does raise the question, how many is enough? What are all these
forms *for*, in systems that have hundreds & hundreds & hundreds of
forms? Every time I hear of one, I think: "Geez, surely it would be
possible to have a smaller # of forms & let them customize themselves
at runtime".
> I do admit that I've only had a few frustrating encounters
> with access forms applications, however it seemed to me that while
> their databound controls are their strength for a simple application,
> they lack the expressiveness available to .NET controls. I can see the
> advantage of access forms if it's managing the database as well, but
> when you need to have very particular formatting of your controls, and
> tie in to a large variety of events .NET is definitely easier / more
> obvious in how to do this.
It's hard to comment without specifics. You may be right - I don't know
enough about winforms to have an opinion. Certainly the Access event
model has a few deficiencies.

> Secondly, while I do accept that a .NET application can be written
> badly, if written well then it will provide a more scalable and
> maintainable architecture. I don't believe that Access was designed to
> develop complicated middle tier logic, and while it may be possible to
> do it in Access, .NET was built with this in mind.
You may well be right. I don't know enough about .NET & winforms to
have an opinion yet.

> I do agree we shouldn't start bashing other technologies though, so
> please ignore my "torture device" comment posted previously
No probs, thanks for that acknowledgement. I was girding my loins, to
enter the fray!
Cheers,
TC (MVP Access)
http://tc2.atspace.com|||Thanks for your perspective TC.
A couple of thoughts:
RE:
<< It would be a non-trivial consulting task to provide the detailed
comparative report that you want.>>
Exactly! That's my job and that's why I provided the initial lists in the OP
(to get the ball rolling). I hope I provided at least the "big hits" and
that that good folks here in the NG can just scan and say "oh, you missed x,
y, or z".
RE:
<< And you'd really want it to be done by someone who was expert in both
technologies (winforms and Access). >>
That's me to some extent also. FWIW I have 5 years of full-time and non
trivial MS Access programming experience (Access 2.0 through 97), 4 years in
VB, and 3 in .NET. So I can lay claim to some awareness of the strengths of
MS Access, plus some other technologies.
RE:
<< Otherwise, it's too easy for the winforms person to slag Access... and
vice versa >>
I'm not here to bash MS Access nor start any flame war. I'm just recognizing
a situation where it may not be the *best* tool for the job. Rather than
just saying "geeze we shouldn't use Access for non trivial UI programming" I
want to be able to state specifically why. And if I'm wrong in my
assumptions or beliefs then I also want to know specifically why.
Finally, towards avoiding a flame war, I'd like to encourage respondents to
avoid arguing against any points anyone here makes. All are taken as either
[completely valid] or [perceived as valid] and thus are greatly appreciated.
Thanks again!

.net Security Exception

I am trying to access a stored proc from windows app (VS 2005 running on windows 2003 server).

The code is

SqlConnection conn = new SqlConnection(sCn);

SqlCommand command = new SqlCommand(sCmd, conn);

SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet ds = new DataSet();

adapter.Fill(ds, "Location");

this.dg.DataSource = ds;

this.dg.DataMember = "Location";

I get the following exception!!!

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Internet

What am I doing wrong?

Thanks

SqlCommand command = new SqlCommand(sCmd, conn);

SqlDataAdapter adapter = new SqlDataAdapter(command);

what exactly is command refering to?

Secondly, if it's refering to a networked application, you probably have to publish the assemblies via caspol on the server.

Based on the error, you need to add a key for the Internet codebase.

.Net integration with SQL Reporting Services

We are working on a project that includes the development of business classes
to contain all our data access routines. The goal is to be avoid writing
numerous sql statements (some quite complex and some duplicative) in SQL
Reporting Services, forms development etc..
We've tested this model in .Net 2005 using the Report Viewer control and
have a successful pilot. The data access class returns a data set(s) that we
use in the Report Viewer. We would like to move this model to use Reporting
Services.
The question is:
We will compile our data access classes into a dll. We would like to be
able to reference the data access class and return a data set(s) that can be
used by Reporting Services designer. So far we have not figured out how to
do this.
Any suggestions or tips? Thx,your starting point:
http://msdn2.microsoft.com/en-us/library/ms154655.aspx
also you'll found a sample data extension in the official SQL Server website
(part of the samples for SQL 2005)
"def59485" <def59485@.discussions.microsoft.com> wrote in message
news:C3079CEB-6497-46AC-8949-44975EA320A2@.microsoft.com...
> We are working on a project that includes the development of business
> classes
> to contain all our data access routines. The goal is to be avoid writing
> numerous sql statements (some quite complex and some duplicative) in SQL
> Reporting Services, forms development etc..
> We've tested this model in .Net 2005 using the Report Viewer control and
> have a successful pilot. The data access class returns a data set(s) that
> we
> use in the Report Viewer. We would like to move this model to use
> Reporting
> Services.
> The question is:
> We will compile our data access classes into a dll. We would like to be
> able to reference the data access class and return a data set(s) that can
> be
> used by Reporting Services designer. So far we have not figured out how
> to
> do this.
> Any suggestions or tips? Thx,

.Net Deployment ....

Hi everybody,

I want install the project in client side .. In installation time i want to access the my server .. How i can make the setup file to access the internet.. please help me.....

Thanks & Regards,

S.Sajan

Not only is that NOT a good idea, many users will have firewalls that may keep your installation from accessing the internet.

See my post to your previous question for information about remote and unattended installations.

.net Data Provider (YOU can help)

I don't get a Data Provider to access my oracle database.
Can somebody send me his Data Provider?
If you have a Provider accessing the SQL-Server you can send also this
Provider.
E-Mail: Data.Prov@.Freenet.de
I'm a novice in Report Services and Data Provider and search for days now
for a solution. All I find doesn't help, I guess something simple and very
basic is missing.
Please help!You will need Reporting Services SP1 installed.
How to use Oracle in RS:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
Note: The Visual Data Tools (VDT) query designer (2 panes) actuall­y uses
OLE DB. The text-based generic query designer (GQD; 4 panes) uses th­e .NET
provider for Oracle, which indeed allows for named parameters.
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:4F451D47-B161-49C1-95ED-A6E5428E7ACB@.microsoft.com...
> I don't get a Data Provider to access my oracle database.
> Can somebody send me his Data Provider?
> If you have a Provider accessing the SQL-Server you can send also this
> Provider.
> E-Mail: Data.Prov@.Freenet.de
> I'm a novice in Report Services and Data Provider and search for days now
> for a solution. All I find doesn't help, I guess something simple and very
> basic is missing.
> Please help!|||I have SP1 installed and I still create reports with the oracle database. I'm
using in depth stored procedures for this reports, the SPs encapsulate comlex
applications.
I'm programming with VB since 10 years, so I have some general knowledge.
But I'm a novice in reporting services and .Net Data Providers. I wrote
application using ADO .net, so I have a basic understanding of ADO.Net.
But it is absolut impossible for me to get this Data Provider for reporting
services running. I'm realy annoyed about the documentation and the news
group posts about this. There is a huge amount of descriptions like
"implement interface A, B, C ..." instate of publishing somewhere a usefull
sample application.
The FSI... Sample is a joke. How many people will list files from the
filesystem in a report and how many will access a real database to create
reports? This are all "doghouse" samples. For a doghouse you need a hammer
and some nails but for a real building used by people there is something more
required.
"Alexandre Mineev [MSFT]" wrote:
> You will need Reporting Services SP1 installed.
> How to use Oracle in RS:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> Note: The Visual Data Tools (VDT) query designer (2 panes) actuall­y uses
> OLE DB. The text-based generic query designer (GQD; 4 panes) uses th­e .NET
> provider for Oracle, which indeed allows for named parameters.
>
> --
> Alex Mineev
> Software Design Engineer. Report expressions; Code Access Security; Xml;
> SQE.
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:4F451D47-B161-49C1-95ED-A6E5428E7ACB@.microsoft.com...
> > I don't get a Data Provider to access my oracle database.
> >
> > Can somebody send me his Data Provider?
> > If you have a Provider accessing the SQL-Server you can send also this
> > Provider.
> > E-Mail: Data.Prov@.Freenet.de
> >
> > I'm a novice in Report Services and Data Provider and search for days now
> > for a solution. All I find doesn't help, I guess something simple and very
> > basic is missing.
> >
> > Please help!
>
>|||No answer is a answer too!
"Dev Main" wrote:
> I have SP1 installed and I still create reports with the oracle database. I'm
> using in depth stored procedures for this reports, the SPs encapsulate comlex
> applications.
> I'm programming with VB since 10 years, so I have some general knowledge.
> But I'm a novice in reporting services and .Net Data Providers. I wrote
> application using ADO .net, so I have a basic understanding of ADO.Net.
> But it is absolut impossible for me to get this Data Provider for reporting
> services running. I'm realy annoyed about the documentation and the news
> group posts about this. There is a huge amount of descriptions like
> "implement interface A, B, C ..." instate of publishing somewhere a usefull
> sample application.
> The FSI... Sample is a joke. How many people will list files from the
> filesystem in a report and how many will access a real database to create
> reports? This are all "doghouse" samples. For a doghouse you need a hammer
> and some nails but for a real building used by people there is something more
> required.
> "Alexandre Mineev [MSFT]" wrote:
> > You will need Reporting Services SP1 installed.
> >
> > How to use Oracle in RS:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> >
> > Note: The Visual Data Tools (VDT) query designer (2 panes) actuall­y uses
> > OLE DB. The text-based generic query designer (GQD; 4 panes) uses th­e .NET
> > provider for Oracle, which indeed allows for named parameters.
> >
> >
> > --
> > Alex Mineev
> > Software Design Engineer. Report expressions; Code Access Security; Xml;
> > SQE.
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:4F451D47-B161-49C1-95ED-A6E5428E7ACB@.microsoft.com...
> > > I don't get a Data Provider to access my oracle database.
> > >
> > > Can somebody send me his Data Provider?
> > > If you have a Provider accessing the SQL-Server you can send also this
> > > Provider.
> > > E-Mail: Data.Prov@.Freenet.de
> > >
> > > I'm a novice in Report Services and Data Provider and search for days now
> > > for a solution. All I find doesn't help, I guess something simple and very
> > > basic is missing.
> > >
> > > Please help!
> >
> >
> >|||Two things. Unless it is a managed newsgroup posting you are not guaranteed
a MS response. This is peer response with MS people jumping in from time to
time.
Second, are you trying to create your own data processing extension or is
the issue that you are having trouble using stored procedures in Oracle?
First you asked for people to send you a data provider for SQL Server or
Oracle which I didn't answer because it didn't make sense to me. If the
issue is using Oracle people are successfully using Oracle. What
specifically is your problem using Oracle with Reporting Services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:64B97405-AECC-45DA-8A93-551BBAE82FA2@.microsoft.com...
> No answer is a answer too!
> "Dev Main" wrote:
> > I have SP1 installed and I still create reports with the oracle
database. I'm
> > using in depth stored procedures for this reports, the SPs encapsulate
comlex
> > applications.
> >
> > I'm programming with VB since 10 years, so I have some general
knowledge.
> >
> > But I'm a novice in reporting services and .Net Data Providers. I wrote
> > application using ADO .net, so I have a basic understanding of ADO.Net.
> >
> > But it is absolut impossible for me to get this Data Provider for
reporting
> > services running. I'm realy annoyed about the documentation and the news
> > group posts about this. There is a huge amount of descriptions like
> > "implement interface A, B, C ..." instate of publishing somewhere a
usefull
> > sample application.
> >
> > The FSI... Sample is a joke. How many people will list files from the
> > filesystem in a report and how many will access a real database to
create
> > reports? This are all "doghouse" samples. For a doghouse you need a
hammer
> > and some nails but for a real building used by people there is something
more
> > required.
> >
> > "Alexandre Mineev [MSFT]" wrote:
> >
> > > You will need Reporting Services SP1 installed.
> > >
> > > How to use Oracle in RS:
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> > >
> > > Note: The Visual Data Tools (VDT) query designer (2 panes) actuall­y
uses
> > > OLE DB. The text-based generic query designer (GQD; 4 panes) uses th­e
.NET
> > > provider for Oracle, which indeed allows for named parameters.
> > >
> > >
> > > --
> > > Alex Mineev
> > > Software Design Engineer. Report expressions; Code Access Security;
Xml;
> > > SQE.
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
rights
> > >
> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > > news:4F451D47-B161-49C1-95ED-A6E5428E7ACB@.microsoft.com...
> > > > I don't get a Data Provider to access my oracle database.
> > > >
> > > > Can somebody send me his Data Provider?
> > > > If you have a Provider accessing the SQL-Server you can send also
this
> > > > Provider.
> > > > E-Mail: Data.Prov@.Freenet.de
> > > >
> > > > I'm a novice in Report Services and Data Provider and search for
days now
> > > > for a solution. All I find doesn't help, I guess something simple
and very
> > > > basic is missing.
> > > >
> > > > Please help!
> > >
> > >
> > >|||Make you use RS SP1.
How to use Oracle in RS:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
Note: The Visual Data Tools (VDT) query designer (2 panes) actuall­y uses
OLE DB. The text-based generic query designer (GQD; 4 panes) uses th­e .NET
provider for Oracle, which indeed allows for named parameters.
Alex Mineev
Software Design Engineer. Report expressions; Code Access Security; Xml;
SQE.
This posting is provided "AS IS" with no warranties, and confers no rights
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:4F451D47-B161-49C1-95ED-A6E5428E7ACB@.microsoft.com...
> I don't get a Data Provider to access my oracle database.
> Can somebody send me his Data Provider?
> If you have a Provider accessing the SQL-Server you can send also this
> Provider.
> E-Mail: Data.Prov@.Freenet.de
> I'm a novice in Report Services and Data Provider and search for days now
> for a solution. All I find doesn't help, I guess something simple and very
> basic is missing.
> Please help!|||@.Bruce: Thank you for the answer and sory for my last comment.
I don't have problems with oracle. I build reports using oracle data. I have
no problem with stored procedures, packages and everything else. Everything
works.
I'm unable to get a data provider for RS running and access the oracle
database. I'm familare with VB, with VB.net, ADO.net, Databases, Stored
Procedures etc. But it seems I don't know the right syntax to use in this
dataprovider. I like to complete the FSI... Example that it fullfills my
requirements. I read all documentation and spend many hours on it.
If I would implement a application using ado.net I would create a object for
the connection and refer to them. This seems to be not the right aproach in
this Data Provider. All the Implement interface blabla doesn't help me,
becouse I simply don't know how to do this exactly.
A simple real sample would save the hundrets of hours I'm waste on this.
The reason why I like to implement the data provider is, that im must
execute several stored procedures and return the result from a temporary
table. I can't use the stored procedure to query the result, becouse Oracle
(7.3) requires that I specify the amount of expected records, before I'm send
this stored procedure. If the dataprovider is running, I like to change them
in a way, that I can do something else before I execute the query. Returning
a dataset with a stored procedure from oracle (even if I know the expected
amount of returned records) is not efficient and results in bad performance.
Inside the dataprovider I like to create datasets and return them to
Reporting Services.
Thanks in advance!
"Dev Main" wrote:
> I don't get a Data Provider to access my oracle database.
> Can somebody send me his Data Provider?
> If you have a Provider accessing the SQL-Server you can send also this
> Provider.
> E-Mail: Data.Prov@.Freenet.de
> I'm a novice in Report Services and Data Provider and search for days now
> for a solution. All I find doesn't help, I guess something simple and very
> basic is missing.
> Please help!|||Ouch, 7.3. Everyone I have seen use Oracle has been on at least 8i.
One possible solution for you would be to use the controls coming out with
the next beta of Yukon and Widbey. These will work the way you are
expecting. It depends on when you have to go into production. There might be
a go live license for the next beta of Widbey but I don't know if that
includes the new controls or not. These new controls are really going to be
a help.
Although similar what it sounds like you are wanting to do is to create a
data processing extension. I suggest posting asking if anyone has additional
examples of data processing extensions (which is different from have a data
provider, similar but different).
I have two books which I suggest buying that between the two of them would
really get you going writing your own data processing extension: Microsoft
Reporting Services In Action by Teo Lachev (an MVP) and Hitchhiker's Guide
to SQL Server 2000 Reporting Services by Peter Blackburn and Willam Vaughn.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:AE9B3520-233F-4D6D-8EBC-746A29D68FF3@.microsoft.com...
> @.Bruce: Thank you for the answer and sory for my last comment.
> I don't have problems with oracle. I build reports using oracle data. I
have
> no problem with stored procedures, packages and everything else.
Everything
> works.
> I'm unable to get a data provider for RS running and access the oracle
> database. I'm familare with VB, with VB.net, ADO.net, Databases, Stored
> Procedures etc. But it seems I don't know the right syntax to use in this
> dataprovider. I like to complete the FSI... Example that it fullfills my
> requirements. I read all documentation and spend many hours on it.
> If I would implement a application using ado.net I would create a object
for
> the connection and refer to them. This seems to be not the right aproach
in
> this Data Provider. All the Implement interface blabla doesn't help me,
> becouse I simply don't know how to do this exactly.
> A simple real sample would save the hundrets of hours I'm waste on this.
> The reason why I like to implement the data provider is, that im must
> execute several stored procedures and return the result from a temporary
> table. I can't use the stored procedure to query the result, becouse
Oracle
> (7.3) requires that I specify the amount of expected records, before I'm
send
> this stored procedure. If the dataprovider is running, I like to change
them
> in a way, that I can do something else before I execute the query.
Returning
> a dataset with a stored procedure from oracle (even if I know the expected
> amount of returned records) is not efficient and results in bad
performance.
> Inside the dataprovider I like to create datasets and return them to
> Reporting Services.
> Thanks in advance!
> "Dev Main" wrote:
> > I don't get a Data Provider to access my oracle database.
> >
> > Can somebody send me his Data Provider?
> > If you have a Provider accessing the SQL-Server you can send also this
> > Provider.
> > E-Mail: Data.Prov@.Freenet.de
> >
> > I'm a novice in Report Services and Data Provider and search for days
now
> > for a solution. All I find doesn't help, I guess something simple and
very
> > basic is missing.
> >
> > Please help!|||Thank you!
I will order the recomended books today.
I have to go productive with one single report in 2 weeks.
The oracle version is in the scope from the customer and I have no influence
on it. It seems I'm living in a different world then other developers. My
customers are in most cases not uptodate. Wether with OS nor with Databases,
Exchange, Office etc. They say: "why should we change a running system, new
version new troubles". And if I see the ripeness of Reporting Services or the
ESH (Exchange Server Hell), I can understand them.
"Bruce L-C [MVP]" wrote:
> Ouch, 7.3. Everyone I have seen use Oracle has been on at least 8i.
> One possible solution for you would be to use the controls coming out with
> the next beta of Yukon and Widbey. These will work the way you are
> expecting. It depends on when you have to go into production. There might be
> a go live license for the next beta of Widbey but I don't know if that
> includes the new controls or not. These new controls are really going to be
> a help.
> Although similar what it sounds like you are wanting to do is to create a
> data processing extension. I suggest posting asking if anyone has additional
> examples of data processing extensions (which is different from have a data
> provider, similar but different).
> I have two books which I suggest buying that between the two of them would
> really get you going writing your own data processing extension: Microsoft
> Reporting Services In Action by Teo Lachev (an MVP) and Hitchhiker's Guide
> to SQL Server 2000 Reporting Services by Peter Blackburn and Willam Vaughn.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:AE9B3520-233F-4D6D-8EBC-746A29D68FF3@.microsoft.com...
> > @.Bruce: Thank you for the answer and sory for my last comment.
> >
> > I don't have problems with oracle. I build reports using oracle data. I
> have
> > no problem with stored procedures, packages and everything else.
> Everything
> > works.
> >
> > I'm unable to get a data provider for RS running and access the oracle
> > database. I'm familare with VB, with VB.net, ADO.net, Databases, Stored
> > Procedures etc. But it seems I don't know the right syntax to use in this
> > dataprovider. I like to complete the FSI... Example that it fullfills my
> > requirements. I read all documentation and spend many hours on it.
> >
> > If I would implement a application using ado.net I would create a object
> for
> > the connection and refer to them. This seems to be not the right aproach
> in
> > this Data Provider. All the Implement interface blabla doesn't help me,
> > becouse I simply don't know how to do this exactly.
> > A simple real sample would save the hundrets of hours I'm waste on this.
> >
> > The reason why I like to implement the data provider is, that im must
> > execute several stored procedures and return the result from a temporary
> > table. I can't use the stored procedure to query the result, becouse
> Oracle
> > (7.3) requires that I specify the amount of expected records, before I'm
> send
> > this stored procedure. If the dataprovider is running, I like to change
> them
> > in a way, that I can do something else before I execute the query.
> Returning
> > a dataset with a stored procedure from oracle (even if I know the expected
> > amount of returned records) is not efficient and results in bad
> performance.
> > Inside the dataprovider I like to create datasets and return them to
> > Reporting Services.
> >
> > Thanks in advance!
> >
> > "Dev Main" wrote:
> >
> > > I don't get a Data Provider to access my oracle database.
> > >
> > > Can somebody send me his Data Provider?
> > > If you have a Provider accessing the SQL-Server you can send also this
> > > Provider.
> > > E-Mail: Data.Prov@.Freenet.de
> > >
> > > I'm a novice in Report Services and Data Provider and search for days
> now
> > > for a solution. All I find doesn't help, I guess something simple and
> very
> > > basic is missing.
> > >
> > > Please help!
>
>

.Net 2003 access Crystal Report XI: Error:Failed to open the connection: [Database Vendor Code:

I am receving this error: Failed to open the connection: [Database Vendor Code: 17 ], from .Net 2003/C# to Crystal Report XI with SQL Server 2000 has backend database.

Is there a security permission that has been overlooked?

Here is the code I use to access CR XI

ConnectionInfo connectionInfo = new ConnectionInfo();
TableLogOnInfo tableLogOnInfo;
Database DB;
'CrystalDecisions.CrystalReports.Engine.Table' table;
Tables tables;

//Log in
connectionInfo.ServerName = "ServerName";
connectionInfo.DatabaseName = "Database Namet";
connectionInfo.UserID = "userid";
connectionInfo.Password = "password";

//Get Table inf from report
DB = reportDocument..Database;
tables = DB.Tables;

//Looping through all the tables in CR and apply connection info
for(int i = 0; i < tables.Count; i++)
{
table = tables[ i ];
tableLogOnInfo = table.LogOnInfo;
tableLogOnInfo.ConnectionInfo = connectionInfo;
table.ApplyLogOnInfo(tableLogOnInfo);

}

CrystalReportViewer.ReportSource = ReportDocument.ReportSource;
CrystalReportViewer.Visible = true;

Any advise would be helpful.
Thank you

Hi, Koolaidbomber

First, try to find osql.exe on your client box, "c:\program files\microsoft sql server\80\tools\binn", osql.exe /S<servermachine> /E, or "osql.exe /S<servermachine>\<Instancename> /E"( the latter is for named instance). would you also get the same error? For remote connection specific you need following checklist:

A. Enable "File and printer sharing" in Firewall if your server is behind that.

B. Add program "sqlbrowser.exe" or udp port "1434" in your firewall exception list.

C. Add tcp port "1433" of default instance or <port number> of named instance in firewall, check you errorlog to figure out which port server is listening on. If you want your connection over TCP and make sure your server has TCP enabled.

D. Try ping your remote server machine, name and ipaddress.

E. Try to telnet <remoterservername> <portnumber>

F. Try to access a share of your remote server.

If above list all passed, you should be able to connect to linked server unless other problem exists, normally, you could see event log or server log to get clue.

Good Luck!

Ming.

|||Ming,

Thank you for your response. Will try it and let you know.

Thanks again|||Hey Ming,

I gave what you suggested a go, but I still get the error when I access CR XI from .Net 2003. I have connection to the DB server, and I am able to run the CR XI on a stand alone bases, but when it come to accessing from .Net 2003 Web App, I get the error.

Thank again for your idea|||

I recommend reading http://msdn2.microsoft.com/en-us/library/ms227528.aspx regarding how to setup Crystal Reports to connect to SQL Server tutorial.

Since are you able to run the CR XI on standalone, sounds like your Web App config is wrong.
Please post the config setting for your web app.

Jimmy

|||Hi Jimmy -

As requested, here is a copy of my web.config file

<?xml version="1.0" encoding="utf-8"?>
<configuration>

<system.web>

<compilation defaultLanguage="c#" debug="true"><assemblies><add assembly="CrystalDecisions.CrystalReports.Engine, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.ReportSource, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Shared, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Web, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.ReportAppServer.ClientDoc, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Enterprise.Framework, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Enterprise.InfoStore, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Enterprise.Web, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/></assemblies></compilation>

<customErrors mode="RemoteOnly"/>

<authentication mode="Windows"/>

<authorization>
<allow users="*"/>

</authorization>

<trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true"/>

<sessionState mode="InProc" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes" cookieless="false" timeout="20"/>

<globalization requestEncoding="utf-8" responseEncoding="utf-8"/>

</system.web>

</configuration>

I will read over the documentation, and let you know if this error gets firgured out.

Thank you

.Net 2003 access Crystal Report XI: Error:Failed to open the connection: [Database Vendor Co

I am receving this error: Failed to open the connection: [Database Vendor Code: 17 ], from .Net 2003/C# to Crystal Report XI with SQL Server 2000 has backend database.

Is there a security permission that has been overlooked?

Here is the code I use to access CR XI

ConnectionInfo connectionInfo = new ConnectionInfo();
TableLogOnInfo tableLogOnInfo;
Database DB;
'CrystalDecisions.CrystalReports.Engine.Table' table;
Tables tables;

//Log in
connectionInfo.ServerName = "ServerName";
connectionInfo.DatabaseName = "Database Namet";
connectionInfo.UserID = "userid";
connectionInfo.Password = "password";

//Get Table inf from report
DB = reportDocument..Database;
tables = DB.Tables;

//Looping through all the tables in CR and apply connection info
for(int i = 0; i < tables.Count; i++)
{
table = tables[ i ];
tableLogOnInfo = table.LogOnInfo;
tableLogOnInfo.ConnectionInfo = connectionInfo;
table.ApplyLogOnInfo(tableLogOnInfo);

}

CrystalReportViewer.ReportSource = ReportDocument.ReportSource;
CrystalReportViewer.Visible = true;

Any advise would be helpful.
Thank you

Hi, Koolaidbomber

First, try to find osql.exe on your client box, "c:\program files\microsoft sql server\80\tools\binn", osql.exe /S<servermachine> /E, or "osql.exe /S<servermachine>\<Instancename> /E"( the latter is for named instance). would you also get the same error? For remote connection specific you need following checklist:

A. Enable "File and printer sharing" in Firewall if your server is behind that.

B. Add program "sqlbrowser.exe" or udp port "1434" in your firewall exception list.

C. Add tcp port "1433" of default instance or <port number> of named instance in firewall, check you errorlog to figure out which port server is listening on. If you want your connection over TCP and make sure your server has TCP enabled.

D. Try ping your remote server machine, name and ipaddress.

E. Try to telnet <remoterservername> <portnumber>

F. Try to access a share of your remote server.

If above list all passed, you should be able to connect to linked server unless other problem exists, normally, you could see event log or server log to get clue.

Good Luck!

Ming.

|||Ming,

Thank you for your response. Will try it and let you know.

Thanks again|||Hey Ming,

I gave what you suggested a go, but I still get the error when I access CR XI from .Net 2003. I have connection to the DB server, and I am able to run the CR XI on a stand alone bases, but when it come to accessing from .Net 2003 Web App, I get the error.

Thank again for your idea|||

I recommend reading http://msdn2.microsoft.com/en-us/library/ms227528.aspx regarding how to setup Crystal Reports to connect to SQL Server tutorial.

Since are you able to run the CR XI on standalone, sounds like your Web App config is wrong.
Please post the config setting for your web app.

Jimmy

|||Hi Jimmy -

As requested, here is a copy of my web.config file

<?xml version="1.0" encoding="utf-8"?>
<configuration>

<system.web>

<compilation defaultLanguage="c#" debug="true"><assemblies><add assembly="CrystalDecisions.CrystalReports.Engine, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.ReportSource, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Shared, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Web, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.ReportAppServer.ClientDoc, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Enterprise.Framework, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Enterprise.InfoStore, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/><add assembly="CrystalDecisions.Enterprise.Web, Version=11.0.3300.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/></assemblies></compilation>

<customErrors mode="RemoteOnly"/>

<authentication mode="Windows"/>

<authorization>
<allow users="*"/>

</authorization>

<trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true"/>

<sessionState mode="InProc" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes" cookieless="false" timeout="20"/>

<globalization requestEncoding="utf-8" responseEncoding="utf-8"/>

</system.web>

</configuration>

I will read over the documentation, and let you know if this error gets firgured out.

Thank you

.MDF DataBase

I was using: Microsoft Access Database File as Data Source of my Data Connection, because my Database was created in Microsoft Access (so it has an .mdb extenstion).

But I read that SQL Server has feature-rich than Access Server, as you know SQL Server use .mdf files extension.

So,How can I create a .mdf database ??Can I convert a .mdb database to .mdf ??

Thanks

Hi,

SQL Server is more advanced than Access and you can download SQL Server 2005 Express edition for free. In order to change the exisitng MDB database to SQL Server, you need to import it in SQL Server. Merely renaming file extensions will not work as both data engines are inherently different.

Hope this helps,

Vivek

|||

Hellovivek_iit ,

Thank you for replay .. I was downloading SQL Server Express Edition on my computer ..

Ok , but can you tell me step by step how could I import the .mdb database to SQL Server.

I am waiting for your next replay .

Have a nice day.

|||

Hi,

The process is somewhat reverse in SQL Server Express Edition (as it does not have any import functionality like SQL Server 2005). First create an ODBC connection to your SQL Server express edition database. Next, open you Access database,select your Tables, right click and chooseExportfrom the menu. In the resulting window, select the Sql Server Express Edition database ODBC connection you created.

Hope this helps,

Vivek

|||

You can also use the upsizing wizard in Access... Tools> Database Utilites> Upsizing Wizard. I have used this on occasion at it seems to work ok...

|||

Hello again ,

I did it, Looooolz

Thank youvivek_iit for your help

Thank yousayitfast ,it is very easy step to flow it and fast.

Have a nice day.

.CreateReport using another credentials

I have an asp/asp.net application that uses forms authentication to
autheticate to the report server. My app constructs the url to access the
reports. No one has access to the report manager. Everything works great.
Now I've created a .net module that gets a report from the report server,
modifies it and deployes back to another folder. Its like an ad hoc solution.
For security reasons each role only has "View Report" permission. To do all
the backend stuff described in my "ad hoc" solution i'm forced to grant more
permissions such as create, delete datasources, reports to these roles. Is it
possible to programmically logon as a another user, get the report, modify it
and redeploy(.createReport)? This way my module has permissions to do this
and not the user. I'm using forms authentications. Does this make sense?found a solution:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_3d7q.asp
"Marvin" wrote:
> I have an asp/asp.net application that uses forms authentication to
> autheticate to the report server. My app constructs the url to access the
> reports. No one has access to the report manager. Everything works great.
> Now I've created a .net module that gets a report from the report server,
> modifies it and deployes back to another folder. Its like an ad hoc solution.
> For security reasons each role only has "View Report" permission. To do all
> the backend stuff described in my "ad hoc" solution i'm forced to grant more
> permissions such as create, delete datasources, reports to these roles. Is it
> possible to programmically logon as a another user, get the report, modify it
> and redeploy(.createReport)? This way my module has permissions to do this
> and not the user. I'm using forms authentications. Does this make sense?

.adp, .ade, MSDE and security

Hi all,
I have an Access Project for the front end and a MSDE as back end. The
database is in a Server and only administrators have access to it. I've
installed a copy of the .adp on each user's computer. I plan to convert to
..ade once the project is finalized.
I have a UserName and password set up (and saved) to connect the project
file to the server.
Next, I need to set up user level security for the forms. When the project
opens, there is a login form asking for username and password, if valid a
main form (switchboard) opens. Based on the login, the user will have access
to certain areas - subforms. Administrators can open all the subforms on the
main form. Managers don't have access to the admin area. And so on.
I need a set up for the Owners of the Company to be able to manage the
permissions from the project file itself, not having to access the database
manager on the server (thanks Andrea, you saved my life with this one). Also
I have a cmd button to change the user, therefor change the permissions.
Is there a way to do this, like creating some code behind the forms, or do I
need to do it on the SQL server?
I've created a table for users with ID's, passwords and departments. Only
the Company owners should be able to access the form to update these
records...
This is an small company without any IT person around and the users need a
simple, straighforward project to use.
Any help and/or ideas will be greatly appreciated.
gaba
hi,
gaba wrote:
> Hi all,
> I have an Access Project for the front end and a MSDE as back end. The
> database is in a Server and only administrators have access to it.
> I've installed a copy of the .adp on each user's computer. I plan to
> convert to .ade once the project is finalized.
> I have a UserName and password set up (and saved) to connect the
> project file to the server.
> Next, I need to set up user level security for the forms. When the
> project opens, there is a login form asking for username and
> password, if valid a main form (switchboard) opens. Based on the
> login, the user will have access to certain areas - subforms.
> Administrators can open all the subforms on the main form. Managers
> don't have access to the admin area. And so on.
> I need a set up for the Owners of the Company to be able to manage the
> permissions from the project file itself, not having to access the
> database manager on the server (thanks Andrea, you saved my life with
> this one). Also I have a cmd button to change the user, therefor
> change the permissions.
> Is there a way to do this, like creating some code behind the forms,
> or do I need to do it on the SQL server?
> I've created a table for users with ID's, passwords and departments.
> Only the Company owners should be able to access the form to update
> these records...
> This is an small company without any IT person around and the users
> need a simple, straighforward project to use.
> Any help and/or ideas will be greatly appreciated.
I think to understand your indtroduction as you use some sort of user
defined application role...
you have a kind of hardcoded user+pwd used throughout all your app for all
your users and an internal tables referencing the "real users" to their
authorized actions...
thus you can not rely on standard SQL Server policies to block unauthorized
users to access some portions of your project...
if this is not correct, then "amen"... nice to hear you again :D
but if this is correct, you are stuck to write your own authorization
policies.. you have then to write a dialog where all "tasks" are listed and
you have to store, in your actual tables, what is granted and what is not...
you can perhaps define a relation like
tb_Users tb_Groups tb_Permissions
-- -- --
Id |--1 Id 1--| Id
Name | Name |--8 IdGroup
Pwd | |--8 IdTask
IdGroup 8--| | Enable/Disable
|
| tb_Tasks
| --
|--1 Id
Name
IdMaster
IdRif
Permission
(hope is readeble)
where you map users (8=many) to 1 group...
tb_Tasks enumerates all your "securable" activities ...
tb_Permissions stores the actual permissions referencing tb_Tasks and
tb_Groups ...
depending on your needs you can default [Enable/Disable] as desired (always
false, always true)...
or, if you need a very little and simple management, you can even drop that
column and insert a row only for denied (or granted) tasks...
relations always are 1 to many (8 stand for many :D) so that 1 user can only
be member of 1 group...
you know that you have not a "trusted" security policy as anyone can grab
oSql.exe, log in as "whatever" and perform whatever action on the database
depending on the "whatever" login's permissions... more. the "user defined
application role" must be enabled to perform whatever activitiy on the
database it self, and even probably perform backup/restore... quite large
permissions for a "normal" user..
can you reconsider the security design?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Nice to hear from you again. Thanks for your answer. The price of learning
under pressure...
The users of this project are going to use forms to manipulate the data.
They'll never have direct access to the database itself. The groups are going
to be
Administrator - all rights.
managers - limited rights
technicians - limited to their areas
data entry - limited to their areas
I need to design the project in a way that any of the members of these
groups can access the .ade file at any computer and get the information they
are allow to view/change. Also they need to login as another user without
leaving the database.
I'll study your ideas and how I can apply them. I was thinking on putting
some VBA code behind the login form and depending on the username and
password verify the group the user belongs to. Based on this info, I'll
disable the buttons on the main form therefore the user only can access their
areas. Is this aproach too naiive? These users don't know/care much about
computers and/or databases. The only thing they care is to access the
information quickly.
Have a nice weekend. I'll let you know if I get somewhere.
Thanks a lot,
gaba
"Andrea Montanari" wrote:

> hi,
> gaba wrote:
> I think to understand your indtroduction as you use some sort of user
> defined application role...
> you have a kind of hardcoded user+pwd used throughout all your app for all
> your users and an internal tables referencing the "real users" to their
> authorized actions...
> thus you can not rely on standard SQL Server policies to block unauthorized
> users to access some portions of your project...
> if this is not correct, then "amen"... nice to hear you again :D
> but if this is correct, you are stuck to write your own authorization
> policies.. you have then to write a dialog where all "tasks" are listed and
> you have to store, in your actual tables, what is granted and what is not...
> you can perhaps define a relation like
> tb_Users tb_Groups tb_Permissions
> -- -- --
> Id |--1 Id 1--| Id
> Name | Name |--8 IdGroup
> Pwd | |--8 IdTask
> IdGroup 8--| | Enable/Disable
> |
> | tb_Tasks
> | --
> |--1 Id
> Name
> IdMaster
> IdRif
> Permission
> (hope is readeble)
> where you map users (8=many) to 1 group...
> tb_Tasks enumerates all your "securable" activities ...
> tb_Permissions stores the actual permissions referencing tb_Tasks and
> tb_Groups ...
> depending on your needs you can default [Enable/Disable] as desired (always
> false, always true)...
> or, if you need a very little and simple management, you can even drop that
> column and insert a row only for denied (or granted) tasks...
> relations always are 1 to many (8 stand for many :D) so that 1 user can only
> be member of 1 group...
> you know that you have not a "trusted" security policy as anyone can grab
> oSql.exe, log in as "whatever" and perform whatever action on the database
> depending on the "whatever" login's permissions... more. the "user defined
> application role" must be enabled to perform whatever activitiy on the
> database it self, and even probably perform backup/restore... quite large
> permissions for a "normal" user..
> can you reconsider the security design?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

.....SQL Server does not exist or access denied .. Help !

3 workstations previously installed and able to access server.
4th workstation am able to map to server access files but when
attempting to log in receive the following error.
EXTERNAL DATABASE OPEN FAILURE
Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does not exist.
The workstation that is unable to connect is running XP SP2, MDAC 2.7..
the difference btwn this and the others is that it is running wireless.
Ruled out wirelss as being the problem because hooked LAN cable and
receive the same error.
Checked that TCP/IP is enabled and port is 1433
Went to Adminstrative Tools ODBC to access server and receive the
following error:
Connection failed
SQLState: '01000'
SQL Server Error: 20
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionOpen(PreLoginHandshake()).
Connection failed;
SQLState: '08001'
SQL Server Error:20
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption support
on SQL Server
Tried some other setting changes as well can someone help.. PLEASE !!
*** Sent via Developersdex http://www.codecomments.com ***
What version of SQL is on your server?
From WS4, try the odbcping.exe utility. YOu can find it on your SQL Server
install media. This will determine if you can make a conn over odbc, and will
confirm the SQL instance responds to the ping...
Also - make sure the XP built in firewall (or other fw sw) is not blocking
comm...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Jessica McMillan" wrote:

> 3 workstations previously installed and able to access server.
> 4th workstation am able to map to server access files but when
> attempting to log in receive the following error.
> EXTERNAL DATABASE OPEN FAILURE
> Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
> Server does not exist.
> The workstation that is unable to connect is running XP SP2, MDAC 2.7..
> the difference btwn this and the others is that it is running wireless.
> Ruled out wirelss as being the problem because hooked LAN cable and
> receive the same error.
> Checked that TCP/IP is enabled and port is 1433
> Went to Adminstrative Tools ODBC to access server and receive the
> following error:
> Connection failed
> SQLState: '01000'
> SQL Server Error: 20
> [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]ConnectionOpen(PreLoginHandshake()).
> Connection failed;
> SQLState: '08001'
> SQL Server Error:20
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption support
> on SQL Server
> Tried some other setting changes as well can someone help.. PLEASE !!
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

.....SQL Server does not exist or access denied .. Help !

3 workstations previously installed and able to access server.
4th workstation am able to map to server access files but when
attempting to log in receive the following error.
EXTERNAL DATABASE OPEN FAILURE
Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared Mem
ory]SQL
Server does not exist.
The workstation that is unable to connect is running XP SP2, MDAC 2.7..
the difference btwn this and the others is that it is running wireless.
Ruled out wirelss as being the problem because hooked LAN cable and
receive the same error.
Checked that TCP/IP is enabled and port is 1433
Went to Adminstrative Tools ODBC to access server and receive the
following error:
Connection failed
SQLState: '01000'
SQL Server Error: 20
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionOpen(PreLoginHandshake
()).
Connection failed;
SQLState: '08001'
SQL Server Error:20
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption su
pport
on SQL Server
Tried some other setting changes as well can someone help.. PLEASE !!
*** Sent via Developersdex http://www.codecomments.com ***What version of SQL is on your server?
From WS4, try the odbcping.exe utility. YOu can find it on your SQL Server
install media. This will determine if you can make a conn over odbc, and wil
l
confirm the SQL instance responds to the ping...
Also - make sure the XP built in firewall (or other fw sw) is not blocking
comm...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Jessica McMillan" wrote:

> 3 workstations previously installed and able to access server.
> 4th workstation am able to map to server access files but when
> attempting to log in receive the following error.
> EXTERNAL DATABASE OPEN FAILURE
> Error: -2147467259[Microsoft][ODBC SQL Server Driver][Shared M
emory]SQL
> Server does not exist.
> The workstation that is unable to connect is running XP SP2, MDAC 2.7..
> the difference btwn this and the others is that it is running wireless.
> Ruled out wirelss as being the problem because hooked LAN cable and
> receive the same error.
> Checked that TCP/IP is enabled and port is 1433
> Went to Adminstrative Tools ODBC to access server and receive the
> following error:
> Connection failed
> SQLState: '01000'
> SQL Server Error: 20
> [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]ConnectionOpen(PreLoginHandshake
()).
> Connection failed;
> SQLState: '08001'
> SQL Server Error:20
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption
support
> on SQL Server
> Tried some other setting changes as well can someone help.. PLEASE !!
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

**find connected application**

Hi
I'm working with SQL 2000 and I want to know how can I avoid my users
connecting via other app such as:SQL query analyzer,MS Access,... in order
to modify data, when they are connecting through VB in their interfaces?
in other word, I want to limit the ways of connecting to DBs for a
particular login name (for example: login1)?
Any help would be thankful.With SQL 2000, that is not easy to accomplish. A user can use his/her login
credentials to access SQL Server and whatever objects he/she has permissions
for using any client side tool they choose.
You may wish to investigate using an Application Role, providing access and
permissions to that Application Role only, and not providing access and
permissions to any other login credentials for users. You would want to be
sure to assign explicit permissions, use stored procedures for data access,
and deny direct table access.
Here are some articles to start your research:
Security -Best Practices
http://vyaskn.tripod.com/sql_server...t_practices.htm
Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tjl2pwupn9ig5y@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know how can I avoid my users
> connecting via other app such as:SQL query analyzer,MS Access,... in order
> to modify data, when they are connecting through VB in their interfaces?
> in other word, I want to limit the ways of connecting to DBs for a
> particular login name (for example: login1)?
> Any help would be thankful.|||Hi M,
Please drop me a note if the following description sounds interesting to
you.
Best regards
Adrian
Here we go:
Corrupt users and compromised user-accounts, cf Phishing, account for the
majority of attacks in the commercial world. We present a two-stage
anti-corruption system (ACS) for database servers that, in the first stage,
makes it very hard to gain access to a database with an unauthorised client
application or from an unauthorised client PC and, in the second stage,
provides
early precise hints on users performing suspicious activities. Our first
implementation of the ACS is for the MS SQL Server 2000. It uses only
documented
functions and relies completely on mechanisms and data already provided by
the
database server. Its smooth and efficient operation for several months in a
middle-sized decentralised company proves it a valuable addition to the pool
of
security measures.
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tjl2pwupn9ig5y@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know how can I avoid my users
> connecting via other app such as:SQL query analyzer,MS Access,... in order
> to modify data, when they are connecting through VB in their interfaces?
> in other word, I want to limit the ways of connecting to DBs for a
> particular login name (for example: login1)?
> Any help would be thankful.

**** Need Help in setting OPENQUERY() with linked servers ****

Hello!
I'm a newbie running queries against linked servers. Someone had
already
setup the linked servers in SQL2000 to go access AS400/DB2 and ORACLE
tables.
Let's say there is linked server in SQL2000 called "MyLinked_AS400" and
I have
the following tables in which I need to retrieve data. These are
ficticious data.
MyAS400_DB.Table01, with these columns PRODUCT_ID, PROD_DESC, PROD_QTY
MyAS400_DB.Table02, with these columns PRODUCT_ID, ORG_DESC, ORG_QTY
and I am trying to setup a tsql statement to join these two tables and
pass
it to OPENQUERY() but getting errors. I don't remember the error right
now but it
said something about "The provider did not provide any additional
information"
The error seems to be that of when you misspelled a column name.
I had something like this:
SELECT * OPENQUERY(MyLinked_AS400,
'SELECT
Table01.PROD_DESC,
Table02.ORG_DESC
FROM MyAS400_DB.Table1 INNER JOIN
MyAS400_DB.Table2 ON
Table01.PRODUCT_ID = Table02.PRODUCT_ID
WHERE Table01.PRODUCT_ID = ''123''
FOR FETCH ONLY WITH UR
') AS dt
--
I have been getting all kinds of strange problems today partly because
I am new in
setting up these type of statements. What's wrong with the above code?
Is there
a better way to write this and still using "MyLinked_AS400"?
I had another statement like this:
SELECT * OPENQUERY(MyLinked_AS400,
'SELECT *
Table01.ZIP
FROM MyAS400_DB.Table1
WHERE Table01.ZIP = ''21157''
FOR FETCH ONLY WITH UR
') AS dt
--
It produced one record. But if I had ''19020'', it failed with the
same exact
error produced in the above statement. Does it mean if there is no row
matching
this condition, I need to code it differently?
I need you, Gurus, out there to help me. I'd greatly appreciate if you
can include a copy to my work email in below.
Thank you in advance!
Sydney
sluu@.nfiinteractive.comHi
The most obvious problem is that you are missing FROM in the SELECT * FROM
OPENQUERY(...)
I am not sure of your AS400 queries, have you checked that they work
directly on the AS400?
If you posted the error message and number it may be clearer what the errors
are without having your systems!
John
"sydney.luu@.gmail.com" wrote:

> Hello!
> I'm a newbie running queries against linked servers. Someone had
> already
> setup the linked servers in SQL2000 to go access AS400/DB2 and ORACLE
> tables.
> Let's say there is linked server in SQL2000 called "MyLinked_AS400" and
> I have
> the following tables in which I need to retrieve data. These are
> ficticious data.
> MyAS400_DB.Table01, with these columns PRODUCT_ID, PROD_DESC, PROD_QTY
> MyAS400_DB.Table02, with these columns PRODUCT_ID, ORG_DESC, ORG_QTY
> and I am trying to setup a tsql statement to join these two tables and
> pass
> it to OPENQUERY() but getting errors. I don't remember the error right
> now but it
> said something about "The provider did not provide any additional
> information"
> The error seems to be that of when you misspelled a column name.
> I had something like this:
> --
> SELECT * OPENQUERY(MyLinked_AS400,
> 'SELECT
> Table01.PROD_DESC,
> Table02.ORG_DESC
> FROM MyAS400_DB.Table1 INNER JOIN
> MyAS400_DB.Table2 ON
> Table01.PRODUCT_ID = Table02.PRODUCT_ID
> WHERE Table01.PRODUCT_ID = ''123''
> FOR FETCH ONLY WITH UR
> ') AS dt
> --
> I have been getting all kinds of strange problems today partly because
> I am new in
> setting up these type of statements. What's wrong with the above code?
> Is there
> a better way to write this and still using "MyLinked_AS400"?
> I had another statement like this:
> --
> SELECT * OPENQUERY(MyLinked_AS400,
> 'SELECT *
> Table01.ZIP
> FROM MyAS400_DB.Table1
> WHERE Table01.ZIP = ''21157''
> FOR FETCH ONLY WITH UR
> ') AS dt
> --
> It produced one record. But if I had ''19020'', it failed with the
> same exact
> error produced in the above statement. Does it mean if there is no row
> matching
> this condition, I need to code it differently?
> I need you, Gurus, out there to help me. I'd greatly appreciate if you
> can include a copy to my work email in below.
> Thank you in advance!
> Sydney
> sluu@.nfiinteractive.com
>