Best Practice For Data Dictionary In SQL Server 2005

Sep 1, 2006

Hi ,all here,

Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?

I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.

Will anyone gives me advices for that? Really need help.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

View 11 Replies


ADVERTISEMENT

SQL 2005 Data Dictionary (get Code Of Stored Procedure)

Apr 6, 2006

Hi all

Does somebody know how to get the code of a stored procedure?

I would like to know the name of the view in the data dictionary
which holds the code of the functions or stored procedures ...

Thanks for any help

Best regards
Frank Uray

View 10 Replies View Related

Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries

May 3, 2007

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

View 4 Replies View Related

Data Dictionary

Jun 24, 2000

Hi

Can anyone point me in right direction as to how to prepare a DATA DICTIONARY for a
given databse ??.
And what is the usual format of preparing a data dictionary...Any help in this regard is
greatly appreciated.

Thanks
VENU

View 2 Replies View Related

Data Dictionary

Jun 26, 2000

Hi

can anyone forward me the format to prepare a DATA DICTIONARY For a GIVEN DATABASE. It's Kind off very urgent

Thanks VENU

View 1 Replies View Related

Data Dictionary?

Mar 9, 1999

We have several SQL 6.5 data bases and a few Access Data Bases. We would like to build a data dictionary. Has anyone seen any 3rd party products which might help, or any ideas of how to manipulate the existing objects to do so?

View 1 Replies View Related

Data Dictionary

Feb 16, 2006

Hi

I need to document all the tables & the fields of it & the data type , the PK & FK keys & all that

Can anyone suggest the fastest method to get all the data , or some link on how the format should be

Please help

View 16 Replies View Related

Data Dictionary

Dec 11, 2007

Is there any tool for data dictionary? Basically there are no
descriptions/comments to many of the columns (about 99%) in the database. Datawarehouse team does not know exactly what those columns are, where its used, how its used. No comments have been defined in the system tables. We would like to have a tool where SME's/Bussiness analysts enter the description for the columns in the production database. We have SQL Server databases, many oracle dbs, few sybase databases, ACCESS. Sometimes we might have to search for strings in the descriptions entered.

View 1 Replies View Related

Data Dictionary

Jul 20, 2005

Hello,I am working on a project to reverse engineer requirements for adatabase. The database is sitting on a MS SQL Server. How would Iget a listing of all tables and columns within each table to helpcreate a data dictionary.I have developed a script in Oracle to do this, but I am just notfamiliar with MS SQL Server synthax.Thanks,Hitesh

View 6 Replies View Related

Best Practice? SQL 2000 And 2005 On Same Server

Aug 28, 2006

(I tried to search for answers before posting, but had difficulty)

I have read in Microsoft forums that you "can" install SQL 2005 as an instance on a SQL 2000 server (not clustered.)

My decade+ of experience tells me it would be a bad idea, I'd expect the next service pack to fail or some other un-expected result. This is for a high availability application where the vendor requires SQL2000, and our custom coders want to use some SQL2005 featuers.

Does anyone have experience with two instances of different versions in a high visibility production system?

Does anyone have some points I could use to argue against this other than me sounding paranoid.

Thanks!

View 2 Replies View Related

Data Dictionary For Exisiting DB

Jul 31, 2000

I need to build a data dictionary for a SQL 7 DB that already exists that has over 16000 columns. What I really need is to add a comment to the table and column names.We use ERWin and have looked at the Microsoft Repository
but I would like a quick interface to create the descriptions which neither one of these have. I scripted out all the table and column objects into tables and built a simple Access Form to handle the updates. Now I need to
commite these updates to the Microsoft Repository. Has anyone seen or tried something like this?
THanks

View 1 Replies View Related

Creating A Data Dictionary

Oct 7, 2007

I am adding tables to a DB. I want to put table and column descriptions in as I go.

1. Is there a "Description" property for the table? I see in the column properties list a "description".

2. Once I have my descriptions all in, indexes set etc, is there a system stored procedure that I can run that will generate the data dictionay? (With a table description.)

Thanks

View 14 Replies View Related

