Help in creating xsd file

Hello everybody,
I have following XML and i do want to create xsd file to BulkLoad data
into SQL SERVER table, so pls help me how can i create xsd file out of
following XML file...
<PTLV_PROJ>
<PROJECT>
<PID>10649280</PID>
<PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
<ACTIVITIES>
<ACT>
<AID>0000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>10000</AID>
<ADESC>General test</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
<PROJECT>
<PID>10649489</PID>
<PDESC>AREA KNOWLEDGE NETWORK</PDESC>
<ACTIVITIES>
<ACT>
<AID>20000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>3001</AID>
<ADESC>AWS CHAMPION</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
</PTLV_PROJ>Hi
You post may be better answered in microsoft.public.sqlserver.xml
Check out the following on how to use updategrams
http://msdn.microsoft.com/library/d...
egram_375f.asp
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...ations_0gqb.asp
John
"mvp" wrote:

> Hello everybody,
> I have following XML and i do want to create xsd file to BulkLoad data
> into SQL SERVER table, so pls help me how can i create xsd file out of
> following XML file...
>
> <PTLV_PROJ>
> <PROJECT>
> <PID>10649280</PID>
> <PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>0000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>10000</AID>
> <ADESC>General test</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> <PROJECT>
> <PID>10649489</PID>
> <PDESC>AREA KNOWLEDGE NETWORK</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>20000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>3001</AID>
> <ADESC>AWS CHAMPION</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> </PTLV_PROJ>

help in bulk load - first time user

Hi all,
I've a xml file like this:
<ROOT>
<customerlist>
<Customers sequence="1">
<details>
<PersonID>1</PersonID>
</details>
<name>
<LastName>Vinod</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
<Customers sequence="2">
<details>
<PersonID>2</PersonID>
</details>
<name>
<LastName>Saravana</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
</customerlist>
</ROOT>
how should I write an xsd file to store the data in the following sql server table (Person) with columns:
personId - primary key, LastName and FirstName.
Thanks.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:exDw0UcdEHA.320@.TK2MSFTNGP10.phx.gbl...
[snip]
> how should I write an xsd file to store the data in the following sql
> server table (Person) with columns:
I would start with the examples given in the SQLXML documentation. Look at
how the XSD is mapped to the XML. That is the best way to learn.
Bryant

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.
Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into sql server 2000 relational tables. The database has simple recovery model.

The total size of data to be load is about 1gb (each file)

source:

Multiple source files with different layout.

Destination:

3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3 column unique index (alternate key) and a detail table that has a DETAIL_ID (IDENTITY) primary key and a foreign key ID to the parent table.

So, for each source file, I need to convert some columns to decimal and separate the data into parent and detail tables.

Here is one way to do this:

For each source file

* use a thread for each file with Microsoft Visual Studio .NET 2003

* run insert query with SP's por each record, and join parent table with details.

* process next record

My app works fine in XP and SQL express, When I run the queries the CPU utilization is consitantly around 90%. This is my development environment.

My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.

When I run the queries the CPU utilization is consitantly around 3%.

I have 2 Questions:

1- Is there a better way to do this?

2- Why XP has utilization around 90%, and Win2003 3%?

Speed is the primary concern.

Thank you for any suggestions.

Using SPs calls is not the fastest way. The quickest way is to generate files including the identity values and then bcp/.bulk insert the data into SQL. You could use SSIS to do this as well.

The reason you should generate you ID values outside of the database is so that when spliting your input file into your tables you can just insert the data straight into the tables. You can let SQL generate the PK identity values of the detail tables.

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp
1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a
3
> column unique index (alternate key) and a detail table that has a DETAIL_I
D
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition
Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

help importing large flat file into relational tables

