Regarding History tables in Replication...

Hi,

How frequently will the history tables get deleted in Merge Replication and Transactional Replication with updatable subscriptions?

When transactional replication with updatable subscriptions is running in the continuous mode, will the history tables get deleted frequently?

Parameters :

Version : SQL Server 2005 SP1

Mode : Continuous running mode

Subscription type : Pull (for transactional)

Regards,

Swapna.B.

There's a history cleanup job that runs and will clean up rows based on your history retention.

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Reg:Tables data changes auditing

Hi,

I have more than 1100 tables in my databse. From existing application data will insert into Database tables. I need to track the tables module wise when data inserting/Updating into these tables.

One way is, I have to write the triggers for each table[1100 tables with auditing]. In my case this is not possible to write like that.

Is thesre any other solution to find the updated,inserted tables when data is changing from application without doing bulk changes.

Regards

Hanu

unfortunately SQL Server can not do this until unless we have our own mechanism.

Madhu

Reg: Table data changes auditing

Hi,

I have more than 1100 tables in my databse. From existing application data will insert into Database tables. I need to track the tables module wise when data inserting/Updating into these tables.

One way is i have to write the triggers for each table[1100 tables with auditing]. In my case this is not possible to write.

Is thesre any other way to find the updated,inserted tables when data is changing from application.

Regards

Hanu

hi,

You may get more help posting it in

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

SQL Server Database Engine forum

Although i maybe wrong

Good luck

Matt

REG : SQL JOBS

Hi,
Is there anyway that we would be able to get the start time of the job
when it was started manually..I already looked into all the tables related
to jobs and everything gets updated only after the completion of the job.But
i would like to know the start time as soon as the job is started..Not aware of any. A work around:
add a step at the very beginning of the job, put something like "select 1"
in it. Then check job history after starting the job.
HTH
Quentin
"Karthik" <nkvatsan@.hotmail.com> wrote in message
news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there anyway that we would be able to get the start time of the job
> when it was started manually..I already looked into all the tables related
> to jobs and everything gets updated only after the completion of the
job.But
> i would like to know the start time as soon as the job is started..
>|||Click on the job in Enterprise manager and view job history
It gets the time from sysjobhistory in the msdb database.
Regards
John|||sysjobhistory is not updated after the completion of the job. It gets an
update (or insert) after the completion of every step of every job.
"Karthik" <nkvatsan@.hotmail.com> wrote in message
news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there anyway that we would be able to get the start time of the job
> when it was started manually..I already looked into all the tables related
> to jobs and everything gets updated only after the completion of the
job.But
> i would like to know the start time as soon as the job is started..
>|||Thanks for the reply...Well thats what i meant.I dont want to add a step at
the beginning of every job because we have 300 jobs...But i was thinking of
modifying the sp_start_job proc..But was just wondering is there any
undocumented sp that will give the start time of the job as soon as it is
started..
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:OJ6TXgHTDHA.2260@.TK2MSFTNGP12.phx.gbl...
> sysjobhistory is not updated after the completion of the job. It gets an
> update (or insert) after the completion of every step of every job.
> "Karthik" <nkvatsan@.hotmail.com> wrote in message
> news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > Is there anyway that we would be able to get the start time of the job
> > when it was started manually..I already looked into all the tables
related
> > to jobs and everything gets updated only after the completion of the
> job.But
> > i would like to know the start time as soon as the job is started..
> >
> >
>|||The reason i wanted to know the start time is that i want to send
notification to the right group about the jobs that are taking more time
than the average run duration.I did already write scripts for that. But it
works only if the jobs are on their normal schedule because i can get the
next_run_time from sysjobschedule table. But if somebody starts the job
manually, i would like to know the start time for that so that i would be
able to calculate the rest of the information based on the start time of the
job..I hope i am clear on what i said...
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:u2wkO$HTDHA.2148@.TK2MSFTNGP11.phx.gbl...
> Don't see why you need to do such check for so many jobs. A couple or
many,
> you may look at sp_add_jobstep to add the dummy step if so desire.
> I believe you are aware of the fact that if you need to start a job after
> another one, you can do it in a separate job to launch them one after the
> other.
> Quentin
>
> "Karthik" <nkvatsan@.hotmail.com> wrote in message
> news:e26Rx4HTDHA.1576@.TK2MSFTNGP12.phx.gbl...
> > Thanks for the reply...Well thats what i meant.I dont want to add a step
> at
> > the beginning of every job because we have 300 jobs...But i was thinking
> of
> > modifying the sp_start_job proc..But was just wondering is there any
> > undocumented sp that will give the start time of the job as soon as it
is
> > started..
> > "Quentin Ran" <quentinran@.yahoo.com> wrote in message
> > news:OJ6TXgHTDHA.2260@.TK2MSFTNGP12.phx.gbl...
> > > sysjobhistory is not updated after the completion of the job. It gets
> an
> > > update (or insert) after the completion of every step of every job.
> > >
> > > "Karthik" <nkvatsan@.hotmail.com> wrote in message
> > > news:eoOSuHHTDHA.2120@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > Is there anyway that we would be able to get the start time of the
> job
> > > > when it was started manually..I already looked into all the tables
> > related
> > > > to jobs and everything gets updated only after the completion of the
> > > job.But
> > > > i would like to know the start time as soon as the job is started..
> > > >
> > > >
> > >
> > >
> >
> >
>

