Accessing Database on another server

Hi All,
I have application where databases/tables spread between two
servers(different building, wireless connection). During data entries from
front-end, the application must access the tables on both servers
interactively.
The question is :
- is Linked Server is the only way to access tables on another server ?
- How is the performance of accessing tables on local server compared to
accessing tables on linked server ?
Thanks for your comments,
KristTristant,
"tristant" <krislioe@.cbn.net.id> wrote in message news:OpzPgQRnDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have application where databases/tables spread between two
> servers(different building, wireless connection). During data entries from
> front-end, the application must access the tables on both servers
> interactively.
> The question is :
> - is Linked Server is the only way to access tables on another server ?
No. You can replicate the data from one server to the other, or use
openrowset for an adhoc connection
> - How is the performance of accessing tables on local server compared to
> accessing tables on linked server ?
As you would expect - much worse.
Especially with WiFi, where you don't get all the bandwidth claimed anyway
and should be using VPN or other overhead to keep the link safe.
That said, you are not normally puting a whole lot of data through the link, so it
is still perfectly useable, my preference would be to have a client connection to
only one server, and use replication. But you don't say anything about updates,
or data volumes so it's a bit hard to comment.
Regards
AJ

Accessing Custom Assemblies

We are building a report via Business Intelligent Studio. We have deployed the custom assembly dll to two folders:

1. Microsoft Sql Server\MMSQL.3\Reporting Services\Report Server\bin

2. Microsoft Visual Studio 8\Common\IDE\private assemblies.

This dll is an instance dll.

When we open the report properties/reference tab, we can find and link to the dll. Which one of the above locations is the correct one? (We have #2).

We created the class and instance entry.

Next, we added a textbox to the report layout. When we try to use the =Code keyword we get an unrecognized identifier error.

Can anyone shed any light on why this is happening. We've spent considerable time trying to find any info on this with no luck.

Any help is appreciated

Information about using custom assemblies with reports can be found here:

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

For me, to use the assembly in BIDS the path is \Visual Studio 8\Common7\IDE\PrivateAssemblies

Note "Common7" and no spaces in "PrivateAssemblies".

Access Yes/No field equivalent in Sql Server

Hi,
I am building a table in sql server. One of the field is equivalent to
Access yes/no field. This will have values Yes or No obtained from a form in
Asp page. I am wonering what would be the correct datatype for this field.
Thanks.char(1) - "y" or "n"
varchar(3) - "yes" or "no"
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
> Hi,
> I am building a table in sql server. One of the field is equivalent to
> Access yes/no field. This will have values Yes or No obtained from a form
> in
> Asp page. I am wonering what would be the correct datatype for this field.
> Thanks.|||Jack,
You could use a bit datatype with support for 0 or 1 then translate that to
Yes or No in code. You could also use a char(3) with a check contraint
limiting the valid entries to Yes/No. A default constratint can be added as
well for a default of Yes or No when a user does not explicitly enter a
value.
HTH
Jerry
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
> Hi,
> I am building a table in sql server. One of the field is equivalent to
> Access yes/no field. This will have values Yes or No obtained from a form
> in
> Asp page. I am wonering what would be the correct datatype for this field.
> Thanks.|||You may consider using Bit for boolean data type in Presentation layer.
Perayu
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
> Hi,
> I am building a table in sql server. One of the field is equivalent to
> Access yes/no field. This will have values Yes or No obtained from a form
> in
> Asp page. I am wonering what would be the correct datatype for this field.
> Thanks.|||Thanks for all the help from Raymond, Jerry and Perayu. I appreciate it.
"Perayu" wrote:

> You may consider using Bit for boolean data type in Presentation layer.
> Perayu
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:EAF7A165-2386-442D-881C-775B3C161473@.microsoft.com...
>
>

access to data in the cube

Hi,

i'm currently building a cube which data has a level of access depending who the logged in user is in the windows authentication. My cube's fact table is actually a VIEW with this line of code:

SELECT a.serviceid AS [service code], a.agentid AS [agent code], a.timeid AS date, a.talktime AS [talk time], a.wraptime AS [wrap time]
FROM dbo.factAgentActivity AS a INNER JOIN
dbo.factaccess AS b ON a.agentid = b.agent AND b.username = SUSER_NAME()

I used SUSER_NAME to get the name of the user currently logged in and compared it against the username table. If i open this VIEW in the SQL SERVER database, i can retrieve the right data that user will see. But when I used this in the Analysis Services and Browse the cube, the metrics and cubes have blank values. The same thing if i link my cube to excel.

Is my setup correct or im missing something here?

thanks in advance!

regards,

cherriesh

I'm not sure to understand but:

"SUSER_NAME()" return the current user logged in.

It means that when you process the cube "SUSER_NAME()" is replaced with the AS account.

If it is correct, how can AS dynamically change the user on browser time?

Maybe I'm missing something.

Francesco

|||if you already know what access they should have, why wouldn't you just create a role(s) within your SSAS database project and assign the NT Users (or NT Groups) accordingly. Depending on your storage mode for your cube once you process the cube the view wouldn't work anyway, that would just be used to perform the initial load of the cube.

Access sub-select question

NewBe Access Question

Hi folks,

I am having trouble building a query that retrieves what I need. The table I query is pre-filled with rows of records both empty and filled that I will continue to fill or empty from a form. Two columns in the table are completely pre-filled, the ID column and another, which has three different text categories. I need to sub-select an entire group of rows with one kind of text entry and then select the lowest ID value in that group whos columns are otherwise empty.

The table might look like:

ID CAT INFO2 INFO3 Etc.
1 aaaa
2 aaaa something
3 aaaa
4 bbbb
5 bbbb something
6 bbbb
7 cccc
8 cccc
9 cccc something

So, if Row ID 4 was the MIN ID of CAT bbbb whos columns were otherwise empty, I would like to query it and fill the other columns.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl)

