How to execute a store procesure using a link server to oracle

Is it possible to execute a store procedure via a link server to oracle.
I tried these two options but none of them worked.
select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
Deepak VermaDverma,
Can you execute the sp directly as with SQL Server?
EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
i.e.,
EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
HTH
Jerry
"dverma" <dverma@.discussions.microsoft.com> wrote in message
news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
> Is it possible to execute a store procedure via a link server to oracle.
> I tried these two options but none of them worked.
> select * from openquery(oracle_linked_svr,'exec oraschema.ora_proc_name')
> select * from openquery(oracle_linked_svr,'oraschema.ora_proc_name')
>
> --
> Deepak Verma|||If this linked server were a sql server then there is no problem, I would
like to know specific answer against a ORACLE database.
Deepak Verma
"Jerry Spivey" wrote:

> Dverma,
> Can you execute the sp directly as with SQL Server?
> EXEC LINKEDSERVERNAME.CATALOG.SCHEMA.OBJECTNAME
> i.e.,
> EXEC MYSERVER.PUBS.DBO.SP_HELP AUTHORS
> HTH
> Jerry
> "dverma" <dverma@.discussions.microsoft.com> wrote in message
> news:ABF7DBB4-7987-443D-A95A-2AD4811901E1@.microsoft.com...
>
>

how to encypt data before i put in the db

i want to know what function to use to encrypt data before i store it in the database and decrypt the same when i read it.Pls its urgent...can someone help:confused: :confused:i want to know what function to use to encrypt data before i store it in the database and decrypt the same when i read it.Pls its urgent...can someone help:confused: :confused:

What type of data do you want to encrypt?Is it a password field or something else?|||its an integar field which i want to be encrypted in the database so that anyone who views the database is not able to view unless data is retrieven thru the program.|||You need to encrypt it before you insert it.

if you are running on windows, see the methods in System.Security.Cryptography (for managed code) and CryptoAPI for native code.

http://www.codeproject.com/system/icrypto.asp (example of how to use CryptoAPI in native code)

http://msdn2.microsoft.com/en-us/library/93bskf9z.aspx (managed code)
http://msdn2.microsoft.com/en-us/library/system.security.cryptography.aspx|||If you are running SQL 2005 (which you probably ought to consider if this is a new application), there are encrypt/decrypt functions built right into the database. It's another option; but CryptoAPI will work as well.

Regards,

hmscott

How to encrypt store in sql2005?

I have a Store in sql 2005!
I want to encrypt this ? how ?

Thanks !

Hi, a good starting point for learning SQL Server 2005 encryption is located here:

http://blogs.msdn.com/lcris/archive/2005/06/09/427523.aspx

and

http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx

Both Laurentiu and Raul have many good posts regarding SQL Server Security in their blogs which you can reference here:

http://blogs.msdn.com/lcris

http://blogs.msdn.com/raulga

Please let us know if you have any further questions.

Thanks,

Sung

|||Thanks Sung !

I had a Store procedure with Encryption;

Create Produce TestEnCryption With Encryption

AS

Begin

Select * from Customer

End

|||

Ah, I see, sorry for the misunderstanding. I didn't realize you were trying to encrypt a stored procedure.

The encryption we have for stored procedures is weak and is probably better referred to as obfuscation. In general, it's very difficult to keep a stored procedure secret from the user, especially if the user is a sysadmin for the database.

The blogs I outlined above are for data encryption (and other security related features) and these have very strong encryption.

Please let us know if you have more questions.

Thanks,

Sung

|||

As Sung pointed out, the WITH ENCRYPTION option for procedures is an old option that is kept around for background compatibility. It is not a security feature.

Thanks

Laurentiu

How to encrypt store in sql2005?

I have a Store in sql 2005!
I want to encrypt this ? how ?

Thanks !

Hi, a good starting point for learning SQL Server 2005 encryption is located here:

http://blogs.msdn.com/lcris/archive/2005/06/09/427523.aspx

and

http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx

Both Laurentiu and Raul have many good posts regarding SQL Server Security in their blogs which you can reference here:

http://blogs.msdn.com/lcris

http://blogs.msdn.com/raulga

Please let us know if you have any further questions.

Thanks,

Sung

|||Thanks Sung !

I had a Store procedure with Encryption;

Create Produce TestEnCryption With Encryption

AS

Begin

Select * from Customer

End

|||

Ah, I see, sorry for the misunderstanding. I didn't realize you were trying to encrypt a stored procedure.

The encryption we have for stored procedures is weak and is probably better referred to as obfuscation. In general, it's very difficult to keep a stored procedure secret from the user, especially if the user is a sysadmin for the database.

The blogs I outlined above are for data encryption (and other security related features) and these have very strong encryption.

Please let us know if you have more questions.

Thanks,

Sung

|||

