Bulkload XML Code: load

Showing posts with label load. Show all posts

Wednesday, March 7, 2012

Cache Dependency Problem

Hello.

I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.

Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.

Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.

On my local machine everything seems to work just fine. On the testing server the cache is always null.

Is cache dependency related

- to the platform used?

- to the number of IIS servers connected to the DB?

- to the sql user used?

Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops.

Here is the code i wrote:

public ManufacturerList GetAllManufacturers()
{
if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null)
return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList;

SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS"));

_manufacturerList = new ManufacturerList();

DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null);

while (reader.Read())
{
int manufacturerID = reader.GetInt("ID");

Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID);

if (findManufacturer == null)
{
findManufacturer = new Manufacturer();
findManufacturer.LoadFromDataReader(reader);
_manufacturerList.Add(findManufacturer);
}
}

reader.Close();

HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep);

return _manufacturerList;
}

Thanks a lot.

Hi,

Look at this article describing some ways of using SQL Dependency Cache.

http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html

Friday, February 24, 2012

C# code or document for loading excel sheet into an sql table

Hi,

I am trying to find some document or code that will load an excel spreadsheet into an sqlserver database.

Can anyone please point me in the right direction.http://www.databasejournal.com/features/mssql/article.php/3331881|||Thanks a lot for your help I will take a look at this now.

Thursday, February 16, 2012

Bypassing recovery for database 'mydb' because it is makred IN LOAD