Refreshing Tables In Database

I have a stored procedure that creates a table. It works fine except until
I
refresh the database, the table doesn't appear ... and views and reports
can't find it.
Does anyone know how I can do this automatically with SQL or VBA (I'm using
an Access project as my front end)?
Thanks!
HIf I had to guess, I would say that you create the table as one user and the
n
try to access it as another - just a guess
"Howard Brody" wrote:

> I have a stored procedure that creates a table. It works fine except unti
l I
> refresh the database, the table doesn't appear ... and views and reports
> can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm usin
g
> an Access project as my front end)?
> Thanks!
> H|||Howard Brody wrote:
> I have a stored procedure that creates a table. It works fine except
> until I refresh the database, the table doesn't appear ... and views
> and reports can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm
> using an Access project as my front end)?
> Thanks!
> H
This seems to be an Access related issue. I would post the question to
an Access group and see what they say.
David Gugick
Imceda Software
www.imceda.com|||I dunno ... the table doesn't appear until the database is refreshed whether
I run the code from a stored procedure or the query analyzer - which has
nothing to do the Access front end.
I would think that SQL would have a command or function for refreshing your
database. I just haven't found it yet.
H
"David Gugick" wrote:

> This seems to be an Access related issue. I would post the question to
> an Access group and see what they say.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Howard Brody wrote:
> I dunno ... the table doesn't appear until the database is refreshed
> whether I run the code from a stored procedure or the query analyzer
> - which has nothing to do the Access front end.
> I would think that SQL would have a command or function for
> refreshing your database. I just haven't found it yet.
> H
>
There's really no such thing as refreshing a database. Seeing the
objects in a list, for example, is a client issue (Access in this case
or could just as well be Query Analyzer). Once you create an object in
SQL Server, it's there, whether you see it in the user-interface of an
application or not. There's no real live-feed of database objects like
you have when viewing file, for instance, in Explorer - which keeps an
eye on folders for changes - most times. Even in QA, you don't need to
see the object in the Object Browser to run a query against it. Whereas,
I suspect, you need to see the object in Access to create a query or
open up the table in the Access UI. That was my reason for suggesting
you post to the Access group because possibly there is a feature of
Access that can mitigate this problem somewhat.
David Gugick
Imceda Software
www.imceda.com

Refreshing Links in Access doesn't allow me to Add Records!

Hi
I have an application in Access2003 with linked tables. When I create the
links manually (picking DNS etc) I have no problems. When I then try to
recreate the links programatically (different users) I am not able to add an
y
records to any of the tables! If I erase all the linked tables and reconnect
manually with the other user I have no problems!
I am using the relink code from
http://support.microsoft.com/defaul...kb;en-us;159691
Any and all suggestions Welcome!!!
Regards
Meir
Suggestions?There is a seperate newgroup for acceess/adp/sql server integration.
Post there
--
Regards
R.D
--Knowledge gets doubled when shared
"mrrcomp" wrote:

> Hi
> I have an application in Access2003 with linked tables. When I create the
> links manually (picking DNS etc) I have no problems. When I then try to
> recreate the links programatically (different users) I am not able to add
any
> records to any of the tables! If I erase all the linked tables and reconne
ct
> manually with the other user I have no problems!
> I am using the relink code from
> http://support.microsoft.com/defaul...kb;en-us;159691
>
> Any and all suggestions Welcome!!!
> Regards
> Meir
>
> Suggestions?
>|||As long as the user has permissions in the database and you have a
primary key defined on the table, inserts an dupdates shouldn't be a
problem. However, instead of using a DSN, you can supply connection
information in your code. This should get you started:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=password"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
On Fri, 7 Oct 2005 05:17:03 -0700, "mrrcomp"
<mrrcomp@.discussions.microsoft.com> wrote:

