Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error
.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just fo
r
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005).
I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the err
or.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!
Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:
> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Alias for a (non-default) SQL Server 2005 Instance

I am trying to create an alias for a particular SQL Server 2005
Instance.
I've tried doing this in the host file but this was unsucessful
because all the host file seems to do is give you an IP address for
the required server (not the acutal SQL instance).
How do I go about setting up the alias correctly, do I need to use DNS
or something like that?
The alias is fixed and of the form "dbsrvMyDatabase"
Any suggestions would be gratefully received.Use cliconfg.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<charlieopenshaw@.gmail.com> wrote in message
news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>I am trying to create an alias for a particular SQL Server 2005
> Instance.
> I've tried doing this in the host file but this was unsucessful
> because all the host file seems to do is give you an IP address for
> the required server (not the acutal SQL instance).
> How do I go about setting up the alias correctly, do I need to use DNS
> or something like that?
> The alias is fixed and of the form "dbsrvMyDatabase"
> Any suggestions would be gratefully received.
>|||Tibor,
Thanks, that worked a treat.
I went to Alias > Add...
Selected TCP/IP, entered the Server Alias, Server name, checked
Dynamically determine port and entered my port number.
I had to get my port number setting from:
SQL Server Configuration Manager > SQL Server 2005 Network
Configuration > TCP/IP > TCP/IP Properties > IP Addresses > TCP
Dynamic Ports
Charlie
On 4 May, 11:40, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Usecliconfg.exe.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <charlieopens...@.gmail.com> wrote in message
> news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -

Creating an Alias for a (non-default) SQL Server 2005 Instance

I am trying to create an alias for a particular SQL Server 2005
Instance.
I've tried doing this in the host file but this was unsucessful
because all the host file seems to do is give you an IP address for
the required server (not the acutal SQL instance).
How do I go about setting up the alias correctly, do I need to use DNS
or something like that?
The alias is fixed and of the form "dbsrvMyDatabase"
Any suggestions would be gratefully received.
Use cliconfg.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<charlieopenshaw@.gmail.com> wrote in message
news:1178272012.359623.8200@.y80g2000hsf.googlegrou ps.com...
>I am trying to create an alias for a particular SQL Server 2005
> Instance.
> I've tried doing this in the host file but this was unsucessful
> because all the host file seems to do is give you an IP address for
> the required server (not the acutal SQL instance).
> How do I go about setting up the alias correctly, do I need to use DNS
> or something like that?
> The alias is fixed and of the form "dbsrvMyDatabase"
> Any suggestions would be gratefully received.
>
|||Tibor,
Thanks, that worked a treat.
I went to Alias > Add...
Selected TCP/IP, entered the Server Alias, Server name, checked
Dynamically determine port and entered my port number.
I had to get my port number setting from:
SQL Server Configuration Manager > SQL Server 2005 Network
Configuration > TCP/IP > TCP/IP Properties > IP Addresses > TCP
Dynamic Ports
Charlie
On 4 May, 11:40, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Usecliconfg.exe.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <charlieopens...@.gmail.com> wrote in message
> news:1178272012.359623.8200@.y80g2000hsf.googlegrou ps.com...
>
>
>
>
> - Show quoted text -
sql

Creating an Alias for a (non-default) SQL Server 2005 Instance