I am trying to find the best (fastest) way to import large text files into
sql server 2005 relational tables. The database has simple recovery model.
The total size of data to be load is about 1gb (each file)
source:
Multiple source files with different layout.
Destination:
3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
column unique index (alternate key) and a detail table that has a DETAIL_ID
(IDENTITY) primary key and a foreign key ID to the parent table.
So, for each source file, I need to convert some columns to decimal and
separate the data into parent and detail tables.
Here is one way to do this:
For each source file
* use a thread for each file with Microsoft Visual Studio .NET 2003
* run insert query with SP's por each record, and join parent table with
details.
* process next record
My app works fine in XP and SQL express, When I run the queries the CPU
utilization is consitantly around 90%. This is my development environment.
My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
When I run the queries the CPU utilization is consitantly around 3%.
I have 2 Questions:
1- Is there a better way to do this?
2- Why XP has utilization around 90%, and Win2003 3%?
Speed is the primary concern.
Thank you for any suggestions.Macisu wrote:
> I am trying to find the best (fastest) way to import large text files into
> sql server 2005 relational tables. The database has simple recovery model.
> The total size of data to be load is about 1gb (each file)
> source:
> Multiple source files with different layout.
>
> Destination:
> 3 sql server tables; a parent table with a ID (IDENTITY) primary key and a 3
> column unique index (alternate key) and a detail table that has a DETAIL_ID
> (IDENTITY) primary key and a foreign key ID to the parent table.
>
> So, for each source file, I need to convert some columns to decimal and
> separate the data into parent and detail tables.
> Here is one way to do this:
> For each source file
> * use a thread for each file with Microsoft Visual Studio .NET 2003
> * run insert query with SP's por each record, and join parent table with
> details.
> * process next record
> My app works fine in XP and SQL express, When I run the queries the CPU
> utilization is consitantly around 90%. This is my development environment.
> My production enviroment is with SQL 2005 and Win 2003 Enterprise Edition Sp1.
> When I run the queries the CPU utilization is consitantly around 3%.
> I have 2 Questions:
> 1- Is there a better way to do this?
> 2- Why XP has utilization around 90%, and Win2003 3%?
>
> Speed is the primary concern.
> Thank you for any suggestions.
SQL Server Integration Services is the most obvious solution to try.
Read about Integration Services in Books Online. Depending on the
format of your files BCP may also be an option.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Help if possible

I have very limited skill in Access, but am working on a project for work. I have a database file that includes fields like item number and ticket number. There are currently identical records for both of these fields. I'm trying to create a query that dislplays a specific item only if it has the same ticket number as any other item.

I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql

Help How Make Backup and Restore ?

Hi all. I'm newbie user of sqlserver 7.

I need make Backup from specific tables of my database and my application send this file by mail to other users that restore this information in yours Database.

How i can do it (i dont know the specifics commands to backup and Restore)

Other question

if I only backup the log file and restores this in other database
I'll have the same information in all users ?

Help me please

Thanks in AdvanceCheck BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.|||Check BOL on the syntax. You can place your specific tables on filegroups and have your users restore those filegroups after initial full db restore.

Excuse me, but i don't understand what is BOL. ?

How i said i'm newbie.|||Books
On
Line|||Originally posted by rdjabarov
Books
On
Line

Thanks, I will Try.|||I connect like database admin and

try to make differential backup using this command

1- Back up Database Cyber to XXX With Differential

2- Back up Log Cyber to XXX

The to test the back up

I run then this restore command

1- Restore database Cyber from XXX with norecovery and

2- Restore Log Cyber from XXX with recovery.

All is perfect

I close the connection

When I try to connect to Cyber database the server send me this message

Server Msg 927, Level 14 State 2

Database Cyber Cannot Be opened. It is in the middle of restore ?

Why the database cannot be opened ?

Thank In advance

Franklin|||Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.|||I'm trying to open from query analizer, because my application must be do it (the backup - restore) using transaq - sql.

thanks
franklin

Originally posted by rdjabarov
Are you trying to open it from Enterprise Manager? I suspect that if you right-mouse click on Databases folder and select Refresh, - you won't get this error.sql