>Hi
>I have an application in Access2003 with linked tables. When I create the
>links manually (picking DNS etc) I have no problems. When I then try to
>recreate the links programatically (different users) I am not able to add a
ny
>records to any of the tables! If I erase all the linked tables and reconnec
t
>manually with the other user I have no problems!
>I am using the relink code from
>http://support.microsoft.com/defaul...kb;en-us;159691
>
>Any and all suggestions Welcome!!!
>Regards
>Meir
>
>Suggestions?sql

Refresh Data In Cube

Hi,
How to refresh data for a day in a cube. I want to do this without deleting
the records from the fact tables for the specified day.
Regards,
Yash
You do this with an incremental build of the cube... You must be able to
write a sql expression which identifies WHICH rows in the fact table are
new. If you allow rows to come in twice your cube data will be inaccurate.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:8909B51B-58CB-48FD-BF42-3E226354B578@.microsoft.com...
> Hi,
> How to refresh data for a day in a cube. I want to do this without
deleting
> the records from the fact tables for the specified day.
> --
> Regards,
> Yash
>

Refresh a windows form without closing it

Hi all

I have a couple of windows forms which share tables or parts of tables.

When I edit a tables data on one form (form2), where it ,s data is linked to another form ( form1), when I go back to form1 I find that the fields have not been updated until I close the form and reopen it.

Is there a way to refresh the field on form1 by using a button in the menu bar and what would the code behind look like. I use vb .

Thanks

Rob

You can create a button and then in the click event use:

me.refresh which will refresh the whole form, or you can do controlname.refresh which will refresh just the specific control.

|||

Hi

Thanks for your help so far. I have tried the "me. refresh ()" but nothing happens.

I have also added a few things ( as below) to the code to try and get my dataset to refresh , but still without succsess . Can you suggest any other code snippit which I can add to the me.refresh() which would refresh the data in the client dataset.

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

Me.Refresh = ProjectManagmentDataSet1.Client()

End Sub

Thanks

Rob

|||You need to re-fill the dataset. If you are using a table adapter, it would be like the following:

Code Snippet

Me.MyTableAdapter.Fill(Me.MyDataSet.MyTable)

If you look in the Form_Load event, this is usually where the code is located that will load your data. You can simply copy those steps into the ToolStipButton1_Click event. Once the dataset has been re-filled, use Me.Refresh() to refresh the form. If that doesn't make sense, could you post the code that you are using to load the data into your form?

|||

Thanks for your patience so far

Public Class Form2

Private Sub ClientBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClientBindingNavigatorSaveItem.Click

Me.Validate()

Me.ClientBindingSource.EndEdit()

Me.ClientTableAdapter.Update(Me.ProjectManagmentDataSet1.Client)

Me.SHIPSBindingSource.EndEdit()

Me.SHIPSTableAdapter.Update(Me.ProjectManagmentDataSet1.SHIPS)

End Sub

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'ProjectManagmentDataSet1.SHIPS' table. You can move, or remove it, as needed.

Me.SHIPSTableAdapter.Fill(Me.ProjectManagmentDataSet1.SHIPS)

'TODO: This line of code loads data into the 'ProjectManagmentDataSet1.Client' table. You can move, or remove it, as needed.

Me.ClientTableAdapter.Fill(Me.ProjectManagmentDataSet1.Client)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Client.Show()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Form1.Show()

End Sub

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

Me.Refresh()

End Sub

End Class

|||

Can you show me the code for Form1 as well? I think that is where we need to do the updating. If I understand your question correctly, you edit in Form2 and need it to update into Form1 without having to close and re-open Form1.

One other item that is not really a huge thing but you may want to change the ClientBindingNavigatorSaveItem_Click to the following:

Private Sub ClientBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClientBindingNavigatorSaveItem.Click

Try

Me.Validate()

Me.ClientBindingSource.EndEdit()

Me.ClientTableAdapter.Update(Me.ProjectManagmentDataSet1.Client)

Me.SHIPSBindingSource.EndEdit()

Me.SHIPSTableAdapter.Update(Me.ProjectManagmentDataSet1.SHIPS)

Catch ex as Exception

MsgBox("Update Failed: " & ex.ToString)

End Try

End Sub

This will pop up a message box with an explanation as to why the database was not able to be updated if there is a problem updating the data.

|||

This is as far as I have gotten. I edit in form1and hope to refresh form2 without closing and reopening.

I have no problem with updating the data , just the long rout around of closing and reopening forms.

Regards

Rob

|||