Hi,
Im having a problem with a SQL server that gets an error after trying to
restore a Database. The restore says completed but it doesn't actually
complete. I have tred the Knowlege base artical 822852 with no luck still
has the same problem after going into the systemdatabases table and changing
status to 16 for the database 'mydb'.
Thanks
MattPerhaps you didn't specify WITH RECOVERY for the restore? Try:
RESTORE DATABASE dbname WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matt" <mattbeach@.hotmail.com> wrote in message news:uWEmD3mLGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Im having a problem with a SQL server that gets an error after trying to
> restore a Database. The restore says completed but it doesn't actually
> complete. I have tred the Knowlege base artical 822852 with no luck still
> has the same problem after going into the systemdatabases table and changing
> status to 16 for the database 'mydb'.
> Thanks
> Matt
>|||Yes I did try however it said it restored it in 0 seconds and actually
didn't work.
Matt
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23xlbknLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Perhaps you didn't specify WITH RECOVERY for the restore? Try:
> RESTORE DATABASE dbname WITH RECOVERY
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matt" <mattbeach@.hotmail.com> wrote in message
> news:uWEmD3mLGHA.2624@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Im having a problem with a SQL server that gets an error after trying to
>> restore a Database. The restore says completed but it doesn't actually
>> complete. I have tred the Knowlege base artical 822852 with no luck still
>> has the same problem after going into the systemdatabases table and
>> changing status to 16 for the database 'mydb'.
>> Thanks
>> Matt|||My guess then is that the restore wend bad and you have to redo the operation. I'd do it from Query
Analyzer (so you can save the TSQL commands used and post here if that doesn't work).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matt" <mattbeach@.hotmail.com> wrote in message news:e3d%23z9nLGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Yes I did try however it said it restored it in 0 seconds and actually didn't work.
> Matt
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u%23xlbknLGHA.3936@.TK2MSFTNGP10.phx.gbl...
>> Perhaps you didn't specify WITH RECOVERY for the restore? Try:
>> RESTORE DATABASE dbname WITH RECOVERY
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Matt" <mattbeach@.hotmail.com> wrote in message news:uWEmD3mLGHA.2624@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Im having a problem with a SQL server that gets an error after trying to restore a Database. The
>> restore says completed but it doesn't actually complete. I have tred the Knowlege base artical
>> 822852 with no luck still has the same problem after going into the systemdatabases table and
>> changing status to 16 for the database 'mydb'.
>> Thanks
>> Matt
>

Bypassing recovery for database 'mydb' because it is makred IN LOAD

Bypassing recovery for database

MS SQL-Server 7.0
Bypassing recovery for database 'EfW_765' because it is marked IN LOAD.
What does this mean?
Our customer is backing up is maindatabase and is recovering it to this
database for testing.
Our custumer tries it serveral times and then the recovery works and the
data are corrupt.
I have written a little programm which does some selects to this database.
The program is stopped during recovery but our customer beleves that this
has something to do with our program.
http://support.microsoft.com/defaul...kb;en-us;272683
This article doesnt halp me much. So hwat is the meaning of IN LOAD and what
does the server do if it bypasses the recovery.

THX

Jens"Jens Kalkbrenner" <jens.kalkbrenner@.wsk.de> wrote in message
news:c5gccs$73a$01$1@.news.t-online.com...
> MS SQL-Server 7.0
> Bypassing recovery for database 'EfW_765' because it is marked IN LOAD.
> What does this mean?
> Our customer is backing up is maindatabase and is recovering it to this
> database for testing.
> Our custumer tries it serveral times and then the recovery works and the
> data are corrupt.
> I have written a little programm which does some selects to this database.
> The program is stopped during recovery but our customer beleves that this
> has something to do with our program.
> http://support.microsoft.com/defaul...kb;en-us;272683
> This article doesnt halp me much. So hwat is the meaning of IN LOAD and
what
> does the server do if it bypasses the recovery.
> THX
> Jens

I'm not entirely sure, but it sounds as if the database has been partially
recovered, and then the MSSQLServer service has been restarted. This might
be normal, if someone is recovering the database by applying mutiple log
files, for example. Have you tried this, to restore the database and make it
available?

RESTORE DATABASE EfW_765 FROM ... WITH RECOVERY

If this doesn't help, can you give some more details about exactly what is
happening? Is the recovery from a full backup only or from a full backup
plus logfiles? What do you mean when you say the data is "corrupt"? Are you
sure you're recovering the correct backup set? Is the customer trying to
copy database A to database B using backup/restore? If so, have you followed
the steps in Books Online under "Copying Databases"?

Simon|||"Jens Kalkbrenner" <jens.kalkbrenner@.wsk.de> wrote in message
news:c5gccs$73a$01$1@.news.t-online.com...
> MS SQL-Server 7.0
> Bypassing recovery for database 'EfW_765' because it is marked IN LOAD.
> What does this mean?
> Our customer is backing up is maindatabase and is recovering it to this
> database for testing.
> Our custumer tries it serveral times and then the recovery works and the
> data are corrupt.
> I have written a little programm which does some selects to this database.
> The program is stopped during recovery but our customer beleves that this
> has something to do with our program.
> http://support.microsoft.com/defaul...kb;en-us;272683
> This article doesnt halp me much. So hwat is the meaning of IN LOAD and
what
> does the server do if it bypasses the recovery.
> THX
> Jens

RESTORE DATABASE EfW_765 FROM ... WITH RECOVERY

Simon

Sunday, February 12, 2012

Business intelligence studio - Integration services project problem

Hi Guys,

I'm trying to create an Integration services project from Business intelligence studio but i can't go further then this:
Could not load file or assembly "Microsoft.DataTransformationServices.Wizard" or one of its dependencies.

I have SQL Server 2005 Enterprise edition and Integration services service is started. I tried stopping the service and restarting it but it doesn't affect anything.

Can any of the clever guys of IT please help me.

Thank you

Gemma

hi,

have you tried to repair your installation of SQL client tools?

regards,

Janos

|||

Hi there,

Sorry for the late reply but i tried it and it doesn't work. I also tried uninstalling components and then tried to install them again, didn't work.

Any other clever ideas as I have to finish this thing soon.

Thank you

Gemma

|||

Check if you are administrator of your machine, or if you have all the permissions.

Try to change the user that are running the SQL services.

regards!

|||

Hi Pedro

I have tried both and it doesn't work. Any other things which i can check before i ask my manger to send me on SSIS course.

Thank you

Gemma

|||

Try another machine..

And install it with Administrator

:-(

Friday, February 10, 2012

BulkLoad and .xsd problem

I would like to load a xml file to a database. For reasons this I use the
BulkLoad
COM object model. The elements of the project are following:
The XML file:
--
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
---
ceg_id char(10)
rovat_id varchar(10)
alrovat_id varchar(10)
mezo_id varchar(50)
mezo_text varchar(1000)
The following xsd file has been used:
--
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 0 1 bir piros
0000000147 0 1 cf tarka
0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
0000000153 etc.
But I've got this:
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 Null Null Null Null
0000000153 Null Null Null Null
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. AttilaYou have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ---
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>|||Thanks, but I have tried it. I get the following error:
.....constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:

> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
>
rights.
the
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
maxOccurs="unbounded">
sql:field="alrovat_id"
/>
> ----
> ----
that
no|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:

> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>

BulkLoad and .xsd problem

I would like to load a xml file to a database. For reasons this I use the
BulkLoad
COM object model. The elements of the project are following:
The XML file:
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
ceg_idchar(10)
rovat_idvarchar(10)
alrovat_idvarchar(10)
mezo_idvarchar(50)
mezo_textvarchar(1000)
The following xsd file has been used:
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_idrovat_idalrovat_idmezo_idmezo_text
000000014701birpiros
000000014701cftarka
000000014721nevFA-MAG Ipari Kisszovetkezet
0000000153etc.
But I've got this:
ceg_idrovat_idalrovat_idmezo_idmezo_text
0000000147NullNullNullNull
0000000153NullNullNullNull
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. Attila
You have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>
|||Thanks, but I have tried it. I get the following error:
......constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:

> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>
|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...[vbcol=seagreen]
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
rights.[vbcol=seagreen]
the[vbcol=seagreen]
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">[vbcol=seagreen]
maxOccurs="unbounded">[vbcol=seagreen]
sql:field="alrovat_id"[vbcol=seagreen]
/>[vbcol=seagreen]
> ----
> ----
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:

> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>

Bulkadmin role (BULK INSERT)

Hello,

I am trying to load a simple tab-delimited data file to SQL Server. I
created a format file to go with it, since the data file differs from
the destination table in number of columns.

When I execute the query, I get an error saying that only sysadmin or
bulkadmin roles are allowed to use the BULK INSERT statement. So, I
proceeded with the Enterprise Manager to grant myself those roles.
However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?

2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.

3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?

Thanks!> So, I proceeded with the Enterprise Manager to grant myself those
> roles. However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?

The roles are there but you need to be a sysadmin role member or a member of
that fixed server role in order to add members. Ask your DBA to do this.

> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.

The main security implication is that BULK INSERT accesses external data
under the security context of the SQL Server service account rather than the
invoking user's account.

> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?

Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
BCP access data under the security context of the invoking user.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0406052020.16b6b4e6@.posting.google.c om...
> Hello,
> I am trying to load a simple tab-delimited data file to SQL Server. I
> created a format file to go with it, since the data file differs from
> the destination table in number of columns.
> When I execute the query, I get an error saying that only sysadmin or
> bulkadmin roles are allowed to use the BULK INSERT statement. So, I
> proceeded with the Enterprise Manager to grant myself those roles.
> However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?
> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.
> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?
> Thanks!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<bRGwc.3644$uX2.3489@.newsread2.news.pas.earthlink.n et>...
> > So, I proceeded with the Enterprise Manager to grant myself those
> > roles. However, I could not find sysadmin or bulkadmin roles using the
> > Enterprise Manager. From what I read from my books, I thought these
> > were fixed server roles and that they would be there.
> > So I have a few questions:
> > 1) How do I create a user account/role that can issue BULK INSERT
> > commands?
> The roles are there but you need to be a sysadmin role member or a member of
> that fixed server role in order to add members. Ask your DBA to do this.

Hello Dan,

This was for personal use, so that makes me the DBA. I believe I
disabled the "sa" account when I first installed SQL Server (based on
some suggestions due to security risks). Perhaps that has something
to do with it. I will look into it.

> Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
> BCP access data under the security context of the invoking user.

Thanks! This clarifies the risk implications of BULK INSERT vs. bcp
that was not in the books. It looks like Bcp is the sure way to go
for most users.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

Wednesday, March 7, 2012

Cache Dependency Problem

Friday, February 24, 2012

C# code or document for loading excel sheet into an sql table

Thursday, February 16, 2012

Bypassing recovery for database 'mydb' because it is makred IN LOAD

Bypassing recovery for database 'mydb' because it is makred IN LOAD

Bypassing recovery for database 'mydb' because it is makred IN LOAD

Bypassing recovery for database

Sunday, February 12, 2012

Business intelligence studio - Integration services project problem

Friday, February 10, 2012

BulkLoad and .xsd problem

BulkLoad and .xsd problem

Bulkadmin role (BULK INSERT)

Bulkload XML Code

Blog Archive

About Me