Feeds:
Posts
Comments

Archive for the ‘Sql Server’ Category

I recently moved a database to a new Sql Server installation for a pro-bono client.  They have a legacy Microsoft Access application (adp file) which needed to be re-pointed to the new server.   Since they do not use domains, I simply entered the IP address and Sql Server credentials for the new server on the connections page and it worked.

image

Well, it worked for users that had an id on the server and had elevated privileges.  Everyone else was getting the infamous “Sql Server does not exist or access denied message “ message.   There are many things than can cause this message but they did not apply in this case.

How could user authentication be getting in the way of connecting when I am specifying Sql Server credentials and forcing the connection to use tcp/ip instead of named pipes?   

The answer is that this instance of Sql Server is running on port 5000 instead of 1433.   The authenticated users were able to connect because, during the connection attempt, the client software queried the Sql Server Browser service and determine that the correct port number was 5000.  The anonymous clients did not have this advantage and therefore tried to connect on port 1433 and subsequently failed.

The All tab on the Data Link Properties page has a Property were the port number can be specified.  This solved the problem for everyone.

image

Advertisements

Read Full Post »

One really handy feature of Microsoft Sql Server is the ability to link to non-Sql Server databases, such as Oracle.  This allows you to write simple queries in Sql Server against the other database.

For example, to link to Oracle, you would use the following commands:

EXEC sp_addlinkedserver   ‘ServerAlias‘,  ‘Oracle’,  ‘MSDAORA’,  ‘TNS_NAME’
EXEC sp_addlinkedsrvlogin ‘ServerAlias‘, ‘FALSE’, NULL, ‘OracleId, ‘OraclePwd’

Then, in theory, you should be able to write queries like the following in Sql Server Management Studio:

select * from [ServerAlias]..[OracleSchema].[TableName]

When I first tried this, the following error message was given:

Msg 7319, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “XYZ” returned a “NON-CLUSTERED and NOT INTEGRATED” index “TABLE_IDX2” with the incorrect bookmark ordinal 0.

The solution to this problem is to force the Oracle Indexes to be ignored by changing the “Index as access path” option on the MSDAORA provider (not the link).

image

 

The next problem I ran into was:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider “MSDAORA” for linked server “XYZ” supplied inconsistent metadata for a column. The column “NUMBER_FIELD” (compile-time ordinal 39) of object “”SA”.”TABLENAME”” was reported to have a “DBTYPE” of 130 at compile time and 5 at run time.

Microsoft’s proposed solution to this problem requires recreating the tables on oracle to fix the inconsistency.  But what if you only have read only access to the Oracle server?

The workaround is to use the OPENQUERY function to execute the sql in pass through-mode on Oracle.  So, if you rewrite the query as follow, the inconsistency error goes away:

SELECT * FROM OPENQUERY(ServerAlias, ‘SELECT * FROM SA.MYBADTABLE’).

Two other important points:

  • The Oracle table names must be upper ciase.
  • The queries should be relatively simple.  The optimization of these linked queries is relatively limited so keep it simple!

Hope this helps.

Read Full Post »

Unlike stored procedures, one cannot simply omit a parameter (with a default) to a Sql Server 2000/2005 user-defined function.  For example, if you had a function such as:

    create function fn_just_do_it(@a varchar(10), @b varchar(10) = ‘xyzzy’)

You would not call it like this:

    select dbo.fn_just_do_it(‘Hi mom’)

Instead, insert the word DEFAULT as a placeholder:

      select dbo.fn_just_do_it(‘Hi mom’, DEFAULT)

From Sql Help:

When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

Read Full Post »

Rebuilding Constraints

Using Sql Server 2000 Enterprise Manager, it is very easy to generate a sql script to re-generate your indices.  If you created your primary key indices using the primary key constraint syntax, then you have to generate the scripts yourself.

All of the following code is generated from the information in the Information_Schema.Key_Column_Usage view which is found in any database that supports the Sql-92 standard.

select constraint_name, table_name, column_name, ordinal_position, *
from information_schema.Key_Column_Usage
order by table_name, constraint_name, ordinal_position

To generate the sql to remove all of your primary key constraints, execute the following sql:

 -- Generate the drops.
select distinct
 
'alter table ', table_name, ' drop constraint ', constraint_name
from
 information_schema.Key_Column_Usage
where
 constraint_name like 'PK__%__%' and
 table_name 'dtproperties'
order by table_name, constraint_name

Generating the sql for the creation of the primary key constraints is a bit more complicated and involves the use of nested cursors:

set nocount ondeclare @table_name varchar(255)
declare @constraint_name varchar(255)
declare @column_name varchar(255)
declare @ordinal_position int
declare @sql varchar(1024)
declare ConstraintCursor cursor for
 select distinct
  table_name, constraint_name
 from
  information_schema.Key_Column_Usage
 where
  constraint_name like 'PK__%__%' and
  table_name 'dtproperties'
 order by
  table_name, constraint_name

open ConstraintCursor

while 1 = 1
begin
 fetch next from ConstraintCursor
  into @table_name, @constraint_name

 if (@@fetch_status 0)
  break

 set @sql = 'alter table ' + @table_name + ' add primary key ('

 declare ColumnCursor cursor for
  select
   column_name, ordinal_position
  from
   information_schema.Key_Column_Usage
  where
   constraint_name = @constraint_name
  order by
   ordinal_position

 open ColumnCursor

 while 1 = 1
 begin
  fetch next from ColumnCursor
   into @column_name, @ordinal_position

  if (@@fetch_status 0)
   break

  if @ordinal_position > 1
   set @sql = @sql + ', '

  set @sql = @sql + @column_name
 end

 close ColumnCursor
 deallocate ColumnCursor

 set @sql = @sql + ')'

 print @sql
end

close ConstraintCursor
deallocate ConstraintCursor
 

 

 

Read Full Post »