OK, the code in form2 should be like the following:

Code Snippet

Public Class Form2

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Call loadData()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Client.Show()

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Form1.Show()

End Sub

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

Call loadData()

Me.Refresh()

End Sub

Private Sub loadData()

Me.SHIPSTableAdapter.Fill(Me.ProjectManagmentDataSet1.SHIPS)

Me.ClientTableAdapter.Fill(Me.ProjectManagmentDataSet1.Client)

End Sub

End Class

You shouldn't need the "save" part of the code in form2, just in form1. All you need to do is to fill the adapters again and then refresh the form.

|||Thanks ,all sorted out.

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
Thanks and Regards,
Prasanth
HI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:

> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth
|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
[vbcol=seagreen]
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
--
Thanks and Regards,
PrasanthHI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
> > Hi,
> > I am facing a problem while reading user database tables from a procedure in
> > the master database.
> > Example.
> > create procedure sp_Test
> > as
> > select count(*) from syscolumns
> > select count(*) from UserTable
> > go
> >
> > I granted public access to this procedure in the master database.
> > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > the record counts from the master tables.
> > When I execute it for some other user database, it returns the count of
> > records from syscolumn tables of that local database, but still takes the
> > count from UserTable of master database. It does not take the count from the
> > local database.
> >
> > Could anyone help me know, if there is any setting, to point to the local
> > database instead of master while refering user tables (for system tables it
> > takes from local databases). Version : SQL Server 2000
> > --
> > Thanks and Regards,
> > Prasanth|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:
> > HI
> > USE master
> > GO
> > ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> > as
> >
> > DECLARE @.strSQL as nvarchar(100)
> >
> > set @.strSQL = 'select count(*) from ' + @.UserTable
> >
> > select count(*) from syscolumns
> > EXECUTE sp_executesql @.strSQL
> >
> > GO
> >
> > USE pubs
> > GO
> > sp_test 'pubs', 'employee'
> > GO
> >
> > Andras Jakus MCDBA
> >
> > "Prasanth" wrote:
> >
> > > Hi,
> > > I am facing a problem while reading user database tables from a procedure in
> > > the master database.
> > > Example.
> > > create procedure sp_Test
> > > as
> > > select count(*) from syscolumns
> > > select count(*) from UserTable
> > > go
> > >
> > > I granted public access to this procedure in the master database.
> > > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > > the record counts from the master tables.
> > > When I execute it for some other user database, it returns the count of
> > > records from syscolumn tables of that local database, but still takes the
> > > count from UserTable of master database. It does not take the count from the
> > > local database.
> > >
> > > Could anyone help me know, if there is any setting, to point to the local
> > > database instead of master while refering user tables (for system tables it
> > > takes from local databases). Version : SQL Server 2000
> > > --
> > > Thanks and Regards,
> > > Prasanthsql

Referential Integrity In Mutiple Tables. Need Help.

I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.

Example:

Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|

Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|

Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|

Table 4
======|====|
StateID Name
======|====|

How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?You have it already...you just need to enforce the relationship with a FOREIGN KEY CONSTRAINT

And please don't tell me that StateID is an entity column...|||I can only relate table 4 with either table 2 or 3 but not both. what do you mean by entity table? Excuse my ignorance.

Thanks|||I'm not sure I follow you...

USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table4 (
StateID int PRIMARY KEY
, [Name] varchar(50)
)

CREATE TABLE Table2 (
BillingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table2 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)

CREATE TABLE Table3 (
ShippingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table3 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)
GO

INSERT INTO Table3(ShippingAddressID, StateID) SELECT 1,1
INSERT INTO Table2(BillingAddressID, StateID) SELECT 1,1
GO
INSERT INTO Table4(StateID, [Name]) SELECT 1, 'New Jersey'
GO
INSERT INTO Table3(ShippingAddressID, StateID, OtherFields) SELECT 1,1, 1
INSERT INTO Table2(BillingAddressID , StateID, OtherFields) SELECT 1,1, 1
GO

SELECT 'Table3' AS Source, l.ShippingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table3 l
JOIN Table4 r
ON l.StateID = r.StateID
UNION ALL
SELECT 'Table2' AS Source, l.BillingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table2 l
JOIN Table4 r
ON l.StateID = r.StateID
GO

SET NOCOUNT OFF
DROP TABLE Table2
DROP TABLE Table3
DROP TABLE Table4
GO|||Oh, I meant IDENTITY Column by the way...not Entity...sorry|||why wouldn't StateID be an identity column?

are you perhaps suggesting that a natural key like NJ would be more suitable?