I am trying to create an alias for a particular SQL Server 2005
Instance.
I've tried doing this in the host file but this was unsucessful
because all the host file seems to do is give you an IP address for
the required server (not the acutal SQL instance).
How do I go about setting up the alias correctly, do I need to use DNS
or something like that?
The alias is fixed and of the form "dbsrvMyDatabase"
Any suggestions would be gratefully received.Use cliconfg.exe.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<charlieopenshaw@.gmail.com> wrote in message
news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>I am trying to create an alias for a particular SQL Server 2005
> Instance.
> I've tried doing this in the host file but this was unsucessful
> because all the host file seems to do is give you an IP address for
> the required server (not the acutal SQL instance).
> How do I go about setting up the alias correctly, do I need to use DNS
> or something like that?
> The alias is fixed and of the form "dbsrvMyDatabase"
> Any suggestions would be gratefully received.
>|||Tibor,
Thanks, that worked a treat.
I went to Alias > Add...
Selected TCP/IP, entered the Server Alias, Server name, checked
Dynamically determine port and entered my port number.
I had to get my port number setting from:
SQL Server Configuration Manager > SQL Server 2005 Network
Configuration > TCP/IP > TCP/IP Properties > IP Addresses > TCP
Dynamic Ports
Charlie
On 4 May, 11:40, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Usecliconfg.exe.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <charlieopens...@.gmail.com> wrote in message
> news:1178272012.359623.8200@.y80g2000hsf.googlegroups.com...
>
> >I am trying to create analiasfor a particular SQL Server 2005
> >Instance.
> > I've tried doing this in the host file but this was unsucessful
> > because all the host file seems to do is give you an IP address for
> > the required server (not the acutal SQLinstance).
> > How do I go about setting up thealiascorrectly, do I need to use DNS
> > or something like that?
> > Thealiasis fixed and of the form "dbsrvMyDatabase"
> > Any suggestions would be gratefully received.- Hide quoted text -
> - Show quoted text -

Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--

Creating a TXT file with SQL

Hi , I am new in this group
I have a simple question.
How can i create a TXT file from a SQL statement?
Something like this
Select * from table to Txtfile
is there any way?
Tks
Carlos Lages
Rio de JaneiroThis is something I've used before for a business case that needed me to create a text file with the latest primary key (sequential ID)

DECLARE @.sCmd varchar(256)
declare @.maxid varchar(12)
set @.maxid = (select max(pk) from test_tbl)
SET @.sCmd= 'echo>c:\temp\test.txt The new Primary Key is -' + @.maxid
EXEC master..xp_cmdshell @.sCmd, no_output

You can play with this and get what you need to throw to the text file or post back if you need help.

HTH|||rhigdon

tks
i will change to my needs , but i think will work fine

because what i realy need is select several fields from
several tables and create a txt file.
Carlos Lages|||THis article may be of help as well

http://www.sqlteam.com/item.asp?ItemID=4722

HTH

Creating a trace file with the date appended to its name

