First Time User. How Do I Execute SQL Like @ In Oracle

Mar 26, 2001

In order to execute sql in Oracle, I use @sample.sql.

In SQL server, how can I execute it?

View 3 Replies


ADVERTISEMENT

Reporting Services :: SSRS Loses Ability To Delegate User Credentials To Database Time To Time?

Apr 30, 2015

we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.

View 2 Replies View Related

How To Execute An Oracle Stored Procedure From DTS

Apr 14, 2006

In SQL Server 2000 DTS

How do I call and oracle stored procedure?

I've tried using the Execute SQL task with the

Exec <my procedure name> ;

and it errors.

I've been searching for the answer on how to execute Oracle Stored procedures from DTS without any luck.

The stored procedure creates the table and data that I want to pump into SQL Server.

Any help would be greatly appreciated.



Thanks!

View 10 Replies View Related

Execute Oracle Procedure From SSIS

Sep 18, 2007



is it possible to execute Oracle procedure from within SSIS?
thanks

View 16 Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

Jun 30, 2007

This is driving me nuts..



I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.



I'm struggling with this for a quite a while and I'm not able to get it working.



I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000



the space in MM : SS is intentional here, since without that space it appread as smiley



I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data



I'm struck with error for hours now. Any pointers would be helpful.



Thanks

View 3 Replies View Related

Execute Oracle Stored Procedure From SQLDataSource

Jun 11, 2008

Hi, I'm trying to do a straight forward call to an Oracle Stored Procedure from a GridView Web Control using UpdateQuery.
I created a simple procedure (then tried a package) with no parameters (then with a parameter) and tried to call it from UpdateQuery.
I either get a Internal .Net Framework Data Provider error 30 with a parameter or Encountered the symbol "UARF_FUNCTIONAL_UPDATE" when expecting one of the following: := . ( @ % ; without using a paremeter.
My updatequery on the sqldatasource control is: exec uarf_functional_update;
I'm sure I'm missing something very simple (syntax or something), but I can't find the right direction in my help material. Any assistance would be greatly appreciated.
Thanks,
E.

View 2 Replies View Related

How Do You Execute An Oracle Stored Procedure From A SQL Server DTS Package?

Jul 23, 2005

I've tried several different way to execute a oracle storedprocedure from a DTS package but to no avail.I have a Linked Server setup which does bring back Oracle tables from theserver when I click on the Tables icon.Here's my DTS statement:exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);omsd is the linked serveroms_teco is the owner of the oracle stored procedureSP_Callback_Update_Pkg is the oracle stored procedure(116) is the parameter passed to the oracle stored procedureI put the above exec statement in a DTS Execute SQL Task using a Connectionthat I tried using several OLE and ODBC Data Sources. I can't seem to findthe right combination.Please Help!!!!!!!!--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Problem Executing A Oracle Procedure Using Execute Sql Task

Oct 18, 2006

Hi All,

I am trying to execute a Oracle Procedure through Execute Sql Task but it keeps throwing back an error ORA:00900. Is it possible to execute the procedure from this object? Do I need to use the script task to execute this. If yes then how?

Any help would be appreciated.

Thanks in advance.

Amol





View 2 Replies View Related

Problem While Trying To Execute Oracle Parameterized Stored Procedure

May 31, 2007

Hi,

My name is Iram Levinger.

I'm trying to build ssis package that select rows from SQL Server table and

according to Conditional Split cube results I should execute oracle stored

procedure with in and out parameters.

The in parameters should come from the SQL select and the output parameters

should be inline parameter that I should declare on in the OleDB Command

selecet statement.

Here is the sql statement I wrote in the OleDB Command:

declare @out_return_status as int
declare @out_return_msg as varchar (1000)
begin
exec clickace.cns_clk_sst_iw_pkg.update_instal_warranty_dates ?,?,?,?,?,

@out_return_status,@out_return_msg
end



When I click on the Refresh button in the ssis GUI I get the following error:

An OLE DB error has occured. Error Code : 0x80040E51.

An OLE DB record is available. Source: OraOLEDB Hresult : 0x80040E51.

Description :"provider cannot provide parameter information and