better hide, because all the surrogate key fanatics are gonna come gunnin' for ya...|||Hey...it's their perogative...let them join till the cows come home...

And there are times for it...but not as many as it's abused...

Here's a question. Would you use an identity column for a phone number? SSN? Stock Market symbol?|||Would you use an identity column for a phone number? SSN? Stock Market symbol?me? never

you must have me confused with those surrogate key fanatics, who blindly require an identity column in every table...

i know a guy on, ahem, another forum web site, who once took a poll on the subject

see Surrogate Keys.The Devils spawn (OK Not really) (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)

Referential integrity different databases

I would like to apply referential integrity between two
tables that are not part of the same database.
Is this possible ?You can't do it with foreign key constraints. They are not allowed across
databases.
You can do it with triggers or by only allowing inserts, deletes, and
updates through stored procedures and enforcing the integrity in the
triggers or stored procs. Note that if you do this, there are still some
problematic situations. For example, the triggers and/or stored procs can't
prevent you from restoring one of the databases from last night's backup and
thus breaking the integrity.
Tom
"TheOne" <TheOne@.discussions.microsoft.com> wrote in message
news:61E4E107-5E5C-4D40-9575-D5B8E17D9F04@.microsoft.com...
>I would like to apply referential integrity between two
> tables that are not part of the same database.
> Is this possible ?
>sql

Referential Integrity - Which Column violates this?

Hi All,

I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

Any one can shed some light here? Many thanks!

CyherusDo you have any ddl that's behind the VB application? Can you match it with the values you want to insert?|||nope, I am basically connecting to the SQL server via odbc.. and using INSERT INTO queries to add records as I read from a text file..
Since the SQL Server is able to throw me an error description that says which table and column name violates the RI, I thought I would be able to manage this with codes. That is create the primary record in the primary table (affected table) and resume to insert the record in the foreign table again..

Cyherus|||It should be done with sp, not with FE-based action queries.|||The text of the error message (in the errors collection) will tell you which foreign key caused the problem. The code is the same for them all, so it only tells you that a foreign key was the problem.

-PatP|||If you do it in sp you can customize the way errors are returned to the client. And of course Errors (rdoErrors) collection should be looped to retrieve ALL the errors that came from the server.|||Alright.. as per your advise.. I am now performing these actions on the BE via sp.
now, I am using both return value, out parameters and capture @.@.ERROR.
Issue is that the system it prompting me before I can capture the errors returned.. sp example:

ALTER PROCEDURE dbo.sp_insert_bl
(
@.AAA varchar(12),
@.BBB int,
)
AS
DECLARE @.err int

SET NOCOUNT ON
BEGIN TRAN
INSERT INTO dbo.Table ([AAA], [BBB])
VALUES (@.AAA, @.BBB)
SELECT @.err = @.@.ERROR
IF @.err <> 0
BEGIN
ROLLBACK TRAN
RETURN @.err
END
ELSE
BEGIN
SELECT @.blid = SCOPE_IDENTITY()
COMMIT TRAN
RETURN 0
END

oh yes, using ado to execute exec the sp
cmd.parameter.........
cmd.execute
cmd.parameter("return value")

do you suggest using RAISERROR?|||Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?

Referential Integrity - linking multiple tables to transaction table

I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:

Transaction Table
-TranId
-Calc Amount

Table 1 (the amount is inserted into the transaction table)
- Tb1Id
- Tb1Amt

Table 2 (an amount is calculated based on the percentage and inserted
into the transaction table)
-Tbl2Id
-Tb2Percentage

Table 3 (the amount is inserted into the transaction table)
-Tbl3Id
-Tbl3Amut

Table 4 (an amount is calculated based on the percentage and inserted
into the transaction table. )
-Tbl2Id
-Tb2Percentage

How do I create referential integrity between the Transaction table and
the rest of the tables. When I make changes to the values in Table 1 -
4, I need to be able to reflect this in the Transaction table.

Thanks.Can you make "Transaction Table" into a view
based on joins between your other 4 tables?

Where does TransID come from?|||Thanks,

Trans ID is generated - identify column.

