transfer Data From Acces: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>

Wednesday, March 28, 2012

How do I add a new column to an existing Data Source View in SSRS?

I've got a table in my Data Source View that is one of our central entity
tables in our warehouse, and there are LOTS of relationship lines (Roles)
linking to this table. We've just added 6 new columns to this table, and I
need to add them to the Data Source View so the new columns will be available
to the end users running Report Builder.
I am pulling my hair out trying to find the option to add the new columns!
Completely removing and re-adding the table is NOT an option, as we have 37
relationship lines coming into this central entity table.Hello here,
From your description, my understanding of this issue is that, you add some
new columns in the source table in database and you want to reflect in the
Data Source View. If I am offset, please feel free to let me know.
Based on my research, you could not add the new column in the data source
view directly. My suggestion is that you could regenerate the model. Since
the wizard will generate the relationship automatically, you will not
concern about creating many relationships.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thanks for your post. The wizard does not automatically re-establish all
the relationship lines. These 37 relationship lines I had to manually create
the very first time when I generated the model, even though most of them
already have a foreign key in the database expressing the relationship. I do
not want to have to manually re-create all these relationship lines. Also, I
have other computed expression columns that would be blown away if I
re-generate the entire Data Source View using the Wizard. I would have to
manually recreate those as well.
"Wei Lu [MSFT]" wrote:
> Hello here,
> From your description, my understanding of this issue is that, you add some
> new columns in the source table in database and you want to reflect in the
> Data Source View. If I am offset, please feel free to let me know.
> Based on my research, you could not add the new column in the data source
> view directly. My suggestion is that you could regenerate the model. Since
> the wizard will generate the relationship automatically, you will not
> concern about creating many relationships.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
I would like to suggest you use the Refresh button in the DSV designer,
then use the Generate option on the corresponding entity in the report
model.
Please let me know if this resolved your problem.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Perfect!! The Refresh button did the trick!! Thank you so much!
=Steve=
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to suggest you use the Refresh button in the DSV designer,
> then use the Generate option on the corresponding entity in the report
> model.
> Please let me know if this resolved your problem.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Glad to hear that you resolve this issue. If you have any question, please
feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Monday, March 26, 2012

How disable IDENTITY on column

Hello,

I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO|||Yes I know this manner, but I can`t disable IDENTITY on all tables because system say me that I can use this function only one table in this moment and don`t allow me disable IDENTITY on tables.|||

something to start with

use northwind
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'
BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END
ROLLBACK TRANSACTION
SELECT * FROM #ALLTABLES
rollback transaction

|||

I am not clear on what you want to do. Do you want to disable it temporarily on multiple tables? Or eliminate it permanently? I can whip up the basis of a routine to change the identity column to no longer be an identity column, but from a further reply that doesn't seem to be what you want.

You can only use SET IDENTITY_INSERT ON on only one table at a time, per connection, but this should be acceptable because you can only insert into one table at a time per connection. So if this is a temporary thing, then all you need to do is just turn it off for the table you are working on.

Expand and a better answer can possibly be arrived at.

|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||

hey take a look at this

use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END

it says 33 rows affected

|||Try this:

DECLARE @.Statement nvarchar(2000)
DECLARE Statements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'SET IDENTITY_INSERT ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) + ' OFF'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN Statements
FETCH NEXT FROM Statements INTO @.Statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Statements INTO @.Statement
BEGIN TRY
EXEC(@.Statement)
END TRY
BEGIN CATCH
SELECT 'Error:'+ERROR_MESSAGE()+' on:'+@.Statement
END CATCH
END
-- Clean up work
CLOSE Statements

If you are sure that all the tables have identity columns you can use this:

sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"

How disable IDENTITY on column

Hello,

I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

something to start with

use northwind
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'
BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END
ROLLBACK TRANSACTION
SELECT * FROM #ALLTABLES
rollback transaction

|||

Expand and a better answer can possibly be arrived at.

|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||

hey take a look at this

use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'

it says 33 rows affected

|||Try this:

If you are sure that all the tables have identity columns you can use this:

sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"sql

how create mining model

i have a problem, because i have 3 tables.
One name "CallPair" contain: id- identity number, from - phone number, to - phone number.
Second name "Talk" contain: id - identity number, callPairId - id from "CallPair" table, date
Third name "Transaction" contain: id - identity number is a number of transaction, idTalk - id from Talk table, callNr - number of talk in transaction

I want mining model to sequence clustering, but i don't know how build case and nested table

If you are trying to build a model across all phone numbers to see what number a customer is likely to call next, or use perform any anaysis across all numbers, sequence clustering is not likely to work very well.