Use Data Dictionary To Create Tables

Oct 16, 2007

I have a data dictionay table with these columns













field_name
field_type
field_size
nulls
#1
#2
#3

column1
char
20
y
1
0
1

column2
char
30
y
1
1
1

column3
int
5
n
0
1
1


How to create a table using the content of the dd table above as the structure of my new table?
cb

View 1 Replies View Related

Data Dictionary Info In Extension Properties

May 28, 2008

In SQL 2k5 every db object say table , table fields etc have extension properties . I plan to populate these extension properties with meta data related information like field description , string length , case conversion , field alignment , enum options etc .

I wish to know if anybody has done, anything like this . Can this properties be easily avaialble in .Net development , reporting services , BIDS etc . Can there be significant advantages wrt this .

I would like to get user experiences .

View 1 Replies View Related

Implementing A Simple Data Dictionary Via Extented Properties

Jun 25, 2007

Hi,



I'm thinking of implementing a rudimentary Data Dictionary for a database using extended properties to store table and column descriptions.

Does having this information for every table and column in a database (200 tables; 2000 columns) cause any (negative) impact on database performance? I'm guessing it shouldn't but want to make sure before implementing this in the production environment.



Thanks,

Smitha

View 3 Replies View Related

Best Practice - Data Sources And Data Views Vs OLE DB Source

Feb 26, 2008

Hi, i'm wondering which is the best way to search data in a SQL Server.
I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query.
I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices?
Thanks for your help.

Beli

View 6 Replies View Related

Bad Practice For Data Sources?

Feb 27, 2006

I'm just wondering if it is bad programming practice to have many data sources on one page. I need a "Matrix" type of page that grabs data from many tables and so far I have 5 data sources. Is this common or is there something I should be doing to make it more efficient?

View 1 Replies View Related

Moving Data For Audit - Best Practice

Oct 17, 2005

Here is the situation:Tables:AccountAccountDocumentAccountTestThen we have the following "Activity" table refencing "Account" tableabove:Table: ActivityColumns: ActivityID, AccountNo,...when "Activity" with accountno is created, we'd like to able to takesnapshop or copy all Account related data records from tables: account,accountdocument, accounttestand store it somewhere.This way even if account, accountdocument and accounttest tables changelater, then "Activity" accountno integrity in maintained.Do we need to create or replicate similar tables to store data foraudit?or Which other ways can this be achieved?

View 3 Replies View Related

Best Practice To Copy A Lot Of Data Between Databases

Oct 30, 2007

Hi,

I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables.

Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows:


Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database)
Some people do some checks to see if the new data in the Test-database is correct
If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database.For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up? Is my current way of working good practice?

Thx!

View 1 Replies View Related

Best Practice/recommendation Dev Data Maint Plans

Feb 9, 2007

We are working on converting to SQL 2005 database. During the conversion we are having to rewrite a lot of code and doing a lot of intital testing and development on development data. This is causing our transaction logs to really big. I have created a maint plan that runs nightly that does a back up of database and tran log but throughtout the day the tran logs are getting really big and eating up a ton of disk space. Does anyone have suggestions on what sort of maint plan I can setup to run on my developement data where as at this point I am not concened about being able to roll back the database just keep is small as possible and "healthly"

All ideas are appreciated

Thanks

Chris

View 5 Replies View Related

Service Broker In Data Push Scenario:best Practice

May 7, 2007

Hallo I need some hints because I would like to set up my service broker in the proper way.

First: I'm going to setup service Broker between 2 databases on the same sql server instance.

My Goal: I insert the data on table1 in the DB1.On table1 there is a trigger that begin conversation and send the message to the service on the DB2.
On the receiving queue there is an Activation that take the xml message, shred it and save the content on th table 2 on the DB2.
Actually this SP is my main concern because the shred and insert involves also several checks so it could take "a while".

Volume of data: it seems that there are 100-200 daily insert in the table1 on the DB1 and it is possible that some of them arrives at the "same time".

Very quickly:
On the DB1 the trigger:

Notice that I reuse always the same conversation and the send queue as RETENTION = OFF