We thought of using a view. But the when the core object is set to
LIVE, all changes from then on will be tracked as new transactions in
the transaction table. So in essence, you have have a row in Table 1 -
4 that has multiple transactions in the transacation table. We could
add a column to the Transaction Table set it to refer to the Ids of
Table 1 - 4. But we won't be able to use the DBMS contraints to enfore
this. Possibly a middle table to each Table 1 to 4?|||(heyvinay@.gmail.com) writes:
> I have transaction table where the rows entered into the transaction
> can come a result of changes that take place if four different tables.
> So the situation is as follows:
> Transaction Table
> -TranId
> -Calc Amount
> Table 1 (the amount is inserted into the transaction table)
> - Tb1Id
> - Tb1Amt
> Table 2 (an amount is calculated based on the percentage and inserted
> into the transaction table)
> -Tbl2Id
> -Tb2Percentage
> Table 3 (the amount is inserted into the transaction table)
> -Tbl3Id
> -Tbl3Amut
> Table 4 (an amount is calculated based on the percentage and inserted
> into the transaction table. )
> -Tbl2Id
> -Tb2Percentage
> How do I create referential integrity between the Transaction table and
> the rest of the tables. When I make changes to the values in Table 1 -
> 4, I need to be able to reflect this in the Transaction table.

Depends on what you mean with changes, but obviously if you change an
amount in one table and you want that to affect the CalcAmount in the
Transaction table, then you should consider a trigger. For that to
work, the Transaction table need to have one FK column per referencing
table.

Your description was quite brief, and very abstract. It is not at all
impossible that there is a better design, if you can give more meat of
what is behind the various tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks.
Well the application we are designing is for Premium calcuation. The
transaction table will store all the premium calcuation breakdowns.
The reference to table 1 - 4 are various elements within the system
that will generated chargeable premiums. Eg. the product selected;
discounts applied to the customer; specific endorsements; Payment
menthod discounts etc. Once policy is live, and changes to the source
tables (eg product, discount amount) etc or even the cancellation of
the policy will raise NEW records in the transaction tables.

We can manage all this from code directly to ensure all is in sync, but
I prefer to apply referential integrity managed by the DBMS.

You wrote: "For that to work, the Transaction table need to have one FK
column per referencing table. " - does MS SQL allow null values for
foreign key constraints?

Thanks|||(heyvinay@.gmail.com) writes:
> We can manage all this from code directly to ensure all is in sync, but
> I prefer to apply referential integrity managed by the DBMS.

Referential integrity is about the integrity of - references. That is,
if the Orders table has a FK constraint to table Customers, you cannot
add an Order for a non-existing customer, and you cannot delete a customer
that has an order.

As I understood it, you want one data in one table be the result of data
in other tables. This cannot be achieved with referential integrity. You
can use views, and under some circumstances you can materialise a view.

> You wrote: "For that to work, the Transaction table need to have one FK
> column per referencing table. " - does MS SQL allow null values for
> foreign key constraints?

Yes. For instance, in a Customers table, you may have Citizen column
table that has a foreign-key constraint to the Countries table. But
this column must be nullable, since some people stateless. Not talking
of juridical persons, who normally are not citizens.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Off the top of my head, two fundamental ways.

Insert into the 4 tables, and have triggers on those tables for insert,
update, and delete that add rows to the trans table.

Two. utilize standardized stored procedures you use to add/edit/update
the 4 tables. Inside the stored procedures, do a begin trans/ commit
around the changes and the trans table. In this way, you can pretty
much ensure that it will keep up.

The stored procedure has the advantage of allowing more business logic
around changes. You can validate data changes external to the app.
However, you MUST enforce that all changes to the 4 tables MUST uses
the specified stored procedures.

The Triggers have the advantage of "simplicity." Again though,. you are
somewhat limited in what logic you can use.

Two comments you didn't ask for. Be very stingy with indexes on the
trans table. I'm not saying ot have none, BUT don't have a lot of
indexes. And the indexes you do have should be fairly unique. Trans
tables get very large very fast, and they really slow everything down
in a sneaky insidious sort of way in a few years.

Secondly, it can be hard to get the :"right" infomration into the trans
table. As a test to see whether you have all data you need, manually
try to take each trans record, and recreate the final data results. In
a perfect world, your trans table will allow a picture of exactly what
changed when and how, allowing hte final results to be seen.
For a final test, just go ahead and write the program that will read
the trans data and recreate teh table. This is almost never trivial,
but almost always worthwhile.
-dougsql

Referential Integrity

