Retruning Stored XML in a XML EXPLICIT statement

I have data stored in the database in XML format.

The data looks like this...

<entries><entry name = "Scott" /></entries>

When I return it using an xml explicit statement, it is return like this...

"Scott" /></entries>"/>

Is there a way to force it to use the normal tags instead of sql changing it?SELECT * FROM table for xml auto, elements

Retrieving Return value from stored procedure declaratively

Hi.

I have a stored procedure "sp1" which returns a value (with the sql statement Return @.ReturnValue).

Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.

Thanks in advance .

i do not know what will you sp return but i suppose that it is and INT

so you write this way;

int retrunvalue=sqlcommad.excutenonequery();

so the returned value will be passed to you int.

hope this will help

|||

this is sample code, it can help you:

Here is a sample sproc that populates output parameters
from the Northwind Products table:

CREATE PROCEDURE CustOrderOne
@.CustomerID nchar(5),
@.ProductName varchar(50) output,
@.Quantity int output

AS
SELECT TOP 1 @.ProductName=PRODUCTNAME, @.Quantity =quantity
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @.CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

And here is an example of some C# code to return and display the output parameters:

using System;
using System.Data;
using System.Data.SqlClient;
namespace OutPutParms
{
class OutputParams
{
[STAThread]
static void Main(string[] args)
{
using(SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;"))
{
SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@.CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@.ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@.Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters["@.ProductName"].Value);
Console.WriteLine(cmd.Parameters["@.Quantity"].Value.ToString());
Console.ReadLine();
}
}
}
}

|||

The above 2 replies does not actually get the return value, which is a special parameter.

The first reply returns the row affected count and the second reply just gets the value out output parameters.

I am afraid I do not know how to retrieve the return value declaratively using controls like object data sources.

However of you are familiar with using SqlCommands then the following code shows you how to get the return values from stored procedures assuming your stored procedure is returning values which is different to result sets, row counts, and output parameters.

SqlCommand cmd =new SqlCommand("this is the query", connection);//create a parameter for the return valueSqlParameter param =new SqlParameter();param.Direction = ParameterDirection.ReturnValue;param.ParameterName ="returnValue";//add to parameter to collectioncmd.Parameters.Add(param);//execute commandcmd.ExecuteNonQuery();//get the return valueint retVal =int.Parse(cmd.Parameters["returnValue"].Value.ToString);

Retrieving Last 'N' records from a Table in the Database

Hello House,

Please, I need the SQL statement/keyword used to Retrieve the Last 'N' records from a table in the Database; just as we have TOP 'N' for the First N-records in a table.

For example, ("Select TOP 'N' * From Particulars Order by Phone DESC", conn)

where N is the number of records to be retrieved.

I use both SQL Server 2005 and Microsoft Access.

Thanks.

Quote:

Originally Posted by Temidayo

Hello House,

Please, I need the SQL statement/keyword used to Retrieve the Last 'N' records from a table in the Database; just as we have TOP 'N' for the First N-records in a table.

For example, ("Select TOP 'N' * From Particulars Order by Phone DESC", conn)

where N is the number of records to be retrieved.

I use both SQL Server 2005 and Microsoft Access.

Thanks.

create an identity column on your table...select your table, ORDER BY (that identity column) DESC...the first record will be the last record inserted on your table

Retrieving database record with the lowest value in field aaa?

How do I code a SQL SELECT statement so that always only this record is retr
ieved
which matches a certain criteria AND has the lowest ID (= value in key field
aaa)?
It must me something like
SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aaa)
)
As a result either zero or at most 1 record should be passed back.
George"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
aaa=lowestkey(column(aaa))
> As a result either zero or at most 1 record should be passed back.
> George
>
I am not exactly sure what you are looking for, but typically this done with
a subselect:
SELECT a.col1, b.col2 FROM table-name a
WHERE a.col1 = 'somevalue'
AND a.col2 = (select b.min(col2) from FROM table-name b where ...)|||You can use TOP clause.
SELECT top 1 * FROM ... WHERE somefield='somevalue'
order by aaa asc
AMB
"George Dainis" wrote:

> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
>|||This would appear to be a good candidate for an inline view using
rownum.
Something like;
Select ...<information you want to see>
from (Select ...<information you want to see>
_ from ...
_ where somefield='somevalue'
_ order by aaa)
where rownum < 2;
(Ignore the underscore, they're just there for holding the indentation)|||George Dainis wrote:
> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
>
Lookup min() (in your textbook?)
Regards,
Frank van Bortel|||try
ROW_NUMBER() OVER(order by ...)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
> retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
> field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
> aaa=lowestkey(column(aaa))
> As a result either zero or at most 1 record should be passed back.
> George
>
AND aaa = ( <use a subquery with the MIN() function> )
++ mcs|||George Dainis wrote:

> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
I am going to assume, given that you have posted this to every usenet
group you can spell, among them comp.databases.oracle.misc,
microsoft.public.sqlserver.programming, comp.databases.oracle, and
comp.databases.ibm-db2, that you are trying to find someone to do your
homework for you.
The optimal solution will vary by product and even version so posting
as you have says something about what you are trying to do.
As we don't do other people's homework for them and you seemingly have
made no attempt to solve this on your own ... go talk to your faculty
advisor about what you have done and ask for help there.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
> retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
> field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
> aaa=lowestkey(column(aaa))
lookup MIN in the sql reference.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com|||On Thu, 27 Jan 2005 19:50:22 +0100, george.dainis@.bluecorner.com (George
Dainis) wrote:

>How do I code a SQL SELECT statement so that always only this record is ret
rieved
>which matches a certain criteria AND has the lowest ID (= value in key fiel
d aaa)?
>It must me something like
>SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aaa
))
>As a result either zero or at most 1 record should be passed back.
SELECT *
FROM ...
WHERE somefield = 'somevalue'
AND aaa = (SELECT MIN(aaa)
FROM ...
WHERE somefield = 'somevalue')
Andy Hassall / <andy@.andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Retrieving Data using ADO

I need help retrieving data using ADO (executing a select statement)
From a SQL Server 2000 database and directly populate a table in a SQL Server Database on a different server. Does anyone have an idea on how to accomplish this task?
Thanks in advance.
RegardsR U Sure U need to do it manually with ADO ?

There are many ways of doing this type of thing|||Yes, I would like to know if anyone has ever accomplish this task using ADO. I need to incorporate the solution of this task into a project I'm working on. Thanks inadvance.

Rgeards

retrieving data from table with 7 million entries takes time

Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..

Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?

Andy

Hi Andy,

Could you post the DDL please, including the the definintion of the indexes and the query you're running. Having an index doesn't help you if you don't select on fields that are in the index. (And even then....)

Also, pulling all 7 million rows over the network can take some time, especially when the rows are 'wide' or the network is slow. Are you selecting all the rows or just a small subset?

What you can also use is Tools > Database Engine Tuning Advisor in Mamagement studio. I haven't used it, but for these kind of question it could very well help you out.

Regards,

GJ

|||When is used a criteria (WHERE clause) you force engine to use that index that mean speed. When is not, the system make full table scan and it have to go to 1,2,3 , to the 7 million records that need time. So, you have to build or invent an appropriate WHERE clause to speed and resolve all user request.|||

o The following query will get the 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

You can get the missing index details in the following way:

The following query determines which missing indexes comprise a particular missing index group, and displays their column details.

For the sake of this example, the missing index group handle is 24.(You will need to change the handle value with handle values which comes up from the earlier query)

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats migs

INNER JOIN sys.dm_db_missing_index_groups mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24 <<put your handle value here>>

For details on this refer to the following articles:

http://msdn2.microsoft.com/en-us/library/ms345421.aspx

Using Missing Index Information to Write CREATE INDEX Statements

http://msdn2.microsoft.com/en-us/library/ms345405.aspx

Retrieving data from 4 tables

hi guys, i need some help in making an SQL statement.
i am really having a hard time making the ryt one so please
help!!!!!! :eek:

i'll first give an introduction.
i have this program that needs to display a data, however these
data will come from 4 tables.

(actual contents of the tables and scenarios of the program
was changed to make it easier for others to understand the
situation and the problem) :p

TABLE 1 (COLLEGES)
This table consists of the different colleges that a university has.
ie. College of Engineering, College of Law, etc

table design:
COLLEGE_NO
COLLEGE_NAME
SEM_NO

TABLE 2 (COURSES)
This table consist of the courses that a university offers.
ie. Theology, Chemistry, Algebra, etc.

table design:
COURSE_NO
SEM_NO

TABLE 3 (OFFERINGS)
This table consist of the number of course offerings of a
specific course for a specific college and semester.

table design:
COLLEGE_NO
COURSE_NO
NO_OF_OFFERING
SEM_NO

TABLE 4 (COURSE_MASTER)
Master table for the different courses available

table design
COURSE_NO
COURSE_NAME

--