I'm trying to create a trace file with the file as part of it's name.
For example, I'd like to create a file called FailedLogins-20050428. So
far I haven't been able to figure out how to get the name of the file
and the date together (I'm sill very new to SQL Server and tracing).

What I've done is:
declare @.rc int
declare @.traceid int
declare @.maxfilesize bigint
set @.maxfilesize = 50
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins', @.maxfilesize=@.maxfilesize,
@.stoptime=NULL
if @.rc > 0 print 'sp_trace_code failed with error code ' +
rtrim(cast(@.rc as char))
else print 'traceid for the trace is ' + rtrim(cast(@.traceid as char))

I can create a trace file on C drive without difficulty. I've tried
creating a file like this:
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins + convert (varchar,getdate(),112',
@.maxfilesize=@.maxfilesize, @.stoptime=NULL

But what I end up created is a file on C called
failedlogins + convert(varchar,getdate(),112).trc

I have no doubt what I want to do can be done. I just done know how to
do it.

If anyone could tell me where I'm going wrong, I'd really appreciate
it.

Thanks in advance.Hiya Bill,

Try this in Query Analyzer.. @.tracefile is your variable,
@.tracefile_new is the proposed fix. Take note of the @.tracefile_new
output..

declare @.tracefile varchar(1000),
@.tracefile_new varchar(1000)

set @.tracefile=N'C:\trace\failedlogins + convert
(varchar,getdate(),112'
set @.tracefile_new=N'C:\trace\failedlogins_' + convert
(varchar,getdate(),112)

print @.tracefile

print @.tracefile_new|||Thanks Greg! It works great. Just the way I wanted it.

creating a trace definition file (TDF) via code

Is there any way (either TSQL/SMO) to create a new TDF file, I'm using the traceserver InitializeAsReader sub, so need to pass it a reference to a TDF file, and I'd like to control what it traces via the TDF rather than parsing the TextData of the output.

Cathal

There is a Trace API that ships with SMO (see the Microsoft.SqlServer.Management.Trace namespace).

See the TraceServer.InitializeAsReader() method. You can specify a predefine trace template as the second parameter and then start the trace with this template.

|||

Thanks Michiel, but I'm actually using that method already, my question is on the creation of the TDF itself.

Ideally I'd like a t-sql/smo script to generate a TDF template, so I could dynamically determine what events to track and also filter for particular users/databases e.g. if I was only interested in tracking northwind events, I'd create a template based on TSQL_SPs, but that also used a DatabaseName column filter set to like '%Northwind%' . Obviously i can do this manually, but I would like to automate this step for an application I'm writing. I suspect it's not possible to serialize a trace definition to a TDF file. I can use TSQL to create a trace with the correct filters I require, but as I can't pass a running trace to InitializeAsReader, I can't use it. At present I've assumed I'll have to filter the eventData via code, but I thought I'd ask just in case.

Cathal

|||This is not possible, AFAIK. I'll make sure this gets logged as a feature request.

creating a text file from the contents of the database?

please help!!

i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

i will also need to create the text file for multiple records.

can someone help me please?? i read something about SELECT INTO OUTFILE... how exactly does this work?Hello,

use UTL_FILE package to spool records into a file via PL/SQL.
In AlligatorSQL you can use a template "How to spool a ...".

See at http://www.alligatorsql.com/download/alligator116.zip

But if you wish I can post an example again (it has been already posted in this forum)

Hope that helps ?

Manfred Peter
(Alligator Compay Software GmbH)
http://www.alligatorsql.com|||oh i see! thanks, i found the thread on extracting. will post again if i have any problems!|||sir manfred,

would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

this is what i have to do:
- save the information that was entered in Oracle Forms (this is finished)
- when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)

This is what i have done so far:

/*WHEN-BUTTON-PRESSED trigger*/

DECLARE

CURSOR cuProcess IS
SELECT *
FROM SIR
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;

rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);

N_FILE VARCHAR2(2000);

BEGIN

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
||rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT;
END LOOP BeginLoop;

CLOSE cuProcess;

CREATE_TEXT('filename', cOut);

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;

END;

then i have a simple procedure that creates the text file:

PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

N_FILE text_io.file_type;

BEGIN
N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
TEXT_IO.PUT_LINE(N_FILE, selected);
TEXT_IO.FCLOSE(N_FILE);

END;

my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
also, after the text file is created, how can i load it using sqlloader?

also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

i appreciate the help! thanks again!|||Hello,

sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

Sorry again.

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Creating a text file

Any one can show me how to dump the entire content of a SQL Table into either a text file or FoxPro file.
Thanks.Thanks I got it...|||Hi
I am relatively new to working with MS SQL. I need to do the same, dumping data from a table in a text file. You seem to have learned the trick. Could you please tell me how to do that?

Thanks..|||For SQL 2000:

Expand database
Right click tables
Select "All tasks" --> "Export Data..."
Then follow the wizard

For SQL 2005:

Dont have a frickin clue...

Creating a stored procedure in SQL Server Management Studio

I have a database that I want to add stored procedures but when I go to save the stored procedure it asked me to save it as a .sql file and doesn;t add it to the database.

This is what I'm doing:

right click on database > programability > stored procedures

select "new Stored Procedure"

writting the procedure

then save - this is when i get the save dialogue rather than seeing the sp added to the database.

There must be something very simple that I'm doing wrong but I just can't figure it out, any help would be greatly appreciated.

Damien

You need to execute this command (that begins with CREATE PROCEDURE) instead of saving to disk.

So, you need to press F5 or press the Execute Button from program bar.

|||Thanks

creating a standard template for dev team

I want to create a standard look and feel for my reports. I can do this from
the style perspective by editing the xml file.
But how can I create a template report that always has headers and footers
and has standard contents within them such as print date\time, page count
and a logo ?
Is there an external way of doing this or will it always have to be done in
code so that when a report is being previwed or printed, I edit the report
and add the appropriate code ?
thanks
BObHi Bob.
What I ended up doing was using the designer to build my standard look and
feel (one for portrait, one for landscape and one for charts), then save
them off to the Report Designer templates folder.
HTH
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"BOb Pardoe hayslogistics.co.uk>" <bob.pardoe@.<RemoveAntiSpam> wrote in
message news:OiwzVc0tEHA.2300@.TK2MSFTNGP09.phx.gbl...
> I want to create a standard look and feel for my reports. I can do this
from
> the style perspective by editing the xml file.
> But how can I create a template report that always has headers and footers
> and has standard contents within them such as print date\time, page count
> and a logo ?
> Is there an external way of doing this or will it always have to be done
in
> code so that when a report is being previwed or printed, I edit the report
> and add the appropriate code ?
> thanks
> BOb
>

Creating a SQL script file from a stored procedure

I'd like to create a SQL script file from within a stored procedure. How do I:

1. Open the text file
2. Write to it
3. Close it when done

Thanks in advance,


Barrysounds like a job better accomplished through application code. filesystem object in vbscript perhaps.

but if you insist, perhaps I would write my sql string to a table and then fire a dts package from my sp to export the field to a text file.

what are you trying to accomplish? I can't imagine how this might be particularly useful.

more details would be helpful.

are you trying to open an application on the server side. Not a good idea. Saw someone trigger Access to open on the server side from the web once. If the the user closed the browser or something that instance of Access just stayed open on server and the mess this caused until I fixed it.|||You can use master..xp_cmdshell extended stored proc to run DOS command. It should look like

Exec master..xp_cmdshell 'Echo Whatever > MylogFile.Txt'

Cheers.|||Thrasymachus and mkostadinovic, thank you for your replies.

I ended up using osql to output the text from the SQL code:

osql -U xx -P xxxxxx -S (local) -d xxxx -h-1 -w 4000 -n -i "C:\...\ExtractUpdatePlateData.sql" -o "C:\...\Insert plate data.sql"

In case you are still wondering why, the reason for doing this was to script changes made to the contents of a number of database tables from ~20 updates that had built up over the last 18 months or so. I needed to be able to extract out the data, but had a constraint that I couldn't just use a file containing the data, and a script to import it. Here is part of the SQL from ExtractUpdatePlateData.sql:

SELECT InsertScript = 'INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES (' + '''' + vv_secttype + '''' + ', ' + CAST(vi_proctype AS NVARCHAR) + ', ' + CAST(vi_numwells AS NVARCHAR) + ', ' + '''' + vv_descrip + '''' + ')'
FROM tblUPDsecttype