SetParameterInfo has not been called."



Is someone can help with it?

Thanks

View 3 Replies View Related

Reporting Services :: How To Execute Oracle 12c Procedures From SSRS

Oct 8, 2015

I am trying to generate a report using  SSRS ( SQL server 2012 & Visual studio 2013).  when I try to execute stored procedures I am getting below error. Below are the details of enviroment.

1. SQl serverr 2012
2.Oracle 12c client
3.Oracle Server 12c
4.Visual studio 2013

[code]....

View 3 Replies View Related

Calling Oracle Procedure From SSIS 'Execute SQl Task' Is Not Working

Nov 6, 2007

Hi ,


Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine

Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.

Please help me.. thanks in advance

Regards,
GK

View 5 Replies View Related

Time To Execute?

Dec 27, 2006

Is there a built-in method or function that can display how long a report took to execute? (Soup to nuts...issuing of the SQL/MDX statement, through all pages being rendered)



Thanks!



View 1 Replies View Related

Design Time Support For Oracle Db

Dec 22, 2006

Dear all,

i have a question concerning the design time support when accessing different oracle database schemas with SSIS.

I'm using an OLEDB-Connection, and in general everything is working fine. I would like to access two different schemas. One for production and one testing. So i have stored my connection properties in a configuration file, which looks different for prod and test.

I'm not allowed to connect to the oracle db with the user in who's schema the tables are located.
(e.g. schema=prod, my user=readonly)

Normally i must type SQL like: "select * from prod.MyTable" for my production datasource and "select * from test.MyTable" for my test datasource.

In order to clean up my sql i use the oracle "alter session" command. (alter session set current_schema=prod) The command is stored with the connection properties within my configuration file.

All of my packages start with this sql statement for the oracle datasource. (The connection property RetainSameConnection is true and the package property DelayValidation is set.)

So the solution is working fine.

But during the development i have a problem. When crating a datasource i can't retrieve the metadata infromation or build sql with the provided tools, because of "Table of View not found" (ok, the alter session statement is missing)

So does anyone know how to define a alter session for the design time conneciton?
Maybe something with the "Extended Properties" of the connection?

Thanks for your help!
Sven

View 1 Replies View Related

Time To Execute A Query?

Aug 1, 2003

Is there a way in sql server to find out how much time a sql server took to execute a query.

Thanx in advance.

Regards,

Samir

View 2 Replies View Related

Choose Provider (SQL Or Oracle) At Deployment Time

Mar 9, 2007

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?

For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.

When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?



Thanks,

Rick

View 3 Replies View Related

Sql Server Time Format In HH:mm AM, Oracle HH:mm AM Equivalent In SQL Sever

Jul 31, 2006

Hi All,

I need time in the formate HH:mm AM (ex: 06:25 AM, 08:30 PM)

I have tryed with the following query
"SELECT right(CONVERT( varchar, getDate(), 100),7)"
it is giving time like 6:25 AM, But I need in the format 06:25 AM.

I need exaclty equivalent of following oracle query. TO_CHAR(sysdate,'HH:mm AM')

Please reply me.

Thanks & Regards
Dutt

View 4 Replies View Related

Best Way To Retrieve Oracle Data Into SS2000 Near Real-time?

Jul 19, 2007

I have no idea where to post this kind of question, so here it is!



I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )



I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.



Any suggestions?



thanks!

View 1 Replies View Related

Losing Oracle User Name And Password

Mar 17, 2006

I have written a simple SQL Server 2005 package to pull some data from Oracle (using ODBC) and pumping it into SQL Server. When I run it from the server in debug mode in VS it works fine. When I schedule the job it errors out with "ora-01005: null password given; logon denied." The password is there. Has anyone experienced this? Is there a security setting somewhere preventing me from saving passwords? Is there a work around? Thanks.

View 2 Replies View Related

Query Takes Longer To Execute The Second Time

Feb 13, 2001

Has anybody come across situations where queries take longer to execute the second time? The server is a dedicated sql server box with 1gb memory.

Thanks in advance.
Praveena

View 2 Replies View Related

Command Execute Fails The Second Time It Is Invoked

