Create table from text file, extract data, create new table from extracted data.

Hello all,

Please help...

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem...

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.select *
INTO ExtractedDataFile
from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%'|||Thanks so much Brett!

I have more question though......

I will be needing to do the importation of text file & extraction of data at least once a month. Then after I import & extract data I will need to append the extracted data into the table ExtractedDataFile. But I will only need to append the data if there is no duplicate DF_SC_Case_Nbr.

Can all this be done in just one stored procedure? How will I do this?

Thanks again.|||What's a duplicate?

Do you have books online?

Open it up, and leave it open...

Look into bcp

Create table from Text

Hi, all. I'm fairly new to SQL, and I have been trying to create a table
from a text file. I have been looking at this for days, and can't find the
problem. I get a syntax error " Line 55: Incorrect syntax near
'DateUpdated'." Here is the query. Any suggestions would be appreciated,
as I am trying to learn and improve.

Use ACH
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[ImportFiles]
GO

CREATE Procedure ImportFiles
@.FilePath varchar(1000),
@.MergeProc varchar(128) = 'MergeData'
AS
DECLARE @.cmd varchar(2000),
@.Command_String varchar(3000)

DECLARE @.FileName varchar(1000),
@.File varchar(1000)

CREATE table ##Import (datarow varchar(200))
CREATE table #Dir (datarow varchar(200))

DROP TABLE ACHParticipants

select @.cmd = 'dir /B' + @.FilePath
delete #Dir
insert #Dir exec master..xp_cmdshell @.cmd

delete #Dir where datarow is null or datarow like '%not found%'

while exists (select * from #Dir)

BEGIN
select @.FileName = min(datarow) from #Dir
select @.file= @.FilePath + @.FileName
select @.cmd = 'bulk insert'
select @.cmd = @.cmd + ' ##Import'
select @.cmd = @.cmd + ' from'
select @.cmd = @.cmd + ' @.File,'
select @.cmd = @.cmd + ' with (FIELDTERMINATOR=''\n'''
select @.cmd = @.cmd + ',ROWTERMINATOR = '':\n'')'

truncate table ##Import

-- import the data
exec (@.cmd)

-- remove filename just imported
delete #Dir where datarow = @.FileName

exec @.MergeProc
END

drop table ##Import
drop table #Dir
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MergeData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeData]
GO

CREATE PROCEDURE MergeData
AS
CREATE table ACHParticipants
(RoutingNum varchar(9),
OfficeCode varchar(1),
ServicingFRBNum varchar(9),
RecordType varchar(1),
ChangeDate varchar(8),
NewRoutingNum varchar(9),
BankName varchar(36),
BankAddress varchar(36),
City varchar(20),
State varchar(2),
Zipcode varchar(10),
Phone varchar(14),
StatusCode varchar(1),
DataView varchar(1),
Filler varchar(5),
DateUpdated datetime)

INSERT INTO ACHParticipants
(Routing_Number
, Office_Code
, Servicing_FRB_Number
, Record_Type_Code
, Change_Date
, New_Routing_Number
, Customer_Name
, Address
, City
, State_Code
, Zipcode
, Telephone
, Institution_Status_Code
, Data_View_Code
, Filler
, DateUpdated)

SELECT Substring(DataRow,1,9) AS RoutingNum,
Substring(DataRow,10,1) AS OfficeCode,
Substring(DataRow,11,9) AS ServicingFRBNum,
Substring(DataRow,20,1) AS RecordType,
convert(datetime,Substring(DataRow,21,6)) AS ChangeDate,
Substring(DataRow,27,9) AS NewRoutingNum,
Substring(DataRow,36,36) AS BankName,
Substring(DataRow,72,36) AS BankAddress,
Substring(DataRow,108,20) AS City,
Substring(DataRow,128,2) AS State,
Substring(DataRow,130,5) + '-' + Substring(DataRow,135,4) AS Zipcode,
Substring(DataRow,139,3) + '-' + Substring(DataRow,142,3) + '-' +
Substring(DataRow,145,4) AS Phone,
Substring(DataRow,149,1) AS StatusCode,
Substring(DataRow,150,1) AS DataView,
Substring(DataRow,151,5) AS Filler
DateUpdated datetime AS DateUpdated
FROM ##Import
GO