Sequence Clustering works well when you have less than 100 possible sequence states. You could categorize calls potentially and sequence those - e.g. user recieves call, doesn't answer, sends text message, makes free call, etc. and then use Sequence Clustering.

In any case, I would need to know more about the problem you are trying to solve to advise how to build a model.

Friday, March 23, 2012

how control to Tempdb Growth

Hi all

I am using Sql Server 2000. My application deals with lot data in the db(some tables have data in the range of 34 million records to 50 million records). When i run my application the tempdb grows upto 70 GB and i am getting the error Disk out of space.

How to control this tempdb growth?

Note - No Index or Keys are defined on those tables.

Removed those keys to improve the performance of another application which uses the same database.

Sankar

I think you gave the solution yourself. Removing the indexes will cause a lot of hash joins which in its turn causes tempdb usage.

You really should consider using indexes. The only time indexes can cause you grief is when loading or inserting massive amounts of data and you have lots of indexes. If this is not the case maybe your storage subsystem is simply not well scaled for the database.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Tempdb is the workarea for the instance. u need to findout what consume TEMPDB more. Whether there is any open transaction(DBCC OPENTRAN) . Droping of index is not a solution at all rather you must see the query and create proper indexes. I believe your tempdb Transaction log is growing not the data file . in that case you can shrink the log also.

Madhu

|||No i dont have permission to create index now as the same database is used by other applications also ....|||

Then you will probably have to live with the fact that you need a big tempdb.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Monday, March 19, 2012

How can you use a variable tablename and retrieve the output from the Insert?

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @.tTemp.

How can you use a variable table name and retrieve the output from the Insert?

declare @.tTestTable varchar (20)

set @.tTestTable = 'mis.test_sequence'

--DECLARE @.tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)

declare @.testsql varchar (4000), @.testseq int

DECLARE @.tTemp table (mainpk int)

set @.testsql = 'DECLARE @.tTemp table (mainpk int) INSERT ' + @.tTestTable + ' OUTPUT INSERTED.sqVal into @.tTemp VALUES (getdate() ) SELECT @.testseq=mainpk FROM @.tTemp'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output,@.tTemp table (mainpk int),@.tTemp table (mainpk int) ',@.tTemp,@.tTemp,@.testseq output,@.tTemp

SELECT * FROM @.tTemp

Please help

Thanks Tim.

Why not to create an sp per each table, instead trying to come with a general one?

Code Snippet

use tempdb

create table #t (c1 int not null identity, c2 datetime)

declare @.tTestTable varchar (20)

set @.tTestTable = '#t'

declare @.testsql nvarchar (4000), @.testseq int

set @.testsql = 'INSERT into' + quotename(@.tTestTable) + '(c2) values(getdate()); set @.testseq = scope_identity()'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output',@.testseq output

SELECT @.testseq

drop table #t

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Thankyou hunchback,

Your Code Snippet helped me solve my problem.

Tim.

Here's my final code.

USE [TestDB]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Create proc GetNext (@.sequenceName varchar(40) , @.nextVal int output)

begin

declare @.sqlStmt nvarchar (4000)

declare @.tTemp table (mainpk int)

set nocount on

-- This option works using the OUTPUT clause.

set @.sqlStmt = 'DECLARE @.tTemp table (mainpk int) Insert ' + @.sequenceName + ' OUTPUT INSERTED.sqVal into @.tTemp DEFAULT VALUES SELECT @.nextVal=mainpk FROM @.tTemp'

execute sp_executesql @.sqlStmt, N'@.nextVal int output',@.nextVal output

set nocount off

end

How can you stop and cycle through a column(s) of data?

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + " ";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + " ";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + " ";
Label4.Text = "Your relatives: " + " ";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.I tried this:

while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
//take the read data from the reader and display it for testing purposes
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + " ";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + " ";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + " ";
Label4.Text = "Your relatives: " + " ";
while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + " ";
}
}

But for some reason the relationships print out multiple times rather than just until there are no more relatives in the database.|||You will most likely get a usable response by posting this question in one of the .NET forums.|||

Arnie Rowland wrote:
You will most likely get a usable response by posting this question in one of the .NET forums.

Ok, this is where I am really confused. Why does the reading of data loop all over again from the beginning? I would think that when it read the last row that then it would just exit out of the loop. Just like what's happening with the first loop with while(reader.Read()).

while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + " ";
}

Ok, thank you. I will try.

How can you prevent the data changes message during editing a row