begin transaction;

begin

set @dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceiveService');



if @dialog_handle is null

BEGIN

BEGIN DIALOG CONVERSATION @dialog_handle

FROM SERVICE [SendService]

TO SERVICE 'ReceiveService'

ON CONTRACT [MainContract]

WITH ENCRYPTION = OFF;

END

SEND ON CONVERSATION @dialog_handle

MESSAGE TYPE Message ('uyiuy')

commit;

END


On the DB2
ReceiveQueue is defined as:

CREATE QUEUE [dbo].[ReceiveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OnReceivedTrade] , MAX_QUEUE_READERS = 100 , EXECUTE AS N'dbo' ) ON [PRIMARY]



But most important is the Activation's SP and this is the main code:

while (1 = 1)

begin

begin transaction

-- Receive the next available message from the queue

WAITFOR (

RECEIVE top(1)
@message_type=message_type_id,

@message_body=message_body,

@dialog = conversation_handle
FROM ReceivedQueue

)
if (@@ROWCOUNT = 0)

BEGIN

Rollback Transaction

BREAK

END



SET @ErrorSave = @@ERROR ;

IF (@ErrorSave <> 0)

BEGIN

ROLLBACK TRANSACTION ;

SET @ErrorDesc = N'An error has occurred.' ;

END CONVERSATION @dialog

WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;

INSERT INTO [dbo].[tblLog] VALUES(@ErrorDesc,NULL)

END

ELSE



BEGIN

'this is the SP that does most of the work

exec [dbo].[sp_ShredXMLMessageToRelationalData] @message_body

END

commit transaction



I have to say that everything works fine but I don't think that it is completly performant.
I read several blogs/forums and now I'm confused on the best way to implement service broker messaging:
These are my questions:

1) I read that is the DB's are in the same instance It can improve performances the send the message directly on the Queue2.
Can be useful in my case? How to implement it?
2)In my case it is necessarly that I send a message back to the initiator?
3) In my case the conversation never end. I don't think that this is correct but in case of the data push who has to END the conversation? The target? the initiator? never end to avoid overhead (I read that big overhead is caused by the BEGIN and END DIALOG)?

3) where (initiator or Target) and how to handle the service broker error? In my case my applications are SPs and I need to inform the developer or DBA that something went wrong during the processing ( conversation or shredding stored procedure).

4) In my case is should be a good idea to think about how to solve the possibility of the "poison messages"



for sure I will find out other questions...
Any hint, link is appreciated!

Thank you very much!

Marina B.

View 17 Replies View Related

What Is The Best Practice To Change A Column's Data Type In A Very Big Table?

Dec 11, 2007



Hi All,

I am using SQL Server 2000 Enterprise Edition fully patched. Database is in Simple Recovery mode.

I need to change a column's data type from "int(4)" to "smallint(2)". I know for sure that there will be no data (precision) lost, because I know the possible values that this column could have.

My problem is that the table I am dealing with has 600,000,000 records in it. I dropped all indexes before I tried to alter the table. But still it is taking forever and filling up my 280GB disk with transaction log file.

I know that in Oracle, if I want I can turn off logging and do these kind of modifications relatively faster.

I was wondering if there is a way of disabling logging before running this alter command.

What is the best practice to handle a situation of this sort?

I appreciate your help.

Thanks in advance,
Sinan Topuz

View 3 Replies View Related

What Is The Best Practice To Handle Cobol Redeines In Flat File Data.

Mar 1, 2007



Here is my idea but I am looking for the best practice.

Each record can have 3 possibilites.

I would read and write the data 3 times to different tables and add an identity key on all 3 files then I would reassembe the data back together on the identity key and map the data to a fourth and final table.

Any other ideas?



Thanks,

Larry

View 2 Replies View Related

SQL Server Best Practice With IIS

Aug 9, 2006

Hi Everyone

We are building a new site using ASP and SQL as the backend.
Any idea where I can find some information about best practices to coonect the 2.
I was thinking about IIS on a DMZ with port 80 only open and the SQL inside the internal network and open port 1443 between them.