There is a screen where a user can add Colleges
(ie. College of Architecture) and the data is stored in the
COLLEGES table (TABLE 1).
There is also a screen where a user can add Courses
(ie, Calculus, Programming, P.E., etc) and the data is stored
in COURSE_MASTER table (TABLE 4) and COURSES table (TABLE 2)
* this may be weird but please bare with me since i am just
immitating the actual scenario for better understanding

Now I have a 3rd screen where a user will input the number of
offering for a particular course for a specific college.
This is a sample image of the screen

ENGINEERING ARTS LAW
COURSE
Programming 3 0 0
Theology 3 3 3
Biology - - -

Now this data will be saved on the OFFERINGS table (TABLE 3)
The Colloge_No, the Course_No, the No_Of_Offering, and Sem_No
will be saved.

Now the behaviour of the program will be like this...
All the Colleges that is stored in the COLLEGES table will be
displayed as Column Headers in the screen
All the Courses that is stored in the COURSES table will be
displayed as Row Headers.
However, if you guys noticed we need to display the Course Name
and this data is not stored in COURSES table, it is stored in
COURSE_MASTER table.

Lastly we need to display the number of offerings.
this is displayed on the OFFERINGS table.
In the example, the Course Programming has 3 offerings
for the College of ENgineering and 0 offerings for Law
This data is stored in the OFFERINGS table.

If you guys noticed, that under the Course Biology,
"-" is written. This means that there is no record of
Number of Offerings yet at the OFFERINGS table

So now this is my problem, how or what SQL statement do
I need to make to retrieve all the data stored in the
OFFERINGS table, (this already contains the No of offerings,
courses, and colleges) and aside from this also retireve all
the other Colleges and Courses that is already stored
in their respective tables but may
not yet exists in the OFFERINGS table.

so this is my problem...
please help...
thnx in advance...Looks like you need to use a LEFT JOIN.
What have you got so far? (Post SQL statement)|||this is what i've done so far.

select t1.SEM_NO, t1.COURSE_NO, t2.COLLEGE_NAME, null
from COURSES t1, COLLEGES t2
where t1.SEM_NO = t2.SEM_NO and
t1.SEM_NO = 'User Input' and
and (t1.SEM_NO, t1.COURSE_NO, t2.COLLEGE_NAME)
not in (select
t4.SEM_NO, t4.COURSE_NO,t4.COLLEGE_NAME
from OFFERINGS t4
where t4.SEM_NO = 'User Input')
group by t1.COURSE_NO, t2.COLLEGE_NAME
union
select t4.SEM_NO, t4.COURSE_NO, t4.COLLEGE_NAME, t4.NO_OF_OFFERING
from OFFERINGS t4 where t4.SEM_NO = 'User Input';

This SQL statement is already OK, but I am having some problem
including the table COURSE_MASTER, so I can retrieve the
COURSE_NAME and I cant make the ORDER BY work...

any suggestions.
thnx.|||* this may be weird but please bare with me since i am just
immitating the actual scenario for better understanding
can you change your example to use the real tables?

the table designs you gave in post #1 don't make any sense

FYI it should be "please bear with me" because "please bare with me" means let's take our clothes off together|||*chuckle* :)|||already fixed the problem.

thnx for all the help.

:)|||Would you like to post your solution so that others with similar problems may benefit from it? :)

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.
use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/
|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
>
|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/de...reate_7r1l.asp
http://sqlservercode.blogspot.com/
|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.
use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/
|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
>
|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/de...reate_7r1l.asp
http://sqlservercode.blogspot.com/
|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the w
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like W('01/08/06') to return 2. I cannot find
such a thing.
Thanks.use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/|||No, don't use DATEPART for wnumbers. The function doesn't calculate w
numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for wnumbers. The function doesn't calculate
> w numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
>|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||you don't need to, that was just to show you 2 values
The ISOW ifunction can be found here
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
http://sqlservercode.blogspot.com/|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different w numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week
numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
>|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
http://sqlservercode.blogspot.com/|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