I just upsized my Access application (mail-order processing system) and
linked to SQL server 2000 via ODBC tables links from an Access 2000 front
end.
By doing so the standard optimistic locking of SQL server 2000 comes into
force and the option "lock edited record" on the Access Client gets ignored.
I cannot work with optimistic locking because if e.g. an order gets entered
in a table and e.g. a stock allocation job runs in the background that
updates the order header,
all the entered data will be lost (or must be copied to the clipboard and
edited ...).
Do you know how to overcome this problem - problems like this should
actually be quite common ...
Several years ago I used to work with a database called DataFlex.
This database had the best locking mechanism I every have experienced.
If one enters dat in a form the pulled record does not get locked (like
optimistic locking).
It only gets locked shortly before the update - it workes like this:
read a record from the table and display in the form
user modifies data in form
user clicks update button
record gets locked and reread into the record buffer
client program compares every form field with the record buffer if any data
was changed
if a field was changed the record buffer was overwritten with the data the
user entered
the record gets saved and unlocked
This way only the changed data fields get updated and changes of other users
do not get overwritten!
Is there a way on SQL server to do something similar or how do you deal with
this problem?
I also thought of writting an INSTEAD OF UPDATE trigger and check if any
column is different from the Inserted to the "REREAD" record buffer and appl
y
the above mentioned logic of DataFlex but this streches my SQL knowledge jus
t
a bit too far ...
May be you can help?
Any comments are appreciated!
Thanks in advance.
OliverBracket your user actions between BEGIN TRANS...COMMIT TRANS and to an
UPDATE WITH(ROWLOCK) setting a dateModified field to getDate()
immediately after BEGIN. All subsequent modifications will be queued
until you fire your update and commit. You could also do a check on the
dateModified field and simply warn the user if it has incremented since
they read the record, but doing a field-by-field comparison is more
work than is necessary.
The warning is almost always the preferable solution, since it is
dangerous to assume that such a collision should proceed in whichever
more or less arbitrary order when human intervention is not only
possible, but desirable.|||Oliver wrote:
> I just upsized my Access application (mail-order processing system)
> and linked to SQL server 2000 via ODBC tables links from an Access
> 2000 front end.
> By doing so the standard optimistic locking of SQL server 2000 comes
> into force and the option "lock edited record" on the Access Client
> gets ignored. I cannot work with optimistic locking because if e.g.
> an order gets entered in a table and e.g. a stock allocation job runs
> in the background that updates the order header,
> all the entered data will be lost (or must be copied to the clipboard
> and edited ...).
> Do you know how to overcome this problem - problems like this should
> actually be quite common ...
> Several years ago I used to work with a database called DataFlex.
> This database had the best locking mechanism I every have experienced.
> If one enters dat in a form the pulled record does not get locked
> (like optimistic locking).
> It only gets locked shortly before the update - it workes like this:
> read a record from the table and display in the form
> user modifies data in form
> user clicks update button
> record gets locked and reread into the record buffer
> client program compares every form field with the record buffer if
> any data was changed
> if a field was changed the record buffer was overwritten with the
> data the user entered
> the record gets saved and unlocked
> This way only the changed data fields get updated and changes of
> other users do not get overwritten!
> Is there a way on SQL server to do something similar or how do you
> deal with this problem?
> I also thought of writting an INSTEAD OF UPDATE trigger and check if
> any column is different from the Inserted to the "REREAD" record
> buffer and apply the above mentioned logic of DataFlex but this
> streches my SQL knowledge just a bit too far ...
> May be you can help?
> Any comments are appreciated!
> Thanks in advance.
> Oliver
Yes. This is easy. Add a TIMESTAMP column to each table where you need
this support. A timestamp column is not a date, but a column that SQL
Server automatically changes each time a row is updated. When you
initially query the row data, select the TIMESTAMP as well. When you
save the data from your stored procedure (ideally, you 'll be using
stored procedures), compare the TIMESTAMP you selected with the
timestamp currently in the row. If they are different, then you know the
data was changed in the interim and can raise an error and have the
client application automatically re-query the data. Your update
statement can look something like this:
Update dbo.MyTable
Set
Col1 = @.Col1,
Col2 = @.Col2
Where
ColPK = @.ColPK
and
timestamp = @.timestamp
If @.@.ROWCOUNT != 1 -- either the row was changed or it no longer exists
RAISERROR ...
Else
-- Everything is good to go
David Gugick - SQL Server MVP
Quest Software|||Hi David,
Thanks for your tips but this sounds like a lot of programming to overcome
a problem that actually is a server's job.
Raising an error message when data changed in the background can only be
useful if there is a user at the other end.
What happens if a program gets caught out by a user e.g. the user changes da
ta
during the time the program read the row to update one column?
You will have to program some code to get arround the problem for every
transaction you are trying to do in a job like stock allocation, release
orders for delivery... - that would be too much work for me as my applicatio
n
is big!
I think it would be much better if the server could check in an update
trigger if there was a concurrent update of columns and just updates the
column(s) that were changed by the current transaction. This way all changes
other transactions did will be kept and nobody has to decide which
data/changes to keep.
I am quite new to SQL server programming and do not know all the ins and
outs of trigger transaction programming.
Could you or somebody else suggest some code how to achieve this?
Thanking you in advance.
Oliver
"David Gugick" wrote:

> Oliver wrote:
> Yes. This is easy. Add a TIMESTAMP column to each table where you need
> this support. A timestamp column is not a date, but a column that SQL
> Server automatically changes each time a row is updated. When you
> initially query the row data, select the TIMESTAMP as well. When you
> save the data from your stored procedure (ideally, you 'll be using
> stored procedures), compare the TIMESTAMP you selected with the
> timestamp currently in the row. If they are different, then you know the
> data was changed in the interim and can raise an error and have the
> client application automatically re-query the data. Your update
> statement can look something like this:
> Update dbo.MyTable
> Set
> Col1 = @.Col1,
> Col2 = @.Col2
> Where
> ColPK = @.ColPK
> and
> timestamp = @.timestamp
> If @.@.ROWCOUNT != 1 -- either the row was changed or it no longer exists
> RAISERROR ...
> Else
> -- Everything is good to go
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Oliver wrote:
> Hi David,
> Thanks for your tips but this sounds like a lot of programming to
> overcome
> a problem that actually is a server's job.
> Raising an error message when data changed in the background can only
> be useful if there is a user at the other end.
> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
> You will have to program some code to get arround the problem for
> every transaction you are trying to do in a job like stock
> allocation, release orders for delivery... - that would be too much
> work for me as my application is big!
> I think it would be much better if the server could check in an update
> trigger if there was a concurrent update of columns and just updates
> the column(s) that were changed by the current transaction. This way
> all changes other transactions did will be kept and nobody has to
> decide which data/changes to keep.
> I am quite new to SQL server programming and do not know all the ins
> and
> outs of trigger transaction programming.
> Could you or somebody else suggest some code how to achieve this?
> Thanking you in advance.
> Oliver
I don't agree with your assessment. This is an application programming
issue and not one for the database to manage on its own. You're
suggesting that the server update a row that has been updated in the
interim by another process just because the columns being updated are
not the same. I would argue that there's no way for SQL Server to know
if changing a single column value in a row would somehow affect business
rules and know whether or not the proposed row changes are valid.
Many applications can deal with this scenario by assuming the last
update should be the most current. In that scenario, there is no
additional programming required. If you need to manage concurrency and
changes to a row by another session to avoid overwriting those changes,
you should use a timestamp. You pass the timestamp value with the other
column values to your stored procedure and the procedure does the work.
The application should be able to handle the condition where the row was
not updated because an error was raised and then requery the data and
inform the end-user. All your DML should be done in stored procedures.
In the case where there is no end-user, your application code should log
an error condition that can be managed manually in an interactive
fashion or it could requery the data, assuming this is something it can
work around.
For a nightly batch process, you could lock the entire table within your
transaction.

> What happens if a program gets caught out by a user e.g. the user
> changes data during the time the program read the row to update one
> column?
I'm not sure what you are describing here. A change is a change.
Presumably, you have a stored procedure to manage each type of change to
your data. Maybe you can elaborate on this part a little more.
David Gugick - SQL Server MVP
Quest Software

How can XML Bulk Load assign automatically a PK to a column?

Hi to everybody!
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David GrantThe table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.com...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?xml version="1.0" encoding="UTF-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant

>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>

<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>

<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
" PROVIDER=SQLOLEDB;SERVER=(local);DATABAS
E=Northwind;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant

>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com

How can XML Bulk Load assign automatically a PK to a column?

