Is using Enterprise Manage across the internet secure?

If I am using enterprise manager across the internet to access an SQL Server
, is it secure? Can I make database changes, passwords changes and view con
fidential table data securely. Or do I need to use another mechanism.Enterprise Manager is just an application, it sounds like you are worried
about the data transferred over the wire. If so, you should look into SSL
encryption.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft...
http://support.microsoft.com/?id=316898
324777 WebCast: Microsoft SQL Server 2000: How to Configure SSL Encryption
http://support.microsoft.com/?id=324777
318605 INF: How SQL Server Uses a Certificate When the Force Protocol
http://support.microsoft.com/?id=318605
325757 INF: Using SQL Server 2000 with FIPS 140-1 Ciphers
http://support.microsoft.com/?id=325757
302409 FIX: Unable to Connect to SQL Server 2000 When Certificate
Authority... http://support.microsoft.com/?id=302409
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:

> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
>
>
>
>
> - Show quoted text -

Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.
Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =
Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:

> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>
|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew =
> Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
>
>
> - Show quoted text -

Is this the correct syntax?

Hi,
Is this the correct syntax for this Access pass-thru query? I'm new to
TSQL and I have a book on order but I still need to move forward with
this project while I'm waiting for it.
I want to update a field in a SQLServer table,
BillingNumber(datatype=text) has one(1) to many leading zero's, I need
to strip one off so I had the dba create a new field BillingNumberNew
which I trying to update with BillingNumber minus one leading zero.
Would this be correct?
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew = Substing(tblPhysLineFinal.BillingNumber,
2,Len(tblPhysLineFinal.BillingNumber)-1)
thanks
bobh.Hi bobh,
pls try this
This will remove leading first from BillingNumber
UPDATE tblPhysLineFinal
SET tblPhysLineFinal.BillingNumberNew =Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
varchar))-1) from tblPhysLineFinal
Regards,
Amol Lembhe
"bobh" wrote:
> Hi,
> Is this the correct syntax for this Access pass-thru query? I'm new to
> TSQL and I have a book on order but I still need to move forward with
> this project while I'm waiting for it.
> I want to update a field in a SQLServer table,
> BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> to strip one off so I had the dba create a new field BillingNumberNew
> which I trying to update with BillingNumber minus one leading zero.
> Would this be correct?
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew => Substing(tblPhysLineFinal.BillingNumber,
> 2,Len(tblPhysLineFinal.BillingNumber)-1)
> thanks
> bobh.
>|||Thanks!
bobh.
On Nov 12, 1:27 pm, Amol Lembhe <amo...@.gmail.com> wrote:
> Hi bobh,
> pls try this
> This will remove leading first from BillingNumber
> UPDATE tblPhysLineFinal
> SET tblPhysLineFinal.BillingNumberNew => Substring(cast( BillingNumber as varchar),2,Len(cast (BillingNumber as
> varchar))-1) from tblPhysLineFinal
> Regards,
> Amol Lembhe
>
> "bobh" wrote:
> > Hi,
> > Is this the correct syntax for this Access pass-thru query? I'm new to
> > TSQL and I have a book on order but I still need to move forward with
> > this project while I'm waiting for it.
> > I want to update a field in a SQLServer table,
> > BillingNumber(datatype=text) has one(1) to many leading zero's, I need
> > to strip one off so I had the dba create a new field BillingNumberNew
> > which I trying to update with BillingNumber minus one leading zero.
> > Would this be correct?
> > UPDATE tblPhysLineFinal
> > SET tblPhysLineFinal.BillingNumberNew => > Substing(tblPhysLineFinal.BillingNumber,
> > 2,Len(tblPhysLineFinal.BillingNumber)-1)
> > thanks
> > bobh.- Hide quoted text -
> - Show quoted text -

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/l...an2.asp?ad_id=7
http://brettatkin.com/clients/tgc/l...ean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
BrettIf the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/listings_edit_clean2.asp?ad_id=7
http://brettatkin.com/clients/tgc/listings_edit_clean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").V alue)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value )%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br >
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><b r>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%>< br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><b r>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)% ><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> <br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value )%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").V alue)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value )%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br >
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value )%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><b r>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value )%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value )%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br >
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").V alue)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><b r>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%>< br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><b r>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)% ><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> <br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value )%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value )%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br >
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value )%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><b r>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value )%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%><b r>
Submit:<%=(rs_listing.Fields.Item("submit_date").V alue)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
Brett
If the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/listings_edit_clean2.asp?ad_id=7
http://brettatkin.com/clients/tgc/listings_edit_clean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
BrettIf the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this possible? please help (MS access query of sql database)

