I am trying to fill a table from 2 other tables in MS SQL 2000
the structure ::
Table 1 --> Info
InfoID
Name
Table 2 --> Item
InfoID
Num
Value
TRANSFORM Max(Item.Value) AS MaxValue
SELECT Info.Name
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
WHERE Item.Num In (10,12,15,100)
GROUP BY Info.Name
PIVOT Item.Num
in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
but in MS SQL it doesnt work at all
does someone knows how to translate it for MS SQL (the table structures are exactly the same)?
thank youI have tried
SELECT Info.Name
,(CASE WHEN Item.Num = 10 THEN Item.value END) AS A
,(CASE WHEN Item.Num = 12 THEN Item.value END) AS B
,(CASE WHEN Item.Num = 15 THEN Item.value END) AS C
,(CASE WHEN Item.Num = 100 THEN Item.value END) AS D
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
i must get one line and i get > 1000 lines !
with GROUP BY Info.Name it doesn't work
what is wrong ?
thank you|||Info
InfoID | Name
............
1 | jean
2| Pierre
------------
Item
InfoID | Num | Value
...............
1 | 400 | 1_400
1 | 10 | 1_10
1 | 15 | 1_15
1 | 12 | 1_12
1 | 100 | 1_100
1 | 40 | 1_40
2 | 400 | 2_400
2 | 10 | 2_10
2 | 15 | 2_15
2 | 12 | 2_12
2 | 100 | 2_100
2 | 40 | 2_40
I must get ::
Name | A | B | C | D
................
Jean | 1_10 | 1_12 | 1_15 | 1_100
Pierre | 2_10 | 2_12 | 2_15 | 2_100|||Dunno if you have gotten a response yet. Crosstab is a native Access function that is not available in SQL 2000. You can google ags crosstab or RAC or SQL for a 3rd party add-on to SQL.
Regards,
hmscott
Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts
Friday, February 24, 2012
'Crosspost' Structure a New App (SQLServer C# - Security & Db Connecctions)
Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
--Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.
> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.
> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.
> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.
> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.
> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.
> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?
> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
--|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.
> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
--
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
--Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.
> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.
> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.
> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.
> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.
> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.
> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?
> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
--|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.
> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
--
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
'Crosspost' Structure a New App (SQLServer C# - Security & Db Connecctions)
Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.
> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.
> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.
> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.
> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.
> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.
> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?
> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.
> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.
> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.
> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.
> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.
> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.
> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.
> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?
> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.
> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Sunday, February 19, 2012
cross-database query from ASP.NET
How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?You don't need 2 connections. Are your 2 databases on the same server?
If they are on the same server, the syntax would be like this:
SELECT
D1.column1,
D2.column2
FROM
database1.dbo.Table1 D1
INNER JOIN
database2.dbo.Table2 D2 ON D1.ID = D2.ForeignKey
If they are on different servers, the syntax would be like this:
SELECT
D1.column1,
D2.column2
FROM
Server1.database1.dbo.Table1 D1
INNER JOIN
Server2.database2.dbo.Table2 D2 ON D1.ID = D2.ForeignKey
You might encounter this error:Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. which means that the system stored procedure sp_addlinkedserver would need to be run to allow access to Server2 from Server1. SeeSQL Server Link Server Performance Tips for some information pertaining to linked servers.
Terri
Thursday, February 16, 2012
Cross Join without Table?
I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jbDear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.
Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables. :(
I think this will work...
Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
From (Select 'Employer' as countCat
UNION
Select 'Individual' as countCat
UNION
Select 'Both' as countCat) RegTypes
Left outer join
(Your Query Goes Here) SubQuery
on RegTypes.countCat = RegTypes.countCat|||You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.
It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jbDear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.
Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables. :(
I think this will work...
Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
From (Select 'Employer' as countCat
UNION
Select 'Individual' as countCat
UNION
Select 'Both' as countCat) RegTypes
Left outer join
(Your Query Goes Here) SubQuery
on RegTypes.countCat = RegTypes.countCat|||You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.
It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.
Cross Join Without Table?
I have the following structure with remote select permissions; I cannot
create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a
cross-join, group by in the past - but what do you do if you don't have a
table? For example, the end result when selecting event_pk=(112,113) would
be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jbYou can use a derived table construct with a set of numbers. Your
requirements are not very clear from the narrative, can you post your table
structure & sample data along with expected results? For details refer to:
www.aspfaq.com/5006
Anith
create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a
cross-join, group by in the past - but what do you do if you don't have a
table? For example, the end result when selecting event_pk=(112,113) would
be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jbYou can use a derived table construct with a set of numbers. Your
requirements are not very clear from the narrative, can you post your table
structure & sample data along with expected results? For details refer to:
www.aspfaq.com/5006
Anith
Subscribe to:
Posts (Atom)