Help getting error when using operation rename a file in the File system task editor?

Does anyone know how to do this using variables? Everytime I try it, I get the

Error: Failed to lock variable for read access with error 0xc00100001.

I also tried it writing a script and still the same error. If I hard code the values into the variables it works fine but I will be running this everday so that it will pull in the current date along with the filename. So the value of the variables will change everyday. Here is my expression:

@.[User::Variable] +(DT_WSTR,4) YEAR( GETDATE() )+"0"+(DT_WSTR,2) MONTH( GETDATE() ) + (DT_WSTR,2) DAY( GETDATE() )

The result:

C:\Documents and Settings\mroush\Desktop\OSU20060818

the 20060818 part will change everyday ie.(tomorrow will be 20060819, next day 20060820 and so on.)

I am not sure how you are doing this, were is the expression and which variable is selected in the File System Task editor.

How about this-

Create a variable called Filename.

Then go to the Properties grid for the Filename variable and set the EvaluateAsExpression to true. Now set the Expression property to your expression.

Now go back to the File System Task, and select variable Filename for the operation you want.

Is this what you have done or not?

Do you really need to rename the file? Normally I would use an Expression, like you have above, but on the ConnectionString property for my File/FlatFile connection, such that when I use the connection it automatically gets the "current" filename.

|||

Darren, thanks for the reply and

yes I done this in the expression property of the variable and set it to true, that works except it adds the file name twice. The result is below.

C:\Documents and Settings\mroush\Desktop\Cnet Apps File\Apps0821.txt0821.txt

How do I get it to only evaluate once and and it looks like everyday that it runs, it is going to just keep adding to the string? Is there away to make it clear the old filename before renaming the current one everyday or is there another way to do this? I have to rename the file with the current date everyday, instead of using 1 filename and setting the overwrite destination to true. Maybe this can't be done using this task?

Mike

|||

I don't get why there appears to be two filename parts. You have got two variables, one for the base path an then one for the full filename, upon which you set the expression?

Creating a new file everyday should be fine using an expression. If you are exporting thsi file, then why not set the expression on the flat file connection manager's connection string property, and save the extra step of the rename.

|||

Hi Darren, thanks again for responding.

Here are the two files that I am working with:

The file comes in everyday and is zipped as AAA20060822 with no extension. The numbers are the date and it changes everyday with the current date so I need the expression to get that date everyday when a new file appears. Like tomorrows file will be AAA20060823

The filename that I need to rename it as is BBBB0822.txt. It has to be renamed this way with the date format this way. I'm using 2 variables so that the dates are updated everyday automatically so that the task can be scheduled nightly. I know this is confusing but this is how the file comes in everday and this is how it needs to be renamed so the data can be processed correctly through our mainframe.

I've tried setting the isdestination and issourcepath variables to true and using the expressions in the source and destination properties through the expressions option but it errors telling me that it can't lock the variables or the variables have been renamed or do not exist in the container. Error 0xC00100001

So then I tried putting the expressions in the variables expression property and setting the evaluateasexpression property to true but it evaluates the expression twice each time I run the task adding the dates twice to the path causing it not to find the source file. I'm stupmed! I don't know what else to do? I works either way without an expression hard coding the filenames but not using variables that will change everday with the date?

Thanks,

mike

help for shrinking database

Hi guys.

I have a huge database file. When I run sp_spaceused this are the
results:

db_size 1337,31 Mb
unallocated 14,62 Mb
reserved 1088456 Kb
data 258992 Kb
index_size 6224 Kb
unused 823240 Kb

Now, when I run dbcc shrinkfile(database_name, 50) or whatever value of
percent, it says there is not enough free space in db.

Please advice how to shrink the size of that file and why is it so big?

Thanks in advance

