MS SQL(2000) table export to excel

I′m looking for a script (sql transaction, or function) to export MS
SQL(2000) Table to formated excel sheet (cells width, text format, ...).how about using the DTS
to export that to excel
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Milo?,
You also can use BCP command. See Books Online.
Regards,
"Milo?" wrote:

> I′m looking for a script (sql transaction, or function) to export MS
> SQL(2000) Table to formated excel sheet (cells width, text format, ...).|||Thank You Jose,
DTS export ist very easy, for my situation is the best, but it has litlle
problem with text encoding.
Milo?
?Jose G. de Jesus Jr MCP, MCDBA" nap_sal (nap_sala):
> how about using the DTS
> to export that to excel
>
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Milo?" wrote:
>sql

MS SQL SERVER via C programming

Hi All,

I am new in SQL world, I was wondering if it is possible to access my sql databases via C programming...perhaps through some function calls.
How to proceed with that ? Any reference or sample is welcome...!!
thanks in advance..I think that just a C++ coder would be able to give a good answer, try to post the question on the site's C++ forum too.

MS SQL Server searching functions

Hi,
the Soundex search words that sounds similar.
Does MS SQL Server has some function to make some intuitive search?
For example, for search term database, it should return rows that contains: "database" word, but also rows that contains "Oracle", "MySQL", "MS SQL" etc. terms.

Yes the Soundex string function is implemented in SQL Server and the others like MySQL and Oracle are not close to SQL Server in String functions because SQL Server implemented all. I have posted it a while back check the post below for all the SQL Server String functions. Hope this helps.

http://forums.asp.net/910656/ShowPost.aspx

MS SQL Server command function to send UDP packets from a stored procedure similar to syb_

Sorry to bug people, I searched google and the newsgroups but the
problem is that there is so much about the Slammer Worm that I was
just getting all those hits.
Is there a command or function in MS SQL Server to send a UDP packet
from a stored procedure similar to syb_sendmsg?
Something like syb_sendmsg(w.x.y.z, portnum, @.stringbuf)
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/21457;pt=5472
I have some monitoring projects and I would like to code them
similarly using sybase and MS SQL Server, I don't have much experience
with MS SQL Server but have tons with sybase. I was also not able to
find any good online command/function references, I guess I am spoiled
by the Sybase online and pdf manuals.
TIA for any help on either of those topics.Hi,
Use the procedure "sp_add_notification" to send a notification .The details
and usage you can get from books online.
You could install the SQL server 2000 books online. DOwnload the books
online form below link:-
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Could you install the latest service pack in your server to secure your sql
server from Slammer. Download and install the
sp3a from below link.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Thanks
Hari
MCDBA
"forsale" <google.20.webinfo@.xoxy.net> wrote in message
news:b754dde0.0407251800.20129d4d@.posting.google.com...
> Sorry to bug people, I searched google and the newsgroups but the
> problem is that there is so much about the Slammer Worm that I was
> just getting all those hits.
> Is there a command or function in MS SQL Server to send a UDP packet
> from a stored procedure similar to syb_sendmsg?
> Something like syb_sendmsg(w.x.y.z, portnum, @.stringbuf)
>
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__Boo
kTextView/21457;pt=5472
> I have some monitoring projects and I would like to code them
> similarly using sybase and MS SQL Server, I don't have much experience
> with MS SQL Server but have tons with sybase. I was also not able to
> find any good online command/function references, I guess I am spoiled
> by the Sybase online and pdf manuals.
> TIA for any help on either of those topics.|||Thanks, that might almost work, but I was really looking for something to send UDP.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<#T69eEscEHA.1356@.TK2MSFTNGP09.phx.gbl>...
> Hi,
>
> Use the procedure "sp_add_notification" to send a notification .The details
> and usage you can get from books online.

MS SQL getDate() Function remove Time

Hi,

I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.

The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.

In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.

Any help will be appricated.

Thank you!

YasOn Jul 4, 3:53 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.
>
The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.
>
In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.
>
Any help will be appricated.
>
Thank you!
>
Yas

AFAIK a there is no DATE type in MS SQL, only DATETIME so you cannot
store only the date part.
You can use SELECT CONVERT(VARCHAR(8),datevalue,112) to return the
datetime in YYYYMMDD format without the time but it's stored as a
VARCHAR not a DATETIME.|||On 4 Jul, 17:14, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

To remove the time from a datetime such as getdate():
>
SELECT dateadd(day,datediff(day,0,getdate()),0)
>
I strongly suggest not storing a date column as a string. Use a
datetime and just set the time to zeroes if you only need the date.

Hi I am storing the coumn as datetime and not string. However, using
the above suggestion (day,datediff(day,0,getdate()),0) I get a column
with Date+Time set to Zeros. OK, but the problem is when I run the
update/insert records command again It doesn't overwrite the columns
with today's date, its as if sql is secretly inserting the time by it
self and even though to my eyes the rows is exactly the same SQL adds
a new row thinking it is distinct.

I would like that if the table had a rowOld with: (ColValue1,
ColValue2,2007-07-04 00.00.00.000)
If I use the above suggestion and insert a rowNew with same values
(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
...It should overwrite rowOld with rowNew, not insert rowNew as a new
row.
...and only insert as a new rowNew2 when this row has a different date
eg. 2007-07-05 00.00.00.000

I thought it would as time is now set to Zeros, but it doesn't. Is SQL
marking each row in the examples above with a time stamp? even though
it is not shown in the row value?

Thanks again :-)