Thanks,
KarenThe error is probably because you are missing a comma after "AS
Filler". In general, you should avoid creating permanent tables from
within stored procedures, as it makes it very hard to control your data
model correctly, and if the proc is run multiple times you may have
problems.

A common approach is to create a permanent staging table (instead of
using a temporary one as you are), and bulk load your files into that.
A stored proc can then do the final INSERT into ACHParticipants, after
making any other data changes that might be needed.

You might also want to consider loading the data using bcp.exe instead
of BULK INSERT - it can often be easier to deal with file names etc.
outside the database, in a batch file or a script of some other sort.

Simon|||Thank you, Simon. I have put the comma in, and I am still getting the
error. I am going to try setting up a staging table - thanks again for the
suggestion.

Thank you for the good advice.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1117006217.229228.129070@.g14g2000cwa.googlegr oups.com...
> The error is probably because you are missing a comma after "AS
> Filler". In general, you should avoid creating permanent tables from
> within stored procedures, as it makes it very hard to control your data
> model correctly, and if the proc is run multiple times you may have
> problems.
> A common approach is to create a permanent staging table (instead of
> using a temporary one as you are), and bulk load your files into that.
> A stored proc can then do the final INSERT into ACHParticipants, after
> making any other data changes that might be needed.
> You might also want to consider loading the data using bcp.exe instead
> of BULK INSERT - it can often be easier to deal with file names etc.
> outside the database, in a batch file or a script of some other sort.
> Simon

CREATE TABLE and CONSTRAINTS

well very new to sql,as I am...
I am posting this query,please explain me what it is,

Code: ( text )

1. CREATE TABLE [dbo].[DOORD_ORDER] (

2. [ORDER_NBR] [int] NOT NULL ,

3. [TOTAL_SEQ_NBR] [int] NOT NULL ,

4. [DELIVERY_IND] [char] (1) NOT NULL ,

5. [CONTACT_LST_NME] [varchar] (30) NOT NULL ,

6. [CONTACT_FRST_NME] [varchar] (40) NOT NULL ,

7. [CONTACT_TYP] [varchar] (30) NOT NULL ,

8. [CONTACT_TXT] [varchar] (100) NOT NULL ,

9. [OR_CDE] [char] (1) NOT NULL ,

10. [DROPOFF_DTE] [datetime] NOT NULL ,

11. [PICKUP_DTE] [datetime] NOT NULL ,

12. [LST_OPR_ID] [char] (8) NOT NULL ,

13. [LST_MNT_TSMP] [datetime] NOT NULL ,

14. [WAIT_INSTORE_IND] [char] (1) NOT NULL

15. ) ON [PRIMARY]

16. GO

17. ALTER TABLE [dbo].[DOORD_ORDER] WITH NOCHECK ADD

18. CONSTRAINT [PK_DOORD] PRIMARY KEY CLUSTERED

19. (

20. [ORDER_NBR]

21. ) WITH FILLFACTOR = 90 ON [PRIMARY]

22. GO

Hirak

This query creates a new table called [dbo].[DOORD_ORDER] and then adds a constraint to the table in this case designates the primary key [ORDER_NBR].

Mary|||well mary,is this oracle?

we didnt do like this in oracle,right?
please tell me what type of db query is this
hirak

Quote:

Originally Posted by mmccarthy

Hirak

This query creates a new table called [dbo].[DOORD_ORDER] and then adds a constraint to the table in this case designates the primary key [ORDER_NBR].

Mary

|||

Quote:

Originally Posted by hirak1984

well mary,is this oracle?