Hi to everybody!
I'm trying to make the Bulk Load of an XML file with some tables and I
want to make SQL Server 2000 to assign a PK to a column determined in
the XSD file (I expecto to find an option that allows me to see the
key icon on a column when I click the Design Table option.)I've been
searching in the MSDN library and I haven't found anything. If anybody
had any suggerence it'd be very wellcomed.
These are the XML and XSD file:
<?xml version="1.0" encoding="utf-8" ?>
<IND_UNICO schemaVersion="1">
<DATOS NUM_DOC="3789">
<NOTARIO_ID>7</NOTARIO_ID>
<DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
<DOCUMENTO_ID>555</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>4052</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>44</INMUEBLE_ID>
<INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
<INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
<DATOS NUM_DOC="3791">
<NOTARIO_ID>9</NOTARIO_ID>
<DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
<DOCUMENTO_ID>666</DOCUMENTO_ID>
<OPE>
<OPERACION_ID>3036</OPERACION_ID>
<INMUEBLE>
<INMUEBLE_ID>99</INMUEBLE_ID>
<INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
<INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
</INMUEBLE>
</OPE>
</DOC>
</DATOS>
</IND_UNICO>
And this is the XSD file:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
version="2.1.3">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="INDICE_DATOS"
parent="tblINDICE"
parent-key="idIndice"
child="tblDATOS"
child-key="idIndice"
/>
<sql:relationship
name="DATOS_DOCUMENTOS"
parent="tblDATOS"
parent-key="idDatos"
child="tblDOCUMENTOS"
child-key="idDatos"
/>
<sql:relationship
name="DOCUMENTOS_OPERACIONES"
parent="tblDOCUMENTOS"
parent-key="idDocumento"
child="tblOPERACIONES"
child-key="idDocumento"
/>
<sql:relationship
name="OPERACIONES_INMUEBLES"
parent="tblOPERACIONES"
parent-key="idOperacion"
child="tblINMUEBLES"
child-key="idOperacion"
/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="IND_UNICO" sql:relation="tblINDICE">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DATOS" maxOccurs="unbounded"
sql:relation="tblDATOS"
sql:relationship="INDICE_DATOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DOC"
sql:relation="tblDOCUMENTOS"
sql:relationship="DATOS_DOCUMENTOS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OPE"
sql:relation="tblOPERACIONES"
sql:relationship="DOCUMENTOS_OPERACIONES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE"
sql:relation="tblINMUEBLES"
sql:relationship="OPERACIONES_INMUEBLES">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INMUEBLE_ID"
type="xsd:short"/>
<xsd:element name="INMUEBLE_TIPO"
type="xsd:string"/>
<xsd:element name="INMUEBLE_VALOR"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="OPERACION_ID"
type="xsd:short"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="DOCUMENTO_ID"
type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_OBJ" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_OPE" type="xsd:short"
use="required"/>
<xsd:attribute name="NUM_SUJ" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="NOTARIO_ID" type="xsd:short"/>
</xsd:sequence>
<xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:short"
use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Greetings,
David Grant
The table is not created by the bulk load tool is it? From memory it isn't
so I would expect you to have created the PK in advance. I don't think a
load should change the structure, just the data.
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0503070740.1c11c37f@.posting.google.c om...
> Hi to everybody!
> I'm trying to make the Bulk Load of an XML file with some tables and I
> want to make SQL Server 2000 to assign a PK to a column determined in
> the XSD file (I expecto to find an option that allows me to see the
> key icon on a column when I click the Design Table option.)I've been
> searching in the MSDN library and I haven't found anything. If anybody
> had any suggerence it'd be very wellcomed.
>
> These are the XML and XSD file:
> <?xml version="1.0" encoding="utf-8" ?>
> <IND_UNICO schemaVersion="1">
> <DATOS NUM_DOC="3789">
> <NOTARIO_ID>7</NOTARIO_ID>
> <DOC NUM_OBJ="14" NUM_OPE="781" NUM_SUJ="587">
> <DOCUMENTO_ID>555</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>4052</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>44</INMUEBLE_ID>
> <INMUEBLE_TIPO>casa</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>37000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> <DATOS NUM_DOC="3791">
> <NOTARIO_ID>9</NOTARIO_ID>
> <DOC NUM_OBJ="17" NUM_OPE="784" NUM_SUJ="589">
> <DOCUMENTO_ID>666</DOCUMENTO_ID>
> <OPE>
> <OPERACION_ID>3036</OPERACION_ID>
> <INMUEBLE>
> <INMUEBLE_ID>99</INMUEBLE_ID>
> <INMUEBLE_TIPO>piso</INMUEBLE_TIPO>
> <INMUEBLE_VALOR>46000000</INMUEBLE_VALOR>
> </INMUEBLE>
> </OPE>
> </DOC>
> </DATOS>
> </IND_UNICO>
>
> And this is the XSD file:
> <?xml version="1.0" encoding="UTF-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
> version="2.1.3">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="INDICE_DATOS"
> parent="tblINDICE"
> parent-key="idIndice"
> child="tblDATOS"
> child-key="idIndice"
> />
> <sql:relationship
> name="DATOS_DOCUMENTOS"
> parent="tblDATOS"
> parent-key="idDatos"
> child="tblDOCUMENTOS"
> child-key="idDatos"
> />
> <sql:relationship
> name="DOCUMENTOS_OPERACIONES"
> parent="tblDOCUMENTOS"
> parent-key="idDocumento"
> child="tblOPERACIONES"
> child-key="idDocumento"
> />
> <sql:relationship
> name="OPERACIONES_INMUEBLES"
> parent="tblOPERACIONES"
> parent-key="idOperacion"
> child="tblINMUEBLES"
> child-key="idOperacion"
> />
> </xsd:appinfo>
> </xsd:annotation>
>
> <xsd:element name="IND_UNICO" sql:relation="tblINDICE">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DATOS" maxOccurs="unbounded"
> sql:relation="tblDATOS"
> sql:relationship="INDICE_DATOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DOC"
> sql:relation="tblDOCUMENTOS"
> sql:relationship="DATOS_DOCUMENTOS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="OPE"
> sql:relation="tblOPERACIONES"
> sql:relationship="DOCUMENTOS_OPERACIONES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE"
> sql:relation="tblINMUEBLES"
> sql:relationship="OPERACIONES_INMUEBLES">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INMUEBLE_ID"
> type="xsd:short"/>
> <xsd:element name="INMUEBLE_TIPO"
> type="xsd:string"/>
> <xsd:element name="INMUEBLE_VALOR"
> type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="OPERACION_ID"
> type="xsd:short"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="DOCUMENTO_ID"
> type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_OBJ" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_OPE" type="xsd:short"
> use="required"/>
> <xsd:attribute name="NUM_SUJ" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="NOTARIO_ID" type="xsd:short"/>
> </xsd:sequence>
> <xsd:attribute name="NUM_DOC" type="xsd:short" use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="schemaVersion" type="xsd:short"
> use="required"/>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> Greetings,
> David Grant
|||Thank you for answering, Darren.
I let the XML Bulk Load to create the tables from scratch
because if I create them on my own and I assign the primary
key to a field I begin to receive a lot of errors about
NULL values (MS SQL Server complains about not being able
to insert nulls on the PK/FK fields).
Actually this .XSD file creates 5 tables from scratch:
tblINDICE
tblDATOS
tblDOCUMENTOS
tblOPERACIONES
tblINMUEBLES
and the columns are filled with the right values except for
those which are supposed to become the PK/FK (all the
parent-key/ child-key fields)
These PK/FK fiels are those defined in the sql:relationship
statement which, as it can be seen, start with the prefix
id. Unfortunately, with this Schema, XML Bulk Load only
fills these parent-key and child-key fields with NULL
values instead of the proper indexes. I guess I am doing
something wrong but after reviewing all the info at the
MSDN library, can't discover the source of this issue.
Do you (or anybody else) know an instruction to set a field
either as a PK or a FK from the XSD file?
I'd also be interested in knowing a way to avoid the NULL
autofilling of these PK/FK fields.
Thank you from beforehand,
David Grant