Yas|||On Wed, 04 Jul 2007 08:55:49 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

>I would like that if the table had a rowOld with: (ColValue1,
>ColValue2,2007-07-04 00.00.00.000)
>If I use the above suggestion and insert a rowNew with same values
>(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
>...It should overwrite rowOld with rowNew, not insert rowNew as a new
>row.
>...and only insert as a new rowNew2 when this row has a different date
>eg. 2007-07-05 00.00.00.000

You can write an INSERT for a new row, or an UPDATE for an existing
row, but you have to choose which it is to be. In your case you have
to find out if the row exists and then run INSERT or UPDATE depending
on what you find.

Microsoft is adding MERGE to the next release of SQL Server, which
would allow you to write one command to accomplish both functions, but
it is not available today.

Roy Harvey
Beacon Falls, CT

MS SQL Function return string - what am I doing wrong?

Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.

CREATE FUNCTION [dbo].[getNextProjectID] ()
RETURNS varchar(10) AS
BEGIN
''.....................
DECLARE @.vNextProjectID varchar(10)
RETURN @.vNextProjectID
END

Sub LoadNextProjectNumber()
Dim vProjectID As String
Dim cmd As New SqlClient.SqlCommand()
cmd.Connection = sqlConn
cmd.CommandText = "getNextProjectID"
cmd.Parameters.Add("@.vNextProjectID", vProjectID)
cmd.Parameters("@.vNextProjectID").Direction = ParameterDirection.ReturnValue
cmd.ExecuteScalar()
vProjectID = cmd.Parameters("@.vNextProjectID").Value
txtProjectID.Text = vProjectID
cmd.Dispose()
End Sub
Hi Jagdipa,
Are you sure that your function executes correctly? I can run your example and get back string successfully. Try to run Profiler in MS SQL to see function execution log.|||check ifthis articlehelps|||hello..
try to use :
cmd.CommandType = storedprocedure
and
cmd.ExecuteNonQuery|||Thanks guys.
All I had to do was write the following line (thanks busyweb):
cmd.commandType = storedProcedure
Dont know why I need that line when the sql is a function and not astored procedure. I have had this code work before without that line.But, as long as it works...
Jagdip

MS SQL (min function To min2ndlowest)

Hi,
Below stated is my original query from my database but for the sake ofsimplicity , I have used the pubs database to convey my thoughts.

select p.pub_name,min(t.price),max(t.price) from publishers p,titles t
where
p.pub_id=t.pub_id
group by p.pub_name

Instead of the min(Price), I would like to get the min2ndLowest(Price).
Is there away to manipulate the Min function so as to change it . Or is itsomehow possible to rewrite another function like Min2ndLowest() toeasily solve this situation.I will be more happy to solve via thisroute as I later have to solve other queries like Max2ndHighest() andso forth.
Or is it only possible thru some serious query design

Thanks for help guys..

My Original Query

select b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported,min(t.begintime),max(t.begintime)
from
batches b, queues q,tasks t
where
b.queueid=q.queueid
and
b.batchid = t.batchid
group by b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported

--SELECT MIN(b.PRICE1) AS Max2ndHighest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1 DESC) AS b

--SELECT MAX(b.PRICE1) As MIN2ndLowest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1) AS b

--table is from this thread with more data:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619492&SiteID=1

create

table #x

(

ProductID

int,

MarketID

int,

Date

datetime,

PRICE1

decimal(9,2),

PRICE2

decimal(9,2),

PRICE3

decimal(9,2)

)

insert

#x

select

1, 2,'1/01/2006', 2.78, 3.4, 2.97unionall

select

1, 2,'2/01/2006', 2.51, 3.5,NULLunionall

select

1, 2,'3/01/2006',NULL, 3.6,NULLunionall

select

1, 2,'4/01/2006',NULL, 3.55,NULLunionall

select

2, 4,'1/01/2006', 3.44, 1.23, 4.33unionall

select

2, 4,'2/01/2006', 3.55, 1.22, 4.22unionall

select

2, 4,'3/01/2006', 3.13, 1.51, 4.54unionall

select

2, 4,'4/01/2006',NULL, 1.50, 4.25

SELECT b.ProductID, b.MarketID,

(

SELECTMIN(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDORDERBY a.PRICE2DESC)AS c)AS Max2ndHighest,

(

SELECTMAX(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDorderby a.PRICE2)AS c)AS MIN2ndLowest

FROM

#xAS b

GROUP

BY b.ProductID, b.MarketID

drop

table #x|||

Insert this into the joins area of your original query:

LEFT JOIN ({a complete copy of your original query}) t1 ON ({field1 from original query}=t1.{field1 from original query} AND {field2}=t1.{field2} ... AND {field to be min-ed from original query}={min-ed field result from subquery})

Then add

WHERE t1.{field1} IS NULL to the where clause of your original query.

For example:

SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1

becomes

SELECT field1,MIN(field2) AS MinField2
FROM table1
LEFT JOIN (
SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1) t1 ON (table1.field1=t1.field1 AND table1.field2=t1.MinField2)
WHERE field3='something'AND t1.field1 IS NULL
GROUP BY field1

Ms Sql

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

Quote:

Originally Posted by clear1140

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)|||

Quote:

Originally Posted by ck9663

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)

Hey ck9663....... thanks a lot...... it really works.......... thank you!!!!!!!!!!!