As Sung pointed out, the WITH ENCRYPTION option for procedures is an old option that is kept around for background compatibility. It is not a security feature.

Thanks

Laurentiu

How to encrypt a column(field in a table) in MS SQL 2000

Hi,
I want to store user-id and passwords in a table in SQL Server. But as passwords are very secure, I want to encrypt them while storing and may be decrypt them when reqd.
How can I achieve this functionality
Thanks
-Sudhakarpublic key encryption. it is not built into sql2k. google it.|||It is unnecessary to decrypt passwords.
Store the encrypted string in the database. When someone submits a passwords for authentication, encrypt it using the same algorithm and compare the results with what is stored in the database.
This is called one-way encryption, and is both much simpler and much more secure than two-way encryption. I have a one-way encryption algorithm you can use if you want it.sql

How to edit store proc from Manegement Studio

I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !

Thanks

Click the execute button at the top to run the sql.|||Is it possible that when I try this it does excute the store proc but does not save it?|||You RIGHT click on the stored proc from the explorer on the left and click on "Modify". After you make your changes, you hit F5 key.|||

freedom1029 wrote:

I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !

Thanks

SQL Server files and that includes service packs are.sql files but you can always open them with note pad and save as .txt, I usually save a copy as .txt which I can open and adjust as needed. Hope this helps.

|||