Zvonkowhat about initially shrinking the log , and then the data file?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Zvonko" <zvonko_NOSPAM_@.velkat.net> wrote in message
news:449799a2@.ns1.novi-net.net...
> Hi guys.
> I have a huge database file. When I run sp_spaceused this are the
> results:
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
of
> percent, it says there is not enough free space in db.
> Please advice how to shrink the size of that file and why is it so
big?
> Thanks in advance
> Zvonko|||Zvonko (zvonko_NOSPAM_@.velkat.net) writes:
> I have a huge database file. When I run sp_spaceused this are the
> results:
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of percent, it says there is not enough free space in db.
> Please advice how to shrink the size of that file and why is it so big?

Big? 1.3 GB is a small database by today's stanadards, at least for a
production system.

The fact that the amount of "unused" is so much larger than data, indicates
that you have one or more tables that suffers from high level of
fragmentation.

This SELECT:

SELECT object_name(id), reserved FROM sysindexes WHERE indid IN (0,1)
ORDER BY reserved DESC

gives you the tables by size. You can then use DBCC SHOWCONTIG to determine
the level of fragmentation, and DBCC DBREINDEX to defragment the tables.
However, you cannot reindex tables that does not have a clustered index
with DBCC DBREINDEX. You can however, create a clustered index on these
tables, and then drop it if you absolutely do not want it.

"reserved" = space actually allocated to tables.
"unused" = space within the reserved space that is not actually used.

DBCC SHRINKFILE operates only with unallocated space. Once you have
defragmented the tables, you may be able to shrink the file.

--
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|||Do not forget to do some backup B4 the operation...
Jack Vamvas wrote:
> what about initially shrinking the log , and then the data file?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Zvonko" <zvonko_NOSPAM_@.velkat.net> wrote in message
> news:449799a2@.ns1.novi-net.net...
> > Hi guys.
> > I have a huge database file. When I run sp_spaceused this are the
> > results:
> > db_size 1337,31 Mb
> > unallocated 14,62 Mb
> > reserved 1088456 Kb
> > data 258992 Kb
> > index_size 6224 Kb
> > unused 823240 Kb
> > Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of
> > percent, it says there is not enough free space in db.
> > Please advice how to shrink the size of that file and why is it so
> big?
> > Thanks in advance
> > Zvonko

Help --FOr Each Loop Container (For each file Enumerator) SSIS package

Can anyone help me with FOr Each Loop Container (For each file
Enumerator) control flow SSIS package
If I use FOr Each Loop Container (For each file Enumerator), it will
select all the files in that folder. What if I want to select just 100
files (assuming 500 files in the folder)
How do i do this?
Thanks
Hi
I am not a SSIS expert, but a for loop can be used with a counter
http://www.sqlis.com/default.aspx?310 but I am not sure how this could be
done with a foreach loop. I guess you could have an activeX task that checks
a variable and returns failure when the counter is exceeded or increments it
and returns success when not.
John
"Vishal" wrote:

> Can anyone help me with FOr Each Loop Container (For each file
> Enumerator) control flow SSIS package
> If I use FOr Each Loop Container (For each file Enumerator), it will
> select all the files in that folder. What if I want to select just 100
> files (assuming 500 files in the folder)
> How do i do this?
> Thanks
>

Help --FOr Each Loop Container (For each file Enumerator) SSIS package

Can anyone help me with FOr Each Loop Container (For each file
Enumerator) control flow SSIS package
If I use FOr Each Loop Container (For each file Enumerator), it will
select all the files in that folder. What if I want to select just 100
files (assuming 500 files in the folder)
How do i do this'
ThanksHi
I am not a SSIS expert, but a for loop can be used with a counter
http://www.sqlis.com/default.aspx?310 but I am not sure how this could be
done with a foreach loop. I guess you could have an activeX task that checks
a variable and returns failure when the counter is exceeded or increments it
and returns success when not.
John
"Vishal" wrote:
> Can anyone help me with FOr Each Loop Container (For each file
> Enumerator) control flow SSIS package
> If I use FOr Each Loop Container (For each file Enumerator), it will
> select all the files in that folder. What if I want to select just 100
> files (assuming 500 files in the folder)
> How do i do this'
> Thanks
>