retrieve the primary keys with SQL DMO and vb.net

Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier
Each Table object has a Keys collection. Each Key has a Type property.
Failing that, you can use T-SQL:
select
*
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
1 in (
objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsClustKey')
, objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsNonclustKey')
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"oLiVieR" <ocheneson@.hotmail.com> wrote in message
news:OMUoOMCyFHA.3556@.TK2MSFTNGP12.phx.gbl...
Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this INERT, DELETE and UPDATE information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerThe only commands you have to work with are DBCC LOG and fn_dblog. However,
these doesn't return
information in any type of clear text, and there is not information on how t
o decode the information
they return (or even if they contains what you need).
You can talk to MS and ask them for information on how to do this, which wou
ld put you on the same
level as the companies that wrote these applications, but there is no public
ly available API or
command for getting "meaningful" information from the transaction log.
Consider putting a request at http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:Othcy1qcGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Ser
ver, which needs to
>include basically a redo statement such as the actual INSERT and DELETE sta
tement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server
has log files and there
>are third party applications that an retrieve this information, however som
e of them have problems
>getting the correct or even getting any UPDATE information, plus I do not n
eed a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old value
s using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on opera
tion type and timestamp
> as well as other useful parameters. DB2 can even send this INERT, DELETE a
nd UPDATE information to
> a message queue.
>
> I need a way to get this information from SQL Server without using databas
e triggers but using C++
> code. It would be nice to be able to query for this information, similar t
o Oracle's
> implementation. Can anyone point me in the right direction? Thanks in adva
nce for any help you can
> provide.
>
> Charles Parker
>|||Tibor,
Thanks for the quick reply. I will try the feedback link you suggested below
but I do not understand why Microsoft let Oracle and DB2 get ahead of them
in terms of this feature. Could it be in SQL Server 2005?
Charles...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HAfv8qcGHA.536@.TK2MSFTNGP02.phx.gbl...
> The only commands you have to work with are DBCC LOG and fn_dblog.
> However, these doesn't return information in any type of clear text, and
> there is not information on how to decode the information they return (or
> even if they contains what you need).
> You can talk to MS and ask them for information on how to do this, which
> would put you on the same level as the companies that wrote these
> applications, but there is no publicly available API or command for
> getting "meaningful" information from the transaction log.
> Consider putting a request at
> http://lab.msdn.microsoft.com/productfeedback/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Charles Parker" <charles.parker@.whamtect.com> wrote in message
> news:Othcy1qcGHA.3888@.TK2MSFTNGP02.phx.gbl...
>|||> Could it be in SQL Server 2005?
Unfortunately, no. I guess that there haven't been enough customer request t
o warrant spending time
on doing this compared to other feature request MS has on the product. But o
f course, only people
sitting in the product planning meetings can say for sure... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:%23rNuBhscGHA.4312@.TK2MSFTNGP05.phx.gbl...
> Tibor,
> Thanks for the quick reply. I will try the feedback link you suggested bel
ow but I do not
> understand why Microsoft let Oracle and DB2 get ahead of them in terms of
this feature. Could it
> be in SQL Server 2005?
> Charles...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23HAfv8qcGHA.536@.TK2MSFTNGP02.phx.gbl...
>

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerSee my reply in .programming.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:e0f%23N2qcGHA.3936@.TK2MSFTNGP05.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Ser
ver, which needs to
>include basically a redo statement such as the actual INSERT and DELETE sta
tement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server
has log files and there
>are third party applications that an retrieve this information, however som
e of them have problems
>getting the correct or even getting any UPDATE information, plus I do not n
eed a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old value
s using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on opera
tion type and timestamp
> as well as other useful parameters. DB2 can even send this information to
a message queue.
>
> I need a way to get this information from SQL Server without using databas
e triggers but using C++
> code. It would be nice to be able to query for this information, similar t
o Oracle's
> implementation. Can anyone point me in the right direction? Thanks in adva
nce for any help you can
> provide.
>
> Charles Parker
>

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerSee my reply in .programming.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:e0f%23N2qcGHA.3936@.TK2MSFTNGP05.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Server, which needs to
>include basically a redo statement such as the actual INSERT and DELETE statement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server has log files and there
>are third party applications that an retrieve this information, however some of them have problems
>getting the correct or even getting any UPDATE information, plus I do not need a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old values using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on operation type and timestamp
> as well as other useful parameters. DB2 can even send this information to a message queue.
>
> I need a way to get this information from SQL Server without using database triggers but using C++
> code. It would be nice to be able to query for this information, similar to Oracle's
> implementation. Can anyone point me in the right direction? Thanks in advance for any help you can
> provide.
>
> Charles Parker
>

retrieve either day of week or day name for current date

How can I code an mdx statement to return either the number for the current day of the week (ie. 1 - 7), or the name of the current day (Monday, Tuesday, etc.)? day(now()) gets me the actual calendar day

Thank you,

PB

Hopefully, this helps you get where you want to go:

Code Snippet

with member [Measures].[x] as

datepart("w",VBAMDX!Now())

select [x] on 0

from [Adventure Works]

;

B.