This is how it appears in Insert plate data.sql:

INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES ('ANO2B', 1, 96, 'ANAEROBE SYSTEMIC Man MiC')

It all seems to work fine (and has saved me a week's tedious work already).


Best regards,


Barry

Creating a SQL Log file in Event Viewer

I believe I have seen this done or at least read about it, in any event how
can a person setup the Windows Event Viewer log to monitor and log specific
events in a speficic SQL database?
TIABryan,
SQL Server can be configured to write error messages to the event log. See:
SP_ADDMESSAGE and RAISERROR (WITH LOG) in the SQL Server Books Online. You
can also use xp_logevent and write job status's in the log as well.
HTH
Jerry
"Bryan.S.Walker" <BryanSWalker@.discussions.microsoft.com> wrote in message
news:E400E837-5B3E-43BC-B146-F3FA0DEE6462@.microsoft.com...
>I believe I have seen this done or at least read about it, in any event how
> can a person setup the Windows Event Viewer log to monitor and log
> specific
> events in a speficic SQL database?
> TIA

Creating a SQL Log file in Event Viewer

I believe I have seen this done or at least read about it, in any event how
can a person setup the Windows Event Viewer log to monitor and log specific
events in a speficic SQL database?
TIA
Bryan,
SQL Server can be configured to write error messages to the event log. See:
SP_ADDMESSAGE and RAISERROR (WITH LOG) in the SQL Server Books Online. You
can also use xp_logevent and write job status's in the log as well.
HTH
Jerry
"Bryan.S.Walker" <BryanSWalker@.discussions.microsoft.com> wrote in message
news:E400E837-5B3E-43BC-B146-F3FA0DEE6462@.microsoft.com...
>I believe I have seen this done or at least read about it, in any event how
> can a person setup the Windows Event Viewer log to monitor and log
> specific
> events in a speficic SQL database?
> TIA

Creating a SQL Log file in Event Viewer

I believe I have seen this done or at least read about it, in any event how
can a person setup the Windows Event Viewer log to monitor and log specific
events in a speficic SQL database?
TIABryan,
SQL Server can be configured to write error messages to the event log. See:
SP_ADDMESSAGE and RAISERROR (WITH LOG) in the SQL Server Books Online. You
can also use xp_logevent and write job status's in the log as well.
HTH
Jerry
"Bryan.S.Walker" <BryanSWalker@.discussions.microsoft.com> wrote in message
news:E400E837-5B3E-43BC-B146-F3FA0DEE6462@.microsoft.com...
>I believe I have seen this done or at least read about it, in any event how
> can a person setup the Windows Event Viewer log to monitor and log
> specific
> events in a speficic SQL database?
> TIAsql

Creating a SQL Database File in a Specific Location

In SQL Server 9.0 (Standard Edition), I want to create a database from within
the Management Studio. But when I create it, I don't seem to have any control
of WHERE the actual database files are placed. I *must* be able to control
that somewhere, right?
Alex
Hi Alex
I presume you are using the New Database Dialog in Management Studio. In the
box where you supply the db name, you also supply all the details about each
of the files, including their physical location. When you scroll to the
right, you can see the text boxes for the path, and there is a button to
click to allow you to browse and choose a different path than the default.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex
|||Alex
Yes you can specify the database file location
General-> under Database file(move the scroll bar right) section
VT
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex
|||In SQL Server Management Studio on the General page for creating a new
database you can expand the window or scroll to the right to see the Path
field. This is where you change the default location of the database files.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex
|||Yeah, OOPS. Just found that Thanks.
"Kalen Delaney" wrote:

> Hi Alex
> I presume you are using the New Database Dialog in Management Studio. In the
> box where you supply the db name, you also supply all the details about each
> of the files, including their physical location. When you scroll to the
> right, you can see the text boxes for the path, and there is a button to
> click to allow you to browse and choose a different path than the default.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
>
>

Creating a SQL Database File in a Specific Location

In SQL Server 9.0 (Standard Edition), I want to create a database from withi
n
the Management Studio. But when I create it, I don't seem to have any contro
l
of WHERE the actual database files are placed. I *must* be able to control
that somewhere, right?
AlexHi Alex
I presume you are using the New Database Dialog in Management Studio. In the
box where you supply the db name, you also supply all the details about each
of the files, including their physical location. When you scroll to the
right, you can see the text boxes for the path, and there is a button to
click to allow you to browse and choose a different path than the default.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex|||Alex
Yes you can specify the database file location
General-> under Database file(move the scroll bar right) section
VT
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex|||In SQL Server Management Studio on the General page for creating a new
database you can expand the window or scroll to the right to see the Path
field. This is where you change the default location of the database files.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
> In SQL Server 9.0 (Standard Edition), I want to create a database from
> within
> the Management Studio. But when I create it, I don't seem to have any
> control
> of WHERE the actual database files are placed. I *must* be able to control
> that somewhere, right?
> Alex|||Yeah, OOPS. Just found that Thanks.
"Kalen Delaney" wrote:

> Hi Alex
> I presume you are using the New Database Dialog in Management Studio. In t
he
> box where you supply the db name, you also supply all the details about ea
ch
> of the files, including their physical location. When you scroll to the
> right, you can see the text boxes for the path, and there is a button to
> click to allow you to browse and choose a different path than the default.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:EB0663C5-18DF-4ECB-8D43-3C36E6263CB9@.microsoft.com...
>
>