Any ideas will be welcomed.

Thank you

Oren Levy

View 2 Replies View Related

The Given Key Was Not Present In The Dictionary

Feb 4, 2008



I'm developing a report which contains a number of matrixes which are ordered in a hierarchy which can be navigated using a document map. One of the matrixes contains a subreport containing a table which displays a description for the matrix and the corresponding document map label. This is working without problem already in the production environment.

Recently, after updating my machine with numerous updates and patches, this stopped functioning. I now receive the error message, "The given key was not present in the dictionary" when I run this subreport, either as part of the greater report or on its own.

I have seen the posting which recommends deleting the report.rdl.data files. This did not solve the issue.

The data and the dataset are both in sync. When I click the "Print layout" button when previewing the report in Visual Studio .Net 2005, I can view the data. This looks like a bug in the VS.NET 2005 report designer.

I am running SQL Server 2005 SP2.

Any help is appreciated.

Regards,
Bill Mueller

View 3 Replies View Related

Dictionary V/s Hashtable

May 23, 2007

Is dictionary is faster r hashtable.not only which is going to take less memory





With regards

mahender

View 1 Replies View Related

Practice Version Of SQL Server 2K?

Feb 10, 2004

Hi everyone,

Is there a version of MS SQL Server 2000 upon which one can practice writing code? I want to do so on my personal PC at home. Thanks.

ddave

View 6 Replies View Related

Best Practice: How Often To Reboot SQL Server

Feb 15, 2008

An IT dept. I have been consulting with has started to reboot SQL server every night. They are saying that this is the best practices. I would like to know if anyone has any other ideas on this subject. What is the best practices for how often a SQL server should be rebooted, daily, weekly, monthly ... ?

Thanks

View 9 Replies View Related

Sorting Like In Dictionary, Help Plaseeee?

Aug 11, 2000

I need to sort my data more than just order it using Order by clause.
I need the, to appear so that they are sorted like in dictionary's order.
example: if I have 0.1 and 0.2 and 0.11
They need to be ordered into 0.1 then 0.11. then 2.0. etc.

I thought I could use Val() function but it wasn't a successful attempt. Anyone can help me please?

I'm using ColdFusion and this is one of the query :

<cfparam name="sort" default="goal_cd">

<cfquery name="GetTheme" datasource="#application.OUSDSN#">

Select goal.*,theme.*
From goal,Theme
Where goal_theme_id = theme_id
Order by #sort#

<cfif sort is "goal_cd">
Order by Val(#sort#) <--------------------- This doesn't work!!!
<cfelse>
Order by #sort#

</cfif>
</cfquery>

View 1 Replies View Related

SQL Server Capacity Planning Best Practice

Aug 8, 2007

Hello,

I have been working on SQL Server Capacity Planning for a few weeks now and have gathered a lot of materials, but non of thes materials contain recommended best practices on SQL Server capacity planning and also they do not contain operational guidelines.

I would be glad if anyone can recommend a website or book that contain information on SQL Server Capacity Planning Best Practices.

Thanks,
Jide

View 3 Replies View Related

Reporting Services :: The Given Key Was Not Present In The Dictionary

Jul 5, 2006

When I try to preview a SSRS report in VS 2005 I get the following error: "The given key was not present in the dictionary", however when I deploy the report and view it in the Report Manager all is fine. I have not been able to find any useful info in MSDN, what I can try?

View 8 Replies View Related

SQL Server - Best Practice For Local And Remote Development

Aug 13, 2007

What is the usual way to develop ASP.NET websites locally using a database and then migrate everything to the real webserver?
Here's what I'm doing now -- tell me what I should be doing instead (I'm a newbie developer).


I'm using Visual Studio 2005 Standard with a SQL Server 2005 database, to develop a small C# ASP.NET website. I'm the only developer.

View 3 Replies View Related

SQL Server Best Practice Analyzer And T-SQL Coding Standards

Jul 23, 2005

Does anyone know much about this tool? Also, if anyone can point me toa TSQL coding standard, please let me know.-- Dave

View 2 Replies View Related







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