>--Original Message--
>The table is not created by the bulk load tool is it? From
>memory it isn't so I would expect you to have created the
PK >in advance. I don't think a load should change the
structure, >just the data.
>
|||I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||For future reference, you can specify a type of "xsd:ID" for the key field
in the schema, and set the SGUseID property of the SqlXmlBulkLoad object to
True. If you've also set SchemaGen to True, this will create a table with a
primary key on the column you specified as an ID in the schema.
For example:
Schema (NewCatalog.xsd):
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Catalog" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Category" sql:relation="SpecialCategories">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CategoryName" type="xsd:string"
sql:field="CategoryName"
sql:datatype="nvarchar(15)" />
<xsd:element name="Description" type="xsd:string"
sql:field="Description" sql:datatype="ntext" />
<xsd:element name="Product" sql:relation="SpecialProducts">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="SpecialCategories"
parent-key="CategoryID"
child="SpecialProducts"
child-key="CategoryID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ProductName" type="xsd:string"
sql:field="ProductName"
sql:datatype="nvarchar(40)" />
<xsd:element name="UnitPrice" type="xsd:decimal"
sql:field="UnitPrice" sql:datatype="money"
/>
</xsd:sequence>

<xsd:attribute name="ProductID" type="xsd:ID"
sql:field="ProductID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>

