If you are a big fan of using virtual machines like I am, then knowing this trick for SQL Server 2005 really saves time by allowing you to easily provision SQL instances from a base server image. Whether you add the following SQL script to your provisioning scripts or do this by hand, the basics for renaming the default SQL instance to the same name as your new server are very simple -- by utilizing sp_dropserver and sp_addserver, you can reconfigure the name of your default instance. Here's how to do it:
- Run: select * from sys.servers. If you are like me, you probably have more than one record return given linked servers, ADSI connections, etc. Server ID 0 is the one we are concerned about. Confirm the name for this server as this is the one we want to match your new server name
- Run: sp_dropserver YourServerNameforIDZero
- Run: sp_addserver YourNewServerName, LOCAL
- Run: select * from sys.servers. Your new server name will be populated properly
Note: It is important to use the LOCAL parameter in step 3 as this tells the command that you are renaming the default instance. It's pretty simple, recommended mostly for test virtual machines as these steps only take care of getting the correct name set for the SQL Server instance and does not cover any AD accounts, domain renaming(if your server image was part of a previous domain and needs to be something different), etc.
Tags: sql server,
virtualization
Categories: SQL Server
I noticed that the current site template for my blog has some quirks if you are using Internet Explorer 6.0 -- mostly related to the column formatting and width settings (posts look a little squeezed). Mozilla 2.0 seems to work just fine, but I'll play with the CSS to see if I can make it a little backwards compatible for those using IE 6.0.
Thanks!
Tags:
Categories: General
I know there are quite a few people out there struggling with just the basics using Oracle in a Microsoft world. There are several ways to configure your environment whether you plan on doing an MS SQL project, .NET, SSIS or any or all of the above, however the basic requirement is having the Oracle service setup and configured on your machine. Below are the steps to configure that service with one of the recent .NET packages posted by Oracle (You'll have to create a login with them to download it -- I don't make the rules); the latter half of this post shows how to utilize the Oracle client service to then create a linked server in SQL 2005 to connect to an Oracle server.
Software Pre-Requisites:Oracle Data Provider for .NET 10.2.0.2.20
After this package is installed, you should getsomething similar added to your menu: Programs> Oracle - ODACHome1 > Configuration and Migration Tools > Net Configuration Assistant
- Start the Net Configuration Assistant
- On the Welcome screen, choose Local Net Service Name configuration
- After clicking next, choose Add on the next screen
- In the Service Name box, type in the name of the service that the Oracle database runs under (usually something like OracleDB.world)
- After clicking next, confirm the protocol you will be using; it is usually TCP if you are unsure
- Enter the name of the server and the port number you will use to connect on the next screen
- Confirm that you will do a test and then change the user name and password to a valid user name and password for your target database
- After testing, give your new Oracle service a name
Part Two: Setting the Properties on the SQL Server
Your first step will be to open Microsoft SQL Server Management Studio and connect to the SQL Database server and instance that you will be creating the Linked Server on.
-
-
Navigate to the Server Objects folder and then to Linked Servers. Right Click on the Linked Servers folder and click “New Linked Server”
-
You will receive this dialogue screen. Note that you can create multiple linked server connections to the same server and database by giving different names in the “Linked Server” box. One reason for doing this would be to utilize the same TNS entry, but use different logins given the schemas/objects those logins will have access to.
-
For Provider, select Microsoft OLE DB Provider for Oracle. Product Name will be Oracle. Your Data Source will be the TNS name you saved from the Section One steps.
-
Next go to the security tab and fill in the login you have been granted to the Oracle server
-
Go to the server options and set the properties to the following values:
- Collation Compatible = True
- Data Access = True
- Rpc = False
- Rpc Out = False
- Use Remote Collation = True
- Collation Name leave blank
- Connection Timeout leave default value
- Query Timeout leave default value
And that’s it! You have now done the basic steps necessary to configure SQL Server 2005 to connect to an Oracle database via a linked server.
Tags: data migration
Categories: SQL Server
I ran across an interesting error in using SSIS to pull data from an Oracle database that I am posting screenshots for both the error and the fix; it's quite simple, but could cause some problems for anyone if they are either not aware nor know how to resolve the warning (it shows as just a warning and will allow you to continue building and using the package, but you will be exposed to issues down the road if not resolved here).
Here is a screenshot of the error, in this example, my datasource for my OLE DB Source in my Data Flow task in SSIS is an Oracle database:
To resolve this issue, go to the properties window for the OLE DB Source data flow object and set "AlwaysUseDefaultCodePage" to True.
Why does this happen? SSIS cannot read the character set used by the Oracle database and thus gives you this warning. In most cases in your U.S. based projects, you will be fine assumming 1252 which is the Western Alphabet character set -- if this is not the case, then you will need to determine what character set the source database is using and be sure to set your values appropriately. Here is an Oracle script you can execute against the database to try and determine this value (not guaranteed to work against all versions of Oracle): select * from sys.props$ where name = 'NLS_CHARACTERSET'
Tags: ssis,
oracle
Categories: SQL Server |
SSIS
Working on a particular MOSS implementation project, we ran across an issue with performance when new sites were provisioned. The act of creating a new site within the site collection literally brought the site performance to its knees and it didn't recover for several minutes. User experience little to no response from sites in the collection.
I am working on a detailed document to show how to create the issue in a test environment, but here is the nitty gritty detail on what happened: After monitoring all the servers, we noticed disk I/O on the SQL server looked high, not abnormal, but high. We ran SQL Profiler during a site creation event and pulled the results into ClearTrace, a free .NET SQL log analyzer.
We immediately noticed that the stored procedure: proc_GetTpWebMetaDataAndListMetaData was called a large number of times during the site creation process (in this case due to the site collection size, it was in the thousands per site created). The average for the number of calls to this procedure held no matter the site template chosen.
After reporting this issue to Microsoft, the workaround was the following: change the setting in the master page for the site collection to MaximumDynamicDisplayLevels="1". This client had the setting on 3. It appears that the dynamic display level of the top menu navigation drove how many lists and web pieces that the site creation event was looking at during it's build process. I do hope that this is an issue that will be fixed in a service release, however no messaging to that effect has been given. For the time being, if you are facing a similar performance issue during site creation within a site collection, this is one area you can look to in trying to find a root cause.
Look for me to post the details on how to recreate this issue. Thanks!
Tags: sharepoint,
performance
Categories: SharePoint
SQL Server has quite a few neat tricks up its sleave when it comes to metadata around the database and objects. The information_schema views are incredibly useful in finding relationships between objects, constraints and determing underlying objects used by views, however one large gap still remains: determining, at least at a table object level, dependencies between stored procedure code and the objects called by that code. I started looking into this while a friend and I were working on an ETL metadata project -- we had a sandbox server used by several team members for rapid report development, and one issue that kept cropping up was object dependency between the stored procedure code and the underlying tables and views used. Granted, this would not have been as big an issue if we all documented as we went, but ....
So here's a simple script that can help with that documentation; it is entirely SQL based, not guaranteed to get you 100%, but it has worked for everything I have run it against thus far. The basic output is a result set that lets you know which table or view object is being called by your stored procedures in your database.
Try and let me know how it works for you:
---------------------------------------------------------------
-- Get all of our table objects
---------------------------------------------------------------
if(object_id('tempdb..#TableObjects') is not null)
begin
drop table #TableObjects
end
create table #TableObjects
(
DbObject nvarchar(500) collate Latin1_General_CI_AS_KS_WS
, ObjectType nvarchar(50) collate Latin1_General_CI_AS_KS_WS
)
insert into #TableObjects
select DbObject = o.name
, ObjectType = case
when o.type = 'U' then 'Table'
when o.type = 'V' then 'View'
when o.type = 'P' then 'Stored Procedure'
else 'N/A' end
from sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
where o.type in ('U','V','P')
---------------------------------------------------------------
-- Get all of our procedure objects
---------------------------------------------------------------
if(object_id('tempdb..#ProcedureObjects') is not null)
begin
drop table #ProcedureObjects
end
create table #ProcedureObjects
(
ProcedureObject nvarchar(500) collate Latin1_General_CI_AS_KS_WS
, textpulled bit
)
insert into #ProcedureObjects
select ProcedureObject = s.name + '.' + o.name, textpulled = 0
from sys.objects o
inner join sys.schemas s
on s.schema_id = o.schema_id
where o.type = 'P'
---------------------------------------------------------------
-- Build out the procedure text
---------------------------------------------------------------
if(object_id('tempdb..#ProcedureObjectsAndText') is not null)
begin
drop table #ProcedureObjectsAndText
end
create table #ProcedureObjectsAndText
(
ProcedureName nvarchar(1000) collate Latin1_General_CI_AS_KS_WS
, ProcText nvarchar(4000) collate Latin1_General_CI_AS_KS_WS
)
declare @ProcedureObject nvarchar(1000)
while exists(select 9 from #ProcedureObjects where textpulled = 0)
begin
select top 1 @ProcedureObject = ProcedureObject
from #ProcedureObjects
where textpulled = 0
if(object_id('tempdb..#tmpT') is not null)
begin
drop table #tmpT
end
create table #tmpT (s nvarchar(4000))
insert into #tmpT
exec sp_helptext @ProcedureObject
insert into #ProcedureObjectsAndText
select ProcedureName = @ProcedureObject, s
from #tmpT
update #ProcedureObjects
set textpulled = 1
where @ProcedureObject = ProcedureObject
end
---------------------------------------------------------------
-- Map table object references to procedures
---------------------------------------------------------------
declare @table nvarchar(500), @procText nvarchar(3000), @index int
if(object_id(N'tempdb..#ProcsToObjects') is not null)
begin
drop table #ProcsToObjects
end
select p.ProcedureName, t.DbObject, t.ObjectType, HasTable = charindex(t.DbObject,p.ProcText)
into #ProcsToObjects
from #ProcedureObjectsAndText p
cross join #TableObjects t
where charindex(t.DbObject,p.ProcText) > 0
select * from #ProcsToObjects
Tags:
Categories: Business Intelligence