we didnt do like this in oracle,right?
please tell me what type of db query is this
hirak

Actually it does look like Oracle to me. It's been a while since I've used it though so I could be wrong. It could also be SQL Server. Sorry Hirak I haven't used either in a few years and they all just blend together now.

Mary|||you need not be sorry mary because you are right.

this is a sqlserver query I found out now.
I am sorry to post it in oracle forum,because then I didnt have an idea,what it was.
I dont have necessary privileges,please transfer it to the sqlserver forum

Quote:

Originally Posted by mmccarthy

Actually it does look like Oracle to me. It's been a while since I've used it though so I could be wrong. It could also be SQL Server. Sorry Hirak I haven't used either in a few years and they all just blend together now.

Mary

create store procedure to incremental populate full text index tab

I need to create a store procedure to perform incremental population of full
text index table. The store procedure will be call from my program.
Any ideas ?
xxx
This is probably what you want.
exec sp_fulltext_table N'[dbo].[authors]', N'start_incremental'
I suggest you have a look at change tracking and schedule the index updates.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:AEAD8DB8-91F6-484A-AD2C-C6AB06E5B38B@.microsoft.com...
>I need to create a store procedure to perform incremental population of
>full
> text index table. The store procedure will be call from my program.
> Any ideas ?
> --
> xxx

create site search using sql server "full text search"

would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...

so i thought create site search table, with the columns:

id, text, url

and to write every thing to this table.

but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...

what do you think?

Full text search is handled by SQL Server itslef (via an external windows service) and is a good solution but you need to go through pros and cons and take care about when to generate the indexes and what columns to index. Refer these articles for details:

http://www.eggheadcafe.com/articles/20010422.asp

http://www.developer.com/db/article.php/3446891

Hope this helps,

Vivek

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?
You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.c om...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...
> > I am creating a job in master server where in one step, it creates
> > stored procedure in target server. The proc text is exceeding the
> > limit to directly paste in job scheduler. What is the best way to push
> > procedure to target servers?

Create Procedure Command

Good morning,
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch
>I would like to pass a table variable... is this possible?
NO
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>

Create Procedure Command

Good morning,
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch>I would like to pass a table variable... is this possible?
NO
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>

Create Procedure Command

Good morning,
The Transact-SQL Reference documentation states:
"All data types, including text, ntext and image, can be used as a parameter
for a stored procedure."
I would like to pass a table variable... is this possible?
Thanks,
FOrch>I would like to pass a table variable... is this possible?
NO
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:1AAA1E85-4F2D-4110-A6D0-81DDE4255694@.microsoft.com...
> Good morning,
> The Transact-SQL Reference documentation states:
> "All data types, including text, ntext and image, can be used as a
> parameter
> for a stored procedure."
> I would like to pass a table variable... is this possible?
> Thanks,
> FOrch
>

create mobile database from xls, csv, text or mdb

Hello,

Since my experience in VS is extremely limited, i'd like to be exused if questions sound silly.

My problem is that, at my device application project, i want to build a database that will retrieve data from .xls, or .csv, or .txt, or .mbd files. If i've noticed well, there is no support for OLEDB or ODBC, since by the time that i add tableadapter to my database.xsd and use these, after insertion i get multiple errors informing that system.data.oledb. .... or .odbc "type is not defined".

If it cannot be done, and since i still try to figure out how smart devices function, transact with databases, e.c.t. is there any suggestion?

Thank you in advance

Kostas

you'll likely need to use sql server mobile.|||

I'am trying to do so with sql mobile.

Going-> right click on "deviceapplication1" -> add -> new item -> sql mobile database

This gives me an empty database. In order to fill it i go at server explorer, right click on "AppDatabase2.sdf" (new created database), -> modify connection -> change (data source field) -> i choose microsoft ODBC data source -> user or system data source names: ms access database -> OK. Then browsing for mdb file (my access databace), and it's in server explorer; no problem yet. Then going to solution explorer, opening "AppDatabase2DataSet.xsd", drug & drop tables from server explorer to "AppDatabase2DataSet.xsd" (making table adapters(?)). Then i get this huge error list that "system.data.odbc...." Type is not defined.