<xsd:attribute name="CategoryID" type="xsd:ID"
sql:field="CategoryID" sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Data (Catalog.xml):
<?xml version="1.0" ?>
<Catalog>
<Category CategoryID="99">
<CategoryName>Scottish Foods</CategoryName>
<Description>Traditional food from Scotland</Description>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Category>
<Category CategoryID="100">
<CategoryName>Scottish Drinks</CategoryName>
<Description>Traditional drinks from Scotland</Description>
<Product ProductID="103">
<ProductName>Single Malt Whisky</ProductName>
<UnitPrice>100</UnitPrice>
</Product>
</Category>
</Catalog>
VB Script:
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
"PROVIDER=SQLOLEDB;SERVER=(local);DATABASE=Northwi nd;" & _
"INTEGRATED SECURITY=sspi;"
objBulkLoad.SchemaGen = True
objBulkLoad.SGUseID = True
objBulkLoad.Execute "NewCatalog.xsd", "Catalog.xml"
Set objBulkLoad = Nothing
MsgBox "Catalog imported into new tables"
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"David Grant" <anonymous@.discussions.microsoft.com> wrote in message
news:5aee01c523e8$69c6d3b0$a401280a@.phx.gbl...
I have solved my problem by editing the tables once the VBS
has been executed.
Anyway, thank you for your help.
Greetings,
David Grant
|||It works now with the modification you have proposed!
Thank you very much for your help, Graeme!
Greetings,
David Grant

>--Original Message--
>For future reference, you can specify a type of "xsd:ID"
for the key field
>in the schema, and set the SGUseID property of the
SqlXmlBulkLoad object to
>True. If you've also set SchemaGen to True, this will
create a table with a
>primary key on the column you specified as an ID in the
schema.
>Cheers,
>Graeme
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com

Monday, March 12, 2012

How can we list all the tables without any clustered index within a SQL Server 2005 database?

Hi ,All,

Would please anyone here give me any advice and guidance for how to list all the tables without a clustered key within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Switch to the database you want to analyse and run this script:

select

sys.schemas.nameas [Schema],

sys.tables.nameas [Table]

from sys.tables

leftouterjoinsys.schemasonsys.schemas.schema_id =sys.tables.schema_id

where sys.tables.object_id not in

(

select

sys.indexes.object_id

fromsys.indexes

wheresys.indexes.type= 1

)

|||

Hi, Daniel, thanks a lot.

With best regards,

|||

Take a look at

http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ1

It answers exactly the question you have asked

|||

Hi, Asvin, thanks a lot for the link. It's very helpful.

With best regards,

Yours sincerely,

How can we list all the tables without any clustered index within a SQL Server 2005 database

Hi ,All,

Would please anyone here give me any advice and guidance for how to list all the tables without a clustered key within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Switch to the database you want to analyse and run this script:

select

sys.schemas.name as [Schema],

sys.tables.name as [Table]

from sys.tables

left outer join sys.schemas on sys.schemas.schema_id = sys.tables.schema_id

where sys.tables.object_id not in

(

select

sys.indexes.object_id

from sys.indexes

where sys.indexes.type = 1

)

|||

Hi, Daniel, thanks a lot.

With best regards,

|||

Take a look at

http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ1

It answers exactly the question you have asked

|||

Hi, Asvin, thanks a lot for the link. It's very helpful.

With best regards,

Yours sincerely,

How can we INFORMATION_SCHEMA on a different server?

