Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.
> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.
|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................
|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Tuesday, March 27, 2012
Can we declare a Table in a FUNCTION.................?
Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.sql
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.sql
Can we declare a Table in a FUNCTION.................?
Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.
Sunday, March 11, 2012
Can SQLServer produce Excel Spreadsheet output ?
Deaa group,
I am using SQLServer 2000 in an XP Sp2. I would like to do the
following:
I have a program running on a database server that generates some data
which are loaded to the database. This program is used in a web
application, invoked by some java program and JSP scripts. (I am
frontend illiterated.)
The question is, is it possible to write a stored procedure to generate
output in excel spreadsheet? So that user could call this procedure
and get spreadsheet output on the client side.
Any pointer to a solution would be immensely apprecaited.
thanks,
charia<cpeters5@.gmail.com> wrote in message
news:1120580708.814110.191080@.z14g2000cwz.googlegr oups.com...
> Deaa group,
> I am using SQLServer 2000 in an XP Sp2. I would like to do the
> following:
> I have a program running on a database server that generates some data
> which are loaded to the database. This program is used in a web
> application, invoked by some java program and JSP scripts. (I am
> frontend illiterated.)
> The question is, is it possible to write a stored procedure to generate
> output in excel spreadsheet? So that user could call this procedure
> and get spreadsheet output on the client side.
> Any pointer to a solution would be immensely apprecaited.
> thanks,
> charia
As far as I know, there's no direct way to export to an .xls from a stored
proc. DTS can export data to Excel, and you can execute a package from a
stored proc in various ways:
http://www.sqldts.com/default.aspx?210
By using ActiveX steps in a DTS package, you could control all the details
of the .xls file name, structure, column headers etc. via the Excel COM
interface, but you would need to actually install Excel on the server in
order to do that, which may not be possible (or desirable).
Another option would be calling bcp.exe via xp_cmdshell to create a CSV or
tab-delimited file. In the end, the easiest solution might be to find a Java
or JSP module of some sort which can export to Excel - then you just return
the result set to the client or middle tier as usual, and let it create the
file, which is probably a cleaner solution than dealing with presentation in
the database itself.
Simon|||i know ASP can generate an xls from data selected by a SP. i bet there
is some way JSP can do it as well, i'm just not a web developer =P
Thursday, March 8, 2012
Can SQL Server 2005 SP2 applies to Express ?
Can I apply the SP2 to SQL Server 2005 Express or I have to download the SQL
Server 2005 Express SP2 Edition ?
Besides, I would like to know whether SQL Server 2005 Express SP2 Edition is
a full installation of Express (i.e. I can set up SQL Server 2005 Express in
a workstation) or it is only a SP ?
Thanks
PeterHi Peter
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uBCx$35YHHA.688@.TK2MSFTNGP03.phx.gbl...
> Can I apply the SP2 to SQL Server 2005 Express or I have to download the
> SQL Server 2005 Express SP2 Edition ?
> Besides, I would like to know whether SQL Server 2005 Express SP2 Edition
> is a full installation of Express (i.e. I can set up SQL Server 2005
> Express in a workstation) or it is only a SP ?
> Thanks
> Peter
Download SQL Express with SP2 from
http://msdn.microsoft.com/vstudio/e...er/default.aspx and
install it over your current installation. The installation will upgrade
your existing installation or install a new copy of express if you choose to
do so. The difference between your existing express installation and Express
with SP2 would be the SP2 changes
John
Server 2005 Express SP2 Edition ?
Besides, I would like to know whether SQL Server 2005 Express SP2 Edition is
a full installation of Express (i.e. I can set up SQL Server 2005 Express in
a workstation) or it is only a SP ?
Thanks
PeterHi Peter
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uBCx$35YHHA.688@.TK2MSFTNGP03.phx.gbl...
> Can I apply the SP2 to SQL Server 2005 Express or I have to download the
> SQL Server 2005 Express SP2 Edition ?
> Besides, I would like to know whether SQL Server 2005 Express SP2 Edition
> is a full installation of Express (i.e. I can set up SQL Server 2005
> Express in a workstation) or it is only a SP ?
> Thanks
> Peter
Download SQL Express with SP2 from
http://msdn.microsoft.com/vstudio/e...er/default.aspx and
install it over your current installation. The installation will upgrade
your existing installation or install a new copy of express if you choose to
do so. The difference between your existing express installation and Express
with SP2 would be the SP2 changes
John
Thursday, February 16, 2012
Can replication handle existing database?
Hello:
I have the following situatioin. A few months ago, I setup a secondary SQL
server with the same schema and data as the primary. But that server was
shut down for a few months.
Currently, I am thinking to set up replication (transaction). Since the
database is rather huge, I think I will try transaction replication. Do you
think it will work so that the secondary server will be in sync with the
primary server? This is the first time I am doing it.
Thanks,
Q
If the data is now out of sync, this'll need attending to first. You could
use Redgate's DataCompare to synchronize the subscriber with the publisher
then after that do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have the following situatioin. A few months ago, I setup a secondary SQL
server with the same schema and data as the primary. But that server was
shut down for a few months.
Currently, I am thinking to set up replication (transaction). Since the
database is rather huge, I think I will try transaction replication. Do you
think it will work so that the secondary server will be in sync with the
primary server? This is the first time I am doing it.
Thanks,
Q
If the data is now out of sync, this'll need attending to first. You could
use Redgate's DataCompare to synchronize the subscriber with the publisher
then after that do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Tuesday, February 14, 2012
can objects in sql server change your nt login password
hi guys,
i was wondering if there is a bug in sql server 2000 that upon changing settings in the sqlserver agent or sqlserver services that it will actually change your nt user password.
a strange occurence happened to me i switched the sqlmail to a different outlook profile that is MAPI compliant i had to stop and start the sql server agent upon restart it said it could not log me in b/c my nt logon failed (i guess you can figure out by now that i have authentication as NT in my set up of sql server agent). well me knowing microsoft products i was like ah i will just reboot the machine) well upon reboot my administrator password which is empty "null value"no longer is valid.
now noone in the company said that they changed the administrator password and it is not logged into a domain so the machine is not on our companys network. we log in local to the machine as the administrator. the last things that i played around in with sql server was replication setting it up as a publisher and deleteing it and changing a mapi profile. is there anywhere in sql 2000 that messing with any setting that it will actually affect your windows nt user login?? i know i cant be loosing my mind here and i hope someone at my company wouldnt sabotage my machine. right now i have two options i can reinstall win 2000 and choose repair hoping that i will not lose my sql 2000 info or i can try a password cracker. any suggestions that might of cause this to happen?
regards,
Robertof course Microsoft is always providing use with new features, but I doubt this unlikely. What you described sounds exactly as if the user password had been changed at the OS level. The password could have been changed by mistake.
i was wondering if there is a bug in sql server 2000 that upon changing settings in the sqlserver agent or sqlserver services that it will actually change your nt user password.
a strange occurence happened to me i switched the sqlmail to a different outlook profile that is MAPI compliant i had to stop and start the sql server agent upon restart it said it could not log me in b/c my nt logon failed (i guess you can figure out by now that i have authentication as NT in my set up of sql server agent). well me knowing microsoft products i was like ah i will just reboot the machine) well upon reboot my administrator password which is empty "null value"no longer is valid.
now noone in the company said that they changed the administrator password and it is not logged into a domain so the machine is not on our companys network. we log in local to the machine as the administrator. the last things that i played around in with sql server was replication setting it up as a publisher and deleteing it and changing a mapi profile. is there anywhere in sql 2000 that messing with any setting that it will actually affect your windows nt user login?? i know i cant be loosing my mind here and i hope someone at my company wouldnt sabotage my machine. right now i have two options i can reinstall win 2000 and choose repair hoping that i will not lose my sql 2000 info or i can try a password cracker. any suggestions that might of cause this to happen?
regards,
Robertof course Microsoft is always providing use with new features, but I doubt this unlikely. What you described sounds exactly as if the user password had been changed at the OS level. The password could have been changed by mistake.
can not view DTS Packages and Jobs
I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
RK
Yes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK
|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>
>
|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...[vbcol=seagreen]
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
RK
Yes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK
|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>
>
|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...[vbcol=seagreen]
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
can not view DTS Packages and Jobs
I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, b
ut
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them
as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package,
as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...[vbcol=seagreen]
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, b
ut
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them
as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package,
as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...[vbcol=seagreen]
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>
can not view DTS Packages and Jobs
I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
> >I have SQL 2k SP4 on Windows 2003
> > Can A sqlserver user, with out having SA role, see the DTS packages, Job
> > and
> > their status ?
> > --
> > RK
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>> Yes, partially, through some workarounds.
>> If you make a SQL Server user a member of the msdb database
>> TargetServersRole, they will be able to see all jobs and their statuses,
>> but
>> will not be able to create or modify jobs. (So this does not work for
>> someone who should be able to create his own jobs. This is an
>> undocumented
>> sideeffect of the role and will not work in SQL Server 2005. But 2005
>> has
>> specific new roles for granting various degrees of access to SQL Agent
>> jobs.)
>> If, instead of storing DTS packages as SQL Server objects, you store them
>> as
>> files on a file share, then anyone who has rights to the file share
>> (read/only if you want that) can examine the contents of the DTS package,
>> as
>> well. (If there is a workaround for examining DTS packages stored on the
>> server, I don't know it.)
>> RLF
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>> >I have SQL 2k SP4 on Windows 2003
>> > Can A sqlserver user, with out having SA role, see the DTS packages,
>> > Job
>> > and
>> > their status ?
>> > --
>> > RK
>>
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
> >I have SQL 2k SP4 on Windows 2003
> > Can A sqlserver user, with out having SA role, see the DTS packages, Job
> > and
> > their status ?
> > --
> > RK
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>> Yes, partially, through some workarounds.
>> If you make a SQL Server user a member of the msdb database
>> TargetServersRole, they will be able to see all jobs and their statuses,
>> but
>> will not be able to create or modify jobs. (So this does not work for
>> someone who should be able to create his own jobs. This is an
>> undocumented
>> sideeffect of the role and will not work in SQL Server 2005. But 2005
>> has
>> specific new roles for granting various degrees of access to SQL Agent
>> jobs.)
>> If, instead of storing DTS packages as SQL Server objects, you store them
>> as
>> files on a file share, then anyone who has rights to the file share
>> (read/only if you want that) can examine the contents of the DTS package,
>> as
>> well. (If there is a workaround for examining DTS packages stored on the
>> server, I don't know it.)
>> RLF
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>> >I have SQL 2k SP4 on Windows 2003
>> > Can A sqlserver user, with out having SA role, see the DTS packages,
>> > Job
>> > and
>> > their status ?
>> > --
>> > RK
>>
Sunday, February 12, 2012
Can not save relationship
Hi
I am using Sqlserver 2005 and in management I created some relations.
One of them fale with the below error.
I have tried to look for unmatched data and dublicates etc, but still keep
gettitng this error message.
What do you recommend me to do?
Thank you in advance
- Unable to create relationship 'FK_Transaktionsrader_Transaktionhuvud'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"FK_Transaktionsrader_Transaktionhuvud".
The conflict occurred in database "MbaseMuseumServerNetSQL", table
"dbo.Transaktionhuvud", column 'TransaktionhuvudTransaktionsnr'.
It would help us better assist you if you could include table DDL for both
tables; without it, we cannot help you. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general...-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:3EAD7DFC-C005-4415-A17F-5FD29EF8D4A2@.microsoft.com...
> Hi
> I am using Sqlserver 2005 and in management I created some relations.
> One of them fale with the below error.
> I have tried to look for unmatched data and dublicates etc, but still keep
> gettitng this error message.
> What do you recommend me to do?
> Thank you in advance
> - Unable to create relationship 'FK_Transaktionsrader_Transaktionhuvud'.
> The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
> "FK_Transaktionsrader_Transaktionhuvud".
> The conflict occurred in database "MbaseMuseumServerNetSQL", table
> "dbo.Transaktionhuvud", column 'TransaktionhuvudTransaktionsnr'.
|||Hi and thanks for your reply.
I have the DDL for both tables ready here.
Can it be attached to the post or shall I send it separatly somewhere?
I tried to use sql code you recommend to generate inserts, sample data but I
receive the error below
"Could not find stored procedure sp_generate_inserts'." What do you
recommend me to do?
Mattias
"Arnie Rowland" wrote:
> It would help us better assist you if you could include table DDL for both
> tables; without it, we cannot help you. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general...-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:3EAD7DFC-C005-4415-A17F-5FD29EF8D4A2@.microsoft.com...
>
>
|||Please include (copy and paste) the DDL in a post.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:14FF1450-A7AB-4D25-BE5C-4D75B50845F7@.microsoft.com...[vbcol=seagreen]
> Hi and thanks for your reply.
> I have the DDL for both tables ready here.
> Can it be attached to the post or shall I send it separatly somewhere?
> I tried to use sql code you recommend to generate inserts, sample data but
> I
> receive the error below
> "Could not find stored procedure sp_generate_inserts'." What do you
> recommend me to do?
> Mattias
>
> "Arnie Rowland" wrote:
|||Ok here it comes!
Mattias
USE [MbaseMuseumServerNetSQL]
GO
/****** Objekt: Table [dbo].[Transaktionhuvud] Skriptdatum: 10/09/2006
16:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaktionhuvud](
[TransaktionhuvudTransaktionsnr] [int] IDENTITY(1,1) NOT NULL,
[Transaktionskategorinummer] [int] NULL,
[TransaktionhuvudTransaktionhuvudKontaktnr] [int] NULL,
[ForetagsregisterGRUNDNR] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldHandlagge snr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldFramtages nr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldAvsynasnr ] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldPackas nr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldKurirn r] [int] NULL,
[UtskriftsstatusSTATUSNR] [int] NULL,
[BetalningsStatusSTATUSNR] [int] NULL,
[TransaktionhuvudRegistreringsdatum] [datetime] NULL,
[DatumPreliminarRetur] [datetime] NULL,
[UtstallningStartdatum] [datetime] NULL,
[UtstallningSlutdatum] [datetime] NULL,
[Utstallning] [nvarchar](70) COLLATE Finnish_Swedish_CI_AS NULL,
[TransaktionhuvudTransaktionhuvudKontaktTransporto rAvhamtningnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudKontaktTransporto rReturnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudTransportsattAvha mtningnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudTransportsattRetu rnr] [int] NULL,
[TransaktionhuvudDefinitivRetur] [bit] NULL,
[TransaktionhuvudDatumDefinitivRetur] [datetime] NULL,
[TransaktionhuvudAnmarkningar] [nvarchar](max) COLLATE
Finnish_Swedish_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TransaktionhuvudTransaktionsnr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudKontaktTransp ortorAvhamtningnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud1] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudKontaktTransp ortorReturnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud1]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud10] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldAvsyn asnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud10]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud11] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldPacka snr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud11]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud12] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldKurir nr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud12]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud13] FOREIGN KEY([TransaktionhuvudTransaktionhuvudKontaktnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud13]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud2] FOREIGN KEY([BetalningsStatusSTATUSNR])
REFERENCES [dbo].[BetalningsStatus] ([BetalningsStatusSTATUSNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud2]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud3] FOREIGN KEY([UtskriftsstatusSTATUSNR])
REFERENCES [dbo].[Utskriftsstatus] ([UtskriftsstatusSTATUSNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud3]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud4] FOREIGN KEY([ForetagsregisterGRUNDNR])
REFERENCES [dbo].[Foretagsregister] ([ForetagsregisterGRUNDNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud4]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud5] FOREIGN KEY([Transaktionskategorinummer])
REFERENCES [dbo].[Transaktionskategori] ([Transaktionskategorinummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud5]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud6] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudTransportsatt Avhamtningnr])
REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud6]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud7] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudTransportsatt Returnr])
REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud7]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud8] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldHandl aggesnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud8]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud9] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldFramt agesnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud9]
USE [MbaseMuseumServerNetSQL]
GO
/****** Objekt: Table [dbo].[Transaktionsrader] Skriptdatum: 10/09/2006
16:17:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaktionsrader](
[TransaktionsraderTransaktionsraderTransaktionsrad ] [int] IDENTITY(1,1) NOT
NULL,
[TransaktionsraderTransaktionsnr] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr2] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr3] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr4] [int] NULL,
[TransaktionsraderTransaktionsraderEmballagenr] [int] NULL,
[Artikelnummer] [int] NULL,
[Bildnr] [int] NULL,
[TransaktionsraderForetagsnr] [int] NULL,
[TransaktionsraderAntal] [int] NULL,
[ExaktPlacering] [nvarchar](200) COLLATE Finnish_Swedish_CI_AS NULL,
[TillfalligRetur] [bit] NOT NULL,
[DatumTillfalligRetur] [datetime] NULL,
[TransaktionsraderForsakringsvarde] [decimal](17, 6) NULL,
[TransaktionsraderDefinitivRetur] [bit] NOT NULL,
[TransaktionsraderDatumDefinitivRetur] [datetime] NULL,
[AterDeposition] [bit] NOT NULL,
[DatumHamtningUtlamning] [datetime] NOT NULL,
[Undervisningstypnr] [int] NULL,
[DatumVisningStart] [datetime] NULL,
[TidVisningStart] [datetime] NULL,
[DatumVisningSlut] [datetime] NULL,
[TidVisningSlut] [datetime] NULL,
[Amne] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[AntalLektioner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[AntalPersoner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[VisningInstalld] [bit] NULL,
[DatumVisningInstalld] [datetime] NULL,
[VisningUtford] [bit] NULL,
[transkontaktnr] [int] NULL,
[TransraderAnstalldGuideAnstalldnr] [int] NULL,
[accessionsnummer] [nvarchar](20) COLLATE Finnish_Swedish_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TransaktionsraderTransaktionsraderTransaktionsrad ] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[FK_Transaktionsrader_Master] FOREIGN KEY([accessionsnummer])
REFERENCES [dbo].[Master] ([MasterAccessionsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT
[FK_Transaktionsrader_Master]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader] FOREIGN KEY([Artikelnummer])
REFERENCES [dbo].[Artiklar] ([Artikelnummer])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader10] FOREIGN KEY([transkontaktnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader10]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader2] FOREIGN
KEY([TransaktionsraderTransaktionsraderEmballagenr ])
REFERENCES [dbo].[Emballage] ([Emballagenr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader2]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader3] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader3]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader4] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr2])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader4]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader5] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr3])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader5]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader6] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr4])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader6]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader7] FOREIGN KEY([Undervisningstypnr])
REFERENCES [dbo].[UNDERVISNINGSTYP] ([Undervisningstypnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader7]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader8] FOREIGN KEY([Bildnr])
REFERENCES [dbo].[Bilder] ([Bildnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader8]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader9] FOREIGN KEY([TransraderAnstalldGuideAnstalldnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader9]
"Arnie Rowland" wrote:
> Please include (copy and paste) the DDL in a post.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:14FF1450-A7AB-4D25-BE5C-4D75B50845F7@.microsoft.com...
>
>
|||Mattias,
Try this. Use your script and create the two tables on
another test server or some test database.
Then run the script to create the relationship you are
having problems creating - or use Management Studio if
that's how you were doing it.
If you can create the relationship and don't get an error
then that tells you it's likely related to the data in the
two tables violating the constraint. If you can't create the
relationship from Management Studio, hit the script button
(hopefully there is one in the GUI end...don't use that much
so I don't remember) and try just executing the script. If
you still get an error using the script and with no data,
then post the script here.
-Sue
On Mon, 9 Oct 2006 07:20:02 -0700, Mattias
<Mattias@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Ok here it comes!
>Mattias
>USE [MbaseMuseumServerNetSQL]
>GO
>/****** Objekt: Table [dbo].[Transaktionhuvud] Skriptdatum: 10/09/2006
>16:13:57 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[Transaktionhuvud](
>[TransaktionhuvudTransaktionsnr] [int] IDENTITY(1,1) NOT NULL,
>[Transaktionskategorinummer] [int] NULL,
>[TransaktionhuvudTransaktionhuvudKontaktnr] [int] NULL,
>[ForetagsregisterGRUNDNR] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldHandlagge snr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldFramtages nr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldAvsynasnr ] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldPackas nr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldKurirn r] [int] NULL,
>[UtskriftsstatusSTATUSNR] [int] NULL,
>[BetalningsStatusSTATUSNR] [int] NULL,
>[TransaktionhuvudRegistreringsdatum] [datetime] NULL,
>[DatumPreliminarRetur] [datetime] NULL,
>[UtstallningStartdatum] [datetime] NULL,
>[UtstallningSlutdatum] [datetime] NULL,
>[Utstallning] [nvarchar](70) COLLATE Finnish_Swedish_CI_AS NULL,
>[TransaktionhuvudTransaktionhuvudKontaktTransporto rAvhamtningnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudKontaktTransporto rReturnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudTransportsattAvha mtningnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudTransportsattRetu rnr] [int] NULL,
>[TransaktionhuvudDefinitivRetur] [bit] NULL,
>[TransaktionhuvudDatumDefinitivRetur] [datetime] NULL,
>[TransaktionhuvudAnmarkningar] [nvarchar](max) COLLATE
>Finnish_Swedish_CI_AS NULL,
>PRIMARY KEY CLUSTERED
>(
>[TransaktionhuvudTransaktionsnr] ASC
>)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudKontaktTrans portorAvhamtningnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud1] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudKontaktTrans portorReturnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud1]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud10] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldAvsy nasnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud10]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud11] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldPack asnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud11]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud12] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldKuri rnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud12]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud13] FOREIGN KEY([TransaktionhuvudTransaktionhuvudKontaktnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud13]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud2] FOREIGN KEY([BetalningsStatusSTATUSNR])
>REFERENCES [dbo].[BetalningsStatus] ([BetalningsStatusSTATUSNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud2]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud3] FOREIGN KEY([UtskriftsstatusSTATUSNR])
>REFERENCES [dbo].[Utskriftsstatus] ([UtskriftsstatusSTATUSNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud3]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud4] FOREIGN KEY([ForetagsregisterGRUNDNR])
>REFERENCES [dbo].[Foretagsregister] ([ForetagsregisterGRUNDNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud4]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud5] FOREIGN KEY([Transaktionskategorinummer])
>REFERENCES [dbo].[Transaktionskategori] ([Transaktionskategorinummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud5]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud6] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudTransportsat tAvhamtningnr])
>REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud6]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud7] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudTransportsat tReturnr])
>REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud7]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud8] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldHand laggesnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud8]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud9] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldFram tagesnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud9]
>
>USE [MbaseMuseumServerNetSQL]
>GO
>/****** Objekt: Table [dbo].[Transaktionsrader] Skriptdatum: 10/09/2006
>16:17:38 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[Transaktionsrader](
>[TransaktionsraderTransaktionsraderTransaktionsrad ] [int] IDENTITY(1,1) NOT
>NULL,
>[TransaktionsraderTransaktionsnr] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr2] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr3] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr4] [int] NULL,
>[TransaktionsraderTransaktionsraderEmballagenr] [int] NULL,
>[Artikelnummer] [int] NULL,
>[Bildnr] [int] NULL,
>[TransaktionsraderForetagsnr] [int] NULL,
>[TransaktionsraderAntal] [int] NULL,
>[ExaktPlacering] [nvarchar](200) COLLATE Finnish_Swedish_CI_AS NULL,
>[TillfalligRetur] [bit] NOT NULL,
>[DatumTillfalligRetur] [datetime] NULL,
>[TransaktionsraderForsakringsvarde] [decimal](17, 6) NULL,
>[TransaktionsraderDefinitivRetur] [bit] NOT NULL,
>[TransaktionsraderDatumDefinitivRetur] [datetime] NULL,
>[AterDeposition] [bit] NOT NULL,
>[DatumHamtningUtlamning] [datetime] NOT NULL,
>[Undervisningstypnr] [int] NULL,
>[DatumVisningStart] [datetime] NULL,
>[TidVisningStart] [datetime] NULL,
>[DatumVisningSlut] [datetime] NULL,
>[TidVisningSlut] [datetime] NULL,
>[Amne] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[AntalLektioner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[AntalPersoner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[VisningInstalld] [bit] NULL,
>[DatumVisningInstalld] [datetime] NULL,
>[VisningUtford] [bit] NULL,
>[transkontaktnr] [int] NULL,
>[TransraderAnstalldGuideAnstalldnr] [int] NULL,
>[accessionsnummer] [nvarchar](20) COLLATE Finnish_Swedish_CI_AS NULL,
>PRIMARY KEY CLUSTERED
>(
>[TransaktionsraderTransaktionsraderTransaktionsrad ] ASC
>)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[FK_Transaktionsrader_Master] FOREIGN KEY([accessionsnummer])
>REFERENCES [dbo].[Master] ([MasterAccessionsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT
>[FK_Transaktionsrader_Master]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader] FOREIGN KEY([Artikelnummer])
>REFERENCES [dbo].[Artiklar] ([Artikelnummer])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader10] FOREIGN KEY([transkontaktnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader10]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader2] FOREIGN
>KEY([TransaktionsraderTransaktionsraderEmballagen r])
>REFERENCES [dbo].[Emballage] ([Emballagenr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader2]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader3] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsn r])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader3]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader4] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr2 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader4]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader5] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr3 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader5]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader6] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr4 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader6]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader7] FOREIGN KEY([Undervisningstypnr])
>REFERENCES [dbo].[UNDERVISNINGSTYP] ([Undervisningstypnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader7]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader8] FOREIGN KEY([Bildnr])
>REFERENCES [dbo].[Bilder] ([Bildnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader8]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader9] FOREIGN KEY([TransraderAnstalldGuideAnstalldnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader9]
>"Arnie Rowland" wrote:
I am using Sqlserver 2005 and in management I created some relations.
One of them fale with the below error.
I have tried to look for unmatched data and dublicates etc, but still keep
gettitng this error message.
What do you recommend me to do?
Thank you in advance
- Unable to create relationship 'FK_Transaktionsrader_Transaktionhuvud'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"FK_Transaktionsrader_Transaktionhuvud".
The conflict occurred in database "MbaseMuseumServerNetSQL", table
"dbo.Transaktionhuvud", column 'TransaktionhuvudTransaktionsnr'.
It would help us better assist you if you could include table DDL for both
tables; without it, we cannot help you. (For help with that refer to:
http://www.aspfaq.com/5006 and to
http://classicasp.aspfaq.com/general...-answered.html )
The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:3EAD7DFC-C005-4415-A17F-5FD29EF8D4A2@.microsoft.com...
> Hi
> I am using Sqlserver 2005 and in management I created some relations.
> One of them fale with the below error.
> I have tried to look for unmatched data and dublicates etc, but still keep
> gettitng this error message.
> What do you recommend me to do?
> Thank you in advance
> - Unable to create relationship 'FK_Transaktionsrader_Transaktionhuvud'.
> The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
> "FK_Transaktionsrader_Transaktionhuvud".
> The conflict occurred in database "MbaseMuseumServerNetSQL", table
> "dbo.Transaktionhuvud", column 'TransaktionhuvudTransaktionsnr'.
|||Hi and thanks for your reply.
I have the DDL for both tables ready here.
Can it be attached to the post or shall I send it separatly somewhere?
I tried to use sql code you recommend to generate inserts, sample data but I
receive the error below
"Could not find stored procedure sp_generate_inserts'." What do you
recommend me to do?
Mattias
"Arnie Rowland" wrote:
> It would help us better assist you if you could include table DDL for both
> tables; without it, we cannot help you. (For help with that refer to:
> http://www.aspfaq.com/5006 and to
> http://classicasp.aspfaq.com/general...-answered.html )
>
> The less 'set up' work we have to do, the more likely you are going to have
> folks tackle your problem and help you. Without this effort from you, we are
> just playing guessing games.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:3EAD7DFC-C005-4415-A17F-5FD29EF8D4A2@.microsoft.com...
>
>
|||Please include (copy and paste) the DDL in a post.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mattias" <Mattias@.discussions.microsoft.com> wrote in message
news:14FF1450-A7AB-4D25-BE5C-4D75B50845F7@.microsoft.com...[vbcol=seagreen]
> Hi and thanks for your reply.
> I have the DDL for both tables ready here.
> Can it be attached to the post or shall I send it separatly somewhere?
> I tried to use sql code you recommend to generate inserts, sample data but
> I
> receive the error below
> "Could not find stored procedure sp_generate_inserts'." What do you
> recommend me to do?
> Mattias
>
> "Arnie Rowland" wrote:
|||Ok here it comes!
Mattias
USE [MbaseMuseumServerNetSQL]
GO
/****** Objekt: Table [dbo].[Transaktionhuvud] Skriptdatum: 10/09/2006
16:13:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaktionhuvud](
[TransaktionhuvudTransaktionsnr] [int] IDENTITY(1,1) NOT NULL,
[Transaktionskategorinummer] [int] NULL,
[TransaktionhuvudTransaktionhuvudKontaktnr] [int] NULL,
[ForetagsregisterGRUNDNR] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldHandlagge snr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldFramtages nr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldAvsynasnr ] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldPackas nr] [int] NULL,
[TransaktionhuvudTransaktionhuvudAnstalldKurirn r] [int] NULL,
[UtskriftsstatusSTATUSNR] [int] NULL,
[BetalningsStatusSTATUSNR] [int] NULL,
[TransaktionhuvudRegistreringsdatum] [datetime] NULL,
[DatumPreliminarRetur] [datetime] NULL,
[UtstallningStartdatum] [datetime] NULL,
[UtstallningSlutdatum] [datetime] NULL,
[Utstallning] [nvarchar](70) COLLATE Finnish_Swedish_CI_AS NULL,
[TransaktionhuvudTransaktionhuvudKontaktTransporto rAvhamtningnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudKontaktTransporto rReturnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudTransportsattAvha mtningnr] [int] NULL,
[TransaktionhuvudTransaktionhuvudTransportsattRetu rnr] [int] NULL,
[TransaktionhuvudDefinitivRetur] [bit] NULL,
[TransaktionhuvudDatumDefinitivRetur] [datetime] NULL,
[TransaktionhuvudAnmarkningar] [nvarchar](max) COLLATE
Finnish_Swedish_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TransaktionhuvudTransaktionsnr] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudKontaktTransp ortorAvhamtningnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud1] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudKontaktTransp ortorReturnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud1]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud10] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldAvsyn asnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud10]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud11] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldPacka snr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud11]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud12] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldKurir nr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud12]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud13] FOREIGN KEY([TransaktionhuvudTransaktionhuvudKontaktnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud13]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud2] FOREIGN KEY([BetalningsStatusSTATUSNR])
REFERENCES [dbo].[BetalningsStatus] ([BetalningsStatusSTATUSNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud2]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud3] FOREIGN KEY([UtskriftsstatusSTATUSNR])
REFERENCES [dbo].[Utskriftsstatus] ([UtskriftsstatusSTATUSNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud3]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud4] FOREIGN KEY([ForetagsregisterGRUNDNR])
REFERENCES [dbo].[Foretagsregister] ([ForetagsregisterGRUNDNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud4]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud5] FOREIGN KEY([Transaktionskategorinummer])
REFERENCES [dbo].[Transaktionskategori] ([Transaktionskategorinummer])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud5]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud6] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudTransportsatt Avhamtningnr])
REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud6]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud7] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudTransportsatt Returnr])
REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud7]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud8] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldHandl aggesnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud8]
GO
ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
[ITransaktionhuvud9] FOREIGN
KEY([TransaktionhuvudTransaktionhuvudAnstalldFramt agesnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud9]
USE [MbaseMuseumServerNetSQL]
GO
/****** Objekt: Table [dbo].[Transaktionsrader] Skriptdatum: 10/09/2006
16:17:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaktionsrader](
[TransaktionsraderTransaktionsraderTransaktionsrad ] [int] IDENTITY(1,1) NOT
NULL,
[TransaktionsraderTransaktionsnr] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr2] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr3] [int] NULL,
[TransaktionsraderTransaktionsraderAvgiftsnr4] [int] NULL,
[TransaktionsraderTransaktionsraderEmballagenr] [int] NULL,
[Artikelnummer] [int] NULL,
[Bildnr] [int] NULL,
[TransaktionsraderForetagsnr] [int] NULL,
[TransaktionsraderAntal] [int] NULL,
[ExaktPlacering] [nvarchar](200) COLLATE Finnish_Swedish_CI_AS NULL,
[TillfalligRetur] [bit] NOT NULL,
[DatumTillfalligRetur] [datetime] NULL,
[TransaktionsraderForsakringsvarde] [decimal](17, 6) NULL,
[TransaktionsraderDefinitivRetur] [bit] NOT NULL,
[TransaktionsraderDatumDefinitivRetur] [datetime] NULL,
[AterDeposition] [bit] NOT NULL,
[DatumHamtningUtlamning] [datetime] NOT NULL,
[Undervisningstypnr] [int] NULL,
[DatumVisningStart] [datetime] NULL,
[TidVisningStart] [datetime] NULL,
[DatumVisningSlut] [datetime] NULL,
[TidVisningSlut] [datetime] NULL,
[Amne] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[AntalLektioner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[AntalPersoner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
[VisningInstalld] [bit] NULL,
[DatumVisningInstalld] [datetime] NULL,
[VisningUtford] [bit] NULL,
[transkontaktnr] [int] NULL,
[TransraderAnstalldGuideAnstalldnr] [int] NULL,
[accessionsnummer] [nvarchar](20) COLLATE Finnish_Swedish_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TransaktionsraderTransaktionsraderTransaktionsrad ] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[FK_Transaktionsrader_Master] FOREIGN KEY([accessionsnummer])
REFERENCES [dbo].[Master] ([MasterAccessionsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT
[FK_Transaktionsrader_Master]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader] FOREIGN KEY([Artikelnummer])
REFERENCES [dbo].[Artiklar] ([Artikelnummer])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader10] FOREIGN KEY([transkontaktnr])
REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader10]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader2] FOREIGN
KEY([TransaktionsraderTransaktionsraderEmballagenr ])
REFERENCES [dbo].[Emballage] ([Emballagenr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader2]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader3] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader3]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader4] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr2])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader4]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader5] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr3])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader5]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader6] FOREIGN
KEY([TransaktionsraderTransaktionsraderAvgiftsnr4])
REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader6]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader7] FOREIGN KEY([Undervisningstypnr])
REFERENCES [dbo].[UNDERVISNINGSTYP] ([Undervisningstypnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader7]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader8] FOREIGN KEY([Bildnr])
REFERENCES [dbo].[Bilder] ([Bildnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader8]
GO
ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
[ITransaktionsrader9] FOREIGN KEY([TransraderAnstalldGuideAnstalldnr])
REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
GO
ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader9]
"Arnie Rowland" wrote:
> Please include (copy and paste) the DDL in a post.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Mattias" <Mattias@.discussions.microsoft.com> wrote in message
> news:14FF1450-A7AB-4D25-BE5C-4D75B50845F7@.microsoft.com...
>
>
|||Mattias,
Try this. Use your script and create the two tables on
another test server or some test database.
Then run the script to create the relationship you are
having problems creating - or use Management Studio if
that's how you were doing it.
If you can create the relationship and don't get an error
then that tells you it's likely related to the data in the
two tables violating the constraint. If you can't create the
relationship from Management Studio, hit the script button
(hopefully there is one in the GUI end...don't use that much
so I don't remember) and try just executing the script. If
you still get an error using the script and with no data,
then post the script here.
-Sue
On Mon, 9 Oct 2006 07:20:02 -0700, Mattias
<Mattias@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Ok here it comes!
>Mattias
>USE [MbaseMuseumServerNetSQL]
>GO
>/****** Objekt: Table [dbo].[Transaktionhuvud] Skriptdatum: 10/09/2006
>16:13:57 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[Transaktionhuvud](
>[TransaktionhuvudTransaktionsnr] [int] IDENTITY(1,1) NOT NULL,
>[Transaktionskategorinummer] [int] NULL,
>[TransaktionhuvudTransaktionhuvudKontaktnr] [int] NULL,
>[ForetagsregisterGRUNDNR] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldHandlagge snr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldFramtages nr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldAvsynasnr ] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldPackas nr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudAnstalldKurirn r] [int] NULL,
>[UtskriftsstatusSTATUSNR] [int] NULL,
>[BetalningsStatusSTATUSNR] [int] NULL,
>[TransaktionhuvudRegistreringsdatum] [datetime] NULL,
>[DatumPreliminarRetur] [datetime] NULL,
>[UtstallningStartdatum] [datetime] NULL,
>[UtstallningSlutdatum] [datetime] NULL,
>[Utstallning] [nvarchar](70) COLLATE Finnish_Swedish_CI_AS NULL,
>[TransaktionhuvudTransaktionhuvudKontaktTransporto rAvhamtningnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudKontaktTransporto rReturnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudTransportsattAvha mtningnr] [int] NULL,
>[TransaktionhuvudTransaktionhuvudTransportsattRetu rnr] [int] NULL,
>[TransaktionhuvudDefinitivRetur] [bit] NULL,
>[TransaktionhuvudDatumDefinitivRetur] [datetime] NULL,
>[TransaktionhuvudAnmarkningar] [nvarchar](max) COLLATE
>Finnish_Swedish_CI_AS NULL,
>PRIMARY KEY CLUSTERED
>(
>[TransaktionhuvudTransaktionsnr] ASC
>)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudKontaktTrans portorAvhamtningnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud1] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudKontaktTrans portorReturnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud1]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud10] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldAvsy nasnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud10]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud11] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldPack asnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud11]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud12] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldKuri rnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud12]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud13] FOREIGN KEY([TransaktionhuvudTransaktionhuvudKontaktnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud13]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud2] FOREIGN KEY([BetalningsStatusSTATUSNR])
>REFERENCES [dbo].[BetalningsStatus] ([BetalningsStatusSTATUSNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud2]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud3] FOREIGN KEY([UtskriftsstatusSTATUSNR])
>REFERENCES [dbo].[Utskriftsstatus] ([UtskriftsstatusSTATUSNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud3]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud4] FOREIGN KEY([ForetagsregisterGRUNDNR])
>REFERENCES [dbo].[Foretagsregister] ([ForetagsregisterGRUNDNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud4]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud5] FOREIGN KEY([Transaktionskategorinummer])
>REFERENCES [dbo].[Transaktionskategori] ([Transaktionskategorinummer])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud5]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud6] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudTransportsat tAvhamtningnr])
>REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud6]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud7] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudTransportsat tReturnr])
>REFERENCES [dbo].[TRANSPORTSATT] ([TRANSPORTSATTNR])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud7]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud8] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldHand laggesnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud8]
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] WITH CHECK ADD CONSTRAINT
>[ITransaktionhuvud9] FOREIGN
>KEY([TransaktionhuvudTransaktionhuvudAnstalldFram tagesnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionhuvud] CHECK CONSTRAINT [ITransaktionhuvud9]
>
>USE [MbaseMuseumServerNetSQL]
>GO
>/****** Objekt: Table [dbo].[Transaktionsrader] Skriptdatum: 10/09/2006
>16:17:38 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[Transaktionsrader](
>[TransaktionsraderTransaktionsraderTransaktionsrad ] [int] IDENTITY(1,1) NOT
>NULL,
>[TransaktionsraderTransaktionsnr] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr2] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr3] [int] NULL,
>[TransaktionsraderTransaktionsraderAvgiftsnr4] [int] NULL,
>[TransaktionsraderTransaktionsraderEmballagenr] [int] NULL,
>[Artikelnummer] [int] NULL,
>[Bildnr] [int] NULL,
>[TransaktionsraderForetagsnr] [int] NULL,
>[TransaktionsraderAntal] [int] NULL,
>[ExaktPlacering] [nvarchar](200) COLLATE Finnish_Swedish_CI_AS NULL,
>[TillfalligRetur] [bit] NOT NULL,
>[DatumTillfalligRetur] [datetime] NULL,
>[TransaktionsraderForsakringsvarde] [decimal](17, 6) NULL,
>[TransaktionsraderDefinitivRetur] [bit] NOT NULL,
>[TransaktionsraderDatumDefinitivRetur] [datetime] NULL,
>[AterDeposition] [bit] NOT NULL,
>[DatumHamtningUtlamning] [datetime] NOT NULL,
>[Undervisningstypnr] [int] NULL,
>[DatumVisningStart] [datetime] NULL,
>[TidVisningStart] [datetime] NULL,
>[DatumVisningSlut] [datetime] NULL,
>[TidVisningSlut] [datetime] NULL,
>[Amne] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[AntalLektioner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[AntalPersoner] [nvarchar](50) COLLATE Finnish_Swedish_CI_AS NULL,
>[VisningInstalld] [bit] NULL,
>[DatumVisningInstalld] [datetime] NULL,
>[VisningUtford] [bit] NULL,
>[transkontaktnr] [int] NULL,
>[TransraderAnstalldGuideAnstalldnr] [int] NULL,
>[accessionsnummer] [nvarchar](20) COLLATE Finnish_Swedish_CI_AS NULL,
>PRIMARY KEY CLUSTERED
>(
>[TransaktionsraderTransaktionsraderTransaktionsrad ] ASC
>)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[FK_Transaktionsrader_Master] FOREIGN KEY([accessionsnummer])
>REFERENCES [dbo].[Master] ([MasterAccessionsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT
>[FK_Transaktionsrader_Master]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader] FOREIGN KEY([Artikelnummer])
>REFERENCES [dbo].[Artiklar] ([Artikelnummer])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader10] FOREIGN KEY([transkontaktnr])
>REFERENCES [dbo].[Kontakter] ([Kontaktnummer])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader10]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader2] FOREIGN
>KEY([TransaktionsraderTransaktionsraderEmballagen r])
>REFERENCES [dbo].[Emballage] ([Emballagenr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader2]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader3] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsn r])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader3]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader4] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr2 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader4]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader5] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr3 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader5]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader6] FOREIGN
>KEY([TransaktionsraderTransaktionsraderAvgiftsnr4 ])
>REFERENCES [dbo].[Avgifter] ([Avgiftsnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader6]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader7] FOREIGN KEY([Undervisningstypnr])
>REFERENCES [dbo].[UNDERVISNINGSTYP] ([Undervisningstypnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader7]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader8] FOREIGN KEY([Bildnr])
>REFERENCES [dbo].[Bilder] ([Bildnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader8]
>GO
>ALTER TABLE [dbo].[Transaktionsrader] WITH CHECK ADD CONSTRAINT
>[ITransaktionsrader9] FOREIGN KEY([TransraderAnstalldGuideAnstalldnr])
>REFERENCES [dbo].[Anstallda] ([AnstalldaAnstalldnr])
>GO
>ALTER TABLE [dbo].[Transaktionsrader] CHECK CONSTRAINT [ITransaktionsrader9]
>"Arnie Rowland" wrote:
Can not remotely connect to instance of sql server 2000
I had a big problem on connecting sqlserver remotely from other machines on the network .....
From any computer in the network i wanna to register the instance of the sqlserver from the enterprise manager ..... the server instance doesn't appear within the available sqlserver list (the servername which equal to my machine is the only one that appear) ..... when i manualy write the servername\alias manually and i choose the connection type and then i write sqlserver username and password and then finish he give a message to me that access denied or sql server doesn't exist ... !!
by the way from the local machine i registered the local instance of sqlserver successfully and successfully i access the Databases .....
To be noted:
I previously uninstalled this alias and i then i reinstalled it again with the same name and i attached the previously exists Databes.
in the same time i installed on the same machine sqlserver 2005 connectivity clients not the server itself.
Why are you using aliases? Do select @.@.servername, and tell me if this is the exact name you're trying to connect to via enterprise manager.|||
thank you for trying to help me,
I am using an alias from a long time and it was working good until i uninstalled the server and installed it agian .......
when i make "select @.@.servername" it return back "magedsalah\maged" and as i told before it works well on the local machine where i can connect with the enterprise manager to magedsalah\maged without any problems ......
the problem when anybody wanna to register magedsalah\maged in the enterprise manager of another computer the magedsalah\maged doesn't appear in the servers list what appear is magedsalah and it can't be registered and sometimes and some there is nothing appear at all.....
for further details plz refer to original message in the thread.....
thanks
|||Did you ever figure out the solution to this, I'm having the exact same problem, it occured after I installed Sql 2005, and now i'm using named instances.|||Hi,
After i trapped in solving this problem ..... i removed the existing sql server 2000 where i use the alias and then i installed it again without alias (the default case in setup) ..... then i reattached my databases ......... and it works fine now
|||I ended up doing the same thing too, and mine works too now. There must be a trick to making it work with alias'/instance names too though, oh well.
Thanks
Friday, February 10, 2012
Can not load file into Database Help!
I tried to load a file (Data.xml) into SQLServer 2000 using Schema.xml file
but when i load the file i check the table and nothing was loaded, and i
don't get any error in error.log file
A
I create a file called load.vbs
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=miDatabase;uid=sa;pwd=;"
objBL.ErrorLogFile = "C:\error.log"
objBL.Execute "C:\Schema.xml", "C:\Data.xml"
set objBL=Nothing
And schema called Schema.xml
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Registro" sql:relation="Sufisinterno" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Tipo" type="xsd:string" />
<xsd:element name="Identificacion" type="xsd:string" />
<xsd:element name="PrimerApellido" type="xsd:string" />
<xsd:element name="SegundoApellido" type="xsd:string" />
<xsd:element name="Nombre" type="xsd:string" />
<xsd:element name="Genero" type="xsd:string" />
<xsd:element name="PaisNacimiento" type="xsd:string" />
<xsd:element name="tmpFechaNaci" type="xsd:string" />
<xsd:element name="IdentificacionTitular" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
And a file called data.xml
<?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xsd"><Registro><Tipo> 1</Tipo><Identificacion>0031977420010001709</Identificacion><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApellido>MORENO</SegundoApellido><Nombre>HAROLD
AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacimiento><PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197742001 0001709</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>003860579</Identificacion><PrimerApellido>GONZALEZ</PrimerApellido><SegundoApellido>NIETO</SegundoApellido><Nombre>MARIA
DEL
CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNacimiento><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386579</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>013478458</Identificacion><PrimerApellido>FIGUEROA</PrimerApellido><SegundoApellido>VANEGAS</SegundoApellido><Nombre>JOSE
RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
/><IdentificacionTitular>013478458</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><PrimerApellido>SALAZAR</PrimerApellido><SegundoApellido
/><Nombre>BLANCA
ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNacimiento><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038041262</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>043851379</Identificacion><PrimerApellido>SOTO</PrimerApellido><SegundoApellido>CALDERON</SegundoApellido><Nombre>MARIO
ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNacimiento><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043851379</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>046164814</Identificacion><PrimerApellido>CROSSTY</PrimerApellido><SegundoApellido
/><Nombre>KEITH
ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>046164814</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><PrimerApellido>GONZALEZ</PrimerApellido><SegundoApellido
/><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/1937</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>046214376</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificacion><PrimerApellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ</SegundoApellido><Nombre>LIDIA</Nombre><Genero>2</Genero><FechaNacimiento>28/10/1967</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Registro></PadronInternoPersonasFisicas>
Hello junior,
I'm pretty sure its because you've got the namespace in your data XML file.
This means that the parsing needs to understand that namespace.
Not sure how you get it to understand it. I'll try a bit of digging
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> I tried to load a file (Data.xml) into SQLServer 2000 using Schema.xml
> file but when i load the file i check the table and nothing was
> loaded, and i don't get any error in error.log file
> A
> I create a file called load.vbs
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=miDatabase;uid=sa;pwd=;"
> objBL.ErrorLogFile = "C:\error.log"
> objBL.Execute "C:\Schema.xml", "C:\Data.xml"
> set objBL=Nothing
> And schema called Schema.xml
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Registro" sql:relation="Sufisinterno" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Tipo" type="xsd:string" />
> <xsd:element name="Identificacion" type="xsd:string" />
> <xsd:element name="PrimerApellido" type="xsd:string" />
> <xsd:element name="SegundoApellido" type="xsd:string" />
> <xsd:element name="Nombre" type="xsd:string" />
> <xsd:element name="Genero" type="xsd:string" />
> <xsd:element name="PaisNacimiento" type="xsd:string" />
> <xsd:element name="tmpFechaNaci" type="xsd:string" />
> <xsd:element name="IdentificacionTitular" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> And a file called data.xml
> <?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
> xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisi
> cas.xsd"><Registro><Tipo>1</Tipo><Identificacion>0031977420010001709</
> Identificacion><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApelli
> do>MORENO</SegundoApellido><Nombre>HAROLD
> AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacim
> iento><PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197
> 7420010001709</IdentificacionTitular></Registro><Registro><Tipo>1</Tip
> o><Identificacion>003860579</Identificacion><PrimerApellido>GONZALEZ</
> PrimerApellido><SegundoApellido>NIETO</SegundoApellido><Nombre>MARIA
> DEL
> CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNac
> imiento><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386
> 579</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identif
> icacion>013478458</Identificacion><PrimerApellido>FIGUEROA</PrimerApel
> lido><SegundoApellido>VANEGAS</SegundoApellido><Nombre>JOSE
> RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
> /><IdentificacionTitular>013478458</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><Prim
> erApellido>SALAZAR</PrimerApellido><SegundoApellido /><Nombre>BLANCA
> ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNa
> cimiento><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038
> 041262</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>043851379</Identificacion><PrimerApellido>SOTO</PrimerApell
> ido><SegundoApellido>CALDERON</SegundoApellido><Nombre>MARIO
> ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNa
> cimiento><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043
> 851379</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>046164814</Identificacion><PrimerApellido>CROSSTY</PrimerAp
> ellido><SegundoApellido /><Nombre>KEITH
> ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNa
> cimiento><PaisNacimiento
> /><IdentificacionTitular>046164814</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><Prim
> erApellido>GONZALEZ</PrimerApellido><SegundoApellido
> /><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/193
> 7</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>046214376</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificaci
> on><PrimerApellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ
> </SegundoApellido><Nombre>LIDIA</Nombre><Genero>2</Genero><FechaNacimi
> ento>28/10/1967</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Re
> gistro></PadronInternoPersonasFisicas>
>
|||Yes you are right if i delete the namespace
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xsd"
i can load the file, but file is about 1 Gb size and i want to avoid open
the file for delete the namespace.
Any help would be very helpful.
Thank you.
"Simon Sabin" wrote:
> Hello junior,
> I'm pretty sure its because you've got the namespace in your data XML file.
> This means that the parsing needs to understand that namespace.
> Not sure how you get it to understand it. I'll try a bit of digging
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>
but when i load the file i check the table and nothing was loaded, and i
don't get any error in error.log file
A
I create a file called load.vbs
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=miDatabase;uid=sa;pwd=;"
objBL.ErrorLogFile = "C:\error.log"
objBL.Execute "C:\Schema.xml", "C:\Data.xml"
set objBL=Nothing
And schema called Schema.xml
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Registro" sql:relation="Sufisinterno" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Tipo" type="xsd:string" />
<xsd:element name="Identificacion" type="xsd:string" />
<xsd:element name="PrimerApellido" type="xsd:string" />
<xsd:element name="SegundoApellido" type="xsd:string" />
<xsd:element name="Nombre" type="xsd:string" />
<xsd:element name="Genero" type="xsd:string" />
<xsd:element name="PaisNacimiento" type="xsd:string" />
<xsd:element name="tmpFechaNaci" type="xsd:string" />
<xsd:element name="IdentificacionTitular" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
And a file called data.xml
<?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xsd"><Registro><Tipo> 1</Tipo><Identificacion>0031977420010001709</Identificacion><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApellido>MORENO</SegundoApellido><Nombre>HAROLD
AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacimiento><PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197742001 0001709</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>003860579</Identificacion><PrimerApellido>GONZALEZ</PrimerApellido><SegundoApellido>NIETO</SegundoApellido><Nombre>MARIA
DEL
CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNacimiento><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386579</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>013478458</Identificacion><PrimerApellido>FIGUEROA</PrimerApellido><SegundoApellido>VANEGAS</SegundoApellido><Nombre>JOSE
RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
/><IdentificacionTitular>013478458</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><PrimerApellido>SALAZAR</PrimerApellido><SegundoApellido
/><Nombre>BLANCA
ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNacimiento><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038041262</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>043851379</Identificacion><PrimerApellido>SOTO</PrimerApellido><SegundoApellido>CALDERON</SegundoApellido><Nombre>MARIO
ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNacimiento><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043851379</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>046164814</Identificacion><PrimerApellido>CROSSTY</PrimerApellido><SegundoApellido
/><Nombre>KEITH
ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>046164814</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><PrimerApellido>GONZALEZ</PrimerApellido><SegundoApellido
/><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/1937</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>046214376</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificacion><PrimerApellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ</SegundoApellido><Nombre>LIDIA</Nombre><Genero>2</Genero><FechaNacimiento>28/10/1967</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Registro></PadronInternoPersonasFisicas>
Hello junior,
I'm pretty sure its because you've got the namespace in your data XML file.
This means that the parsing needs to understand that namespace.
Not sure how you get it to understand it. I'll try a bit of digging
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> I tried to load a file (Data.xml) into SQLServer 2000 using Schema.xml
> file but when i load the file i check the table and nothing was
> loaded, and i don't get any error in error.log file
> A
> I create a file called load.vbs
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=miDatabase;uid=sa;pwd=;"
> objBL.ErrorLogFile = "C:\error.log"
> objBL.Execute "C:\Schema.xml", "C:\Data.xml"
> set objBL=Nothing
> And schema called Schema.xml
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Registro" sql:relation="Sufisinterno" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Tipo" type="xsd:string" />
> <xsd:element name="Identificacion" type="xsd:string" />
> <xsd:element name="PrimerApellido" type="xsd:string" />
> <xsd:element name="SegundoApellido" type="xsd:string" />
> <xsd:element name="Nombre" type="xsd:string" />
> <xsd:element name="Genero" type="xsd:string" />
> <xsd:element name="PaisNacimiento" type="xsd:string" />
> <xsd:element name="tmpFechaNaci" type="xsd:string" />
> <xsd:element name="IdentificacionTitular" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> And a file called data.xml
> <?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
> xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisi
> cas.xsd"><Registro><Tipo>1</Tipo><Identificacion>0031977420010001709</
> Identificacion><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApelli
> do>MORENO</SegundoApellido><Nombre>HAROLD
> AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacim
> iento><PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197
> 7420010001709</IdentificacionTitular></Registro><Registro><Tipo>1</Tip
> o><Identificacion>003860579</Identificacion><PrimerApellido>GONZALEZ</
> PrimerApellido><SegundoApellido>NIETO</SegundoApellido><Nombre>MARIA
> DEL
> CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNac
> imiento><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386
> 579</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identif
> icacion>013478458</Identificacion><PrimerApellido>FIGUEROA</PrimerApel
> lido><SegundoApellido>VANEGAS</SegundoApellido><Nombre>JOSE
> RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
> /><IdentificacionTitular>013478458</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><Prim
> erApellido>SALAZAR</PrimerApellido><SegundoApellido /><Nombre>BLANCA
> ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNa
> cimiento><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038
> 041262</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>043851379</Identificacion><PrimerApellido>SOTO</PrimerApell
> ido><SegundoApellido>CALDERON</SegundoApellido><Nombre>MARIO
> ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNa
> cimiento><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043
> 851379</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>046164814</Identificacion><PrimerApellido>CROSSTY</PrimerAp
> ellido><SegundoApellido /><Nombre>KEITH
> ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNa
> cimiento><PaisNacimiento
> /><IdentificacionTitular>046164814</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><Prim
> erApellido>GONZALEZ</PrimerApellido><SegundoApellido
> /><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/193
> 7</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>046214376</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificaci
> on><PrimerApellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ
> </SegundoApellido><Nombre>LIDIA</Nombre><Genero>2</Genero><FechaNacimi
> ento>28/10/1967</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Re
> gistro></PadronInternoPersonasFisicas>
>
|||Yes you are right if i delete the namespace
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xsd"
i can load the file, but file is about 1 Gb size and i want to avoid open
the file for delete the namespace.
Any help would be very helpful.
Thank you.
"Simon Sabin" wrote:
> Hello junior,
> I'm pretty sure its because you've got the namespace in your data XML file.
> This means that the parsing needs to understand that namespace.
> Not sure how you get it to understand it. I'll try a bit of digging
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>
Can not load file into Database Help!
I tried to load a file (Data.xml) into SQLServer 2000 using Schema.xml file
but when i load the file i check the table and nothing was loaded, and i
don't get any error in error.log file
A
I create a file called load.vbs
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=miDatabase;uid
=sa;pwd=;"
objBL.ErrorLogFile = "C:\error.log"
objBL.Execute "C:\Schema.xml", "C:\Data.xml"
set objBL=Nothing
And schema called Schema.xml
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Registro" sql:relation="Sufisinterno" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Tipo" type="xsd:string" />
<xsd:element name="Identificacion" type="xsd:string" />
<xsd:element name="PrimerApellido" type="xsd:string" />
<xsd:element name="SegundoApellido" type="xsd:string" />
<xsd:element name="Nombre" type="xsd:string" />
<xsd:element name="Genero" type="xsd:string" />
<xsd:element name="PaisNacimiento" type="xsd:string" />
<xsd:element name="tmpFechaNaci" type="xsd:string" />
<xsd:element name="IdentificacionTitular" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
And a file called data.xml
<?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xs
d"><Registro><Tipo>1</Tipo><Identificacion>0031977420010001709</Identificaci
on><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApellido>MORENO</Segundo
Apellido><Nombre>HAROLD
AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacimiento>
<PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197742001000170
9</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>
003860579</Identificacion><
PrimerApellido>GONZALEZ</PrimerApellido><SegundoApellido>NIETO</SegundoApell
ido><Nombre>MARIA
DEL
CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNacimient
o><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386579</Identif
icacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>013478458<
/Identificacion><PrimerApel
lido>FIGUEROA</PrimerApellido><SegundoApellido>VANEGAS</SegundoApellido><Nom
bre>JOSE
RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
/><IdentificacionTitular>013478458</IdentificacionTitular></Registro><Regist
ro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><PrimerApellido>S
ALAZAR</PrimerApellido><SegundoApellido
/><Nombre>BLANCA
ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNacimien
to><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038041262</Iden
tificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>0438513
79</Identificacion><PrimerA
pellido>SOTO</PrimerApellido><SegundoApellido>CALDERON</SegundoApellido><Nom
bre>MARIO
ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNacimien
to><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043851379</Iden
tificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>0461648
14</Identificacion><PrimerA
pellido>CROSSTY</PrimerApellido><SegundoApellido
/><Nombre>KEITH
ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNacimien
to><PaisNacimiento
/><IdentificacionTitular>046164814</IdentificacionTitular></Registro><Regist
ro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><PrimerApellido>G
ONZALEZ</PrimerApellido><SegundoApellido
/><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/1937</Fec
haNacimiento><PaisNacimiento
/><IdentificacionTitular>046214376</IdentificacionTitular></Registro><Regist
ro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificacion><PrimerAp
ellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ</SegundoApellido>
<Nombre>LIDIA</Nombre><Gene
ro>2</Genero><FechaNacimiento>28/10/1967</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Registro
></PadronInternoPersonasFisicas>Hello junior,
I'm pretty sure its because you've got the namespace in your data XML file.
This means that the parsing needs to understand that namespace.
Not sure how you get it to understand it. I'll try a bit of digging
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> I tried to load a file (Data.xml) into SQLServer 2000 using Schema.xml
> file but when i load the file i check the table and nothing was
> loaded, and i don't get any error in error.log file
> A
> I create a file called load.vbs
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=miDatabase;uid
=sa;pwd=;"
> objBL.ErrorLogFile = "C:\error.log"
> objBL.Execute "C:\Schema.xml", "C:\Data.xml"
> set objBL=Nothing
> And schema called Schema.xml
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Registro" sql:relation="Sufisinterno" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Tipo" type="xsd:string" />
> <xsd:element name="Identificacion" type="xsd:string" />
> <xsd:element name="PrimerApellido" type="xsd:string" />
> <xsd:element name="SegundoApellido" type="xsd:string" />
> <xsd:element name="Nombre" type="xsd:string" />
> <xsd:element name="Genero" type="xsd:string" />
> <xsd:element name="PaisNacimiento" type="xsd:string" />
> <xsd:element name="tmpFechaNaci" type="xsd:string" />
> <xsd:element name="IdentificacionTitular" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> And a file called data.xml
> <?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
> xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisi
> cas.xsd"><Registro><Tipo>1</Tipo><Identificacion>0031977420010001709</
> Identificacion><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApelli
> do>MORENO</SegundoApellido><Nombre>HAROLD
> AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacim
> iento><PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197
> 7420010001709</IdentificacionTitular></Registro><Registro><Tipo>1</Tip
> o><Identificacion>003860579</Identificacion><PrimerApellido>GONZALEZ</
> PrimerApellido><SegundoApellido>NIETO</SegundoApellido><Nombre>MARIA
> DEL
> CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNac
> imiento><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386
> 579</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identif
> icacion>013478458</Identificacion><PrimerApellido>FIGUEROA</PrimerApel
> lido><SegundoApellido>VANEGAS</SegundoApellido><Nombre>JOSE
> RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
> /><IdentificacionTitular>013478458</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><Prim
> erApellido>SALAZAR</PrimerApellido><SegundoApellido /><Nombre>BLANCA
> ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNa
> cimiento><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038
> 041262</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>043851379</Identificacion><PrimerApellido>SOTO</PrimerApell
> ido><SegundoApellido>CALDERON</SegundoApellido><Nombre>MARIO
> ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNa
> cimiento><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043
> 851379</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>046164814</Identificacion><PrimerApellido>CROSSTY</PrimerAp
> ellido><SegundoApellido /><Nombre>KEITH
> ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNa
> cimiento><PaisNacimiento
> /><IdentificacionTitular>046164814</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><Prim
> erApellido>GONZALEZ</PrimerApellido><SegundoApellido
> /><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/193
> 7</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>046214376</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificaci
> on><PrimerApellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ
> </SegundoApellido><Nombre>LIDIA</Nombre><Genero>2</Genero><FechaNacimi
> ento>28/10/1967</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Re
> gistro></PadronInternoPersonasFisicas>
>|||Yes you are right if i delete the namespace
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xs
d"
i can load the file, but file is about 1 Gb size and i want to avoid open
the file for delete the namespace.
Any help would be very helpful.
Thank you.
"Simon Sabin" wrote:
> Hello junior,
> I'm pretty sure its because you've got the namespace in your data XML file
.
> This means that the parsing needs to understand that namespace.
> Not sure how you get it to understand it. I'll try a bit of digging
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>
but when i load the file i check the table and nothing was loaded, and i
don't get any error in error.log file
A
I create a file called load.vbs
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=miDatabase;uid
=sa;pwd=;"
objBL.ErrorLogFile = "C:\error.log"
objBL.Execute "C:\Schema.xml", "C:\Data.xml"
set objBL=Nothing
And schema called Schema.xml
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Registro" sql:relation="Sufisinterno" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Tipo" type="xsd:string" />
<xsd:element name="Identificacion" type="xsd:string" />
<xsd:element name="PrimerApellido" type="xsd:string" />
<xsd:element name="SegundoApellido" type="xsd:string" />
<xsd:element name="Nombre" type="xsd:string" />
<xsd:element name="Genero" type="xsd:string" />
<xsd:element name="PaisNacimiento" type="xsd:string" />
<xsd:element name="tmpFechaNaci" type="xsd:string" />
<xsd:element name="IdentificacionTitular" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
And a file called data.xml
<?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xs
d"><Registro><Tipo>1</Tipo><Identificacion>0031977420010001709</Identificaci
on><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApellido>MORENO</Segundo
Apellido><Nombre>HAROLD
AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacimiento>
<PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197742001000170
9</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>
003860579</Identificacion><
PrimerApellido>GONZALEZ</PrimerApellido><SegundoApellido>NIETO</SegundoApell
ido><Nombre>MARIA
DEL
CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNacimient
o><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386579</Identif
icacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>013478458<
/Identificacion><PrimerApel
lido>FIGUEROA</PrimerApellido><SegundoApellido>VANEGAS</SegundoApellido><Nom
bre>JOSE
RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
/><IdentificacionTitular>013478458</IdentificacionTitular></Registro><Regist
ro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><PrimerApellido>S
ALAZAR</PrimerApellido><SegundoApellido
/><Nombre>BLANCA
ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNacimien
to><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038041262</Iden
tificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>0438513
79</Identificacion><PrimerA
pellido>SOTO</PrimerApellido><SegundoApellido>CALDERON</SegundoApellido><Nom
bre>MARIO
ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNacimien
to><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043851379</Iden
tificacionTitular></Registro><Registro><Tipo>1</Tipo><Identificacion>0461648
14</Identificacion><PrimerA
pellido>CROSSTY</PrimerApellido><SegundoApellido
/><Nombre>KEITH
ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNacimien
to><PaisNacimiento
/><IdentificacionTitular>046164814</IdentificacionTitular></Registro><Regist
ro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><PrimerApellido>G
ONZALEZ</PrimerApellido><SegundoApellido
/><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/1937</Fec
haNacimiento><PaisNacimiento
/><IdentificacionTitular>046214376</IdentificacionTitular></Registro><Regist
ro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificacion><PrimerAp
ellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ</SegundoApellido>
<Nombre>LIDIA</Nombre><Gene
ro>2</Genero><FechaNacimiento>28/10/1967</FechaNacimiento><PaisNacimiento
/><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Registro
></PadronInternoPersonasFisicas>Hello junior,
I'm pretty sure its because you've got the namespace in your data XML file.
This means that the parsing needs to understand that namespace.
Not sure how you get it to understand it. I'll try a bit of digging
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> I tried to load a file (Data.xml) into SQLServer 2000 using Schema.xml
> file but when i load the file i check the table and nothing was
> loaded, and i don't get any error in error.log file
> A
> I create a file called load.vbs
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=miDatabase;uid
=sa;pwd=;"
> objBL.ErrorLogFile = "C:\error.log"
> objBL.Execute "C:\Schema.xml", "C:\Data.xml"
> set objBL=Nothing
> And schema called Schema.xml
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Registro" sql:relation="Sufisinterno" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Tipo" type="xsd:string" />
> <xsd:element name="Identificacion" type="xsd:string" />
> <xsd:element name="PrimerApellido" type="xsd:string" />
> <xsd:element name="SegundoApellido" type="xsd:string" />
> <xsd:element name="Nombre" type="xsd:string" />
> <xsd:element name="Genero" type="xsd:string" />
> <xsd:element name="PaisNacimiento" type="xsd:string" />
> <xsd:element name="tmpFechaNaci" type="xsd:string" />
> <xsd:element name="IdentificacionTitular" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> And a file called data.xml
> <?xml version="1.0" encoding="utf-8"?><PadronInternoPersonasFisicas
> xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisi
> cas.xsd"><Registro><Tipo>1</Tipo><Identificacion>0031977420010001709</
> Identificacion><PrimerApellido>FRESNEDA</PrimerApellido><SegundoApelli
> do>MORENO</SegundoApellido><Nombre>HAROLD
> AMED</Nombre><Genero>1</Genero><FechaNacimiento>13/09/1978</FechaNacim
> iento><PaisNacimiento>CO</PaisNacimiento><IdentificacionTitular>003197
> 7420010001709</IdentificacionTitular></Registro><Registro><Tipo>1</Tip
> o><Identificacion>003860579</Identificacion><PrimerApellido>GONZALEZ</
> PrimerApellido><SegundoApellido>NIETO</SegundoApellido><Nombre>MARIA
> DEL
> CARMEN</Nombre><Genero>2</Genero><FechaNacimiento>15/10/1965</FechaNac
> imiento><PaisNacimiento>CU</PaisNacimiento><IdentificacionTitular>C386
> 579</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Identif
> icacion>013478458</Identificacion><PrimerApellido>FIGUEROA</PrimerApel
> lido><SegundoApellido>VANEGAS</SegundoApellido><Nombre>JOSE
> RAUL</Nombre><Genero>1</Genero><FechaNacimiento /><PaisNacimiento
> /><IdentificacionTitular>013478458</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>038041262</Identificacion><Prim
> erApellido>SALAZAR</PrimerApellido><SegundoApellido /><Nombre>BLANCA
> ESTELLA</Nombre><Genero>2</Genero><FechaNacimiento>26/03/2007</FechaNa
> cimiento><PaisNacimiento>US</PaisNacimiento><IdentificacionTitular>038
> 041262</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>043851379</Identificacion><PrimerApellido>SOTO</PrimerApell
> ido><SegundoApellido>CALDERON</SegundoApellido><Nombre>MARIO
> ALBERTO</Nombre><Genero>1</Genero><FechaNacimiento>22/01/1982</FechaNa
> cimiento><PaisNacimiento>CR</PaisNacimiento><IdentificacionTitular>043
> 851379</IdentificacionTitular></Registro><Registro><Tipo>1</Tipo><Iden
> tificacion>046164814</Identificacion><PrimerApellido>CROSSTY</PrimerAp
> ellido><SegundoApellido /><Nombre>KEITH
> ANTONIO</Nombre><Genero>1</Genero><FechaNacimiento>18/02/1957</FechaNa
> cimiento><PaisNacimiento
> /><IdentificacionTitular>046164814</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>046214376</Identificacion><Prim
> erApellido>GONZALEZ</PrimerApellido><SegundoApellido
> /><Nombre>ENRIQUE</Nombre><Genero>1</Genero><FechaNacimiento>27/02/193
> 7</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>046214376</IdentificacionTitular></Registro><
> Registro><Tipo>1</Tipo><Identificacion>064RE001076001999</Identificaci
> on><PrimerApellido>MONTENEGRO</PrimerApellido><SegundoApellido>SANCHEZ
> </SegundoApellido><Nombre>LIDIA</Nombre><Genero>2</Genero><FechaNacimi
> ento>28/10/1967</FechaNacimiento><PaisNacimiento
> /><IdentificacionTitular>064RE001076001999</IdentificacionTitular></Re
> gistro></PadronInternoPersonasFisicas>
>|||Yes you are right if i delete the namespace
xmlns="http://www.sugef.fi.cr/esquemas/SUGEF-PadronInternoPersonasFisicas.xs
d"
i can load the file, but file is about 1 Gb size and i want to avoid open
the file for delete the namespace.
Any help would be very helpful.
Thank you.
"Simon Sabin" wrote:
> Hello junior,
> I'm pretty sure its because you've got the namespace in your data XML file
.
> This means that the parsing needs to understand that namespace.
> Not sure how you get it to understand it. I'll try a bit of digging
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>
Subscribe to:
Posts (Atom)