Jan 15, 2008



Hello, the following code works perfectly in SQL Server 2000 and SQL Server 2005 Express over WinXP but when run against an instance of SL Server2005 Express over Win2003Server, the first time Command.Execute is invoked returns no error (even though no action seems to be take by the server), subsequent calls return the error -2147217900 couldn't find prepared instruction with identifer -1 (message may vary, it is a translation from may locale)

Any ideas?
Thanks



Code Block
Public Sub Insert_Alarm(sIP As String, nAlarm As Long)
Static cmdInsert As ADODB.Command
Static Initialized As Boolean
On Error GoTo ErrorHndl
If Not Initialized Then
Set cmdInsert = New ADODB.Command
Set cmdInsert.ActiveConnection = db
cmdInsert.Parameters.Append cmdInsert.CreateParameter("IP", adVarChar, adParamInput, Len(sIP), sIP)
cmdInsert.Parameters.Append cmdInsert.CreateParameter("Alarm", adInteger, adParamInput, , nAlarm)
cmdInsert.CommandText = "insert into ALARMS(date_time,ip,alarm,status) values (getdate(),?,?,1)"
cmdInsert.CommandType = adCmdText
cmdInsert.Prepared = True
Initialized = True
End If
cmdInsert.Parameters(0).value = sIP
cmdInsert.Parameters(1).value = nAlarm
cmdInsert.Execute
Exit Sub
ErrorHndl:
...
End Sub

View 4 Replies View Related

Execute As Login Or User

Feb 1, 2008



Say i create a login login1 and create a user user1 map to the login1
Then i grant the login a server-level permission.

if i want to perform the operation that need the server-level permission,
i should execute as login1.
for example i can change the context by execute as login='login1'

but if i change context by execute as user='user1' , then perform the operation , a permission deny error would occur.
why?

The user1 was mapped to login1, why failed?
what is the permission check mechanicalism of sql server?

any suggestions?

View 4 Replies View Related

Question About EXECUTE AS USER

Jun 4, 2007

Can anyone tell me why I'll receive a error via following steps? Thanks in advance!






1. Create a database €œTESTDB€? and a table €œTable1€?

2. Create a login €œTestLogin€?, which is db_owner roles of both msdb and TESTDB


3. Create a DML trigger for Table1 by following script

CREATE TRIGGER TRG1
ON Table1
FOR INSERT, UPDATE, DELETE
AS
SELECT * FROM msdb..sysjobs
GO


4. Open SSMS, login as TestLogin, execute following statement

SELECT * FROM msdb..sysjobs

It will succeed to select data in msdb..sysjobs

5. Open another SSMS, login as sa, execute following statement

use TEST
execute as user='TestLogin'
select * From msdb..sysjobs

I receive an error about permission to select data from sysjobs, why?

View 3 Replies View Related

Help On Execute Proces Task --posting Secoind Time

Feb 20, 2008



Hi,

I have a batch file which is located in different server. When I try to execute in BIDS it is running fine. But when I execute as SQL agent job it is not executing.

In the execute process task under executable I have supplied the batch file path . For example \112.89.93.9abcsample.bat.

It is executing fine when I execute this in BIDS supplying the above path under executable. But when I run this as SQL agent job it keeps on running and does not end and it is doing anything.

Do I need to mention any cmd.exe file under executable and give the path under arguments tab?

Any help is appreciated.

Thanks




View 10 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Permissions For Non Sa User To Execute Xp_cmdShell

Mar 6, 2008

Hi,    I want to execute BCP in Query Analyser in SQL Server 2005 Express for that i surf on net and find that i should execute BCP under xp_cmdShell, That works good for addministritative account on SQL. But i want the working will be done by a non administrative account or non 'sa' user.How can i assign a non sa User permissions to execute xp_cmdShell? or just tell me any other alternative way to run BCP in Query Analyser or code behined. thanx 

View 1 Replies View Related

Execute As &&<Privileged User&&> Not Working

May 13, 2008

Hi all,
I want some low privileged users to get access to some systems Stored procedure and other resources.What is wrong with the below code ?



