newbie question on TableDefs

I am swtiching from access/Jet to msde, and cannot figure out how to
retrieve the tabledefs from msde

This is how I did it with Jet:

Dim T As TableDef, D As Database, dbpath As String
dbpath = Application.CurrentProject.Path & "\data.mdb"
Set D = DBEngine.Workspaces(0).OpenDatabase(dbpath)
Set T = D.TableDefs(symbolName)

What is the equiavlent for msde/mssql?

MANY Thanks in adv."Ernesto" <tsh@.mathematicuslabs.com> wrote in message
news:0vWdnQohvoduenfdRVn-uA@.speakeasy.net...
> I am swtiching from access/Jet to msde, and cannot figure out how to
> retrieve the tabledefs from msde
> This is how I did it with Jet:
> Dim T As TableDef, D As Database, dbpath As String
> dbpath = Application.CurrentProject.Path & "\data.mdb"
> Set D = DBEngine.Workspaces(0).OpenDatabase(dbpath)
> Set T = D.TableDefs(symbolName)
> What is the equiavlent for msde/mssql?
> MANY Thanks in adv.

I'm not sure exactly what information you need, but one or both of these may
be what you're looking for:

-- List of table names in the current database
exec sp_tables @.table_type = '''table'''

-- Structure of an individual table
exec sp_help MyTable

If that's not useful, then you might want to give some more details -
personally, I don't know what a tabledef is in Access. Also, check out the
"System Stored Procedures" and "Metadata Functions" topics in Books Online,
as there are many ways to retrieve different items of metadata. Finally, if
you're interested in using a COM interface, then SQLDMO can retrieve and
manipulate objects in MSSQL.

Simon|||Simon,
Thanks very much for your reply.
TableDefs are simply the design of the tables.
So it seems that sp_Help does this, as you had guessed I needed.

I looked this up in a few places but stilll it is not clear how I should use
it.
e.g.
http://msdn.microsoft.com/library/d...p_help_304w.asp

says that it returns a "result set", but what is a "result set"? same as a
record set?
This shows how uninformed I am. But I have not found the syntax or how to
look at this result set.

Furthermore, the site says that sp_help works only on the current DB. How
can I specify the relevant database?

More of the code I have to work with is:

dbpath = Application.CurrentProject.Path & "\HOG.data.mdb"
'specify the db
Set D = DBEngine.Workspaces(0).OpenDatabase(dbpath) 'set the
db
Set T = D.TableDefs(symbolName) 'get the definition os
the table named symbolName

For i = 0 To T.Fields.Count - 1 ' look thriogh its fields to
see if "tdate" is one of them
If UCase(T.Fields(i).Name) = "TDATE" Then
isTimeSeries = True
...

If you have any further hints, I will be obliged.

E|||Ernesto (tsh@.mathematicuslabs.com) writes:
> says that it returns a "result set", but what is a "result set"? same as a
> record set?