Hi All
I have a DB , when i go into Diagrams & create the diagrams I can see
that a couple of tables have referntial integrity ... I want to create
a script ... that would let me knw the no. of tables that do not have
referentail integrity ...
Has some got that kind of script
ThanksHi
Do you mean tables that do not have PK,and FK?
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1153716353.700403.319830@.b28g2000cwb.googlegroups.com...
> Hi All
> I have a DB , when i go into Diagrams & create the diagrams I can see
> that a couple of tables have referntial integrity ... I want to create
> a script ... that would let me knw the no. of tables that do not have
> referentail integrity ...
> Has some got that kind of script
> Thanks
>|||Yess
Uri Dimant wrote:
> Hi
> Do you mean tables that do not have PK,and FK?
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1153716353.700403.319830@.b28g2000cwb.googlegroups.com...
> > Hi All
> >
> > I have a DB , when i go into Diagrams & create the diagrams I can see
> > that a couple of tables have referntial integrity ... I want to create
> > a script ... that would let me knw the no. of tables that do not have
> > referentail integrity ...
> >
> > Has some got that kind of script
> >
> > Thanks
> >|||Hi
SELECT
name TableName
FROM
sysobjects
WHERE
type = 'U'
AND ( OBJECTPROPERTY(sysobjects.id , 'IsPrimaryKey' ) = 0 OR
OBJECTPROPERTY(sysobjects.id , 'IsForeignKey' ) = 0 )
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1154002811.976393.60290@.h48g2000cwc.googlegroups.com...
> Yess
>
>
>
> Uri Dimant wrote:
>> Hi
>> Do you mean tables that do not have PK,and FK?
>>
>> "Double_B" <bharatbutani@.gmail.com> wrote in message
>> news:1153716353.700403.319830@.b28g2000cwb.googlegroups.com...
>> > Hi All
>> >
>> > I have a DB , when i go into Diagrams & create the diagrams I can see
>> > that a couple of tables have referntial integrity ... I want to create
>> > a script ... that would let me knw the no. of tables that do not have
>> > referentail integrity ...
>> >
>> > Has some got that kind of script
>> >
>> > Thanks
>> >
>

referential integrity

