Microsoft SQL Server

164K

Solutions

50K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Hello Experts,
Any suggestions on the SP where the date is getting inserted as 1900-01-01 when it is suppose to be blank. Thanks in advance.

USE [xyz]
GO
10:57:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER PROCEDURE [dbo].[xyz_InsertUpdate_MOT] (
  
  @Registration				NVARCHAR(255)	= NULL
, @RefNo					NVARCHAR(255)	= NULL
, @RefMOT				    NVARCHAR(255)	=NULL
, @ReportedBy			    NVARCHAR(255)   =NULL
, @Garage				    NVARCHAR(255)
, @Notes				    NVARCHAR(max)	= NULL
, @Vehicle				    NVARCHAR(200)	= NULL
, @VehColor				    NVARCHAR(200)	= NULL
, @Make						NVARCHAR(50)	= NULL
, @Model				    NVARCHAR(50)	= NULL
, @IsNotActive 				NVARCHAR(2)		= NULL	
, @IsDeleted                NVARCHAR(2)		= NULL	
, @MOTRef1				    NVARCHAR(255)
, @MOTRef2				    NVARCHAR(255)
, @MOTRef3				    NVARCHAR(255)
, @Milage				    NVARCHAR(255)
, @MilageRef1				NVARCHAR(255)
, @MilageRef2				NVARCHAR(255)
, @MilageRef3				NVARCHAR(255)
, @AccountName				NVARCHAR(200)	= NULL
, @MachineName				NVARCHAR(200)	= NULL
, @NewID					INT						OUTPUT
) 

AS 

BEGIN

CREATE TABLE #action (
		action varchar(10)
		)



-- UserID will be used to look for the user in the XYZ_Users table.
DECLARE @UserID INT
-- Check to see if we have either an AccountName or Machine name and then check the users table !

IF @AccountName IS NOT NULL
BEGIN
	SELECT @UserID = ID
	FROM XYZ_Users
	WHERE AccountName = @AccountName

	IF @UserID IS NULL
	BEGIN
		INSERT INTO XYZ_Users (AccountName) 

Open in new window

0
I have a windows 2016 server. I get "Security Update for SQL Server 2016 Service Pack 2 CU (KB4535706) failure error when I patch the server. The other Windows patches are ok.
Experts out there might have a solution for this. I would appreciate it if you could shed light on this.
0
Hello Experts,

I know this is just simple to experts but I am struggling to figure this out.

I want to get the @EndDate if the report will run any @TodayDate between First Date and Last Date of any quarters and default @EndDate to "Last Date of any quarters."

@BeginDate will default to Beginning of Year.

Example 1:
Today's Date = 02/20/2020
First Day -1st Quarter  = 1/1/2020
Last Day -1st Quarter  = 3/31/2020
@Begin Date = 1/1/2020
@EndDate = 3/31/2020

Example 2:
Today's Date = 04/05/2020
First Day – 2nd Quarter  = 4/1/2020
Last Day - 2nd Quarter  = 6/30/2020
@Begin Date = 1/1/2020
@EndDate = 6/30/2020

Example 3:
Today's Date = 09/25/2020
First Day – 3rd Quarter  = 7/1/2020
Last Day - 3rd Quarter  = 9/30/2020
@Begin Date = 1/1/2020
@EndDate = 9/30/2020

Example 4:
Today's Date = 10/10/2020
First Day - 4th Quarter  = 10/1/2020
Last Day - 4th Quarter  = 12/31/2020
@Begin Date = 1/1/2020
@EndDate = 12/31/2020


Date Variables:
Declare @TodayDate Date
Set @TodayDate = getdate()
Declare @FirstDayOf1stQtrCurrentYr
Set @FirstDayOf1stQtrCurrentYr = DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf2ndQtrCurrentYr
Set @FirstDayOf2ndQtrCurrentYr = DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf3rdQtrCurrentYr
Set @FirstDayOf3rdQtrCurrentYr = DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf4thQtrCurrentYr
Set @FirstDayOf4thQtrCurrentYr = …
0
MSDTC issue, DTCping  showing failures on both source and destination servers:

Cluster Environment detected
MSDTC Virtual Name:CLUSMSDTC, IP:X.X.X.X (Mask:255.255.255.0)
++++++++++++++++++++++++++++++++++++++++++++++
     DTCping 1.9 Report for SQL-XXXXX
++++++++++++++++++++++++++++++++++++++++++++++
Firewall Port Settings:
      Port:5000-5100
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
02-20, 13:19:44.864-->Start DTC connection test
Name Resolution:
      Host1-->X.X.X.X-->Host1
02-20, 13:19:49.540-->Start RPC test (Host1-->Host2)
Problem:fail to invoke remote RPC method
Error(0x6BA) at dtcping.cpp @303
-->RPC pinging exception
-->1722(The RPC server is unavailable.)
RPC test failed
0
We have around 6 billion rows in a single table
We are running some scripts to clear out old data
However the size of the database does not seem to be reducing by much (i know the MDF file will stay same size and i've looked at space available inside this file)
Do we have to somehow defrag the table?
I've looked into index rebuilds are they are highly fragmented, and wondering if this is what we have to do to release the space

Any feedback appreciated
(Sql server 2017 enterprise)
0
In SQL Server, I have a row that has 3 columns.  1. StartDate, 2. EndDate and 3. TotalOwed.  Example: StartDate=Jan 1, 2020, EndDate = Jan 2, 2020, TotalOwed = $24.  

I need a way to break that single row (above) into mutiple rows.  In this example, it would be creating one new row for every hour (between startdate and enddate)

Since the span between startdate and enddate is 24 hours, then you would take $24 and divide by 24 hours, so that would give you $1 per hour.  

The final result would be 24 records, with a startdate and end date in one hour increments.  The result rows would look like this.  

StartDate Jan 1, 2020 12:00 am, EndDate, Jan 1 2020 1:00 am, TotalOwed = $1
StartDate Jan 1, 2020 1:00 am, EndDate, Jan 1 2020 2:00 am, TotalOwed = $1
StartDate Jan 1, 2020 3:00 am, EndDate, Jan 1 2020 3:00 am, TotalOwed = $1
StartDate Jan 1, 2020 4:00 am, EndDate, Jan 1 2020 5:00 am, TotalOwed = $1
... and so on, until I had 24 rows total.  

The start and end dates may be days or half a day.  It could be any number of hours.  

Is there a more optimal way of doing this, rather than creating a cursor that cycles through the data?  

Thanks.
0
Hello, all.

OK, working with SQL Server 2019, and it works wonderfully with accented characters.
Dvořák Hall
It displays to the page just fine.
However, when I try to read from a URL QueryString, it's not working.
I get
Dvo
Instead of the full name.

I have at the top of my main page.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Response.ContentType = "text/html"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
%>
 

Open in new window


But it still will not get the URL: /Page/Dvořák_Hall
And display it correctly to the page, to use against the database query.

Any suggestions?
Wayne
0
Hi,

In ssrs I get diff from two dates.
How do I get also hours and minutes (now some results show 0 since diff is hours minutes only.

Thanks!
0
Hi,

I'd need a sql query to color code a date:
Items = status NOT equal to 'Status01', 'Status02'
For all items which have date less then a week => green
older then a week => orange
older then 2 weeks => red
Items which have Status Status03
and have date
older then 2 weeks orange
older then a month red

Thanks for helping with the approach!

J
0
Hello all;

The query I am working with, I need a subquery to send back multiple records.
I know I have done this before in the past, but cannot find the code or remember what was done.

This is my query. (Of course, it gives this error: Subquery returned more than 1 value.)

SELECT   

(SELECT     distinct(Artists.ArtistName)
FROM            Albums INNER JOIN
                         Artists ON Albums.ArtistID = Artists.ArtistID INNER JOIN
                         Tracks ON Albums.AlbumID = Tracks.AlbumID INNER JOIN
                         Studios ON Tracks.TrackID = Studios.TrackID
WHERE        (Studios.StudioID = Studio.StudioID)) as Artists,


Studio.StName, ArtistPics.PicsPath
FROM            ArtistPics RIGHT OUTER JOIN
                         Studio ON ArtistPics.StudioID = Studio.StudioID
WHERE        (Studio.StName = 'Studio_308')

Open in new window


I need to return all the ArtistName's with a comma between them.

Thanks for any assistance on this one.
Wayne
0
i'm using sql server 2019.

I have a table that looks like this:

Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesReport](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] ON 
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (1, N'Davolio', N'Nancy')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (2, N'Fuller', N'Andrew')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (3, N'Leverling', N'Janet')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (4, N'Peacock', N'Margaret')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (5, N'Buchanan', N'Steven')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (6, N'Suyama', N'Michael')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (7, N'King', N'Robert')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (8, N'Callahan', N'Laura')
GO
INSERT [dbo].[EmployeesReport] ([EmployeeID], [LastName], [FirstName]) VALUES (9, N'Dodsworth', N'Anne')
GO
SET IDENTITY_INSERT [dbo].[EmployeesReport] OFF
GO

Open in new window



So far i have this query:

DECLARE @IsCurrentMonth VARCHAR(100), @LastName VARCHAR(100), @FirstName VARCHAR(100)
SET @LastName = 'Fuller'
SET @FirstName = 'Laura'
SET @IsCurrentMonth = 'Yes'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[EmployeesReport]

Open in new window



for the WHERE clause I want to use an CASE statement or if else statement.


What i need is the following,

if the parameter called @IsCurrentMonth equals 'Yes' i want the where clause to look like this. Filter by LastName.

-- if @IsCurrentMonth equals Yes use this where clause
WHERE [LastName] = @LastName



if the parameter called @IsCurrentMonth equals 'No' i want to the where clause to look like this. Filter by FirstName.

-- if @IsCurrentMonth equals No use this where clause
WHERE [FirstName] = @FirstName


I know I can just put the whole query inside an if statement, but I know what to apply the CASE statement to the WHERE clause.

What's the best syntax for this?
0
hi,

any offical, practical guide to migrate Oracle and MS SQL to Azure MS SQL ?

Azure do not support Oracle DB yet, right?
0
Hi Experts,

We have a report based on Nurses table.

Then we have a table named Skilled_nursing_Visit_Notes (Nurse_Table.Nurse_UserName = Skilled_Nursing_Visit_note.Nurse_User_ID_num_SNV) where nurses enter visits and notes for patients.

Under each visit note there is a column named Browser which auto saves the browser version used for that entry.
We also have a calculated field (formula) in the Skilled_nursing_Visit_Notes  table named OutdatedBrowser set as follows.
case when ([@field:Browser] like 'Chrome%' and [@field:Browser]>='Chrome 78.0') or ([@field:Browser] like 'Safari%' and CAST((Right([@field:Browser],(Len([@field:Browser])-Charindex(' ', [@field:Browser], 0)))) as FLOAT) >= 13) or ([@field:Browser] like 'Firefox%' and [@field:Browser]>='Firefox 71.0') or ([@field:Browser] like 'InternetExplorer%' and [@field:Browser]>='InternetExplorer 11.0') then 'No' Else 'Yes' end

Open in new window


Now we would like to add to the nurses report the following.

1- Ability to filter for nurses with Outdated browser (According to latest entry in Skilled_Nursing_Visit_Notes table determined by Date_Signed field which is a timestamp field).
2- View on the report the browser field.

Thanks
0
Hello,

I am upgrading my vcenter from 6 to 6.5.  The database is on an external SQL server.  

I am getting the attached error.  ODBC test between the vcenter server and database server is fine.  

I am check out this link http://iamoracleadmin.blogspot.com/2017/03/vc65-db-lock-has-been-detected-on.html but I am not sure now to identify the opened session and kill it.  

I am not sure if I should post this to the vmware team or SQL team so I am posting it on both.

Anyone who knows how to fix it please advise.  

Many thanks.
VC65-DB-Lock.png
0
Hi,

I mail my ssrs report on a daily basis. Opening it in mail (mobile device) takes 30 seconds, so people think the mail is not working and delete it.
Also loading it on Windows, it still takes like 3 seconds to load.
How can I make my report more leightweight?
Suggestions: show less items on a page (there are a few 100 rows now, howto?), leighter formatting (like I have gridelines for better visibility ... howto?) ...?

J
0
Hi,

I have an SSRS KPI in which I have totals like the following expression:

=Sum(IIf(Fields!StatusName.Value = "Status01", 1, 0))

Now, how do I make more difficult queries: like
sum of all items where statusname.value = status02 and status03 but not empty status but also status06,status07

J.
0
Hello, all.

I have these two commands

Example insert

{Something here} {{Something else here}}

Open in new window


Dim Regex2 As Regex = New Regex("\{.*?\}")
Dim Regex4 As Regex = New Regex("\{{.*?\}}")

Open in new window


They do not produce any errors, however.
The single { }
Will insert into the database for the {{ }}
In the database, you will see
{Something here}

Open in new window

With the curly brackets around it.

How can I distinguish between the two commands, so that only the double curly's will insert into its column, and the single curly will only insert into its column?

Thanks
Wayne
0
A client has requested us to develop a custom search page that be able to query Sharepoint Library (via Sharepoint API) and also to query an in-house SQL Server database/table.
We have already developed the part that queries the Sharepoint site but for the SQL Server part we were recommended not to connect the search page using Javascript. Others, have recommended to use PowerApp, WebApp, etc. also someone suggested to use a Middleware, and finally someone recommended to use BCS ("Business Connectivity Services").

So my question is, what would you recommend to achieve this challenge?
0
Hi,

I have an ssrs report for a ticketing system which has names, ticket number etc.

I d like to add several filters:
-show my tickets = filter on username, all tickets
-environment: UAT/PRD
-statuses: open/not resolved, closed

This way a user of the form can see/filter on only specific data like.his requests which are open in prd

Please advise.
J
0
I have an Sql Server 2016 Database with tables TB_Cable,   TB_Item, TB_Connection

Samples data below  ( There are more fileds on each table)
TB_Cable
Cable_ID     Cable_Num    CableLoc
  C1                Cable1             123,St
  C2                 Cable2             345,St
  C3                 Cable3             456,St


TB_Items
 Item_ID            ItemName
    I1                        Item1
    I2                        Item2
    I3                        Item3

TB_Connection
  Cable_ID    Item_ID
    C1                  I1
    C2                  I3

I am looking for the query to give result as follows
Result of query   ( Has all cable . Lists Items when Cable is connected.  For other cables just "Vacant" is displayed for Item
Cable&Connection
-----------------------
   CableNum    CableLoc   ItemName
      Cable1          123,St       Item1
      Cable2          345,st        Item2
      Cable3           456,St       Vacant
0
I am using this data set query that is generating error , The dated diff function requires 3 arguments(s). Incorrect syntax near Label 1. Incorrect syntax near 'Group Order'. The Query generates data for a DemographicsPar2(parameter. I am building a ssrs has board that uses this parameter.

SELECT DISTINCT 
                  'Age' DemographicGroup, 
CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN '<=25'

 WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN '26-35' 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND   45 THEN '36-45' 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN '46+' END Label, CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN 1 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN 2 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND 45 THEN 3 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN 4
END GroupOrder

INTO        #Demo2
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Marital Status', MaritalStatus, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Race', Race, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Sex', Sex, 1
FROM     [dbo].[Dim_Borrower]
                      SELECT *, DemographicGroup + ' - ' + Label RealLabel
                      FROM     #Demo2
                      WHERE  DemographicGroup IN (@Demographics1)
                      ORDER BY DemographicGroup

Open in new window



When I run this query on the SSRS query Designer, the output shows Three columns. DemographicGroup / Label / GroupOrder/ Real Label ..
0
I'm using SQL Server 2019.

I have a table that looks like this:

1_TestTable3.PNG
Here is the script to create this table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable3](
	[RecordID] [int] NULL,
	[FromDate] [smalldatetime] NULL,
	[ToDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (2, CAST(N'2020-01-26T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (3, CAST(N'2019-11-14T00:00:00' AS SmallDateTime), CAST(N'2020-01-19T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (4, CAST(N'2019-08-19T00:00:00' AS SmallDateTime), CAST(N'2019-11-12T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (5, CAST(N'2019-04-12T00:00:00' AS SmallDateTime), CAST(N'2019-08-18T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable3] ([RecordID], [FromDate], [ToDate]) VALUES (6, CAST(N'2019-02-05T00:00:00' AS SmallDateTime), CAST(N'2019-04-11T00:00:00' AS SmallDateTime))
GO

Open in new window




My existing query looks like this.

----- input parameter -----
DECLARE @HireDate SMALLDATETIME
SET @HireDate = '01/29/2020'
----- input parameter -----
----- query -----
SELECT [FromDate]
FROM [TestDatabase].[dbo].[TestTable3]
WHERE [FromDate] < @HireDate
----- query -----

Open in new window


The result of this query is this:

2_ExistingQueryResult.PNG

That's not my desired result. Depending on the input date is should only return 1 FromDate.

Here is the criteria explanation:

3_QueryCriteriaDescription.PNG


My Desired results should be this:


4_DesiredResults.PNG


How do i revise my existing query to get the desired results?
3_QueryCriteriaDescription.PNG
4_DesiredResults.PNG
0
I'm using Sql Server 2019.

I have a table that looks like this.

Question1Table.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable2](
	[RecordID] [int] NOT NULL,
	[FromDate] [smalldatetime] NULL,
	[ToDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (1, CAST(N'2020-08-05T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (2, CAST(N'2020-01-26T00:00:00' AS SmallDateTime), NULL)
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (3, CAST(N'2019-11-14T00:00:00' AS SmallDateTime), CAST(N'2020-01-19T00:00:00' AS SmallDateTime))
GO
INSERT [dbo].[TestTable2] ([RecordID], [FromDate], [ToDate]) VALUES (4, CAST(N'2019-08-19T00:00:00' AS SmallDateTime), CAST(N'2019-11-12T00:00:00' AS SmallDateTime))
GO

Open in new window



I only want the record with the earliest FromDate whose ToDate is NULL

So my desired result is this.

Question1DesiredResult.PNG

How do i revise this query to get that desired result? I was thinking of using MIN but not sure.

SELECT [RecordID]
      ,[FromDate]
      ,[ToDate]
FROM [TestDatabase].[dbo].[TestTable2]
0
I have an application in VB.Net using a connection via ADODB.connection.  We are in the process of moving our "stuff to the cloud".  The new server is Server 2016 and the database is SQL Server 2008 Express.  I have enabled the protocols in SQL Server for Named Pipes, TCPIP.  It appears that I have the Server/Database/User/Pwd info correct, but the connection object is not supported?

The connection string is similar to this:
"Driver={SQL Server};Server=SQL1\SQLEXPRESS;Database=Orders;UID=sa;pwd=Adg991!; providerName=System.Data.SqlClient"

Thanks for you help!
0
I have been following several sources to set up an Oracle DBlink to select from a SQL Server database.

Oracle Version is 12.1.0.2.0 Standard Edition in Windows 2012 Datacenter R2 64-bit

SQL Server version is:  SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64>

The ODBC set up Tests successfully.  I've named it "mysqlServer"

My Oracle Server's name is:  MyOraServer

Here's what I've done:

Step 1:  Added the following entries in the %Grid_Home%\network\admin\listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=mySqlServer)  <-- the name of the ODBC connection
         (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
         (PROGRAM=hsodbc)
       )
      )


Step 2:  Created an init file in the %ORACLE_DBHOME%\hs\admin folder called "inithsMySqlServer.ora" with the following content:

HS_FDS_CONNECT_INFO = mySqlServer    <-- the name of the ODBC connection
HS_FDS_TRACE_LEVEL = off



Step 3:   Made the following entry in the %ORACLE_DBHOME%\network\admin\tnsnames.ora file:

SQLServerDB  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=MyOraServer)(PORT=1521))
    (CONNECT_DATA=(SID=mySqlServer))  <-- the name of the ODBC connection
    (HS=OK)
  )


Step 4:  Created a DBLink in Oracle:

CREATE PUBLIC DATABASE LINK SQLServerDB CONNECT TO "myusername" …
0

Microsoft SQL Server

164K

Solutions

50K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.