Regarding Accessing on every objects within the sql 2005

Hi guys , can I know is that any way to set up some authentication for user access all the objects within the database after login successfully. For example, after access login into the database server and would like to click on certain database (eg : A) , then pop up the user access login page again. Thxcould you explain this a bit more, I did′n′t get your point from the explanation.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Sorry for the short explanation. Er... let me give a scenario. Once I open SQL management studio, there's a user login pop up window in order I able to access into my server. After login successfully,there are 3 database displays and stored in my servers ( Db A,B and C). When I want to click on the A database, then will pop up the user login window again for authorization. After that, if I want to choose a specific object (eg: Table 'Test') , then there's the use login window pop up again for authorization. At here, which means that authorization will show first before proceed any actions on any objects from the database in my server,.

So is there any possible to make such scenario? I'm just curious about it and would like to enhance my SQL security for my server and away from data thefting (For example, while away from the pc for a while and there's a staff come towards the pc for viewing and modifying the data). Thx for any assistance. Have a nice day.

From,

Hans

|||

Hi,

no SSMS does not have this functionality. once autenticated, permissions are checked upon access to the objects not prompting for any credentials again.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You should lock your console when away from your machine.

Thanks
Laurentiu

|||Thx for the advise. Seems like SQL 2005 doesn't have ' Always prompt for username and password login ' feature which in SQL 2000 then.|||

There was no such feature in SQL Server 2000. Maybe you are referring to a client feature, in which case you should post your inquiry on the Tools forum.

Thanks
Laurentiu

Reg URL Report Access Path Format

I'm trying to render a report created using SSRS to open in a web browser in windows form.
This is the URL used:
"http://localhost/reportserver?/C:/Documents and Settings/xsbangalore/My Documents/Visual Studio 2005/Projects/prmRpt/prmRpt&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&StartDate='01/01/2005'&EndDate='01/01/2007'";

I'm not able to see the report.
Error: Unable to link to Web page

What is missing?

The URL must refer to a report that is in the Report Server catalog database. You cannot refer to documents on disc as reports. Upload the report to the Report Server and then use Report Manager or the reportserver url to browse to the report. From there you will see how a URL is constructed.

Example:

http://localhost/reportserver?/Report Project1/report1&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&StartDate='01/01/2005'&EndDate='01/01/2007'";

|||If I dont have a report server created, what's the alternative?

OR
How do I create a resport server instance?

reg linked server

We are using linked servers to access another server, we are using windows authentication. but when we use

EXEC master.dbo.sp_addlinkedserver @.server = 'SERVER', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.locallogin = NULL, @.useself = N'False', @.rmtuser = N'sa', @.rmtpassword = N'sa'

When we connect using a SQL login as specified in the above line we are able to connect to the linked server.

But when we want to connect using a Windows user it is connecting.(we want to connect using a specific windows user i.e only one windows user will be there in the server and we shall access the server using that windows user)

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.locallogin = NULL, @.useself = N'False', @.rmtuser = N'Domain\userid', @.rmtpassword = 'password'

Is there any other way to connect to the linked serrver.. we dont want to use the SQL server login to connect to the linked server.. how to use windows authentication to connect to the linked server. Anyone have tried it out.. Thanks in advance

It should work try the following example...

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go

Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Domain\Userid', @.rmtpassword = 'Password'

go

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

go

Exec sp_droplinkedsrvlogin 'SERVER', null

Exec sp_dropserver 'SERVER'

Refreshing Links in Access doesn't allow me to Add Records!

Hi
I have an application in Access2003 with linked tables. When I create the
links manually (picking DNS etc) I have no problems. When I then try to
recreate the links programatically (different users) I am not able to add an
y
records to any of the tables! If I erase all the linked tables and reconnect
manually with the other user I have no problems!
I am using the relink code from
http://support.microsoft.com/defaul...kb;en-us;159691
Any and all suggestions Welcome!!!
Regards
Meir
Suggestions?There is a seperate newgroup for acceess/adp/sql server integration.
Post there
--
Regards
R.D
--Knowledge gets doubled when shared
"mrrcomp" wrote:

> Hi
> I have an application in Access2003 with linked tables. When I create the
> links manually (picking DNS etc) I have no problems. When I then try to
> recreate the links programatically (different users) I am not able to add
any
> records to any of the tables! If I erase all the linked tables and reconne
ct
> manually with the other user I have no problems!
> I am using the relink code from
> http://support.microsoft.com/defaul...kb;en-us;159691
>
> Any and all suggestions Welcome!!!
> Regards
> Meir
>
> Suggestions?
>|||As long as the user has permissions in the database and you have a
primary key defined on the table, inserts an dupdates shouldn't be a
problem. However, instead of using a DSN, you can supply connection
information in your code. This should get you started:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=password"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
On Fri, 7 Oct 2005 05:17:03 -0700, "mrrcomp"
<mrrcomp@.discussions.microsoft.com> wrote:

>Hi
>I have an application in Access2003 with linked tables. When I create the
>links manually (picking DNS etc) I have no problems. When I then try to
>recreate the links programatically (different users) I am not able to add a
ny
>records to any of the tables! If I erase all the linked tables and reconnec
t
>manually with the other user I have no problems!
>I am using the relink code from
>http://support.microsoft.com/defaul...kb;en-us;159691
>
>Any and all suggestions Welcome!!!
>Regards
>Meir
>
>Suggestions?sql

Refresh with jsp doesnt work

Hi!

I've created a simple jsp page with a crystal report viewer on it. I used Crystal Reports Developer to design the report, as db I use access and IBM WSED to create and run the jsp file on a server. That works fine, but when I would refresh the report it shows no recordset (and also no error message appears). Why I can't refresh the data?

The code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. page import="com.crystaldecisions.report.web.viewer.CrystalReportViewer" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory,
com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2,com.crystaldecisions.sdk.occa.report.reportsource.IReportSource"
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
CrystalTest.jsp

<%
try {

IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();

String report = "/CrystalReport/Report1.rpt";

Object reportSource = rptSrcFactory.createReportSource(report, request.getLocale())

CrystalReportViewer viewer = new CrystalReportViewer();
viewer.setReportSource(reportSource);

viewer.setOwnPage(true);
viewer.setTop(80);
viewer.setDisplayGroupTree(false);
viewer.setHasLogo(false);
viewer.setHasRefreshButton(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
viewer.dispose();
} catch(Exception e)
{

out.println("CrystalTest: "+e);
}
%>

</BODY
</HTML>hi there!

I've now solved the problem with a jdbc/db2 connection.

The solution is, that I must connect the Crystal Report with jdbc/db2 to the database and also connect in java/jsp to the database as the follows (only samplecode from helpfile):

setDbLogonViewReport.jsp

<%@. page import= "com.crystaldecisions.report.web.viewer.*,
com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.report.web.viewer.*" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2" %>

<%
Object reportSource = session.getAttribute("reportSource");
if (reportSource == null)
{
String report = "/reports/sample.rpt";
IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();
reportSource = rptSrcFactory.createReportSource(report, request.getLocale());
session.setAttribute("reportSource", reportSource);
}

ConnectionInfos connInfos = new ConnectionInfos();
IConnectionInfo connInfo1 = new ConnectionInfo();
connInfo1.setUserName("reportLogin");
connInfo1.setPassword("");
connInfos.add(connInfo1);

CrystalReportViewer viewer = new CrystalReportViewer();

viewer.setReportSource(reportSource);
&n bsp; viewer.setEnableLogonPrompt(false);
viewer.setDatabaseLogonInfos(connInfos);

if (session.getAttribute("refreshed") == null)
{
viewer.refresh();
session.setAttribute("refreshed", "true");
}

viewer.setOwnPage(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
%>

Ask if you've more questions to that.

Refresh after import SS2K

Hello,
Whenever I import a table from Access into SQL Server 2K using the data
transformation services wizard (import), I have to shut down Enterprise
Manager and then start it again to see the table. I have tried refreshing
the table and the server group, but to no evail. Any help with this would
be appreciated...
Thanks in advance,
sck10
Hi sck10,
From your descriptions, I understood that you would like to know you will
have to shut down your SQL Server Enterprise Manager when you running DTS
Wizard. Have I understood you? Correct me if I was wrong.
However, I am not sure why you will have to restart SQL Server Enterprise
Manager? Will it hangs? Is there any error messages in Event Log or Error
Logs? More detailed information, I believe, will make us closer to the
resolution.
Additioanlly, you could launch DTS Import/Export Wizard by Start -> Run ->
dtswiz.exe without starting SQL Server Enterprise Manager.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
The problem is that after I import a table using the Import Wizard, I am
unable to see the table name in the table section, even though I can create
queries and view from the table. If I close Enterprise Manager and then
re-start it, the table name appears. I have tried refreshing the table
section, the database section and the server group section, but the table
name still does not appear.
Thanks, sck10
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:LHhxXj0CFHA.764@.cpmsftngxa10.phx.gbl...
> Hi sck10,
> From your descriptions, I understood that you would like to know you will
> have to shut down your SQL Server Enterprise Manager when you running DTS
> Wizard. Have I understood you? Correct me if I was wrong.
> However, I am not sure why you will have to restart SQL Server Enterprise
> Manager? Will it hangs? Is there any error messages in Event Log or Error
> Logs? More detailed information, I believe, will make us closer to the
> resolution.
> Additioanlly, you could launch DTS Import/Export Wizard by Start -> Run ->
> dtswiz.exe without starting SQL Server Enterprise Manager.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||Hi sck10,
It seems strange that you will have to restart Enterprise Manager to see
the table names. Have you installed the latest version of SQL Server
service pack? Is it possible for you to install SQL Server Client Tools to
see whether the reinstallation will take effect?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Refresh after import SS2K

Hello,
Whenever I import a table from Access into SQL Server 2K using the data
transformation services wizard (import), I have to shut down Enterprise
Manager and then start it again to see the table. I have tried refreshing
the table and the server group, but to no evail. Any help with this would
be appreciated...
Thanks in advance,
sck10Hi sck10,
From your descriptions, I understood that you would like to know you will
have to shut down your SQL Server Enterprise Manager when you running DTS
Wizard. Have I understood you? Correct me if I was wrong.
However, I am not sure why you will have to restart SQL Server Enterprise
Manager? Will it hangs? Is there any error messages in Event Log or Error
Logs? More detailed information, I believe, will make us closer to the
resolution.
Additioanlly, you could launch DTS Import/Export Wizard by Start -> Run ->
dtswiz.exe without starting SQL Server Enterprise Manager.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
The problem is that after I import a table using the Import Wizard, I am
unable to see the table name in the table section, even though I can create
queries and view from the table. If I close Enterprise Manager and then
re-start it, the table name appears. I have tried refreshing the table
section, the database section and the server group section, but the table
name still does not appear.
Thanks, sck10
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:LHhxXj0CFHA.764@.cpmsftngxa10.phx.gbl...
> Hi sck10,
> From your descriptions, I understood that you would like to know you will
> have to shut down your SQL Server Enterprise Manager when you running DTS
> Wizard. Have I understood you? Correct me if I was wrong.
> However, I am not sure why you will have to restart SQL Server Enterprise
> Manager? Will it hangs? Is there any error messages in Event Log or Error
> Logs? More detailed information, I believe, will make us closer to the
> resolution.
> Additioanlly, you could launch DTS Import/Export Wizard by Start -> Run ->
> dtswiz.exe without starting SQL Server Enterprise Manager.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hi sck10,
It seems strange that you will have to restart Enterprise Manager to see
the table names. Have you installed the latest version of SQL Server
service pack? Is it possible for you to install SQL Server Client Tools to
see whether the reinstallation will take effect?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Refresh after import SS2K

Hello,
Whenever I import a table from Access into SQL Server 2K using the data
transformation services wizard (import), I have to shut down Enterprise
Manager and then start it again to see the table. I have tried refreshing
the table and the server group, but to no evail. Any help with this would
be appreciated...
--
Thanks in advance,
sck10Hi sck10,
From your descriptions, I understood that you would like to know you will
have to shut down your SQL Server Enterprise Manager when you running DTS
Wizard. Have I understood you? Correct me if I was wrong.
However, I am not sure why you will have to restart SQL Server Enterprise
Manager? Will it hangs? Is there any error messages in Event Log or Error
Logs? More detailed information, I believe, will make us closer to the
resolution.
Additioanlly, you could launch DTS Import/Export Wizard by Start -> Run ->
dtswiz.exe without starting SQL Server Enterprise Manager.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
The problem is that after I import a table using the Import Wizard, I am
unable to see the table name in the table section, even though I can create
queries and view from the table. If I close Enterprise Manager and then
re-start it, the table name appears. I have tried refreshing the table
section, the database section and the server group section, but the table
name still does not appear.
Thanks, sck10
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:LHhxXj0CFHA.764@.cpmsftngxa10.phx.gbl...
> Hi sck10,
> From your descriptions, I understood that you would like to know you will
> have to shut down your SQL Server Enterprise Manager when you running DTS
> Wizard. Have I understood you? Correct me if I was wrong.
> However, I am not sure why you will have to restart SQL Server Enterprise
> Manager? Will it hangs? Is there any error messages in Event Log or Error
> Logs? More detailed information, I believe, will make us closer to the
> resolution.
> Additioanlly, you could launch DTS Import/Export Wizard by Start -> Run ->
> dtswiz.exe without starting SQL Server Enterprise Manager.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hi sck10,
It seems strange that you will have to restart Enterprise Manager to see
the table names. Have you installed the latest version of SQL Server
service pack? Is it possible for you to install SQL Server Client Tools to
see whether the reinstallation will take effect?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Refresh Access Client

Is there a way to update data on a MS Access 2000 client from SQL Server without polling a table using the form's onTimer event?

It would be much more elegant if I could push the data to the clients every 15-30 minutes when the data on SQL Server gets refreshed.

Can DTS do this? The forms I'm talking about are select only, no editing.

I know I can do this with java and multicasting, should work with MM Flash as well with listeners. I would really like to take this app to Flash, but we have a 1 month timeline to port from Access to SQL Server.

Thanks,
CarlTry using Access Data Projects. It uses a direct connection to SQL Server and it is what I reccomend for all Access front ends because it provides performance improvements over local and linked tables.|||Cool, that's what we're doing, and ya, the performance increase is huge, even going from pc app to two tier.

I didn't know that would auto refresh my forms, I'll have to see what you mean, I was just telling the boss how tightly integrated sql server is with Access.

Myforms are firing stored procs for their recordsource, wouldn't I have to refire the proc?

Thanks again,
Carl|||yes. this response is too short.|||I ended up creating a one record, two field table, one's TGGL_BIT which flips back and forth between 0 and 1 and the datetime. The app grabs the state at startup, then polls a stored procedure every 5 seconds to look for the bit to toggle, if it does, it fires all the select procedures.

Suprisingly, it doesn't adversely affect the user experience and works pretty well.
Carl|||I also want to say we're looking into TCP/IP multicasting using java sockets. very cool stuff if we can get it going. We're thinking Flash listener may work on the client side.

Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
Regards
For the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>
|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

Reformed access user needs advise for future

Hi
I have a vba/vb/access background but I have finally seen the light and have
decided to take up my next project with sql server backend (vb.net front).
So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
RegardsFor the database side, see http://www.aspfaq.com/2120
Unfortunately, I wrote the article long before .NET came about, so you won't
get any client app coding specifics, but it should still be helpful.
If you have the opportunity for additional learning curve, I recommend
learning C# as opposed to VB.Net...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>|||As a person who used ASP\VBScript\VBA and had a comfort zone myself I agree
with Aaron. Adopting C# now is the smartest thing you can do now that you're
starting to see that the light can burn even brighter.
Give us a break because we're tired of explaining why over and over. Just
get out of that comfort zone and do it now while you're in transition and
trying to learn OOP.
As for your questions I can briefly say searching the web always works for
me when I have broad open ended questions...
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eGUQVFeaIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a vba/vb/access background but I have finally seen the light and
> have decided to take up my next project with sql server backend (vb.net
> front). So here is
> my question;
> What are the recommended db development guidelines to achieve both a) a
> good
> user experience of being able to scroll to any record using record
> navigation buttons and b) the db efficiency requirement of not loading too
> many records in dataset at one time. If there is such a strategy to which
> many agree then there should be a sample code app somewhere. It would help
> me enormously to see the guts of an actual well written db app - no matter
> how trivial as long as it covers the necessary detail - instead of advise
> like don't do this or that without the coding specifics.
> So here is a chance for the worthy to lead a recent convert (albeit
> reluctant due to self deficiency on sql server side).
> Thanks
> Regards
>
>

Refining MS ACCESS data to csv file using T-SQL

Hello,

I have a table with these sample data pattern:
Col1 Col2
001 2
002 1
002 2
002 1
003 4
003 4
003 3
003 3
003 5

The expected output in the csv file should be exactly like this:
001,2
002,4
002,
002,
003,19
003,
003,
003,
003,

In the csv file, the number next to the topmost 002 is the sum of the Col2 where Col1
is 002 and the remaining field next to other 002 rows will be blank, the same process repeats for 003 and so on.

Would anyone please help me to make a T-SQL statement for the above requirement? I am using VB codes.

Thanks

k

1. Add an AutoNumber field named as ID to your table;

2. Create the query in Access:

SELECT t1.Col1, t2.sumCol2
FROM yourTable AS t1 LEFT JOIN [SELECT Col1, SUM(Col2) as sumCol2, min(ID) as minID
FROM yourTable Group By Col1]. AS t2 ON (t1.ID=t2.minID) AND (t1.Col1=t2.Col1);

3. Export this query to a csv file from Access.

|||

Thank you very much limno. Your T-SQL code perfectly solved my problem.

k
sql

Referring to report items

Hi,
Is there any way to directly access a value contained in, for example, a
textbox?
If I were to create a textbox ('textbox1') with a default 'value' attribute
of "Hello World!", how would I make 'textbox2' get the value of 'textbox1'
and set its own 'value' to equal it?
I thought maybe it would be something like an expression in textbox2 along
the lines of: "=Controls!textbox1.Value"
Of course, I can see problems if controls aren't rendered etc, but I thought
I'd ask.
Thanks,
CraigTry =ReportItems!textbox1.Value
Regards,
Davy Ramirez
Winsight - Paris
http://www.winsight.fr
"CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
news:34FA58AE-092E-42F7-B371-CAC59F9E0568@.microsoft.com...
> Hi,
> Is there any way to directly access a value contained in, for example, a
> textbox?
> If I were to create a textbox ('textbox1') with a default 'value'
attribute
> of "Hello World!", how would I make 'textbox2' get the value of 'textbox1'
> and set its own 'value' to equal it?
> I thought maybe it would be something like an expression in textbox2 along
> the lines of: "=Controls!textbox1.Value"
> Of course, I can see problems if controls aren't rendered etc, but I
thought
> I'd ask.
> Thanks,
> Craig|||Please see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp
for more information about ReportItems and Globals in general.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy Ramirez" <davy.ramirez.removethis@.winsight.fr> wrote in message
news:%23gr%23UsssEHA.820@.TK2MSFTNGP12.phx.gbl...
> Try =ReportItems!textbox1.Value
> Regards,
> Davy Ramirez
> Winsight - Paris
> http://www.winsight.fr
> "CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
> news:34FA58AE-092E-42F7-B371-CAC59F9E0568@.microsoft.com...
>> Hi,
>> Is there any way to directly access a value contained in, for example, a
>> textbox?
>> If I were to create a textbox ('textbox1') with a default 'value'
> attribute
>> of "Hello World!", how would I make 'textbox2' get the value of
>> 'textbox1'
>> and set its own 'value' to equal it?
>> I thought maybe it would be something like an expression in textbox2
>> along
>> the lines of: "=Controls!textbox1.Value"
>> Of course, I can see problems if controls aren't rendered etc, but I
> thought
>> I'd ask.
>> Thanks,
>> Craig
>

Referring to another DB in a stored procedure.

Hey,
I have a stored procedure in a SQL Server DB that I need to access a
table in a differant DB on the same server. Can someone help me with
the syntax for this? I am lost.
EX - I am in DB "XYZ" using stored procedure "QQQ" and in this stored
procedure I want to reference a table "account" on another DB "123".
What is the syntax to do this?
Right now I have 123.account but that does not work.
Thanks for the help
BrianBrian,
Try:
DATABASE.OWNER.OBJECT
so
123.dbo.account
HTH
Jerry
"blinky44" <briandunderhill@.hotmail.com> wrote in message
news:1129310332.718912.225520@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a stored procedure in a SQL Server DB that I need to access a
> table in a differant DB on the same server. Can someone help me with
> the syntax for this? I am lost.
> EX - I am in DB "XYZ" using stored procedure "QQQ" and in this stored
> procedure I want to reference a table "account" on another DB "123".
> What is the syntax to do this?
> Right now I have 123.account but that does not work.
> Thanks for the help
> Brian
>|||perfect, thanks! God I hate being a newbie! :)|||Gotta start somewhere.
Andrew J. Kelly SQL MVP
"blinky44" <briandunderhill@.hotmail.com> wrote in message
news:1129310914.913612.76840@.g14g2000cwa.googlegroups.com...
> perfect, thanks! God I hate being a newbie! :)
>sql

Referencing other databases in SQL Statements

Hello,

I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.

Can anyone tell me the syntax for referencing the access database?

Is it something like: [TABLENAME].dbo.FIELDNAME ?

Just to clarify, i have

Microsoft Access Database
Table 1 (UnitHistory)

SQL Database
Table 1 (UnitHistory)

How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.

Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?

Thanks guys1. look up in Sql book online for the syntax to create a linked server to Access.
2. do something like this to update Access:

Update A
set UnitHistory = S.UnitHistory
from [Access_linkedserver]...[Tb_name] A, [Sql_Tb_Name] S
where A.pkid=S.pkid
-- and

Referencing AS columns

I'm just converting my Access database to SQL Server and have come across a number of differences. In Access I was able to do the following:
SELECT ZNew = Max( ..
ZNew2 = [ZNew] - Price
FROM ...
Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max(' line? Help would be appreciated. Thanks.Try:

SELECT ZNew = Max([ZNew] - Price)
FROM ...

blindman|||Did you mean ZNew2 = Max([ZNew] etc. ...
The first line ZNew = Max( .. is an extensive CASE evaluation.
Nice not to have to repeat it in the ZNew2 expression.|||Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max

No, seems to me that you must specify all the syntax :

SELECT ZNew = Max( ...),
ZNew2 = Max(...) - Price
FROM ...|||If you are using a case function you need to show us your statement.

blindman|||Here's the code:
SELECT TCode, ZValue = Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500 ELSE TValue END) ,
ZNew2 = ZValue - TKgValue|||You haven't included your FROM clause, so I can't tell if ZValue exists in an underlying table as well as being constructed from your case clause. If your tables have a field called ZValue in them, that is the value that will be used when you try to calculate ZNew2. Otherwise, I think you will get an error stating that SQL Server can't find field ZValue. You cannot create it and then reference it in the same statement, so you will have to repeat your case statement.

There are ways to avoid repeating the CASE statement, such as this method using nested queries:

SELECT TCode,
ZValue,
ZValue - TKgValue ZNew2
FROM (SELECT TCode,
Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500
ELSE TValue END) ZValue,
TKgValue
From YourTableReferencese) ZValueSubquery

blindman|||The Value does not exist and a nested query will not work in this case, so I'll just have to repeat the CASE statement. Thanks.|||I think your code would be easier to maintain, (and may run faster) if you use the nested query approach.

blindman

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

reference member in user hierarchy

hi,

I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]

And I can access the member by directly call the memeber name, for example:

select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................

The results will be correct. However, if I use reference to call the member, for example:

select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................

There will be nothing in my results.

Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.

With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.

"[Date].[Calendar].[Years].&[2006].&[2]" might help.

Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.

I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?

|||

It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.

Regards

Thomas

|||

Hi, Thomas,

Thanks and would you please give me a little bit more details?

Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?

And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.

|||

No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.

Regards

Thomas Ivarsson

|||

hi, Thomas,

Thanks and I have tried to add a composite key (year+quarter) according to your steps.

But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.

Would you please tell me how to deal with this problem? Thanks a lot.

|||

Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.

You can also simply use the datepart function for year and quarter and return two integer fields.

Regards

Thomas Ivarsson

|||

hi, Thomas

I have checked that in the AS2005, my Year and Quarter are in the format: Integar.

And in the data source view, I see that the source columns of the key column are both: System.Int32

However, the error still exists, do you have any idea about this? Thanks.

|||

BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?

|||

hi, Thomas,

I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).

And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.

I do not know if this will cause any other problems. Anyway, thanks a lot for your help.

|||

I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.

Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.

Regards

Thomas Ivarsson

|||

Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).

Regards

Thomas Ivarsson

|||

Hi, Thomas

Thanks a lot for your response! I have several questions about your answer.

First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?

Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)

|||

Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.

Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.

Regards

Thomas Ivarsson

Reference another database without hardcoding the name of it...

Hi,
I s there any way to access another database without hardcoding the name in
the stored procedure?
for example:
database1.dbo.sp_Test()
begin
select * from database2.dbo.supertable
end
So instead of writing "database2" (since this name can change in test
environments) is there another way to reference it? One way could be to
dynamicly create some sql, where the name is found in a table - are there
others?
So the database name must set at runtime rather than compile time...
CheersIn SQL Server 2005, you could create a synonym.
In SQL Server 2000, the method I use is what you describe, get the name of
the server and/or database and build a dynamic string. <yuck>
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
> Hi,
> I s there any way to access another database without hardcoding the name
> in
> the stored procedure?
> for example:
> database1.dbo.sp_Test()
> begin
> select * from database2.dbo.supertable
> end
> So instead of writing "database2" (since this name can change in test
> environments) is there another way to reference it? One way could be to
> dynamicly create some sql, where the name is found in a table - are there
> others?
> So the database name must set at runtime rather than compile time...
> Cheers|||In SQL Server 2000, you can't dynamically change the database or table
referenced in a select, update, delete statement without resorting to
building the statement in a varchar and then executing it using Exec. Is it
really necessary to prefix the table name with the database? It's best for
the development / test database to be deployed on a seperate server or
instance.
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:658A1290-95D2-40B9-A44B-4779A434F4D1@.microsoft.com...
> Hi,
> I s there any way to access another database without hardcoding the name
> in
> the stored procedure?
> for example:
> database1.dbo.sp_Test()
> begin
> select * from database2.dbo.supertable
> end
> So instead of writing "database2" (since this name can change in test
> environments) is there another way to reference it? One way could be to
> dynamicly create some sql, where the name is found in a table - are there
> others?
> So the database name must set at runtime rather than compile time...
> Cheers