bcp to import data into a partitioned view

I created a partitioned view as
CREATE VIEW empMaster AS
SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2
(where emp1 and emp2 have check constraints on their primary key)
I tried to run bcp to import data into this view.
i get an error message :"Partitioned view 'empMaster' is not updatable as
the target of a bulk operation."
(iam able to do a simple INSERT into this view though)
Does this mean that a bulk operation to a partitioned view is not possible ?
thanks,
Vivek T S
Member Technical Staff (Inucom)
Vivek,
I believe that is correct. You will have to split your bulk import data and
send it to the correct tables. You also should read the following article:
http://support.microsoft.com/default...b;en-us;286134
Russell Fields
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:5FF78194-3CC7-4BA5-8886-3F036A9C99C4@.microsoft.com...
> I created a partitioned view as
> CREATE VIEW empMaster AS
> SELECT * FROM emp1
> UNION ALL
> SELECT * FROM emp2
> (where emp1 and emp2 have check constraints on their primary key)
> I tried to run bcp to import data into this view.
> i get an error message :"Partitioned view 'empMaster' is not updatable as
> the target of a bulk operation."
> (iam able to do a simple INSERT into this view though)
> Does this mean that a bulk operation to a partitioned view is not possible
?
> thanks,
> --
> Vivek T S
> Member Technical Staff (Inucom)

bcp to import data into a partitioned view

I created a partitioned view as
CREATE VIEW empMaster AS
SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2
(where emp1 and emp2 have check constraints on their primary key)
I tried to run bcp to import data into this view.
i get an error message :"Partitioned view 'empMaster' is not updatable as
the target of a bulk operation."
(iam able to do a simple INSERT into this view though)
Does this mean that a bulk operation to a partitioned view is not possible ?
thanks,
--
Vivek T S
Member Technical Staff (Inucom)Vivek,
I believe that is correct. You will have to split your bulk import data and
send it to the correct tables. You also should read the following article:
http://support.microsoft.com/defaul...kb;en-us;286134
Russell Fields
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:5FF78194-3CC7-4BA5-8886-3F036A9C99C4@.microsoft.com...
> I created a partitioned view as
> CREATE VIEW empMaster AS
> SELECT * FROM emp1
> UNION ALL
> SELECT * FROM emp2
> (where emp1 and emp2 have check constraints on their primary key)
> I tried to run bcp to import data into this view.
> i get an error message :"Partitioned view 'empMaster' is not updatable as
> the target of a bulk operation."
> (iam able to do a simple INSERT into this view though)
> Does this mean that a bulk operation to a partitioned view is not possible
?
> thanks,
> --
> Vivek T S
> Member Technical Staff (Inucom)sql

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advance
bcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance
|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegr oups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>
|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

bcp out..

I need to provide a user with the ability to export the
data from 1 view into a .csv file at any time he needs to
see the data.
I have tried using a commmand line bcp out, but the data
does not come out in a format that is highly useable to
the user.
How can I make it so that bcp out exports to the .csv in
format that it exists in the database ie. distinct
columns and rows.
Also, what does the user need to bcp out data? Is select
permissions for his windows account enough?
Thanks,
MariaMaria
Ex
bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -
above command will create a file with extension XLS but, in fact its a tab delimited flat file since it has extension XLS by default it will get opened in EXCEL and tab character is considered as a column delimiter. I've put TAB as a column delimiter assuming this character is not used in any of the varchar/char field. because if you are including such a character which exists in the varchar/char datatype whole format will get disturbed. comma seperated file/CSV file is a good example for this. Also point to be noted that if any of the char/varchar field contains TAB character even this file's format will get disturbed.So basically you have to make sure that the varchar columns for which you are exporting the data should not contain the character that is being used as a field seperator otherwise it will lead to
you can also run above command line using xp_cmdshell and thus can be used in T-SQL script
E
exec master..xp_cmdshell 'bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -P
Also refer to following UR
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b30612
>Also, what does the user need to bcp out data? Is select
>permissions for his windows account enough
Yes,
- Visha|||You could also connect to the database via ODBC and an
Excel spreadsheet. Open Excel, go to data/get external
data/new dB query. connect using the ODBC setting and
enter the query needed. The user will then have the option
of refreshing the data at their leisure and can
automatically save it as an CSV file.
>--Original Message--
>I need to provide a user with the ability to export the
>data from 1 view into a .csv file at any time he needs to
>see the data.
>I have tried using a commmand line bcp out, but the data
>does not come out in a format that is highly useable to
>the user.
>How can I make it so that bcp out exports to the .csv in
>format that it exists in the database ie. distinct
>columns and rows.
>Also, what does the user need to bcp out data? Is select
>permissions for his windows account enough?
>Thanks,
>Maria
>.
>

bcp into view with derived column

Hi,

I have a view that looks something like this -

CREATE VIEW myview AS SELECT
myudf(col1) as col1, col2, col3
FROM mytable

The view has an 'INSTEAD OF' trigger on it to do the correct thing on
insert. When I run the bcp, it runs successfully, but the value
inserted into col1 is always NULL. I modified my trigger to get a
better idea of what was happening, and when I look at the values of
INSERTED.col1, they are all null. It appears that bcp is setting the
column value to null, presumable because the view definintion for this
column is a derived column.

Does any one know a way around this?

Thanks!On 10 Jan 2006 11:28:32 -0800, bsandell@.gmail.com wrote:

>Hi,
>I have a view that looks something like this -
>CREATE VIEW myview AS SELECT
> myudf(col1) as col1, col2, col3
>FROM mytable
>The view has an 'INSTEAD OF' trigger on it to do the correct thing on
>insert. When I run the bcp, it runs successfully, but the value
>inserted into col1 is always NULL. I modified my trigger to get a
>better idea of what was happening, and when I look at the values of
>INSERTED.col1, they are all null. It appears that bcp is setting the
>column value to null, presumable because the view definintion for this
>column is a derived column.
>Does any one know a way around this?
>Thanks!

Hi bsandell,

By default, triggers are not fired for bulk copy statements.

To override this default, add the option
-h "FIRE_TRIGGERS"

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
being executed. I have added the following line to my trigger code -

SELECT inserted.* into pubs..dummy from inserted

My input file looks like this -
aaa,aaa,aaa
bbb,bbb,bbb
ccc,ccc,ccc

but when the code above runs in my trigger, the output is -
NULL,aaa,aaa
NULL,bbb,bbb
NULL,ccc,ccc

so it looks like bcp has somehow determined that the first column in my
view does not have a 1-to-1 mapping with a column on the database
(because it's based on a udf) and set it to null? I'd like to find a
way to work around this if possible, without changing the view
definition or the underlying table.

Any ideas?

Thanks,
Bruce|||(bsandell@.gmail.com) writes:
> Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
> being executed. I have added the following line to my trigger code -
> SELECT inserted.* into pubs..dummy from inserted
> My input file looks like this -
> aaa,aaa,aaa
> bbb,bbb,bbb
> ccc,ccc,ccc
> but when the code above runs in my trigger, the output is -
> NULL,aaa,aaa
> NULL,bbb,bbb
> NULL,ccc,ccc
> so it looks like bcp has somehow determined that the first column in my
> view does not have a 1-to-1 mapping with a column on the database
> (because it's based on a udf) and set it to null? I'd like to find a
> way to work around this if possible, without changing the view
> definition or the underlying table.

Smells bug to me. To wit, it works with BULK INSERT, which uses OLE DB
in difference of BCP which uses ODBC. The behaviour is the same in
SQL 2000 and SQL 2005, so I've field a bug for it,
http://lab.msdn.microsoft.com/Produ...ackId=FDBK43711
You can vote for it, if you like.

The repro below is also in the bug report:

USE tempdb
go
CREATE TABLE sandell (col1 int NULL,
col2 int NULL,
col3 int NULL)
go
CREATE FUNCTION myudf(@.c int) RETURNS int AS
BEGIN
RETURN (SELECT 80 - @.c)
END
go
CREATE VIEW sandell_vy AS
SELECT col1 = dbo.myudf(col1), col2, col3
FROM sandell
go
CREATE TRIGGER sandell_tri ON sandell_vy INSTEAD OF INSERT AS
INSERT sandell(col1, col2, col3)
SELECT 80 - col1, col2 + 20, col3 + 20 FROM inserted
go
INSERT sandell_vy (col1, col2, col3)
SELECT 12, 98, 23
go
SELECT * FROM sandell_vy
go
EXEC master..xp_cmdshell 'ECHO 9, 2, 98 > C:\TEMP\bulk.csv', 'no_output'
EXEC master..xp_cmdshell 'ECHO 76, 23, 87 >> C:\TEMP\bulk.csv', 'no_output'
go
BULK INSERT sandell_vy FROM 'C:\temp\bulk.csv'
WITH (FIELDTERMINATOR = ',',
DATAFILETYPE = 'char',
FIRE_TRIGGERS)
go
DECLARE @.bcp nvarchar(4000)
SELECT @.bcp = 'bcp tempdb..sandell_vy in C:\temp\bulk.csv -T -c -t, -h "FIRE_TRIGGERS" -S ' + @.@.servername
EXEC master..xp_cmdshell @.bcp
go
SELECT * FROM sandell_vy
go
DROP VIEW sandell_vy
DROP FUNCTION myudf
DROP TABLE sandell
EXEC master..xp_cmdshell 'DEL C:\temp\bulk.csv', 'no_output'

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

As always, I really appreciate your help. Any guesses on the liklihood
that this would get fixed? I didn't think there was much going on with
bcp support these days. Thanks again for you time and effort.

Bruce|||(bsandell@.gmail.com) writes:
> As always, I really appreciate your help. Any guesses on the liklihood
> that this would get fixed? I didn't think there was much going on with
> bcp support these days. Thanks again for you time and effort.

I would guess that it is more likely that it will be fixed in SQL 2005 than
in SQL 2000. Whether the fix will come in SP1 for SQL 2005, I don't want to
speculate in.

If this is critical for you, and you need a hotfix, you will have to open
a case with Microsoft, and try to convince them.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP blank header line

I am using BCP to export the contents of a view into a text file. Everything is running jsut ifne, except the resulting file has a blank first line. Is there a way to preven this?

BCP statement is as follows:

bcp "select pospay from son_db.dbo.vw_pos_pay order by account desc, code asc" queryout D:\elite\USbank\PositivePay_Current\x340.d150364i. d100.txt -T -c

Thanks in advance for any help!probably the first row is a NULL. is the pospay column nullable? if so you can add a where clause to your query to omit the null.|||You can take help of :-F first_row Specifies the number of the first row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. In the absence of this parameter, the default is the first row of the file.|||that only matters for importing data, Satya. the OP is exporting.|||Jezemine
Look at the BOL entry I've pasted there: Specifies the number of the first row to export from a table or import from a data file