I work for an organisation that uses a bespoke document imaging system, the
database of which is an MS sql server.
We have MS Access and already use it for some querying of the database.
The database comprises a large number of distinct cases, which are
differentiated by case reference numbers, in one field (table?) of the
database. Each of these cases may have many documents associated with it,
denoted by the reference number, and these documents may be "new", "pending"
or "complete" shown in another data field.
We need to know how many cases have work outstanding on them.
Our problem is that our bespoke software will only count the number of
documents of each status, and not the cases.

Is it possible to design an MS Access query which will count the number of
different reference numbers which have any "new" documents associated, but
wont count each case more than once?

I am reasonably computer-savvy, I just don't know Access or SQL..
If I know it is possible, I don't mind putting in the effort to find out
how. I just don't want to waste time barking up the wrong tree ;-)

Of course any advice about how this would be achieved, such as pointers to
the right parts of the MS Access helpfiles, or to relevant websites would be
greatly appreciated. Some quick code would be even better...
Also, if there is any 3rd-party software which could easily do this, I need
help discovering it...I have looked long and hard, but don't know enough
about what I am looking for.
Yours in hope..
--
anthonyberet
Please reply in the groups, as my Usenet email address is not working at the
moment.anthonyberet wrote:
> I work for an organisation that uses a bespoke document imaging
> system, the database of which is an MS sql server.
> We have MS Access and already use it for some querying of the
> database.
> The database comprises a large number of distinct cases, which are
> differentiated by case reference numbers, in one field (table?) of the
> database. Each of these cases may have many documents associated with
> it, denoted by the reference number, and these documents may be
> "new", "pending" or "complete" shown in another data field.
> We need to know how many cases have work outstanding on them.
> Our problem is that our bespoke software will only count the number of
> documents of each status, and not the cases.
> Is it possible to design an MS Access query which will count the
> number of different reference numbers which have any "new" documents
> associated, but wont count each case more than once?
> I am reasonably computer-savvy, I just don't know Access or SQL..
> If I know it is possible, I don't mind putting in the effort to find
> out how. I just don't want to waste time barking up the wrong tree ;-)
> Of course any advice about how this would be achieved, such as
> pointers to the right parts of the MS Access helpfiles, or to
> relevant websites would be greatly appreciated. Some quick code would
> be even better...
> Also, if there is any 3rd-party software which could easily do this,
> I need help discovering it...I have looked long and hard, but don't
> know enough about what I am looking for.
> Yours in hope..

You'd probably be better off in an MS Access group - try
microsoft.public.access|||Gordon Burgess-Parker wrote:
> anthonyberet wrote:
>> I work for an organisation that uses a bespoke document imaging
>> system, the database of which is an MS sql server.
>> We have MS Access and already use it for some querying of the
>> database.
>> The database comprises a large number of distinct cases, which are
>> differentiated by case reference numbers, in one field (table?) of
>> the database. Each of these cases may have many documents associated
>> with it, denoted by the reference number, and these documents may be
>> "new", "pending" or "complete" shown in another data field.
>> We need to know how many cases have work outstanding on them.
>> Our problem is that our bespoke software will only count the number
>> of documents of each status, and not the cases.
>>
>> Is it possible to design an MS Access query which will count the
>> number of different reference numbers which have any "new" documents
>> associated, but wont count each case more than once?
>>
>> I am reasonably computer-savvy, I just don't know Access or SQL..
>> If I know it is possible, I don't mind putting in the effort to find
>> out how. I just don't want to waste time barking up the wrong tree
>> ;-)
>>
>> Of course any advice about how this would be achieved, such as
>> pointers to the right parts of the MS Access helpfiles, or to
>> relevant websites would be greatly appreciated. Some quick code would
>> be even better...
>> Also, if there is any 3rd-party software which could easily do this,
>> I need help discovering it...I have looked long and hard, but don't
>> know enough about what I am looking for.
>> Yours in hope..
> You'd probably be better off in an MS Access group - try
> microsoft.public.access

And I didn't see ALL those crossposts.... doh!|||On Sat, 18 Oct 2003 01:03:05 +0100, "anthonyberet"
<witfb001@.sneakemail.com> wrote:

>I work for an organisation that uses a bespoke document imaging system, the
>database of which is an MS sql server.
>We have MS Access and already use it for some querying of the database.
>The database comprises a large number of distinct cases, which are
>differentiated by case reference numbers, in one field (table?) of the
>database. Each of these cases may have many documents associated with it,
>denoted by the reference number, and these documents may be "new", "pending"
>or "complete" shown in another data field.
>We need to know how many cases have work outstanding on them.
>Our problem is that our bespoke software will only count the number of
>documents of each status, and not the cases.
>Is it possible to design an MS Access query which will count the number of
>different reference numbers which have any "new" documents associated, but
>wont count each case more than once?
>I am reasonably computer-savvy, I just don't know Access or SQL..
>If I know it is possible, I don't mind putting in the effort to find out
>how. I just don't want to waste time barking up the wrong tree ;-)
>Of course any advice about how this would be achieved, such as pointers to
>the right parts of the MS Access helpfiles, or to relevant websites would be
>greatly appreciated. Some quick code would be even better...
>Also, if there is any 3rd-party software which could easily do this, I need
>help discovering it...I have looked long and hard, but don't know enough
>about what I am looking for.
>Yours in hope..

this is very difficult without any idea of the tables involved, but
lets try;

Table: Cases
(caseNumber integer) *PK

Table: Documents
(docNumber integer,
caseNumber integer, *FK
status varchar(20))

SELECT COUNT(1) FROM Cases
WHERE caseNumber IN
(SELECT caseNumber
FROM Documents WHERE status='New')

Of course if your tables don't look like this then you need another
approach...|||Lyndon Hills wrote:
> On Sat, 18 Oct 2003 01:03:05 +0100, "anthonyberet"
> <witfb001@.sneakemail.com> wrote:
>> I work for an organisation that uses a bespoke document imaging
>> system, the database of which is an MS sql server.
>> We have MS Access and already use it for some querying of the
>> database. The database comprises a large number of distinct cases,
>> which are differentiated by case reference numbers, in one field
>> (table?) of the database. Each of these cases may have many
>> documents associated with it, denoted by the reference number, and
>> these documents may be "new", "pending" or "complete" shown in
>> another data field. We need to know how many cases have work
>> outstanding on them. Our problem is that our bespoke software will
>> only count the number of documents of each status, and not the cases.
>>
>> Is it possible to design an MS Access query which will count the
>> number of different reference numbers which have any "new" documents
>> associated, but wont count each case more than once?
>>
>> I am reasonably computer-savvy, I just don't know Access or SQL..
>> If I know it is possible, I don't mind putting in the effort to find
>> out how. I just don't want to waste time barking up the wrong tree
>> ;-)
>>
>> Of course any advice about how this would be achieved, such as
>> pointers to the right parts of the MS Access helpfiles, or to
>> relevant websites would be greatly appreciated. Some quick code
>> would be even better... Also, if there is any 3rd-party software
>> which could easily do this, I need help discovering it...I have
>> looked long and hard, but don't know enough about what I am looking
>> for. Yours in hope..
> this is very difficult without any idea of the tables involved, but
> lets try;
> Table: Cases
> (caseNumber integer) *PK
> Table: Documents
> (docNumber integer,
> caseNumber integer, *FK
> status varchar(20))
> SELECT COUNT(1) FROM Cases
> WHERE caseNumber IN
> (SELECT caseNumber
> FROM Documents WHERE status='New')
> Of course if your tables don't look like this then you need another
> approach...

I think only 2 tables are relevant in the first instance - "reference" and
"status".
Can you rcommend a site where I can read about the functions of the
intructions you have posted?
In particular, the "SELECT COUNT(1) FROM Cases" bit looks very powerful.
However, is this SQL or is it bespoke code used by MS Access?
Thank you for your help.
--
Put "usenet" in the subject-line if you want to mail me, otherwise it will
bounce.
Do you use filesharing networks? If so, please visit my online poll:
http://vote.sparklit.com/web_poll.spark/780772
anthonyberet|||On Mon, 20 Oct 2003 23:33:41 +0100, "anthonyberet"
<witfb001@.sneakemail.com> wrote:

<snip>
>> SELECT COUNT(1) FROM Cases
>> WHERE caseNumber IN
>> (SELECT caseNumber
>> FROM Documents WHERE status='New')
>>
>I think only 2 tables are relevant in the first instance - "reference" and
>"status".
>Can you rcommend a site where I can read about the functions of the
>intructions you have posted?
>In particular, the "SELECT COUNT(1) FROM Cases" bit looks very powerful.
>However, is this SQL or is it bespoke code used by MS Access?
>Thank you for your help.

I would google for sql tutorials. www.sqlcourse.com looks basic. Also
there should be some of this at least in the access help files. I
guess you do need to know what your looking for though. Above, all the
words in capitals are sql keywords, and they should be in the help
files. The IN is a subselect which could be replaced with EXISTS and a
slightly different syntax.

SELECT COUNT() FROM, just counts the number of rows that meet the
conditions. It just returns one number, not the actual rows of data.
There are similar options like MAX, MIN AVERAGE which apply to number
columns. A quick word of warning if you plan to use them, be careful
of the case where the number column is null. Average in particular may
give wrong results.

SQL has relatively few keywords, although each of the big
manufacturers have added their own.sql

Is this feasible?

Hi
We have an access desktop app with front-end/back-end situation where all
tables are in the back end and everything else, forms/queries, are in the
front end. We would like to make a web app to use the same access database
but are worried about access being able to handle web app users. Is it
feasible to move the access backend tables to the SQL Server and link the
sql server tables in the access front end? It will not help access desktop
app as all processing will still be done by access but the web app can
presumably benefit from tables being on the SQL Server? Then over time we
can also re-write the access desktop app to be native sql.
Thanks
RegardsHiya John,
The LAST thing you want to do is put Access as the backend of anything with
more than one or two users. Put your data in SQL Server.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||Put all your data in SQL Server (MSDE) and use stored procedures and views
for extracting data and use Access strictly for the front end of the
application.
Jim
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||"William Morris" wrote
> The LAST thing you want to do
> is put Access as the backend of
> anything with more than one or two
> users. Put your data in SQL Server.
Where did you ever get that idea?
There are many factors that enter into multiuser access to Jet databasese,
including the requirements, design, and implementation of the application,
and the hardware, software, and network environments. If all those factors
are near-perfect, we have reliable reports of Access supporting 100+ happy,
concurrent users. Even if all are not near-perfect, we routinely see reports
of split Access-Jet databases supporting 30 to 70 users. We've had whines in
the past about "Access falling over with four users" and, any that we could
get details on turned out that all the factors were about as far from
perfect as possible, but the primary culprit was design by someone who
didn't know what they were doing.
Your user estimate is obviously nearly as low as it could possibly be,
unless some dunderhead claimed Access wouldn't support _any_ users.
And, by the way, "native SQL" does not have a desktop UI capability, just so
you won't make that mistake again.
Larry Linson
Microsoft Access MVP|||What you describe is certainly possible. Access makes a good client
application for server databases on the same LAN or WAN. If the person
needing the rich-client-interface is, however, accessing across the
Internet, you'll want to run Access on the server via some sort of Remote
Access Software (for one user, something like pcAnywhere or ReachOut would
be fine; for multiple users, take a look at Virtual Private Network and
Windows Terminal Server / Citrix).
However, Access itself would not be involved in supporting the web users in
such a situation. You have a Jet database, and you'd access it with either
DAO, or more likely, ADO code from .asp pages. If the database was on the
same machine, you can almost certainly support more concurrent users than
you could with an Access-Jet split database on a LAN. A Jet database can be
quite adequate for a web site with "modest" traffic. See my response to
William Morris for some numbers on concurrent users.
The advice you have received suggesting that you _need_ to convert to SQL
Server, without any indication of the requirements, or the expected number
of concurrent users, is "hasty", to say the least.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||We encountered a similar scenario before. Yes, you can upsize (use the
upsizing utility) the MS-Access database tables to a SQL server and link
them back in the MS-Access database. You may want to check the following in
your front end. (1) AutoNumber (identity) (2) All the queries. There are
some flavors of MS-Access query may not work well with linked SQL server
tables. You may also have to tweak your front end to avoid any performance
issues. You may also want to consider the effort required to do the above
before making the final decision.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:#VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>

Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is there the equivalent of the Access Query Design Grid in SQL Server?

