getting the most latest date

I have a column which stores dates (datetime data type). I would like
to fetch one data which is the most latest date among them. So if there
are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
need to output.

how can I write this in sql stmt?
select datebegin
from testtable
where datebegin = ???

I don't think it is simple as I thought. I have no idea what I need to
write in where clause to make this work.
thanks.select MAX(datebegin) from testtable

TGEAR wrote:
> I have a column which stores dates (datetime data type). I would like
> to fetch one data which is the most latest date among them. So if there
> are 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one I
> need to output.
> how can I write this in sql stmt?
> select datebegin
> from testtable
> where datebegin = ???
> I don't think it is simple as I thought. I have no idea what I need to
> write in where clause to make this work.
> thanks.sql

getting scripts from sql server 2000

I'm interested in writing a program that makes craete scripts for all
views and stores procedures in a sql server 2000 database, like the
ones you can get them from the Query Analyzer.
Can I retrieve these create scripts using some SQL statement? Or do I
have to use some special MS SQL Server API?
Use SQL-DMO Objects Library
<delta.jentry@.gmail.com> wrote in message
news:1140506452.304401.85450@.g14g2000cwa.googlegro ups.com...
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>
|||Hi
There are also scripting options in Enterprise Manager.
John
"delta.jentry@.gmail.com" wrote:

> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>

getting scripts from sql server 2000

I'm interested in writing a program that makes craete scripts for all
views and stores procedures in a sql server 2000 database, like the
ones you can get them from the Query Analyzer.
Can I retrieve these create scripts using some SQL statement? Or do I
have to use some special MS SQL Server API?Use SQL-DMO Objects Library
<delta.jentry@.gmail.com> wrote in message
news:1140506452.304401.85450@.g14g2000cwa.googlegroups.com...
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>|||Hi
There are also scripting options in Enterprise Manager.
John
"delta.jentry@.gmail.com" wrote:
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>

getting scripts from sql server 2000

I'm interested in writing a program that makes craete scripts for all
views and stores procedures in a sql server 2000 database, like the
ones you can get them from the Query Analyzer.
Can I retrieve these create scripts using some SQL statement? Or do I
have to use some special MS SQL Server API?Use SQL-DMO Objects Library
<delta.jentry@.gmail.com> wrote in message
news:1140506452.304401.85450@.g14g2000cwa.googlegroups.com...
> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>|||Hi
There are also scripting options in Enterprise Manager.
John
"delta.jentry@.gmail.com" wrote:

> I'm interested in writing a program that makes craete scripts for all
> views and stores procedures in a sql server 2000 database, like the
> ones you can get them from the Query Analyzer.
> Can I retrieve these create scripts using some SQL statement? Or do I
> have to use some special MS SQL Server API?
>sql

Getting Plan of previously executed Queries

Hi,
I am newbie to SQL Server. I am using SQL Server 2000.

I know SQL server compiles the SQL stmt or SP and stores the plan for
later use(I know its not always done. But should be done to reduce
execution time) in memory somewere. I want to capute all the execution
plan which are kept in memory. In oracle v$sql_plan can be used to
access the plan in oracle. I want to know how to do that in SQL Server.
I can use profiler, but it contributes some CPU utilization.

Thanks,
Thiru.
WantedToBeDBA
WantedToBeDBA {at} gmail {dot} com"Thiru" <WantedToBeDBA@.gmail.com> wrote in message
news:1117211283.969054.121710@.f14g2000cwb.googlegr oups.com...
> Hi,
> I am newbie to SQL Server. I am using SQL Server 2000.
> I know SQL server compiles the SQL stmt or SP and stores the plan for
> later use(I know its not always done. But should be done to reduce
> execution time) in memory somewere. I want to capute all the execution
> plan which are kept in memory. In oracle v$sql_plan can be used to
> access the plan in oracle. I want to know how to do that in SQL Server.
> I can use profiler, but it contributes some CPU utilization.
> Thanks,
> Thiru.
> WantedToBeDBA
> WantedToBeDBA {at} gmail {dot} com

See syscacheobjects in Books Online. But this only gives you some general
information about what's in the cache; as far as I know, there's no way to
actually view the cached plans.

Profiler is probably the only way to capture this information, and unless
the server is under very heavy load, it shouldn't have much of an impact.
You can use a server-side trace (see sp_trace_create) if you don't want to
leave Profiler running on a client PC.

Simon

Getting osql to take a script in codepage 1252