--Login as 'domainadministrator', a privileged user




Code Snippet

Create Proc ExecCmd(@cmd nVarchar(4000)) with execute as 'domainadministrator'

AS

Begin

EXECUTE (@cmd)

End
Command(s) completed successfully.

GRANT IMPERSONATE ON USER::[domainadministrator] to appusers
Command(s) completed successfully.

Grant execute on ExecCmd to appusers
Command(s) completed successfully.







But When I login as appusers, and run the procedure I am getting this error




Code Snippet
ExecCmd 'xp_cmdshell ''DIR C'''

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'








Thanks in advance,
Sanoj

View 1 Replies View Related

User Granted Execute On Schema

Aug 8, 2006

I have granted execute on the dbo schema to a sql user so that he can execute available procedures in the dbo schema. However when he executes a procedure ie..

exec dbo.myproc

The following error is returned:

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'MyType', database 'Mine', schema 'dbo'.

MyType is a table

How can I correct this behavior?

I don't want the user to be able to access the tables except via the procedure calls.

View 5 Replies View Related

Execute User Stored Procedure In Master

Nov 26, 2007

I need some clarification regarding the security inside of the master db.

I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.

Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?

Thanks in advance.

View 6 Replies View Related

How To Define A User Variable On Execute Sql Task??

Sep 7, 2006

Hi everyone,

How to define a Input variable in a Execute Sql Task??
I've defined a User::Inicio variable which contains 4 as value.

In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]
or
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]

Anyway, I'm stuck, both did not work
Thanks in advance for your comments

View 7 Replies View Related

Changing User Used To Execute Scheduled Jobs

Dec 19, 2005

Hello. I am using SQL Server Management Studio (SQL 2005) and created a daily backup job. Inside the job, i have an "Operating system" step to copy backuped up files onto another directory. However, the job kept on failing with an error "Executed as user ... Access is denied." With this error, how can i change the user used to execute the job? Thanks.

View 5 Replies View Related

Execute Package Logon Failed For User....

Sep 27, 2006

Fairly new to SSIS.I have created a package in BIDS, and am trying to execute it so i can test/debug the flow and view the data in the data viewers. I continually get a logon failed for user... its the user id i am using to connect to my SourceConnection OLEDB. I have been struggling with this for a while now, and getting frustrated. I tried Windows Authentication also, still i get an error message logon failed for user......

I have tried all of the security settings, from Do not save sensitive to Encrypt all with password, entered the password, and then try to Execute Package from the solution explorer and still get error message. Thanks for your help!

Here is the error message

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.".

View 6 Replies View Related

Run Time Error While Execute Sql Server 2005 Integration Services Package From C#

Jun 6, 2006

Hi

I am trying to Execute an SSIS Package from C# Dot Net.

Below the code i am using.

try
{
Application app = new Application();
Package package = app.LoadPackage("DTSPackage/WinServeMonitoring.dtsx", null);
DTSExecResult result = package.Execute();
label1.Text = "Package Execution " + result.ToString();


}
catch (Exception exp)
{
label1.Text = exp.Message.ToString();
}

I have added reffrence for DTS Run time (using Microsoft.SqlServer.Dts.Runtime;)

But I am getting an Error like this

Error 1 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.SqlServer.Dts.Runtime.Application' C:Documents and Settingsvahqmd10My DocumentsVisual Studio 2005ProjectsWindowsApplication1WindowsApplication1Form1.cs 23 17 WindowsApplication1


Error 2 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.SqlServer.Dts.Runtime.Application' C:Documents and Settingsvahqmd10My DocumentsVisual Studio 2005ProjectsWindowsApplication1WindowsApplication1Form1.cs 23 39 WindowsApplication1


Error 3 The type 'System.Windows.Forms.Application' has no constructors defined C:Documents and Settingsvahqmd10My DocumentsVisual Studio 2005ProjectsWindowsApplication1WindowsApplication1Form1.cs 23 35 WindowsApplication1

Can any one Help me !

Regards

Deepu M.I

View 9 Replies View Related

SQL 2012 :: Login / User Cannot Execute Or See Stored Procedures

May 6, 2014

We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved