Hello,
i need get value of getdate function on local and oon linked server by one select something like this:
select
getdate() 'locladate'
, [SERVER2SQL05_01, 9999].getdate() 'remotedate'
first problem i dont know in which database is getdate stored, second is it possible call function from linked server in select statement?
Thanks Jakub.
Hi,
I am trying to retreive data using selet into query with nolock. Select query selects around 50,000 records from a linked server and inserting into my database. I have getdate() in my select list and it is returning different datetime in a single query which leads to a problem while taking distinct in later stages. Any info on why this nolock returns different datetime in a single getdate() statement will be a gr8 help.
Thanks in advance,
Senthil
I am trying to call a DB2 procedure in SQl server through a linked server object. I use the IBMDADB2 provider for the linked server. on the DB2 I have a simple procedure with 2 in and 1 out paramater. on DB2 it works no problem.but in SQL server I just cannot get it to work.
so my code:
DB2IBM is the name of the linked server.
declare @I_DIS smallint
declare @I_UID char(8)
declare @ID_TICK int
alternative 1:
Exec ('Call btp.GET_TICK(?,?,?)',@I_DIS, @I_UID, @ID_TICK OUTPUT) AT DB2IBM
alternative 2:
EXECUTE DB2IBM..BTP.GET_TICK 2,'19', @ID_TICK
in both cases I got the same error:
OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2IBM" returned message " CLI0100E Wrong number of parameters. SQLSTATE=07001".
so how can I call this procedure?
Sorry if this is very stupid question, but i've spent too long searching for the answer:
I have a linked Oracle server set up for RPC in SQL server 9 db. How do I call it? I've tried this, but gives Unspecified error:
"
OLE DB provider "OraOLEDB.Oracle" for linked server "SANSORA1" returned message "Unspecified error".
"
From this:
declare @UserName char(20) -- Current Username
declare @Password varchar(20) -- Current Password
declare @PasswordNew varchar(20) -- New Password
declare @PasswordCfm varchar(20) -- Confirm New Password
set @UserName = '900878'
set @Password = '900878'
set @PasswordNew = '777'
set @PasswordCfm = '777'
declare @return int
exec SANSORA1..PCG.USR_CHANGEPASSWORD @UserName ,@Password ,@PasswordNew ,@PasswordCfm;
--Exec ( 'SANSORA1..PCG.USR_CHANGEPASSWORD (' + @UserName + ' ,' + @Password + ' ,' + @PasswordNew + ' ,' + @PasswordCfm + ')')
I have a Progress DB set up as a linked server.
To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.
I've tried
SELECT
{fn PRO_ELEMENT(fldarr1,1,1)} as fld1
from ls1..pub.tab
This just returns an unknown function message which I believe is on the SQL Server end of the call.
This statement works fine through Business Objects.
Any help greatfully received.
We are using Microsoft jdbc driver 4.1 connecting to SQL 2012, which has a linked server to another SQL 2012 server.Will linked server calls work with kerberos authentication using Microsoft jdbc driver 4.1? connection string looks like this:
jdbc:sqlserver://SQL01;database= product_db; integrated Security= true;authenticationScheme=JavaKerberos..We have the linked server connection configured to use "Be made using the login's current security context"
Date      8/3/2015 4:19:56 PM
Log      SQL Server (Current - 8/3/2015 3:49:00 PM)
Source      Logon
Message
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.196.21.4]
what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff
I have a sproc that will generate a dynamic call (and this must be done in a stored procedure),... Anyway the dynamic call I am having problems with is that I need to dynamically create a statement to backup a database. It works as long as the database is on the same server / instance as the stored procedure. I want to be able to from server/instance A create a command that does a backup of a database that is on server B. I just can't figure out the syntax for a database backup where the database is on another server.
Trying something like [ServerName].[DatabaseName] does not work...
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13
Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
Please i need an exmple of ur solution, thanks :)
I'm using some files to show certain pages on certain date for an example
File name : aa.doc
start date: 10/02/00
end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page
Here is my stored procedure:
"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "
Thankx a lot
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
   @server = N'Machine123Instance456',
   @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'Â Â
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
alter function sp_insert_newCustomer
(
@CrmDbIDint,
@DealerIDint
)
returns int
--@AddressLastUpdatedatetime
as
begin
declare @date datetime
set @date = getdate()
insert into dbo.CRMDB values(
@DealerID,
@date --@CreatedDate,
)
return @@Identity
end
GO
I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector.
When I use the SQL Mangment Studio Express to execute this query:
SELECT GETDATE()
it returns 2008-04-22 16:25:03.690 which is OK !!
I want to get that same value using a Java program so I do that query but when I display it it shows as:
2008-04-22 and there is no time there !
I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM
So maybe the time isn´t there ?
This is the code i am using:
Statement st = con.createStatement();
String sql = "SELECT GETDATE()";
ResultSet result1 = st.executeQuery(sql);
result1.next();
Date today = result1.getDate(1);
Any help would be appreciated.
My apologies if my question seems rather naive, but I'm looking for some info on how SQL Server (2K) calculates the GETDATE info. Is it from an internal algorithm of some type, or does it simply pull the info from the local server info?
Thanks to all.
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
Hi All,
View 14 Replies View RelatedHi,
View 36 Replies View Relatedif you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Hi,
Does anyone know if/how SQL server can call a function in a C++ library?
Cheers,
Xiaobing
Hi,
I have two SQL SERVER server1 and server2. A server1's stored procedure should call server2's stored procedure. how can i do this.please explain me in detail.i am new to this concept.
VENKAT
Hi,
I have a situation where I need to call a web service from the database (by an insert trigger on a table). Please let me know how I could do this.
Thanks in advance
Raj
Can I call a csharp method of my custom class from Sql-Server Stored Procedure?
If it's possible can anyone provide some sample code or the configuration required?
Thanks
dear friends,
It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...
Thanks
Hello
I'm trying to call a UDF from another server with the following command:
select i from openquery([10.0.10.240], '[survey].[dbo].[ufnGetAxis_Ana] as i')
error:
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.
Can someone give me some advice on how to pass username and password to access an object on another server?
Many thanks!
I would like to call a web service from a sql server 2000 s.proc/sql job, but if there is a better way please let me know. I'm using Captaris Workflow to create and manipulate workflow processes. I have a sql job that will create some pdf's. I want to call the web service that creates a new process in a step from that sql job. I have read about doing this with extended s.procs, but I'm trying to find any possible solutions. Essentially, the web method I want to call will insert several records into sql so I know I can at least do that. I would still like to know if I can make a call to the web service from the sql job. Thanks for any help.
View 1 Replies View RelatedI am trying to call a view that is already in SQL Server 2005 from my ASP.NET 2.0 application. I get the following error message:
The request for procedure 'vwMyView failed because 'vwMyView' is a view object.
I thought I could call a view the same way that I call a stored procedure but I guess I was wrong. How can I fix this?
Here is some sample code too:
Dim Results As DataSetDim Cmd As IDbCommand
Dim Adapter As IDbDataAdapter Results = New DataSet
Cmd = DB.CreateCommandCmd.CommandText = "vwMyView"
Cmd.CommandType = CommandType.StoredProcedure
Adapter = DataSource.CreateDataAdapter
Adapter.SelectCommand = Cmd
DB.Open()
Cmd.Connection = DB
Adapter.Fill(Results)
Cmd.Dispose()
DB.Close()
Return Results
Hi,
I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.
Is there any way to achieve that?
Thanks a lot!
Regards,
Kevin Jin
I have been trying to execute Oracle Stored Procedure From SQL Server using Linked Server without luck. The scenario is explained in detail below ..
Environment :
SQL Server Version : 2000
OS : Win NT 4.0 SP5
Oracle Version : 8.0
OS : Win NT 4.0 SP5
User : Scott
Oracle Procedure
Name : TEST_PROC
Parameters: None
The procedure look something like this ...
Create or Replace PROCEDURE TEST_PROC
AS
BEGIN
Insert into Table1 Values('A');
END;
Scenario One
Executing the stored procedure using Linked Server (JDEV), configured Using OLEDB For Oracle, results in the following error
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'TEST_PROC' on remote server 'JDEV'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Scenario One
Executing the stored procedure using Linked Server (SCOTT), Configured Using OLEDB for ODBC, results in the following error
Could not execute procedure 'TEST_PROC' on remote server 'SCOTT'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Could you please help me out with a workaround. In case SQL-DMO is the only workaround, can you provide some sample programs.
The bigger picture is, replicating Execution of a SQL Server Stored Procedure on Oracle.
I have created a package which has to move huge volume of data in chuncks from source to target with a few sorter transformations and many lookup transformation. Once a chunck is completed the Execute SQL Task in the package updates the etldates into a control table against the chunkid. I want this run in a loop, as on completion of a chunk another should start and follow the same process until there are no more chuncks in the control table.
First i execute the package manually, then i have written a trigger to call the SQL Server Job (etl) on update of the dates for the chunkids in the control table. The job when being called by the on update trigger is throwing up the following error. "The job failed. The Job was invoked by User fwt
obin. The last step to run was step 1 (Package1)".
Am i missing something basic here?
PS: So i tried putting the package into a for each loop container, performance suffers. A chunck which used to take 2 hours is taking more than 12 hours to complete. Then i removed sorter transformation and used db sorting in my source OLE db. Then thought of cacheing the lookups but did not know how and what to give so i gave up. While iam here, DB sorting/SSIS sorting? which is better?
SQL server agent has to be executed form a user other than windows admin. A SSIS where an excel file is loaded into a SQL Server table has to be scheduled by a job.SSIS runs fine alone in the server but fails when called by agent job. http://support.microsoft.com/?kbid=918760#appliesto article has this specific error described but in a 64 bit server this error cannot be rectified. Any ideas?
View 1 Replies View Related