We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.This is a multi-part message in MIME format.
--010400070406000708090003
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
What OLE DB provider is "Darmstadtium"? What kind of server is server
B? Microsoft SQL Server? If so, what version of SQL Server? Can you
query any object on the remote server? (Every login to a SQL server
should be able to read master.dbo.sysobjects - can you successfully
execute "SELECT * FROM B.master.dbo.sysobjects"?)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Hamish Laws wrote:
>We've working on a system that is being used to populate various tables in
>databases on multiple servers.
>If the application is on Server 'A' and is trying to find information about
>stored procedures on Server 'B' we're getting problems with the following
>query
>SELECT LTRIM(RTRIM(routine_name)) as Save_Query
>FROM [B].[dbName].INFORMATION_SCHEMA.routines
>ORDER BY routine_name
>
>the error message is
>OLE DB provider 'Darmstadtium' does not contain table
>'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
>or the current user does not have permissions on that table.
>Does anybody have any ideas for getting this working?
>There is a logon for Server B with the same logon that Server A is using and
>they have System Administrator as server roles.
>
--010400070406000708090003
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>What OLE DB provider is "Darmstadtium</tt><tt>"?Â What kind of
server is server B?Â Microsoft SQL Server?Â If so, what version of SQL
Server?Â Can you query any object on the remote server?Â (Every login
to a SQL server should be able to read master.dbo.sysobjects - can you
successfully execute "SELECT * FROM B.master.dbo.sysobjects"?)</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Hamish Laws wrote:
<blockquote cite="midA2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com"
type="cite">
<pre wrap="">We've working on a system that is being used to populate various tables in
databases on multiple servers.
If the application is on Server 'A' and is trying to find information about
stored procedures on Server 'B' we're getting problems with the following
query
SELECT LTRIM(RTRIM(routine_name)) as Save_Query
FROM [B].[dbName].INFORMATION_SCHEMA.routines
ORDER BY routine_name
the error message is
OLE DB provider 'Darmstadtium' does not contain table
'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
or the current user does not have permissions on that table.
Does anybody have any ideas for getting this working?
There is a logon for Server B with the same logon that Server A is using and
they have System Administrator as server roles.
</pre>
</blockquote>
</body>
</html>
--010400070406000708090003--|||I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
the system tables instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> We've working on a system that is being used to populate various tables in
> databases on multiple servers.
> If the application is on Server 'A' and is trying to find information about
> stored procedures on Server 'B' we're getting problems with the following
> query
> SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> FROM [B].[dbName].INFORMATION_SCHEMA.routines
> ORDER BY routine_name
>
> the error message is
> OLE DB provider 'Darmstadtium' does not contain table
> '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> or the current user does not have permissions on that table.
> Does anybody have any ideas for getting this working?
> There is a logon for Server B with the same logon that Server A is using and
> they have System Administrator as server roles.|||"Mike Hodgson" wrote:
> What OLE DB provider is "Darmstadtium"?
Sorry, bad editing on my part.
Darmstadtium is the actual name of the server I called 'B'
> What kind of server is server
> B? Microsoft SQL Server? If so, what version of SQL Server?
2000
> Can you
> query any object on the remote server? (Every login to a SQL server
> should be able to read master.dbo.sysobjects - can you successfully
> execute "SELECT * FROM B.master.dbo.sysobjects"?)
>
Yep, I can query other objects on the server.
If I connect to the second server using query analyzer and run the query
direct it works fine so it looks to me like the view isn't available as part
of the connection on a remote server
I took Tibor Karaszi's advice and rewrote it to use sysobjects.
Not as elegant but I'm getting the details I need out.
> Hamish Laws wrote:
> >We've working on a system that is being used to populate various tables in
> >databases on multiple servers.
> >
> >If the application is on Server 'A' and is trying to find information about
> >stored procedures on Server 'B' we're getting problems with the following
> >query
> >
> >SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> >FROM [B].[dbName].INFORMATION_SCHEMA.routines
> >ORDER BY routine_name
> >
> >
> >the error message is
> >OLE DB provider 'Darmstadtium' does not contain table
> >'"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> >or the current user does not have permissions on that table.
> >
> >Does anybody have any ideas for getting this working?
> >There is a logon for Server B with the same logon that Server A is using and
> >they have System Administrator as server roles|||"Tibor Karaszi" wrote:
> I think you will have a problem with the INFORMATION_SCHEMA views. The views actually only exists in
> the master database (in 2000, in 7.0 and 2005 they are in each database), hence your problem. Try
> the system tables instead.
>
Thanks for that.
I've taken your advice and I'm getting the information out of sysobjects
without a hassle.
> "Hamish Laws" <HamishLaws@.discussions.microsoft.com> wrote in message
> news:A2CE4BDB-284D-46E5-B27D-1E9237553167@.microsoft.com...
> > We've working on a system that is being used to populate various tables in
> > databases on multiple servers.
> >
> > If the application is on Server 'A' and is trying to find information about
> > stored procedures on Server 'B' we're getting problems with the following
> > query
> >
> > SELECT LTRIM(RTRIM(routine_name)) as Save_Query
> > FROM [B].[dbName].INFORMATION_SCHEMA.routines
> > ORDER BY routine_name
> >
> >
> > the error message is
> > OLE DB provider 'Darmstadtium' does not contain table
> > '"dbName"."INFORMATION_SCHEMA"."routines"'. The table either does not exist
> > or the current user does not have permissions on that table.
> >
> > Does anybody have any ideas for getting this working?
> > There is a logon for Server B with the same logon that Server A is using and
> > they have System Administrator as server roles.
>
>

Friday, March 30, 2012

how do I alter the column with index on it

Wednesday, March 28, 2012

How do I add a new column to an existing Data Source View in SSRS?

Monday, March 26, 2012

How disable IDENTITY on column

How disable IDENTITY on column

how create mining model

Friday, March 23, 2012

how control to Tempdb Growth

Monday, March 19, 2012

How can you use a variable tablename and retrieve the output from the Insert?

How can you stop and cycle through a column(s) of data?

How can you prevent the data changes message during editing a row

How can XML Bulk Load assign automatically a PK to a column?

How can XML Bulk Load assign automatically a PK to a column?

Monday, March 12, 2012

How can we list all the tables without any clustered index within a SQL Server 2005 database?

How can we list all the tables without any clustered index within a SQL Server 2005 database

How can we INFORMATION_SCHEMA on a different server?

How can we INFORMATION_SCHEMA on a different server?

How can we INFORMATION_SCHEMA on a different server?

transfer Data From Acces

Blog Archive

About Me