Help for bcp command for export data to txt file

I have follow the syntax to export table to .txt file like this.

bcp UL.dbo.CFP OUT C:\a.txt -T -c -S NOTEBOOK-F47731

But it always show error which is:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I have try it many time. Can anyone help me to solve this problem?

Sorry if this is a simplistic answer, but have you tried putting single quotes around your query like so...

'bcp UL.dbo.CFP OUT C:\a.txt -T -c -S NOTEBOOK-F47731'

Here's how I'm using bcp at the moment.

USE MYDATABASE
GO

DECLARE @.LINE_ITEM_DATA SYSNAME
SET @.LINE_ITEM_DATA = 'bcp MYDATABASE.LINE_ITEM_DETAIL OUT c:\LineItemData.txt -T -c -t ","'
EXEC XP_CMDSHELL @.LINE_ITEM_DATA, NO_OUTPUT

Hope that helps!

Help for a new user

I've created a DB in SQL Server, and a user 'X', administrator on it. The DB
is genereted by a file, using osql. The tables' owner is 'X', but when I
try to insert some rows into the tables (by osql), the response is:
"INSERT permission denied on object 'DUMMY', database 'EC_PROVA', owner
'X' "

Is there anybody that can help me?
Thank you.
Fedefede (fedina_no_chicca_spam@.libero_spam_no.it) writes:
> I've created a DB in SQL Server, and a user 'X', administrator on it.
> The DB is genereted by a file, using osql. The tables' owner is 'X',
> but when I try to insert some rows into the tables (by osql), the
> response is: "INSERT permission denied on object 'DUMMY', database
> 'EC_PROVA', owner 'X' "

As whom do you run the INSERT statement?

What does "sp_helprotect DUMMY" say?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
> As whom do you run the INSERT statement?
I launch a script using this command line:
osql -U X -P passX -D SQLPROVA -i "Schema20040723.sql" -o "SchemaCrea.log"
So, I think the user is X...
> What does "sp_helprotect DUMMY" say?
It doesn't say anything...|||fede (fedina_no_chicca_spam@.libero_spam_no.it) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
>> As whom do you run the INSERT statement?
> I launch a script using this command line:
> osql -U X -P passX -D SQLPROVA -i "Schema20040723.sql" -o "SchemaCrea.log"
> So, I think the user is X...

But I don't what it's that script. It is difficult to assist when I don't
have full information of what you are doing. If that script performs

CREATE TABLE dummy (a int NOT NULL)
go
INSERT dummy (a) VALUES(12)

This should succeed. So I assume you are doing something else, but I don't
know what.

>> What does "sp_helprotect DUMMY" say?
> It doesn't say anything...

Not even?

Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.

By the way, what does SELECT @.@.version say on your server?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> ha scritto nel messaggio
news:Xns953279F885DYazorman@.127.0.0.1...

> But I don't what it's that script. It is difficult to assist when I don't
> have full information of what you are doing. If that script performs
This is a piece of code in the script:

CREATE TABLE X.DUMMY (
ATTIVITA varchar(30) NOT NULL,
DATA datetime NOT NULL,
UTENTE varchar(30) NOT NULL,
TEMPO datetime NULL,
NOTA varchar(50) NULL,
DBVERS varchar(15) NULL,
EURO varchar(1) NOT NULL,
PRIMARY KEY (DATA, ATTIVITA, UTENTE)
)

go
insert into X.DUMMY (data, attivita, utente, nota, euro, dbvers)
values ('01/01/1999','POTWIN3','PROJECT','NON ELIMINARE MAI!',
'S', '038')

But the problem is not on the script (this script, with obviously
differences is used to create db in DB2 and Oracle), because I've tried to
launch it from SQL Query Analyzer and it works well (using as default the
administrator user 'sa').
The problem seems to be the authorizations for user X, used to create the
schema of the DB, created as administrator in SQL Server. When I look the
properties of the table, it seems that X has the rights to insert, delete
and update data in the table.