Either i need to add some reference (as i did at VBA) since even autofill doesn't work (I try at the top of code to insert "imports system.data.odbc), or it's just not supported in sql mobile database

|||you can't use access as your datasource. appdatabase2.sdf IS your sql server mobile database, and that needs to remain your datasource. no odbc, no ole db in NETCF. your only mobile database option for .net development is a sql server mobile DB.|||

That is something that i have already understood after some experiment.

The question is how sql mobile works and if i can update the tables from an external (server or just a given path) sourse. I have created an sdb, with the tables required, but this tables are empty, and need to be updated each day from source files of the above mentioned format.

|||I'm not a drag and drop type of guy, but from what you've said, you're trying to create a set of adapters between Access and SQL Server Mobile -- you can't do this, there's no ODBC implementation within NETCF for the CF to talk to Access. I learned yesterday from a posting by Ilya Tumanov that there's a util called AccessSynchronizer for Access<-->SQL Server Mobile synchronization, you may want to look into it. Personally I'd just dump the stuff from Access into XML or CSV, and import using my own code, but that may not be for you. Another option is to wrap your datasource in a web service and proxy the sync operation that way.|||

Thank you Andrew, i'll take a look at accessSynchronizer. If my knowledge and experience wasn't so poor i could ask about the code for importing CSV since this is the format that interest me more, but i guess that it will be rather difficult for me fix.

Thank you very much

|||

I believe opennetcf has a csv importer, you may want to consider that ...

this post is a bit old, but may still be relevant:
http://groups.google.com/group/microsoft.public.dotnet.framework.compactframework/browse_thread/thread/671b1480a962e8e0/92d0ba873b07398d%2392d0ba873b07398d

CREATE FULLTEXT CATALOG inside a user transaction.

hi:

I try to create full text for new created tables.

Since all new created tables will have same columns with different table name.

After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.

But I got error like this:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Any one has idea how to deal with it?

Thanks

This is by design. Full-text catalog cannot be created in nested/embedded transactions.

I dont' think the DDL trigger would work in this case. Perhap, create a store proc that will scan all the tables that do not have full-text index and add them on fly.

Gary

|||

DDL triger not working. I have stored procedure for creating full-text, the SP works when you exec it inside of the Management studio (not being called from other SP, job or triger etc), otherwise, it will return the same error.

anyway, I guess this is the dead end for creating full text on the fly.

Create file using Stored Procedure

I there a way to create a text file from within a stored procedure ?Not directly using T-SQL, but you could use DTS or BCP. See Books Online for
more info. Also check this link: http://vyaskn.tripod.com/code.htm#txtfile
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:u4oRd.667$a96.420@.bignews3.bellsouth.net...
I there a way to create a text file from within a stored procedure ?|||XP_cmdshell and OSQL work great. I just completed a project to do just that.
Hello Rob,

> I there a way to create a text file from within a stored procedure ?
>

Create dynamic reports

Hi,

I receive an xml object that represent just about any kind of a table containing unknown amount of text fields, and need to create a Table report out of it, the header fields are not pre-determined.

Is this possible with Crystal reports and in particular in the .Net free addition of Crystal reports ?

if so - HOW ? please explain as articulately as possible

Thanks
. .
|
~

NimCo.You can try using CDO (Crystal Data Objects) to pass info to Crystal Reports. Basically, you populate an array and pass the array to the report.

Look here for an example of CDO in VB 6, maybe you can adapt it to your needs...

http://www.dev-archive.com/forum/showthread.php?s=&threadid=281658&highlight=cdo

Create DB from Backup