Hi. Is there the equivalent of the Access Query Design Grid in SQL
Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
Grid? If so, does it design queries for XML data? Also if so, I read
somewhere on the web that the Access Query Design Grid doesn't do joins
so would I be correct to assume the SQL Server version wouldn't do
joins either?
Thank you. Regards.
There is no XQuery designer unfortunately.
Sorry
Michael
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133931766.736605.160010@.z14g2000cwz.googlegr oups.com...
> Hi. Is there the equivalent of the Access Query Design Grid in SQL
> Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
> Grid? If so, does it design queries for XML data? Also if so, I read
> somewhere on the web that the Access Query Design Grid doesn't do joins
> so would I be correct to assume the SQL Server version wouldn't do
> joins either?
> Thank you. Regards.
>

Is there the equivalent of the Access Query Design Grid in SQL Server?

Hi. Is there the equivalent of the Access Query Design Grid in SQL
Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
Grid? If so, does it design queries for XML data? Also if so, I read
somewhere on the web that the Access Query Design Grid doesn't do joins
so would I be correct to assume the SQL Server version wouldn't do
joins either?
Thank you. Regards.There is no XQuery designer unfortunately.
Sorry
Michael
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133931766.736605.160010@.z14g2000cwz.googlegroups.com...
> Hi. Is there the equivalent of the Access Query Design Grid in SQL
> Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
> Grid? If so, does it design queries for XML data? Also if so, I read
> somewhere on the web that the Access Query Design Grid doesn't do joins
> so would I be correct to assume the SQL Server version wouldn't do
> joins either?
> Thank you. Regards.
>

Is there any way to know the last access time of a table?

I have some junk tables in my database, but I am not sure if they are really not in use.

Does sql server log the last time that a table gets accessed? when I say access I mean any actions toward the table, including select, update, insert, etc.

thanks in advance.

I am unsure about your answer, but you can log it manually to a table by generating stored procedures for selecting/updating/etc.

|||thanks for the suggestion. I thought about that too but I can't possibly rewrite all codes across the application for this. also it may be too resource intensive. shouldn't there be a way to check the last access in sql server? apparently in the file-based system you can do so.

Is there any way to improve the performance of select statements in MS SQL?

Hi All,
I am getting slower performance of select statements in MS SQL. I am
finding select statements in MS SQL are even slower than MS ACCESS. Is
there any way to improve the performance of select statements in MS
SQL by tuning the database of anything else??

Thanks in advance!
HoqueIn article <b1a93c73.0310311931.79d07486@.posting.google.com>,
sohelcsc@.yahoo.com says...
> Hi All,
> I am getting slower performance of select statements in MS SQL. I am
> finding select statements in MS SQL are even slower than MS ACCESS. Is
> there any way to improve the performance of select statements in MS
> SQL by tuning the database of anything else??
> Thanks in advance!
> Hoque

That's a very open question with MANY answers:

Several things come to mind:

Improperly configured drive arrays
Lack of proper indexes or clustered index
TOO MANY indexes
Failure to run a maintenance plan on the tables
Lack of memory or CPU
Improperly coded select statements - never use SELECT *, always specify
the columns you want.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||sohelcsc@.yahoo.com (Leader) wrote in message news:<b1a93c73.0310311931.79d07486@.posting.google.com>...
> Hi All,
> I am getting slower performance of select statements in MS SQL. I am
> finding select statements in MS SQL are even slower than MS ACCESS. Is
> there any way to improve the performance of select statements in MS
> SQL by tuning the database of anything else??
> Thanks in advance!
> Hoque

There are things you an do

1. If the query is complex try rewriting the logic of the joins e.g.
instead of selecting from tableA joining on to tableB try the reverse.
2. Avoid using LIKE as this causes a table scan to e performed
3. Have the tables you are using in your query got appropriate
indexes? beware of adding indexes as other parts of your application
may slow down because the system has to maintain an additional index.
4. If it has suitable indexes is the index appropriate for being made
a clustered index? There can only be 1 clustered index on a table
as this determines the physical placement of the rows on the disc.
Also if the index has values inserted anywhere in its expected range
of values updating may be slowed too much.

Just a few ideas

Hope this helps

Duncan|||Not without knowing what you are doing.

Run the query in query analyser and look at the execution plan.
This will tell you which tables are causing the problem and you can
index them or recode the query appropriately.

Is there any way to have an email sent on an event

I'm using SQL with an Access form for the front end. Is there anyway to have an email sent on the trigger of an event within the form? I'm not sure where to even begin.

A good place to start is Books Online. Look up Triggers, SQL Agent, Jobs.

The path you are headed down has a short but steep learning curve.

|||

Unless you are using SQL Server 2005, you should not use the trigger approach to send email directly. This is due to the fact that triggers behave synchronously letting your transaction wait for the response of the external mail sending application / procedure. This could stale your transaction process and let other users wait for ressources to transaction might block. SQL Server uses SQL Server Service broker for sending mails with the DBMail, putting the mail in a queue, sending the mail asynchronously. For SQL Server 2000 you would have to implement your custom solution, writing an entry in a table with the appropiate mail information and grabbing the information from the table periodically to send the emails via an agent job.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Is there any way to access report objects at runtime

Hi all,

In my report, I have an image object that I want it to load dynamically at runtime. How can I access it from code.

Many thanks,

Huy Le

You can reference the image as a url, or from the database (I think). I'd probably go the url route, if you can either reference the image file itself via url, or else have a .net web page that displays the image based on query string parameters.

Steve

is there any way AMO app can get information from data source without reprompting user for user

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.

is there any way AMO app can get information from data source without reprompting user for u

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.

Is there any point in views? vs stored procedures?

I do all my data access through stored procedures. Is there any benefit in creating views that the stored procedure accesses. At the moment, I tend to just write the select and join within the stored procedure.

Do you have to do any manipulation of the data? Are the stored procedures doing anything else than just returning data? Will the data change? Do you need to pass any parameters to the stored procedure?

As you can probably guess by my questions, there isn't a straight forward answer as it really depends on the data and what you are doing with it as to which method you should choose for each function.

|||

An interesting philosophical discussion is no doubt about to erupt.

If you are going to be re-using a very complex join, it makes sense to use a view; kind of like a re-usable business object. Its generally easier to maintain than a sp and is more accessible to people coming behind you. You can more easily make little tweaks if you need to make variations on a theme.

But of course sps are a lot more powerful.

|||I have seperate stored procedures for updating the data. These stored procedures return data. But my real question is, within the stored procedure, is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x|||

jagdipa:

is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x

There isn't really a right answer. You've given a dummy scenario so we can only really offer general advice and we can't say whether one method will be better than the other.

Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not. You will also have to look at the execution plans to see if there are any performance issues to take note of.

|||"Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not."

This is really what I want to know. The example I gave is actually what I am doing (with a few more tables).

As far as I understand, a view is optimised. But so is a stored procedure. So, in theory, using either method should have the exact same performance (they are optomised in the same way using the same algorithms). I am probably wrong here.

But there is also the design point of view. Is there a design reason for using views? (exect the obvious putting security on a view seperatly from a stored procedure).|||

jagdipa:

This is really what I want to know.

But we don't know what other objects will be accessing the data. It's your database so only you know the answer to that question.

As I've said there is no right answer. You will have to decide for yourself which is the best approach, based on how often the data gets accessed, what other functions may need the same data and any performance issues that come out of your testing. Sorry I can't give you a direct answer as to which one is best but that's because one isn't simply better than the other. It all boils down to the individual needs of the database and that's something that only you can answer.

jagdipa:

But there is also the design point of view. Is there a design reason for using views?

If the data will be reused in several places then it makes sense to consolidate it into a view. Again, this is just theory and may not be the best solution for your needs.

|||

There are a number of real-world parameters to consider beyond ease of initial coding. Its probably easier to maintain a view in a large organization, plus its a whole lot easier for someone to look at in order to determine whether it's something they need to use (you could set up a view schema to act like a business object library). If you leave, the person coming in behind you can look at the views with a click,as opposed to having to execute the stored procedures.

Generally, the more static an object is, the easier it is to use a view for it's datasource. If you have something that gets databound on page load and doesn't change, a view is fine and dandy. And you could use a view as your base datasource and add filters to it if you are doing cascading ddls, for example.

But if you build a large object library, especially if you build generators for it, it's probably better to go with stored procedures. People following after you will have to be pretty high-speed anyway to be able to work with your code, so accessability really isn't an issue, and users can look at using your business objects as datasources instead of your views. I guess the higher you are scaling, the better sps are versis views.

|||

I stand firmly and forthrightly with those who say, "It depends!"

I would just add these comments (but really I agree with just about everything everyone else has said):

1. If the join is complicated, using a view sure makes it easier to reuse. It's much less error prone.

2. Views sometimes come with an unanticipated consquence, namely that you are guaranteed to access all the tables in the view. Other programmers, who may not know exactly what's in the view, may use a view incorrectly as a result. For example, lets say you have a view that joins 5 tables. Someone else comes along and retrieves data from the view, but really they only need data from 3 of the tables. By using the view, you not only force them to access all 5 but -- more importantly -- it may affect what data gets returned depending on whether you're using inner joins vs outer joins. I've seen this happen.

3. Views also have a nice security implication -- you can control access through views, though in practice this isn't done all that much.

Also, remember that your choice is NOT view vs proc because you can (should, some would argue) use a proc with a view.

|||Thanks for the clarifications. Views are ok, but I think I will use them sparingly. The reason for this is because I use a few user defined functions as well. In a stored procedure, I can create a temp table and pull the data I want into this. Then I can run the function on just that data (instead of all the data).

I think dbland07666 is right - especially with point 2. When I did not have much experience, I stuck everything in views, even when only pulling out very little. The view died very quickly when I added another 5 user defined functions to it !!!|||

Now that you are more experienced, I think you should look into developing a business object layer. It will allow you to do things programatically that would require dozens of lines of code with a single call. For example, you could so something like Personnel.GetList() to create a collection of Personnel objects which you can sort or filter or what-have-you in the object layer as opposed to calling stored procedures to do these various things with parameters. It's faster than going to the database and can be made to reside in cache so it scales up really nicely for multi-user environments.

|||Hi Charles,

I would love to learn more on this. Have you got a good tutorial I can use?
At the moment, I am going towards a sort of middle tier - I am new at this so its not great. I just create classes that access the relevant data (via stored procedures). It has come in very useful at times. But I am only creating web pages for a website that is probably only accessed 5 times an hour !! (its a B2B website).|||

http://aonaware.com/OOP1.htm

You can do stuff like build server-side validation into your objects to protect your database, you can write generic screens that have behaviors that are inherited by child screens which handle specific objects, all sorts of good stuff.

Here's the framework we use at this shop. It's free and its growing...

http://forums.lhotka.net/forums/default.aspx

Is there any better method for DTS ?

I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
ThanksYou could have an 'execute SQL task', that checks for the existence of the
table first, and drops it if needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
I have created a package that just export a number of tables to an Access
Database and it works fine.
However, when I rerun the package, I get error message as it cannot create
the tables (This is because they are already exists in the Access database).
What is a better way for me to handle this problem ? Should I edit the DTS
package to remove the "Create Table" step (It involves 23 tables) OR is
there any better way to create a package that can be reused ?
Thanks|||"Peter" wrote:
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access database).
> What is a better way for me to handle this problem ? Should I edit the DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
Destination Objects First", would this be of any use?
Cheers,
Ian|||Dear Narayana,
Thank you for your advice. However, I don't know how to create an "Execute
SQL Task" to check the existence and delete the table in the Access Table.
Can you give me some advice ?
Thanks
Peter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23KxHhN%23oFHA.3828@.TK2MSFTNGP12.phx.gbl...
> You could have an 'execute SQL task', that checks for the existence of the
> table first, and drops it if needed.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:eSw2cx9oFHA.568@.TK2MSFTNGP10.phx.gbl...
> I have created a package that just export a number of tables to an Access
> Database and it works fine.
> However, when I rerun the package, I get error message as it cannot create
> the tables (This is because they are already exists in the Access
> database).
> What is a better way for me to handle this problem ? Should I edit the
> DTS
> package to remove the "Create Table" step (It involves 23 tables) OR is
> there any better way to create a package that can be reused ?
> Thanks
>
>|||Dear Ian,
Thank you for your advice. However, I find that the options only applies to
database object and don't work for exporting to Access Database.
Peter
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:522E7384-0E4F-4EF3-8E04-45E5158C3757@.microsoft.com...
>
> "Peter" wrote:
>> I have created a package that just export a number of tables to an Access
>> Database and it works fine.
>> However, when I rerun the package, I get error message as it cannot
>> create
>> the tables (This is because they are already exists in the Access
>> database).
>> What is a better way for me to handle this problem ? Should I edit the
>> DTS
>> package to remove the "Create Table" step (It involves 23 tables) OR is
>> there any better way to create a package that can be reused ?
>> Thanks
> In the "Copy SQL Server Objects Task" in DTS, there is an option to "Drop
> Destination Objects First", would this be of any use?
> Cheers,
> Ian