Even if I might sound stupid I dont get it :( Or maybe it's me that does not explain my self clearly enoungh. Previously with sql 2000 with Sql Server Entreprise Manager I sed to go in stored procedures, double click on the procedure that I wanted to modify, the the a window open where I was able to modify my procedure and click ok and that was it.

Now in SQL Server Management Studio
Right click and modify ok but F5 execute it and do not save it. I need to modify my procedure permanently.

And File > Save want to save a sql or txt file somewhere on my disc and does not modify my store procedure premennatly either

So what am I missing?

Thanks for your help

|||

I am sorry I was not clear you need to right click on the stored procedure .sql file and you will see open with and choose note pad, then save a copy as .txt and you can modify that copy and save it back as .txt or .sql. It is not automatic but it keeps management studio or query analyzer out of what I put in or take out.

I have used it to open and read SQL Server 2000 service packs and mile long AdventureWorks installation file. I am sorry I was not clear mine uses notepad to open and save the file because notepad can save it as .sql if you need to run it automatically. Hope this helps.

|||

Ok I see where my confusion is coming from

I copied my database from 2000 over to a 2005 Sql Server and all my store proc have 3 lines added at the top of them

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go

Anyway I was just trying to set those to lines of OFF and F5 was not saving my changes

I guess those settings are controled from somewhere else

How to dynamically assign database name in query or store procedure?

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @.MyDatabaseName varchar(30)

set @.MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

declare @.cmd nvarchar (2000)

declare @.MyDatabaseName nvarchar(30)

select @.MyDatabaseName = 'northwind'
select @.cmd = 'SELECT * from '+ @.MyDatabaseName +'.dbo.employees'

exec (@.cmd)

|||

It is much easier and cleaner if you simply deploy your stored procedure in each database. Using dynamic SQL has lot of security implications and you don't really simplify your code.

For a future version of SQL Server, we are looking at features that will enable you to parameterize identifiers without using dynamic SQL. And also the ability to deploy SPs in one module & resolve objects in the execution context database.

|||

Thanks Uma and Joeydj

I can't use dynamic query like Joeydj, simply because my stps are huge; but I am curious about Uma's statement about the security implication in Dynamic query. Could you elaborate a little Uma? thanks

|||

For one, you need to grant more permissions to users on your SPs if you use dynamic SQL. So you increasing the attack surface area of your database by using dynamic SQL. Additionally, if you don't protect against malicious inputs then you are vulnerable to SQL injection attacks which can compromise the database, entire server or network. Lastly, there is also the performance and maintainence aspect of dynamic SQL depending on the usage. So there are many risks in using dynamic SQL. However, for some problems in SQL Server there is no way other than using dynamic SQL (like parameterizing DDL statements, running DDLs against multiple dbs etc). But you can do these carefully by protecting your code against SQL injection attacks, using QUOTENAME for values that can be used as identifiers etc.

Below is a good link on the issues of using dynamic SQL in SQL Server. Also, search the WWW for "SQL Injection" and you will find lot of articles.

http://www.sommarskog.se/dynamic_sql.html

|||

Thank you Uma for your explaination and thanks for the link about dynamic sql. It's a great article!

appreciated.

how to do this with dataset

hi developers

i've a table named users where i store the city id ,

this city id -> citymaster (cityid,stateid)->state master (cityid,stateid) ->country master (countryid,stateid).

now i need to make the query fetches the columns like this

username,cityid,stateid,countryid

how can i do this

Select U.UserName, C.CityID, S.StateID, CT.CountryID
From UserMaster U
Left Outer Join CityMaster C On U.CityID = C.CityID
Left Outer Join StateMaster S On C.StateID = S.StateID
Left Outer Join CountryMaster CT On S.CountryID = CT.CountryID

hope it helps./.

|||

thanks for that but i did like this

select countrymaster.countryname,countrymaster.countryidentity,

statemaster.statename,statemaster.stateidentity,

citymaster.cityname,citymaster.stateidentity,AdminUserMaster.userid,AdminUserMaster.address1,

AdminUserMaster.address2,AdminUserMaster.postcode,AdminUserMaster.email,AdminUserMaster.isactive,

AdminUserMaster.cityidentity,AdminUserMaster.dob,

(AdminUserMaster.firstname+' '+AdminUserMaster.middlename+' '+AdminUserMaster.lastname)asname

from countrymaster,statemaster,citymaster,AdminUserMaster

where countrymaster.countryidentity=statemaster.countryidentity

and citymaster.cityidentity= AdminUserMaster.cityidentity

How to do this in OLAP report?

I am using SQL 2005 Reporting Services. I need to create an OLAP report. The suitation is similiar to a sub-query in Store Procedure. I am not sure how to do this in an OLAP dataset. The OLAP cube has been prepared using SQL 2005 AS. I believe that this report requires two datasets and it is based on the same OLAP cube. The report itself is based on the second dataset. The first dataset require the user to input two parameters. The second dataset uses two of the fields from the first dataset to filter out the second dataset. I can create the two datasets but I dont know how to assign the two parameters on the second dataset which is depends on the result of the first dataset. Any samples or steps or procedures using SQL 2005 RS would be great. Thanks a million.

If I am not mistaken, the first dataset is nothing but a query which contains two fields from the dimension that you will use it as a parameter and the second dataset is a query that should be filtered based on the two parameters.

So, first create the dataset which retrieve the two fields

then, click the layout tab and go to the report parameter dialog box and create the parameters, and make sure that the avialable values comes from the query (to do this, from the Available values column select the "From query" radio button and select the dataset name that you have created earlier.)

Finally, when you are creating the second dataset, filter your query based on those parameters.

I hope this will help.

Sincerely,

--Amde

How to do such a job ?

Hi, everyone,

In my database , i create 2 table to store all the table description and all table fields description.

So I want the user to use these 2 table to define the report .

How to do such a job ?

Thank u .

Jeffers

If there is any relation between the tables, you could either write a Select query or create a view based on a select query which combines the two tables to a dataset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to do Replication in SQL Server 2005

Hi,

I have two servers with sql server 2005, one is in the main office and the other in a store...

I would like that the items table from the main office send new data to the server at store, and the sales data from the store to be send to the main office, can anyone help me to do that?!

Thanks

moved to replication forum

|||

There are three type of replications in SQL Server

(a) Snapshot

(b) Transactional

(b) Merge

What i feel is you can configure Trasactional replication.

There are many documentation available in net. pse have a look on this

http://blog.csdn.net/longrujun/archive/2006/06/09/783357.aspx

Madhu

how to do backup of msde?

hello,
we use Protection Pilot (from McAffee), it installs a MSDE that it uses
to store info about clients.
so, I need to do a backup of the MSDE to send to their tech support
people but their backup utility isn't working.
They've tried for about 2 weeks now and it's not working.
I asked the tech person about doing backup via command line and she
looked into it, but it started asking for password!
Neither of use knew about any MSDE passwords, so that didn't work either.
I figured I'd ask here, what is a quick/easy way to do a backup of a
MSDE via command line (taking into consideration I don't know what
password it's asking for). I guess the sa account can be used, but how
can I reset that account's password so the backups will work?
thanks,
dave
In message <#3qDpRkjFHA.1204@.TK2MSFTNGP12.phx.gbl>, pheonix1t
<nothing@.nothing.gone> writes
>hello,
>we use Protection Pilot (from McAffee), it installs a MSDE that it uses
>to store info about clients.
>so, I need to do a backup of the MSDE to send to their tech support
>people but their backup utility isn't working.
>They've tried for about 2 weeks now and it's not working.
>I asked the tech person about doing backup via command line and she
>looked into it, but it started asking for password!
>Neither of use knew about any MSDE passwords, so that didn't work either.
This sounds a little fishy if you ask me.
The instance of MSDE they installed for use with their software must
have got one or more user accounts assigned to it and these have
probably been assigned passwords. At a very minimum it MUST have an "sa"
user account. If your normal PC's Administrator password does not work
then it would suggest MSDE is running in SQL User Only mode. This then
brings the point that McAffee MUST know the passwords used when
installing and configuring the installation. It is therefore more likely
that they do NOT want to tell YOU (probably for very good reasons,
however, if their own tools ain't working ...).

>I figured I'd ask here, what is a quick/easy way to do a backup of a
>MSDE via command line (taking into consideration I don't know what
>password it's asking for). I guess the sa account can be used, but how
>can I reset that account's password so the backups will work?
>thanks,
>dave
One possible solution would be to use OSQL to detach their database from
the instance of MSDE and then send them the MDF & LDF files (they will
need both). They could then re-attach the database at their end to fix
the problems.
Another, slightly more risky move, would be to stop MSDE from running
(ie: NET STOP [services]) and then copy the MDF and LDF files to them.
Again, they will need both files. Don't forget to restart the MSDE
services after the files have been copied.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Dave,
pheonix1t wrote:
> hello,
> we use Protection Pilot (from McAffee), it installs a MSDE that it
> uses to store info about clients.
> so, I need to do a backup of the MSDE to send to their tech support
> people but their backup utility isn't working.
> They've tried for about 2 weeks now and it's not working.
> I asked the tech person about doing backup via command line and she
> looked into it, but it started asking for password!
> Neither of use knew about any MSDE passwords, so that didn't work
> either.
> I figured I'd ask here, what is a quick/easy way to do a backup of a
> MSDE via command line (taking into consideration I don't know what
> password it's asking for). I guess the sa account can be used, but
> how can I reset that account's password so the backups will work?
if they did not deny access to the MSDE instance to local administrators you
can log in as one of them and connect to the instance via a trusted
connection, not requiring a standard SQL Server login's password...
once connected (for instance via oSql.exe command line utility,
http://msdn.microsoft.com/library/de..._osql_1wxl.asp ,
http://support.microsoft.com/default...;EN-US;q325003)
c:\...\osql.exe -E
you get a command prompt like
1>
where you can execute the backup statement as desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Why is it more risky to copy the files after stopping the server, rather than
after a detach. I am asking because I want to in part use this as a method
for backup and restore.
Periodically, I want to take copies of the mdf and log files for later
attachment under a new database name - but without detaching the existing
database. So since I will stop the server anyway during backup, I thought it
unnecessary to go through an extra step of detach/attach of the original that
still need to run after the backup.
Regards
Bo
"Andrew D. Newbould" wrote:

> In message <#3qDpRkjFHA.1204@.TK2MSFTNGP12.phx.gbl>, pheonix1t
> <nothing@.nothing.gone> writes
> This sounds a little fishy if you ask me.
> The instance of MSDE they installed for use with their software must
> have got one or more user accounts assigned to it and these have
> probably been assigned passwords. At a very minimum it MUST have an "sa"
> user account. If your normal PC's Administrator password does not work
> then it would suggest MSDE is running in SQL User Only mode. This then
> brings the point that McAffee MUST know the passwords used when
> installing and configuring the installation. It is therefore more likely
> that they do NOT want to tell YOU (probably for very good reasons,
> however, if their own tools ain't working ...).
>
> One possible solution would be to use OSQL to detach their database from
> the instance of MSDE and then send them the MDF & LDF files (they will
> need both). They could then re-attach the database at their end to fix
> the problems.
> Another, slightly more risky move, would be to stop MSDE from running
> (ie: NET STOP [services]) and then copy the MDF and LDF files to them.
> Again, they will need both files. Don't forget to restart the MSDE
> services after the files have been copied.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com
>
|||I can't give you a complete answer as there are others better suited to
explain however in general terms when you detach a database the logs
files are flushed, statistics updated and completed transactions
committed to the database. In other words a clean-up process is
performed ready to transport the database.
When you stop the server processes the clean-up routines are not
performed to the extent that a detach does. The idea being the server
can continue where it left off when it comes back on-line.
In my experience most backup regimes involve stopping the server
processes, backing up all MDF & LDF database files required and then
restarting the server processes. This tends to be a faster process than
scheduling backups to tape etc and causes the databases to be off-line
for the shortest periods.
Don't get me wrong, you can do on-line backups as well via Enterprise
Manager but many DBA's I know won't trust the scheduling as it can get
broken. Some backup software like Veritas can perform on-line backups as
well however be careful in your choice as some only backup the DATA and
not the Structure etc so in a failure situation its no good restoring
data when you don't have the correct structure first.
Kind Regards
Andrew D. Newbould
In message <F662A14E-E095-437D-BC05-0958E00BD903@.microsoft.com>, bo
<bo@.discussions.microsoft.com> writes[vbcol=seagreen]
>Why is it more risky to copy the files after stopping the server, rather than
>after a detach. I am asking because I want to in part use this as a method
>for backup and restore.
>Periodically, I want to take copies of the mdf and log files for later
>attachment under a new database name - but without detaching the existing
>database. So since I will stop the server anyway during backup, I thought it
>unnecessary to go through an extra step of detach/attach of the original that
>still need to run after the backup.
>Regards
>Bo
>"Andrew D. Newbould" wrote:
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com