This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C6A76D.C365CB60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi all, is there an easy way to create a Data Base from a Backup file?
I've tried creating the data base and then trying to restore the prevous = backed up data base but I get some errors like "Directory lookup for the = file (the mdf file) failed with the operating system error 3 (The system = cannot find the specified path)"
TIA
-- .seb
http://sgomez.blogspot.com
--=_NextPart_000_000C_01C6A76D.C365CB60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi all, is there an easy way to create = a Data Base from a Backup file?
I've tried creating the data base and = then trying to restore the prevous backed up data base but I get some errors like = "Directory lookup for the file (the mdf file) failed with the operating system = error 3 (The system cannot find the specified path)"

TIA
-- .sebhttp://sgomez.blogspot.com=
--=_NextPart_000_000C_01C6A76D.C365CB60--Do not create the database. Just do a restore. On the Restore database
window select Options and make sure that the Restore As column shows a valid
path and that you have permissions on it.
Ben Nevarez, MCDBA, OCP
Database Administrator
".seb" wrote:
> Hi all, is there an easy way to create a Data Base from a Backup file?
> I've tried creating the data base and then trying to restore the prevous backed up data base but I get some errors like "Directory lookup for the file (the mdf file) failed with the operating system error 3 (The system cannot find the specified path)"
> TIA
> --
> ..seb
> http://sgomez.blogspot

Create date field from substring of text field

I am trying to populate a field in a SQL table based on the values
returned from using substring on a text field.

Example:

Field Name = RecNum
Field Value = 024071023

The 7th and 8th character of this number is the year. I am able to
get those digits by saying substring(recnum,7,2) and I get '02'. Now
what I need to do is determine if this is >= 50 then concatenate a
'19' to the front of it or if it is less that '50' concatenate a '20'.
This particular example should return '2002'. Then I want to take the
result of this and populate a field called TaxYear.

Any help would be greatly apprecaietd.

MarkMark,

Assuming both RecNum and TaxYear fields are in the same table, you can use
this script to populate TaxYear:

update YourTable
set TaxYear = case
when SubString(RecNum,7,2) >= '50' then '19' +
SubString(RecNum,7,2)
else '20' + SubString(RecNum,7,2)
end

Shervin

"Mark" <markcash@.Hotmail.com> wrote in message
news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> I am trying to populate a field in a SQL table based on the values
> returned from using substring on a text field.
> Example:
> Field Name = RecNum
> Field Value = 024071023
> The 7th and 8th character of this number is the year. I am able to
> get those digits by saying substring(recnum,7,2) and I get '02'. Now
> what I need to do is determine if this is >= 50 then concatenate a
> '19' to the front of it or if it is less that '50' concatenate a '20'.
> This particular example should return '2002'. Then I want to take the
> result of this and populate a field called TaxYear.
> Any help would be greatly apprecaietd.
> Mark|||This work exaclty like I was wanting!!!

Thanks for the advice Shervin!!

Mark

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vorem27pdlp2a9@.corp.supernews.com>...
> Mark,
> Assuming both RecNum and TaxYear fields are in the same table, you can use
> this script to populate TaxYear:
> update YourTable
> set TaxYear = case
> when SubString(RecNum,7,2) >= '50' then '19' +
> SubString(RecNum,7,2)
> else '20' + SubString(RecNum,7,2)
> end
> Shervin
> "Mark" <markcash@.Hotmail.com> wrote in message
> news:57bdc737.0310151257.1dc4d0a9@.posting.google.c om...
> > I am trying to populate a field in a SQL table based on the values
> > returned from using substring on a text field.
> > Example:
> > Field Name = RecNum
> > Field Value = 024071023
> > The 7th and 8th character of this number is the year. I am able to
> > get those digits by saying substring(recnum,7,2) and I get '02'. Now
> > what I need to do is determine if this is >= 50 then concatenate a
> > '19' to the front of it or if it is less that '50' concatenate a '20'.
> > This particular example should return '2002'. Then I want to take the
> > result of this and populate a field called TaxYear.
> > Any help would be greatly apprecaietd.
> > Mark