Yes, "result set" is the proper terminology when you are talking databases.
(Just like "records" are called "rows" and "fields" are called "columns".

> This shows how uninformed I am. But I have not found the syntax or how to
> look at this result set.

You just say:

exec sp_help tbl

The "exec" keyword is optional if this is all you say, but if you are
looking at two, you need it at least for the second call to sp_help.

You run this command from Query Analyzer.

> Furthermore, the site says that sp_help works only on the current DB. How
> can I specify the relevant database?

You can say:

exec some_other_db..sp_help tbl

This applies to all system procedures (those that start with sp_).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You run this command from Query Analyzer.
> You can say:
> exec some_other_db..sp_help tbl
Thanks VERY much for following up.

One more thing, With apologies in advance if this is a stupid question:
I was asking about how to do it from VBA, instead of the Query analyzer.
Where/how will the result set be stored so that the code can inspect it.

TX|||Ernesto (tsh@.mathematicuslabs.com) writes:
> One more thing, With apologies in advance if this is a stupid question:
> I was asking about how to do it from VBA, instead of the Query analyzer.
> Where/how will the result set be stored so that the code can inspect it.

I would guess that you could use ADO to access the database and execute
the commands and get them in record sets.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks,
I found out that this is a direct substitute:

Set Rst = New ADODB.Recordset
Rst.Open "SELECT top 1 * FROM " & symbolName, ConnString

For I = 0 To Rst.Fields.Count - 1
If UCase(Rst.Fields(I).Name) = "TDATE" Then
isTimeSeries = True
...

Newbie question - Syntax for passing parameters to a sub report

Hey, I've looked pretty much everywhere I could, but I am unable to
figure out the correct syntax for my problem.
I have a main report that I am running with a stored procedure.
I also have a sub report that I am running with the same stored
procedure.
The stored procedure has 3 parameters. (a session identifier, an
operator, and a language code)
Here are the steps I have taken so far...
- added the subreport to my main report
- right click on the subreport (in the layout view) and click
Properties; then select the parameters tab.
- not sure what to do here.
** NOTE reportName is clients and the subreport is has the reportName
subClients
I thought the syntax would be... (main report)
Parameter Name: SubClients!@.SessionID
Parameter Value: =Parameters!SessionID.Value
(subreport)
Parameter Name: @.SessionID
Parameter Value: =Parameters!SessionID.Value
This doesn't work. I get this following error message...
A parameter in the subreport ?SubClients' has the name
?SubClients!SessionID.Value'. Parameter names must be CLS-compliant
identifiers.
This error and I also got a few others - not sure what to do.
Please help me, someone, anyone.
Thank you in advance.
Ciao
RobClick on expression when you are mapping the parameters of the subreport.
That wil bring you to the expression builder.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rob" <buju8@.yahoo.com> wrote in message
news:3cff8d2d.0501131505.5862fa69@.posting.google.com...
> Hey, I've looked pretty much everywhere I could, but I am unable to
> figure out the correct syntax for my problem.
> I have a main report that I am running with a stored procedure.
> I also have a sub report that I am running with the same stored
> procedure.
> The stored procedure has 3 parameters. (a session identifier, an
> operator, and a language code)
> Here are the steps I have taken so far...
> - added the subreport to my main report
> - right click on the subreport (in the layout view) and click
> Properties; then select the parameters tab.
> - not sure what to do here.
> ** NOTE reportName is clients and the subreport is has the reportName
> subClients
> I thought the syntax would be... (main report)
> Parameter Name: SubClients!@.SessionID
> Parameter Value: =Parameters!SessionID.Value
> (subreport)
> Parameter Name: @.SessionID
> Parameter Value: =Parameters!SessionID.Value
> This doesn't work. I get this following error message...
> A parameter in the subreport 'SubClients' has the name
> 'SubClients!SessionID.Value'. Parameter names must be CLS-compliant
> identifiers.
>
> This error and I also got a few others - not sure what to do.
> Please help me, someone, anyone.
> Thank you in advance.
> Ciao
> Rob

Newbie question - Need count to return multiple values

Hi all,
This is probably a simple question but I still new enough that I can't
figure it out (this is only my second real query i'm so REALLY new).
I have a table that looks like this:
CustKey InvoiceDate
01 2006-05-19
02 2006-05-19
03 2006-05-19
04 2006-04-28
02 2006-05-19
03 2006-05-19
04 2006-05-19
04 2006-05-19
03 2006-05-19
I want my output to look like this:
CustKey Total for 2006-05-19
01 1
02 2
03 3
04 2
Basically I need a list that will tell me, by CustKey, how many Invoices
were done on a given day.
Now I can do a quick count that will tell me for a given customer and date
but I don't know how to have it check and return values for all 4 customers
based on date.
So far I have:
SELECT COUNT * FROM "Table1"
WHERE CustKey = '01'
AND "InvoiceDate" = ('2006-05-19')
Any help would be appreciated. I'm trying to save myself from having to do
a manual count of invoices on a wly basis.
Thanks in advance,
NancySELECT CustKey,COUNT( *) FROM Table1
WHERE InvoiceDate = ('2006-05-19')
GROUP BY CustKey
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks Denis, I figured it would be something simple.
This works perfectly!
"SQL" wrote:

> SELECT CustKey,COUNT( *) FROM Table1
> WHERE InvoiceDate = ('2006-05-19')
> GROUP BY CustKey
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>sql

Newbie Question

I am trying to sum some data in a table, and I can't figure it out. Below
is some sample data
Date,Customer,Order_Amt,Paid_Amt
2004-10-02 00:00:00,101,1418.82,-1400.00
2004-10-02 00:00:00,101,265.21,-200.00,
2004-10-02 00:00:00,101,648.74,-648.74
2004-10-02 00:00:00,102,95.95,-95.95
2004-10-02 00:00:00,102,457.42,-450.00
I trying to sum all of the orders for each customer from yesterday, without
showing each order, just the date, customer number, total orders, total
paid. I also want to add a 5th column stating order_amt-Paid_amt. Although
pathetic, this is the furthest I got :
select date, cust, order_amt, paid_amt from day_sales
where date > getdate()-2
group by date, cust, order_amt, paid_amt
I would these results :
date,cust,order_total,paid_total,amt_owe
d
2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
Any help would be greatly appreciated.
ThanksTry this:
select date, cust,
Count(*) OrderCount,
Sum(order_amt) TotalAmt,
Sum(paid_amt) TotalPaid,
Sum(order_amt-Paid_Amt) Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||Hi
Check out GROUP BY and SUM in books online.
Try (untested);
Select date, cust, SUM(order_amt), SUM(paid_amt), SUM(order_amt)
-SUM(paid_amt) AS Outstanding, COUNT(*) AS No_Orders from day_sales
where date > getdate()-2
group by date, cust
Your getdate()-2 may not give you the exact information required if you
have times with the orders to round to day one way is to use convert e.g.
Select CONVERT(CHAR(8),date,112) AS Date, cust, SUM(order_amt),
SUM(paid_amt), SUM(order_amt) -SUM(paid_amt) AS Outstanding, COUNT(*) AS
No_Orders from day_sales
where CONVERT(CHAR(8),date,112) >= CONVERT(CHAR(8),getdate()-2,112)
group by CONVERT(CHAR(8),date,112), cust
John
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||thanks for your help, that did the trick. I have one quick question. How
does sql know how to count the total orders per customer ? What if I wanted
a count of all of the orders that equal 1418.82 ? I don't see anything in
the script below that points the count command to the customer number.
Thanks again for your help, I really appreciate it !!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
> Try this:
> select date, cust,
> Count(*) OrderCount,
> Sum(order_amt) TotalAmt,
> Sum(paid_amt) TotalPaid,
> Sum(order_amt-Paid_Amt) Balance
> from day_sales
> where date > DateAdd(day, -2, getdate())
> group by date, cust
> "J Abrams" wrote:
>|||The group By statement says to SQL:
1) Collect all the records which match the criteria, and group them into one
groups, based on the values of the columns Cust, and Date, Then
2) Output ONE ROW for each of those GROUPS...
3) Any expression in the Select Clause, which has an aggregate function
(Sum, Count, Min, Max, etc.) IS then evaluated for ALL The records in each o
f
those constructed groups...
"J Abrams" wrote:

> thanks for your help, that did the trick. I have one quick question. How
> does sql know how to count the total orders per customer ? What if I want
ed
> a count of all of the orders that equal 1418.82 ? I don't see anything in
> the script below that points the count command to the customer number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
>
>|||Hi
If you wanted to restrict the whole query to add up certain rows i.e an
order_amt value of 1418.82 then you would need to add this to the where
clause.
i.e.
where date > DateAdd(day, -2, getdate())
and order_amt = 1418.82
You should notice that the OrderCount column is less than without the
additional clause (unless the customer only ever orders the one
amount!).
If you wanted to have an additional count that summed everything but
counted the number of times they ordered for an amout of 1418.82
select date, cust,
Count(*) AS OrderCount,
SUM(CASE WHEN order_amt = 1418.82 THEN 1 ELSE 0 END) AS
OrderedSpecificAmtCount,
Sum(order_amt) AS TotalAmt,
Sum(paid_amt) AS TotalPaid,
Sum(order_amt-Paid_Amt) AS Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
The rest is in books online. Please spend some time reading it as is a
very rich source of information.
John
J Abrams wrote:
> thanks for your help, that did the trick. I have one quick question.
How
> does sql know how to count the total orders per customer ? What if I
wanted
> a count of all of the orders that equal 1418.82 ? I don't see
anything in
> the script below that points the count command to the customer
number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
out.
yesterday,
total

newbie on SQL Server needs some direction .adp and .mde