When implementing a schema for data warehousing is it common practice not to
implement referential integrity across the tables in the database?
If true is it purely for ETL purposes or does it improve query performance
if the referential integrity is not applied to the tables.
OllieMost of the sql server implementations (both OLTP and OLAP) I have seen in
my many years of consulting on the product have not had much if any ref.
integrity in place. RF can provide the optimizer with useful information,
yet it also takes overhead to maintain/enforce. And without it you get
fewer application errors - yet allow in bad data. Most designers/developers
seem to take the easy road . . .
TheSQLGuru
President
Indicium Resources, Inc.
"news.microsoft.com" <ollie_riches@.hotmail.com> wrote in message
news:uIbwJ7zsHHA.400@.TK2MSFTNGP02.phx.gbl...
> When implementing a schema for data warehousing is it common practice not
> to implement referential integrity across the tables in the database?
> If true is it purely for ETL purposes or does it improve query performance
> if the referential integrity is not applied to the tables.
> Ollie
>|||Yes, this is very common in a data warehouse. In OLTP databases, DRI is
VERY important to ensure the referential integrity of the data because the
data is coming in from applications and possibly other places. In a data
warehouse, the ONLY way data should ever get into your warehouse is through
your ETL process(es). Since these processes should always do thorough
checking of the data inclusing references, you can safely remove the DRI
since it can speed up the ETL loads. However, I always include it by
default even in warehouses as an extra safety check and only remove it if
needed for the performance boost (only ever had to do this twice in many
years when it involved loading 10's to 100's of millions of rows a night in
a tight window of time.
"news.microsoft.com" <ollie_riches@.hotmail.com> wrote in message
news:uIbwJ7zsHHA.400@.TK2MSFTNGP02.phx.gbl...
> When implementing a schema for data warehousing is it common practice not
> to implement referential integrity across the tables in the database?
> If true is it purely for ETL purposes or does it improve query performance
> if the referential integrity is not applied to the tables.
> Ollie
>|||On Jun 20, 3:27 pm, "news.microsoft.com" <ollie_ric...@.hotmail.com>
wrote:
> When implementing a schema for data warehousing is it common practice not
to
> implement referential integrity across the tables in the database?
> If true is it purely for ETL purposes or does it improve query performance
> if the referential integrity is not applied to the tables.
> Ollie
I prefer having referential integrity enabled on the development
environment, removing it in production.
It has the benefit of helping ETL developers finding errors very early
and clearly.
Marco Russo
http://www.sqlbi.eu
http://sqlblog.com/blogs/marco_russo|||same for me.
yes in dev
no in prod.
"Marco Russo" <marco.russo@.loader.it> wrote in message
news:1182449884.703796.298200@.n2g2000hse.googlegroups.com...
> On Jun 20, 3:27 pm, "news.microsoft.com" <ollie_ric...@.hotmail.com>
> wrote:
> I prefer having referential integrity enabled on the development
> environment, removing it in production.
> It has the benefit of helping ETL developers finding errors very early
> and clearly.
> Marco Russo
> http://www.sqlbi.eu
> http://sqlblog.com/blogs/marco_russo
>sql

Referential Integrity

Hi All
I have a DB , when i go into Diagrams & create the diagrams I can see
that a couple of tables have referntial integrity ... I want to create
a script ... that would let me knw the no. of tables that do not have
referentail integrity ...
Has some got that kind of script
ThanksHi
Do you mean tables that do not have PK,and FK?
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1153716353.700403.319830@.b28g2000cwb.googlegroups.com...
> Hi All
> I have a DB , when i go into Diagrams & create the diagrams I can see
> that a couple of tables have referntial integrity ... I want to create
> a script ... that would let me knw the no. of tables that do not have
> referentail integrity ...
> Has some got that kind of script
> Thanks
>|||Yess
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> Do you mean tables that do not have PK,and FK?
>
> "Double_B" <bharatbutani@.gmail.com> wrote in message
> news:1153716353.700403.319830@.b28g2000cwb.googlegroups.com...|||Hi
SELECT
name TableName
FROM
sysobjects
WHERE
type = 'U'
AND ( OBJECTPROPERTY(sysobjects.id , 'IsPrimaryKey' ) = 0 OR
OBJECTPROPERTY(sysobjects.id , 'IsForeignKey' ) = 0 )
"Double_B" <bharatbutani@.gmail.com> wrote in message
news:1154002811.976393.60290@.h48g2000cwc.googlegroups.com...
> Yess
>
>
>
> Uri Dimant wrote:
>|||SQL Server has a basic data modeler (diagrams) that allows you to create
foreign keys between tables. It is not as nice or powerful as Erwin, but it
gets the job done (and it is free).
If you want to stick with Microsoft products you could try using Visio. I
remember seeing database diagramming capabilities in some of the versions.
I haven't looked at the new Visual Studio offering, but they might have
something in there:
http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Here are a few other tools that I am aware of:
WinSQL
http://synametrics.com/SynametricsWebApp/WinSQL.jsp
SQL Diagrams
http://www.skilledsoftware.com/sqldiagrams.htm
Keith Kratochvil
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:059856C5-B1EE-481D-A301-A93D62E11EAD@.microsoft.com...
> We are currently using Erwin for our Data Modeler, but wanted to know if
> there are any good tools, or better data modelers, to help with our
> project
> of referential integrity. Currently we use triggers to enforce our
> referential integrity, but we would like to move towards using constraints
> to
> enforce our data integrity. Currently using SQL Server 2000.

Referential Integrity

Hi

how to set the referential integrity between 2 tables using enterprise
manager (microsoft SQL SERVER).. i tried and the tab doesn't allow me
to choose. pls.help.

thanks
sree

--
chavasreedhar
Message posted via http://www.exforsys.com for all your training needs.Do you have primary keys on your tables? This is a requirement for the
referenced table of the relationship. It is also a Best Practice for all
relational database tables.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"chavasreedhar" <chavasreedhar.20gug3@.no-mx.forums.yourdomain.com.au> wrote
in message news:chavasreedhar.20gug3@.no-mx.forums.yourdomain.com.au...
> Hi
> how to set the referential integrity between 2 tables using enterprise
> manager (microsoft SQL SERVER).. i tried and the tab doesn't allow me
> to choose. pls.help.
> thanks
> sree
>
> --
> chavasreedhar
> Message posted via http://www.exforsys.com for all your training needs.

Referential constraint between two tables in two databases

Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.

i think not possible

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

You should be able to set up the reference as a linked server (http://www.databasejournal.com/features/mssql/article.php/3085211,http://msdn2.microsoft.com/en-us/library/ms188279.aspx -- this is a sql server 2005 article, but earlier versions support linked servers as well)

I doubt that you can enforce the constraint declaritively, but I am quite sure you can do it using a trigger. Let us know if you need help with the triggers.

|||

And what can be used when databases are sitted on the same server?

|||

Then it's even easier because you don't need the linked server, you can simply reference the table on the other database by indluding the DB name, eg,

database2.dbo.sometable

Or (and this is the way I do it), create a view in database1 pointing to the table in database2

use database1
go
create view TableXyz as
select * from databse2.dbo.TableXyz
go
grant insert, update, select, delete on TableXyz to ...
go

Then, from within database1, all references to TableXyz really point to database2.dbo.TableXyz

I think the declaritive RI may work in this scenario but I'm not positive, but in any case a trigger will work

sql