The above returns the lowest ID row in the entire table, of course. Not what I wanted.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl)
AND Cat = 'bbbb'
AND Info2 IS NULL

This returns a No records returned message. Of course! I presume it does so because the lowest ID for an empty bbbb category is not the lowest ID in the table.

Any help would be greatly appreciated.

RosieI hope I understood this correctly.

To return the lowest ID for a certain category has a null value do the following.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl WHERE Cat = 'bbbb'
AND Info2 IS NULL)

Note that there is another where statement in the sub-query

Machado|||Yes, you understood it perfectly!

Two WHERE's. That did it!

Thanks soooo much.

'Rosie'

Access SQL Conversion Issues?

Hi there, apologies if i have entered this into the wrong forum.

I am currently building an application using microsoft access and coding in VBA (what i can!)

When i have got it working as i like after user testing i would like to create an MDE file to distribute to users and i would like to convert the access database held on a file server to a SQL server but have no experience of doing this.

In what order would i do this and would i suffer a loss of functionality in changing from access to SQL i.e. would some of the VBA code not work in SQL?

Thanks for your time,

Rhys.

Yes, you are right, if you do not use the GUI "only", you *could* get into problems after migrating. If you already know that you will switch to the SQL Server option, you should build your application directly on top of that.

HTH, Jens Suessmeyer.

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

Hello

Have you tried importing the Access database using SQL Server Integration Services (or Data Transformation Services if you use SQL Server 2000)?
I'm not sure if it works with MDE files but I would give it try.

Let us know if it works

Worf

|||

one thing you have to watch out are the stored procedure.

in access it looks like this

select * from employees where employee_id=?

while in SQL server it looks like this

Select * from employees where employee_id=@.employeeid

access front end

i am almost finished building the front end for an application to manage restaurants. i am developing the front end with access vba, and intend to use sql server as a back end for the service. i just came across a group of people bashing access developers on a different site. i have several restaurants interested in using the service, and believe that the service should be very successfull (i have a very specific customer base that is currently not being targeted). the access component would not be a multiuser application. before i distribute the app i would appreciate any input. am i getting myself into trouble using access vba? should i run scared and switch to a vb.net web service?
thanks in advance for any thoughts.I think an Access front-end with a SQL Server back-end is perfectly appropriate for small-to-midsize applications.

IF.....
you create an Access Data Project, and not just an mdb file with linked tables. That may get you into trouble.