I have a table that stores ISO-8859-1 data. The default collation for my
database is SQL_Latin1_General_CP1_CI_AS. I have a script that I pass the
"osql" that populates the table with default data. The script works for
normal ASCII character data, but not for the full ISO-8859-1 character set.
If I could specify that the script was in codepage 1252, all would be well.
The script is as follows:
-- drop any previous version of the table
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Salutations' AND type =
'U')
DROP TABLE Salutations
GO
-- create the new table
CREATE TABLE Salutations (
Salutation VARCHAR (10) PRIMARY KEY NOT NULL,
Pronunciation VARCHAR (20) NOT NULL,
Pattern VARCHAR (100) NOT NULL,
UIRepresentation VARCHAR (20) NULL,
UISortOrder INTEGER NULL
)
GO
-- populate the table
-- Salutation Pronunciation Pattern
UIRepresentation UISortOrder
INSERT INTO Salutations VALUES ('mr', 'mister', '^mr\.? +',
'Mr.', 1)
INSERT INTO Salutations VALUES ('mister', 'mister', '^mister +',
NULL, NULL)
INSERT INTO Salutations VALUES ('mrs', 'missus', '^mrs\.? +',
'Mrs.', 2)
INSERT INTO Salutations VALUES ('ms', 'miz', '^ms\.? +',
'Ms.', 3)
INSERT INTO Salutations VALUES ('miss', 'miss', '^miss +',
'Miss', 4)
INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
'Dr.', 5)
INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.? +',
'Prof.', 6)
INSERT INTO Salutations VALUES ('professor', 'professor', '^professor
+', NULL, NULL)
-- Spanish salutations
INSERT INTO Salutations VALUES ('sr', 'seor', '^sr\.? +',
'Sr.', 7)
INSERT INTO Salutations VALUES ('seor', 'seor', '^seor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senor', 'seor', '^senor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('sra', 'seora', '^sra\.? +',
'Sra.', 8)
INSERT INTO Salutations VALUES ('seora', 'seora', '^seora +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senora', 'seora', '^senora +',
NULL, NULL)
INSERT INTO Salutations VALUES ('srta', 'seorita', '^srta\.? +',
'Srta.', 9)
INSERT INTO Salutations VALUES ('seorita', 'seorita', '^seorita +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senorita', 'seorita', '^senorita +',
NULL, NULL)
INSERT INTO Salutations VALUES ('profesor', 'profesor', '^profesor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('profa', 'profesora', '^profa\.? +',
'Profa.', 10)
INSERT INTO Salutations VALUES ('profesora', 'profesora', '^profesora
+', NULL, NULL)
INSERT INTO Salutations VALUES ('dra', 'doctora', '^dra\.? +',
'Dra.', 11)
INSERT INTO Salutations VALUES ('doctora', 'doctora', '^doctora +',
NULL, NULL)
-- the same as in English
--INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
'Dr.', 5)
--INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
NULL, NULL)
--INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.?
+', 'Prof.', 6)
GO
When I run the script, all of the entries that use the character (ANSI
character 0xE1) end up being stored using the character (ANSI character
0xB1). Note that if I copy and paste the script into Query Analyzer and run
it from there, it runs as expected.
I've tried creating an ODBC datasource with the "Perform translation for
character data" option unchecked and using that data source via the -D
command line switch to osql, but it made no difference. I am running against
SQL Server 2000 SP3a.
What else can I try?
Rick
rgenter "at" silverlink.com
save the file as OEM in Query Analyzer.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL Server
"Rick Genter" wrote:

> I have a table that stores ISO-8859-1 data. The default collation for my
> database is SQL_Latin1_General_CP1_CI_AS. I have a script that I pass the
> "osql" that populates the table with default data. The script works for
> normal ASCII character data, but not for the full ISO-8859-1 character set.
> If I could specify that the script was in codepage 1252, all would be well.
> The script is as follows:
> -- drop any previous version of the table
> IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Salutations' AND type =
> 'U')
> DROP TABLE Salutations
> GO
>
> -- create the new table
> CREATE TABLE Salutations (
> Salutation VARCHAR (10) PRIMARY KEY NOT NULL,
> Pronunciation VARCHAR (20) NOT NULL,
> Pattern VARCHAR (100) NOT NULL,
> UIRepresentation VARCHAR (20) NULL,
> UISortOrder INTEGER NULL
> )
> GO
>
> -- populate the table
> -- Salutation Pronunciation Pattern
> UIRepresentation UISortOrder
> INSERT INTO Salutations VALUES ('mr', 'mister', '^mr\.? +',
> 'Mr.', 1)
> INSERT INTO Salutations VALUES ('mister', 'mister', '^mister +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('mrs', 'missus', '^mrs\.? +',
> 'Mrs.', 2)
> INSERT INTO Salutations VALUES ('ms', 'miz', '^ms\.? +',
> 'Ms.', 3)
> INSERT INTO Salutations VALUES ('miss', 'miss', '^miss +',
> 'Miss', 4)
> INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
> 'Dr.', 5)
> INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.? +',
> 'Prof.', 6)
> INSERT INTO Salutations VALUES ('professor', 'professor', '^professor
> +', NULL, NULL)
> -- Spanish salutations
> INSERT INTO Salutations VALUES ('sr', 'se?or', '^sr\.? +',
> 'Sr.', 7)
> INSERT INTO Salutations VALUES ('se?or', 'se?or', '^se?or +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senor', 'se?or', '^senor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('sra', 'se?ora', '^sra\.? +',
> 'Sra.', 8)
> INSERT INTO Salutations VALUES ('se?ora', 'se?ora', '^se?ora +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senora', 'se?ora', '^senora +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('srta', 'se?orita', '^srta\.? +',
> 'Srta.', 9)
> INSERT INTO Salutations VALUES ('se?orita', 'se?orita', '^se?orita +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senorita', 'se?orita', '^senorita +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('profesor', 'profesor', '^profesor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('profa', 'profesora', '^profa\.? +',
> 'Profa.', 10)
> INSERT INTO Salutations VALUES ('profesora', 'profesora', '^profesora
> +', NULL, NULL)
> INSERT INTO Salutations VALUES ('dra', 'doctora', '^dra\.? +',
> 'Dra.', 11)
> INSERT INTO Salutations VALUES ('doctora', 'doctora', '^doctora +',
> NULL, NULL)
> -- the same as in English
> --INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
> 'Dr.', 5)
> --INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
> NULL, NULL)
> --INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.?
> +', 'Prof.', 6)
> GO
>
> When I run the script, all of the entries that use the ? character (ANSI
> character 0xE1) end up being stored using the character ± (ANSI character
> 0xB1). Note that if I copy and paste the script into Query Analyzer and run
> it from there, it runs as expected.
> I've tried creating an ODBC datasource with the "Perform translation for
> character data" option unchecked and using that data source via the -D
> command line switch to osql, but it made no difference. I am running against
> SQL Server 2000 SP3a.
> What else can I try?
> Rick
> --
> rgenter "at" silverlink.com
>
>
|||Mark,
Thanks. That did the trick.
Rick
rgenter "at" silverlink.com
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:3CEC828C-E8EB-41C8-99DE-705C52B62ED1@.microsoft.com...[vbcol=seagreen]
> save the file as OEM in Query Analyzer.
> regards,
> Mark Baekdal
> www.dbghost.com
> Living and breathing database change management for SQL Server
> "Rick Genter" wrote:
my[vbcol=seagreen]
the[vbcol=seagreen]
set.[vbcol=seagreen]
well.[vbcol=seagreen]
type =[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^professor[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^profesora[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^prof\.?[vbcol=seagreen]
character[vbcol=seagreen]
run[vbcol=seagreen]
against[vbcol=seagreen]

Getting meta-data for Linked Servers

My customer has a .NET application that reads meta data from SQL Server, Oracle, DB2, and several propritary databases. Because each DBMS stores the meta data using various techniques, they have written custom code for each DBMS. They are working on a generic ODBC/OLEDB suppport, but in the interim I was trying to use SQL Server to link to an Access database. The Access linked server works fine for queries in Query Analyzer, but I would like to be able to programatically read the metadata for an Access DB (tables, columns, types, etc) via the linked server. SQL Server's usual mechanism for storing meta-data in the Master database aparently is not used for Linked Servers.

Does SQL Server expose Linked Server meta data?

How would one retrieve this meta data if it is exposed?

Thanks,

Ben

Hi Ben,

No SQL Server does not store metadata about linked servers (excluding connection information stored in sys.servers/sysservers). You would need to interrogate the actual linked server for this. So, if it were MSAccess, you would need to refer to the system tables (such as MSysObjects) etc.

Cheers,

Rob

|||

I was afraid that was the answer.

Thanks

getting just the date or from DateTime field

Hi,
I have used smalldatetime datatype to store my date and time values. i want to store just the date or time but the problem is it stores both the date and time. For eg, if i add the the date 03/11/2004, it also the stores the current time automatically. so the new value will be something like 03/11/2004 10:00:00 AM where i want just 03/11/2004. further problem is even though i managed to store just the date like 03/11/2004 in the database, whole date and time shows up when i display it in my pages.

any help will be appreciated.

thanx,If i want to display just the date portion I use the following.
This is just using a datareader then putting the date in a label

 While drDate.Read()
 lblSent.Text = Format(drDate("datesent_column"), "dd/MM/yyyy")
 End While

If you are in the US you might use "MM/dd/yyyy" instead of our aussie way.
It doesn't matter then if the whole date and time is in the column.
Hope this helps
Pete|||You should just use the more generic "short date formats" rather than hard-coding the format, but the general idea is sound.|||hi pkr,
even though i use short date formats, they will be in long date format when saved in database. i have used shortdatetime datatype in database as well.

seems like i need to format it again while displaying. is that the only solution?

thanx,|||SQL Server *only* supports date AND time. There's no getting around that fact, well not really anyway. My posting was talking about using the generic formats for rendering the values not storing them. You should always strive to use the users date formatting not impose one for them.