I'm trying to figure out what is going on. I have an .adp file that has a
connection DNS to a .mde file on the network. It is confusing. In the
connection dialog it has my computer as the server name and it has the
database I am using to connect to the database I want. How can I find out
what is the DNS database source? It seems like it is connected to itself
instead of the right DNS. I don't see any tables in tables but I do get a
list of queries on the server so it must be connected but why don't I see the
tables as well? This is the one I would like to use but I don't get the
world icons that show the real SQL server data. In fact I don't even see the
tables on the access .mde file.
I try to view some queries and I get the error message "ADO error: MSDTC
on Server 'myComputer' is not available. I have MS SQL Server running in
Services as well as SQL Server express. Mainly because I don't know which one
is best. I also get the error 'Ole DB error trace Ole 10B Profvider MS Jet
OlebDB 4.0 Open rowset returned ox.... The specified table does not exist'
which I think means the query cannot access the tables. Why can I get the
queries and not the tables?
If I access it through terminal server access frontend I can make changes to
the queries. I also can see the tables which include dbo tables on a Server
but I would rather write my queries on my own desktop version and not do
anything on the terminal services version.
On the terminal services .mde database there are some pass through queries.
In fact the one I think I need appear to be a pass through query. If I open
the query the data is 2 years old. Can I make a make table from the pass
through query to see if it updates the data? I really do not want to do
anything wrong to the frontend db on terminal services but I need to write
this one query for a report.
tia,DNS are only a name given to a connection string. You don't have to use a
DSN to connect to a SQL-Server; it's only one possibility amongst others.
Second, DNS are only for ODBC while ADP use only ADO. So, you cannot use a
DSN with ADP and even with ADO, ADP can connect only to a SQL-Server and not
to a MDB or a MDE file. Also, the fact that you can connect to a sql-server
doesn't mean that you will be able to see everything (tables, views, sp,
functions, etc.) on the server because of permission issues.
Looks like that you have copied an ADP file from a terminal server to a
local machine but that the permissions on the server are set to block you
from accessing the data from outside the forms in the ADP project or
something like that.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Janis" <Janis@.discussions.microsoft.com> wrote in message
news:7333903F-5E59-4DF0-8BAB-5C45137AF7A5@.microsoft.com...
> I'm trying to figure out what is going on. I have an .adp file that has a
> connection DNS to a .mde file on the network. It is confusing. In the
> connection dialog it has my computer as the server name and it has the
> database I am using to connect to the database I want. How can I find out
> what is the DNS database source? It seems like it is connected to itself
> instead of the right DNS. I don't see any tables in tables but I do get a
> list of queries on the server so it must be connected but why don't I see
> the
> tables as well? This is the one I would like to use but I don't get the
> world icons that show the real SQL server data. In fact I don't even see
> the
> tables on the access .mde file.
> I try to view some queries and I get the error message "ADO error: MSDTC
> on Server 'myComputer' is not available. I have MS SQL Server running in
> Services as well as SQL Server express. Mainly because I don't know which
> one
> is best. I also get the error 'Ole DB error trace Ole 10B Profvider MS
> Jet
> OlebDB 4.0 Open rowset returned ox.... The specified table does not
> exist'
> which I think means the query cannot access the tables. Why can I get the
> queries and not the tables?
>
> If I access it through terminal server access frontend I can make changes
> to
> the queries. I also can see the tables which include dbo tables on a
> Server
> but I would rather write my queries on my own desktop version and not do
> anything on the terminal services version.
> On the terminal services .mde database there are some pass through
> queries.
> In fact the one I think I need appear to be a pass through query. If I
> open
> the query the data is 2 years old. Can I make a make table from the pass
> through query to see if it updates the data? I really do not want to do
> anything wrong to the frontend db on terminal services but I need to write
> this one query for a report.
> tia,

Newbie needs help with User Defined Functions

Well I have never worked with SQL Server before and I am trying to figure things out on my own. I have references that I have been using to teach myself how to use SQL Server. I have run into a problem thou. I have to use an if statement in this User Defined Function. But I'm getting compiling errors on the IFs, ELSE IFs and the THENs. Can someone please tell me what is wrong with the syntax of my function. I have pasted it below. Any help given will be very much appreciated. Thanks in advance.

CREATE FUNCTION pbaweb.MonthTransForm
(
@.MonthNumber CHAR(2)
)
RETURNS CHAR

AS

BEGIN

IF(SELECT PayMonth from GroupActivity) = "1"

THEN

DECLARE @.MonthTitle VARChar(15)
Set @.MonthTitle = "January"
RETURN @.Monthtitle;

ELSE IF (SELECT PayMonth from GroupActivity) = "2" THEN
set @.MonthTitle = "Feburary"
RETURN @.Monthtitle;
ELSE IF (SELECT PayMonth from GroupActivity) = "3" THEN
set @.MonthTitle = "March"
RETURN @.Monthtitle;
ELSE IF (SELECT PayMonth from GroupActivity) = "4" THEN
set @.MonthTitle = "April"
RETURN @.Monthtitle;
ELSE IF (SELECT PayMonth from GroupActivity) = "5" THEN
set @.MonthTitle = "May"
RETURN @.Monthtitle;
END IF;
END;Hi,

Check BOL for IF ELSE syntax. THEN is not part of IF statement in SQL

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

Best of luck|||DeepakKhattar is correct, there is no THEN, you would use BEGIN with END.

IF Boolean_expression BEGIN
{ sql_statement | statement_block }
END

However in your case you should look at the CASE statement
SELECT @.MonthTitle = CASE
WHEN PayMonth = "1" THEN "January"
WHEN PayMonth = "2" THEN "February"
WHEN PayMonth = "3" THEN "March"
.
.
.
ELSE "December"
END
FROM GroupActivity