> By the way, what does SELECT @.@.version say on your server?

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)

Help Files / Metadata on Reports

I had a pervious thread on a metadata KB but didn't get a response. So I was wondering if anyone has added on a web based help file app to SSRS? Something that you could assign additional metadata to each report. Something that would be searchable.

At this point SSRS doesn't provide enough fields for metadata. I am looking to assign some thing like:

1. Owner/Requestor of report

2. Fields in report

3. Description (other than the one provided)

4. etc..

Does anyone have any ideas of a add on, utility, or software?

Thanks

fsugeiger

Hmmm...I'm not sure I understand what you're asking for, but couldn't you create a report on reports?

In other words, store in your database information about reports, then create a report out of it.

|||Yes, i thought of that but I would have to create a table of report fields and parameters. I didn't want to have to maintain something seperate. i wanted something to read the rdl file and list fields and parameters.

Help Files / Metadata on Reports

I had a pervious thread on a metadata KB but didn't get a response. So I was wondering if anyone has added on a web based help file app to SSRS? Something that you could assign additional metadata to each report. Something that would be searchable.

At this point SSRS doesn't provide enough fields for metadata. I am looking to assign some thing like:

1. Owner/Requestor of report

2. Fields in report

3. Description (other than the one provided)

4. etc..

Does anyone have any ideas of a add on, utility, or software?

Thanks

fsugeiger

Hmmm...I'm not sure I understand what you're asking for, but couldn't you create a report on reports?

In other words, store in your database information about reports, then create a report out of it.

|||Yes, i thought of that but I would have to create a table of report fields and parameters. I didn't want to have to maintain something seperate. i wanted something to read the rdl file and list fields and parameters.sql

Help fileof Transaction SQL 2005

Hi,

May I get the help file of Transaction SQL 2005?

Please send it to jemmy@.sentonius.com

Thank you.

Kind Regards,

Jemmy Sentonius

You can downlaod the same from

http://www.microsoft.com/downloads/details.aspx?familyid=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en

Help exporting data from .csv file to Sql server

Hi i am trying to export data from .csv file to sql server and my data is coming as

"xyz" where i want only to store as XYZ i am using derived column but i am not able to capture " and replace it what approach should i take here , also i am trying to convert String True to Boolean 1 and vice versa for False in database how do i do that please help me with this.

Look at the REPLACE function. Also, aren't you specifying (") as the text qualifier in your file setup?

As for converting the string True/False to boolean, you can use conditionals in a derived column. [Column] = "True" ? 1 : 0

You might just be able to cast the column, though I'm not sure. You'll have to test that out.

|||Big Thanks for the Help ... it works .|||

I am trying to store the value of True or False in a bit field in sql server which will be 1 for true and 0 false .. i am confused as to whether i need to cast it before storing it . please guide me.

From the Data Viewer i can see the Values changing to 1 and 0 but how do i store them as bit datatype in sql server

|||

Dev2624 wrote:

I am trying to store the value of True or False in a bit field in sql server which will be 1 for true and 0 false .. i am confused as to whether i need to cast it before storing it . please guide me.

From the Data Viewer i can see the Values changing to 1 and 0 but how do i store them as bit datatype in sql server

It should just work. If the values are 1s and 0s. Have you tried it!?|||I tried and it is storing as True in the column value but not as 1 in sql server , i am not aware of how exactly bit datatype works|||

Dev2624 wrote:

I tried and it is storing as True in the column value but not as 1 in sql server , i am not aware of how exactly bit datatype works

If the column's data type is "bit" it isn't storing as "True" or "False." However you're verifying the results, it's that application that's *presenting* the data as True/False. The underlying data is stored as 0 or 1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1079022&SiteID=1|||It was a big help Thanks!!!

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

sql

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit