Need to create and access a SQL server database that will reside on an external hard drive

I need to create a SQL server database and add some tables to it. Then access it with a C# application. The problem is that the new SQL server database and it's tables must reside on an external hard drive. How do I point SQL server to this external drive, so that I can create a database on this drive and then create tables and access data on it?

Here are two articles with examples.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q307283&id=kb;en-us;q307283&ln=en-us&rnk=1&sd=msdn&fr=0&qry=q307283&src=dhcs_mspss_msdn_srch&spr=msall

http://www.codeproject.com/cs/database/CreateDB.asp

|||

Hi Steve,

I am interested in setting this up through SQL Server management studio. Your refered links talk about doing it programatically. Neat stuff, but I need to do this through SQL Server management studio. Possibly someone has some more information on this?

|||

You can create a database locally, copy the mdf and ldf files to where you want them, and then attach to the files in the new location. Here is a link in the online help:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6732a431-cdef-4f1e-9262-4ac3b77c275e.htm

Or, right click the server icon and select properties.

Select the Database Settings Page. On that page you can specify what directories you want your data to be in.

|||Once the database and tables have been set up on the external drive, do you know if they can then be viewed and queried through SQL Server Query Analyzer?|||Once the database and tables have been set up on the external drive, do you know if they can then be viewed and queried through SQL Server Management Studio?|||

I tried what I suggested and it didn't work. The Management studio would not let me browse away from the PC it was installed on. I tried hard coding some paths to a remote drive but that didn't work.

Finally, I got this to work (from articlehttp://www.sqlteam.com/item.asp?ItemID=128)

DBCC TRACEON (1807);
GO

USE master;

GO
CREATE DATABASE Test
ON (FILENAME = '\\oak\C\temp\DataBase\SGWTest.mdf'),
(FILENAME = '\\oak\C\temp\DataBase\SGWTest_Log.ldf')
FOR ATTACH;
GO

Once attached, the database will act like any other database. However, I'm beginning to think Microsoft wants the engine and files to be on the same machine. It probably has something to do with performance and reliability :)

|||

Hi Steve. I am just getting back to your post. Thank you for your research and help on this. I am really greatfull to you. You have my vote for a Microsoft MVP! If I knew where to vote, I would vote for you.

I will read the article you suggested. With regards to the above script DBC TRACEON (1807) ...etc. Did you execute this as a SQL stored procedure?

If speed is an issue then I suppose I could always go with an external drive. It appears to me that possibly I can have a seperate instance of SQL server stored on this external drive, and then run that instance off of the external drive. The problem with this though is that I would have to power it up and down. Necessary I suppose if I want to have an external database.

Here is an example of a drive I was thinking about, if I had to go the route of getting an external hard drive.http://www.microcenter.com/byos/byos_single_product_results.phtml?product_id=249658

Talk to you later,

Ralph

sql

Need to change connection string for SQL 2005?

Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David RazzettiHmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:

> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQ
L
2005.
Regards
David Razzetti
"Jens" wrote:

> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.

Need to change connection string for SQL 2005?

Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David Razzetti
Hmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.
|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:

> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>
|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.
|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQL
2005.
Regards
David Razzetti
"Jens" wrote:

> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>
|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.
sql

Need to access the impact of changing IP address

we plan to move our sql server from one place to another place. It's IP has
to been changed, yet the hostname can be kept. I need to access the possible
impact of this move. Our application server query the datababase by server
name. Apart from changing the DNS, what else I have to pay attention ?
Nothing. SQL Server needs to have the same hostname for replication, but
other than that, as far as I know there will be no impact from changing the
IP address.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LanLan" <LanLan@.discussions.microsoft.com> wrote in message
news:7E479E8D-ED7B-4D4F-8500-066C7B3C86CF@.microsoft.com...
> we plan to move our sql server from one place to another place. It's IP
> has
> to been changed, yet the hostname can be kept. I need to access the
> possible
> impact of this move. Our application server query the datababase by server
> name. Apart from changing the DNS, what else I have to pay attention ?

Need to access the impact of changing IP address

we plan to move our sql server from one place to another place. It's IP has
to been changed, yet the hostname can be kept. I need to access the possible
impact of this move. Our application server query the datababase by server
name. Apart from changing the DNS, what else I have to pay attention ?Nothing. SQL Server needs to have the same hostname for replication, but
other than that, as far as I know there will be no impact from changing the
IP address.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LanLan" <LanLan@.discussions.microsoft.com> wrote in message
news:7E479E8D-ED7B-4D4F-8500-066C7B3C86CF@.microsoft.com...
> we plan to move our sql server from one place to another place. It's IP
> has
> to been changed, yet the hostname can be kept. I need to access the
> possible
> impact of this move. Our application server query the datababase by server
> name. Apart from changing the DNS, what else I have to pay attention ?

Need to access the impact of changing IP address

we plan to move our sql server from one place to another place. It's IP has
to been changed, yet the hostname can be kept. I need to access the possible
impact of this move. Our application server query the datababase by server
name. Apart from changing the DNS, what else I have to pay attention ?Nothing. SQL Server needs to have the same hostname for replication, but
other than that, as far as I know there will be no impact from changing the
IP address.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LanLan" <LanLan@.discussions.microsoft.com> wrote in message
news:7E479E8D-ED7B-4D4F-8500-066C7B3C86CF@.microsoft.com...
> we plan to move our sql server from one place to another place. It's IP
> has
> to been changed, yet the hostname can be kept. I need to access the
> possible
> impact of this move. Our application server query the datababase by server
> name. Apart from changing the DNS, what else I have to pay attention ?sql

Need to access SQL Server 2000 tables

Hello,

I am trying to write a java program that will connect to a SQL Server DB, run some queries, pull some data and try to find some trends in the data.

The problem is that I have a copy of the Database, but I don't have SQL Server. I have been given a backup copy of the database though (SQL Server 2000). I know that in addition to the JDBC driver for SQL Server 2000 I will need something else to be able to access the DB. They have stopped selling SQL Server 2000, also the software tends to be a little expensive.

Is there anything else that I can use that will allow me the functionality of being able to run queries and pull data from the DB using JDBC? Will installing MSDE allow me to do that? Or is there someother software that will allow me to do that? Or do I have no other option except installing SQL Server 2000?

Any help is greatly appreciated.

ThanksHi there,

Will you only be accessing the database for the purposes of reporting (i.e. you won't be changing data and then merging these changes into a master database from which the backup was taken)?

What I would recommend is that you don't use MSDE but you use SQL Server 2005 Express Edition. You can download it here:

Download SQL Server 2005 Express

NB. The download could be quite hefty as you might need to download the .NET Framework 2.0, SQL Server 2005 Express Edition database engine and the Management Studio.

You are able to restore SQL Server 2000 database backups to SQL Server 2005 so what you would do is:

1) Install SQL Server 2005 Express & Management Studio Express (see previous link)

2) Use Management Studio Express to restore the SQL Server 2000 backup (you can do this by creating a new database and then restoring your backup over the top)

3) Download and install the JDBC driver for SQL Server 2005 here:
Download JDBC Driver

4) Write your Java program as required

Well, OK, you don't have to use the JDBC driver as you could also use the JDBC-ODBC bridge which already comes with Java but I'd recommend you use the JDBC driver.

Hope that helps a bit, but sorry if it doesn't

|||Thanks a lot NateV,

I will be using the DB for reporting purposes. I won't be chaning any data on the backend ( no inserts or updates, just selects). I am going to try using SQL Server Express 05 and let you know if it worked.

Thanks Again

Need to access INFORMATION_SCHEMA on linked server

Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local

select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked

but not

select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]

How can I access the INFORMATION_SCHEMA on the linked server ?

Thanks,
Helenacreate a view in linkedserver on DB which u want to see the table information and access that view from ur local server.
Note: give permission if its required to access that view.

-- create view script--
create view dbo.vINFORMATION_SCHEMATABLES
as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
go

-- select statement from local server
select * from [testSRV].[testDB_far].[dbo].[vINFORMATION_SCHEMATABLES]|||Cheat?EXECUTE [testSrv].[testDB_far].dbo.sp_executesql
N'SELECT * FROM INFORMATION_SCHEMA.TABLES'-PatP|||Thanks for the fast replay .
Mallier - I cant change anything in the remote db.
Pat - you solution works perfect .

Thanks to all
Helena

Need tips on manipulating hierarchical data.

(SQL Server 2000, SP3)
Hello all!
My company is in the process of providing some Access Management capabilities within our
SQL Server database, and as part of this work we will be defining an association between
users of our product and some hierarchical structure (like organization/department) for
those users.
I think the table to house the hierarchical structure is pretty well-understood; in that
there will be some identifier and the table will have a field that is a reference to
itself to represent its parent identifier.
As part of our definition for this hierarchical structure, all child nodes will "inherit"
the parent node access rights. I'm not sure how best to craft the SQL that will help to
quickly identify all the parent nodes given a particular node in the tree.
I'm wondering if there are some good/recommended resources on manipulating hierarchical
data that would help me in this endeavor? Perhaps some suggestions on how I should
structure the table itself, and attendant queries.
If you know of any such material, I'd be very much obliged! :-)
John PetersonYou should definitely consider the Nested Sets approach as your hierarchy
model.
http://www.intelligententerprise.com/001020/celko1_1.shtml
http://www.dbazine.com/tropashko4.html
--
David Portas
--
Please reply only to the newsgroup
--
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23xWg01PYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> My company is in the process of providing some Access Management
capabilities within our
> SQL Server database, and as part of this work we will be defining an
association between
> users of our product and some hierarchical structure (like
organization/department) for
> those users.
> I think the table to house the hierarchical structure is pretty
well-understood; in that
> there will be some identifier and the table will have a field that is a
reference to
> itself to represent its parent identifier.
> As part of our definition for this hierarchical structure, all child nodes
will "inherit"
> the parent node access rights. I'm not sure how best to craft the SQL
that will help to
> quickly identify all the parent nodes given a particular node in the tree.
> I'm wondering if there are some good/recommended resources on manipulating
hierarchical
> data that would help me in this endeavor? Perhaps some suggestions on how
I should
> structure the table itself, and attendant queries.
> If you know of any such material, I'd be very much obliged! :-)
> John Peterson
>|||Thanks, David!
Oddly, that second link seems to suggest that Nested Sets is kind of a poor performer for
typical types of queries. Have you had any experience with Nested Sets?
Thanks!
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eW2BX%23PYDHA.1580@.tk2msftngp13.phx.gbl...
> You should definitely consider the Nested Sets approach as your hierarchy
> model.
> http://www.intelligententerprise.com/001020/celko1_1.shtml
> http://www.dbazine.com/tropashko4.html
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23xWg01PYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > My company is in the process of providing some Access Management
> capabilities within our
> > SQL Server database, and as part of this work we will be defining an
> association between
> > users of our product and some hierarchical structure (like
> organization/department) for
> > those users.
> >
> > I think the table to house the hierarchical structure is pretty
> well-understood; in that
> > there will be some identifier and the table will have a field that is a
> reference to
> > itself to represent its parent identifier.
> >
> > As part of our definition for this hierarchical structure, all child nodes
> will "inherit"
> > the parent node access rights. I'm not sure how best to craft the SQL
> that will help to
> > quickly identify all the parent nodes given a particular node in the tree.
> >
> > I'm wondering if there are some good/recommended resources on manipulating
> hierarchical
> > data that would help me in this endeavor? Perhaps some suggestions on how
> I should
> > structure the table itself, and attendant queries.
> >
> > If you know of any such material, I'd be very much obliged! :-)
> >
> > John Peterson
> >
> >
>

need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman

Need some help regarding duplicate values....

Hi,

I am using access database and cystal reports XI. Since I am facing some problems.

The following are the details:

Table1 = Sales
Table2 = Collections

Sales (table fields):

1. CustomerName
2. InvoiceNumber
3. InvoiceDate
4. InvoiceAmount

Collections (table fields):

1. CustomerName
2. DepositDate
3. ReceivedAmount

I need these fields in a report. There is no relationship in the database for the CustomerName field.

I tried my best to get the report. But there are repeated values (Duplicate values which are not existing in the table/database) present on the report. Beside to the date values there shows 00:00:00 (time) as well which I dont need.

How to solve this problem. Please help.

Sweetie.The following jpg file shows the duplicate/repeated data.|||Make sure "select Distinct Records" is turned on under the Database menu.|||Make sure "select Distinct Records" is turned on under the Database menu.

Hi, thought I have selected the option "select Distinct Records" the same thing happens that the data in collections.AmountReceived repeats.

Any further help please. (Using Crystal Reports XI).

Sweetei.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.

Hi, Raheem! I did the same as you have advised but it didn't work. Actually though the both Sales & Collections have CustomerName field but there is no relationship between them in the database.

Could you please help me out of this problem?

Sweetie.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

While you try to connect the Sales table to the Collection table using CustomerName (seems like the only relationship you can make between the 2), every distinct record in the Sales table will connect to all records in the Collection table which has the same CustomerName. If you look at the DepositDate on your report, you'll see the pattern. It looks like a duplicate problem. In deed, it just a coincident that for each of the 3 customers, you have 2 records in the Collection table.

You can check this out by adding another record for one of the 3 customers in your Collection table. You'll see your report showing triplicate for this customer.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.

Hi, thanks for your kind help. Actually I need the both ways you have mentioned above. But I need to know it step by step to understand easily.
So, Could you please let me know the first way .... ?

"1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link."

Sweetie.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.

Hi, Raheem. Thanks for the advise. But the problem is, assume that if the customer doesn't pay the full payment for each invoice and pay it in 3 installments and by the time he contines purchasing some items (means he will have another 1 - 2 invoices before he pay the first invoice amount). Then it will be messed.

The example is attached.

Regards,

Seema.|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem

Wow! is it ? Actually what I needed for my program is the same. This is the first time that I heard about that method. Could you please explain me in detail, only if you dont mind?

Seema_S|||when i get free i will reply you.|||when i get free i will reply you.

Hi, Raheem!

I feel happy to get your reply which can solve my problem.

Thanks in advance.

Sweetie.|||Hi, guys!

I am waiting for the reply to my querry. Could somebody help me in this regard?

Sweetie.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.
One such error is the city 'JAYPUR' in Collections when it's 'JAIPUR' in Customers and Sales - explains the lack of duplication for that customer!
And what's the FileNumber column in Sales and Collections about? It seems to uniquely identify a customer but this isn't on the customer table. Messy.
You might want to consider ditching all the duplicated rubbish and putting the CustomerID into the Sales and Collections tables at least.

I've knocked together something I think is more like what you're after.See the attached report.
It assumes that the customer name is unique. If you put the CustomerID into the tables then you should use that instead as it really is (should be) unique.

Anyway, have a play with it to see what it does and how. Hope it's of some use.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.

Anyway, have a play with it to see what it does and how. Hope it's of some use.

Hi,

Thanks for your kind help. I am happy that at last I got my querry answered. There is one problem which is attached as jpg file.

Thanks again.

Sweetie.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

The collections of 17,000 and 4,000 are applied in a First In First Out (FIFO) process to the sales (invoices).

The first invoice (103) was for 8,000 and the second invoice (109) was for 17,000.
The first collection was 17,000 so 8,000 of this is applied to invoice 103.
The remaining 9,000 is left over and applied to the next invoice, 109.
The next collection was for 4,000 and is also applied to invoice 109.

If you want to make it clear that a single collection has been split over multiple invoices then you might want to add the original collection amount / collection id to the report.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

Thanks. It is better that it should show the full amount received.

Regards.

Sweetie.|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.

In Collections table, I added invoice number in it and check two

queries. JaganEllis did the same thing. if u remove invoice date then u will get correct collections amounts displayed.

collecionn 1 for 4000 adjusted in 105
collection 2 for 2000 adjusted in 105
collection 3 for 5200 adjustted in 105 and 111

so if u want to omit invoice date, then u can use query2 to make ur report ..else u u can use query1..

Atlast Hope I helped you.

Raheem|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.
Raheem

Thanks for your kind help. I need to learn more about Normalizing database and Crystal reports. If you know any good tutorials site for the above, plz let me know.

Regards.

Sweetie.sql

Need some help A.S.A.P.

Hi Folks,
I'm having a bit of trouble updating my script. I have a
script used for MS Access and I need to transfer it to be
compatible with MS SQL Server. When I run the script it
gives the error
''ODBC driver does not support the requested
properties.''
This is the line of the script
that I have to change to work with access:
''oRS.Open SqlTxt , oConn'' and the sql text is:
''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
Could anybody please help me out in solving this problem?
Cheers!
Hi
Maybe you can conect to the MS SQL database using MS Access? i don't know?
maybe diverent ODBC driver?
I'm still new to this but just trying to gif you some things you may wanna
try.
Greatings,
Crazy Pete
"Will" <anonymous@.discussions.microsoft.com> schreef in bericht
news:2396c01c45ebe$550b2f10$a301280a@.phx.gbl...
> Hi Folks,
> I'm having a bit of trouble updating my script. I have a
> script used for MS Access and I need to transfer it to be
> compatible with MS SQL Server. When I run the script it
> gives the error
> ''ODBC driver does not support the requested
> properties.''
> This is the line of the script
> that I have to change to work with access:
> ''oRS.Open SqlTxt , oConn'' and the sql text is:
> ''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
> FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
> Could anybody please help me out in solving this problem?
> Cheers!
>
sql

need rldblib.lib, llibca.lib,dbdecdec

any body have three files below:
rldblib.lib, llibca.lib,dbdecdec.exe
that file use library clipsql,
using to access sql server under clipper aplication.
please help me...
thank before
Supratman - BogorI think these files last shipped on the SQLSVR 7.0 CD. If you can find an old copy of one you should be able to get the files.|||No, there are files with those names, but they won't help for a Clipper application. Clipper applications are 16 bit MS-DOS applications, they will require the 16 bit versions of those files that shpped with SQL 4.21a.

The quickest way to get those files is probably to open a support incident with Microsoft. If you run into any trouble getting the Microsoft support folks to understand what you need, let me know the SR number and I'll call in to explain it for you.

-PatP|||the last file is dbdecdec.exe|||the last file is dbdecdec.exeTrue, but that is for a network topology that has been dead for more than a decade... The company that created/used it has been bought up and dismantled.

If you truly need support for Pathworks, you have real problems because SQL Server hasn't supported that since at least version 6.5, and probably earlier than that. I know that Microsoft doesn't support it, and I seriously doubt that anyone else does.

-PatP

Need Query Help (with a COUNT(*) for listings) please..

Hey all, I have a problem and it's really making my head spin. If someone could help it would be SO appreciated. I am using an Access DB.

I have the following tables:

listings
- listingID
- cityID (foreign key)
- username (foreign key)
- description
- etc.

listingtypes
- listingtypeID
- listingtype

listingtypelink (linking table for many-many relationship between listings and listingtypes)
- listingID (foreign key)
- listingtypeID (foreign key)

cities
- cityID
- city
- stateID (foreign key)

states
- stateID
- state

members
- username
- password
- etc.

Now my problem, I need to do a SELECT wherein I can select unique city and city ID from the cities table, as well as a COUNT() of the number of listings for each of the cities.

Ideally, results would look like this if I selected the cities that had listings that were of type X (from the listingtypes table, listingtype can be more than one value too such as 12,34,45,46):
Fort Myers (10)
Port Charlotte (1)
Naples (13)
Tampa (26)
etc...

(In other words, I need it to be able to "SELECT DISTINCT cityID, city, numberOfTotalListings FROM listings WHERE cityID = [a single value, such as 233] AND [the listing is active and not disabled] AND [the listing type is either 23 or 45 or 56 or 57 or even a combination of any of these]")

I can't get the count to come out right, no matter what I do, and I just think I am missing something due to my lack of experience with these types of queries.

I have tried nested selects to return a value, which I have had success with in the past, and I know in some way this is the method to use, but I can't figure out the proper query:
strSelect = "SELECT cities.cityID, cities.city, (SELECT COUNT(*) FROM listings WHERE listings.cityID = cities.cityID) AS listingNumber FROM (((cities INNER JOIN listings ON cities.cityID = listings.cityID) INNER JOIN states ON cities.stateID = states.stateID) INNER JOIN members ON listings.username = members.username) LEFT JOIN listingtypelink ON listings.listingID = listingtypelink.listingID WHERE states.stateID = @.stateID AND listings.listingdisabled = False AND members.accountdisabled = False AND listingtypelink.listingtypeID IN (" & objListingType & ")

I can't get the count to come out right. Can someone help me to shed some light on this, please? I would be forever grateful...

My problem seems to lie in the fact that there can be more than one listing type at a time (i.e. - 2,4,5,6,7,20) but I still need the distinct count for the cities, which I can't seem to figure out.

Thank you so much!use group by function
SELECT cityID, city, count(*) FROM listings group by cityID,city|||Sorry, my bad, I forgot to add the GROUP BY to my query in my post, I am already using the GROUP BY clause in my statement exactly as you stated (group by cityID, city)

I just can't seem to get it to grab the correct records and count.

This probably sounds really lame/bad, but if someone has the time, could they write out a of what I need if they know what it might be? I'm normally pretty good at this, but I've been on this simple little problem for 2 days and I am still just as stuck as ever, I just don't know what else to do... =(

Thank you guys... very much.|||How about:SELECT Count(*), cities.city
FROM listings
JOIN cities
ON (cities.cityID = listings.cityID)
WHERE EXISTS (SELECT *
FROM listingtypelink
WHERE listingtypelink.listingID = listings.listingID
AND listingtypeID IN (12,34,45,46))-PatP|||THANK YOU SO MUCH!!

You have no idea how awesome it was for you to take the time to write that out for me, I just copied and pasted it, and made a couple small tweaks and it worked perfectly!

Did not know I could use WHERE EXISTS, will have to look into this more.

THANK YOU THANK YOU THANK YOU!!!! =)|||I'm just curious, but what did you need to tweak?

I couldn't test the code that I posted because I didn't have your MDB file, but nothing jumps out at me as being tweakable. I'd like to know if I missed something so that I get it right next time.

-PatP|||Sorry, when I said tweak, I just kinda meant pasting it and then taking out the line breaks, and changing the numbers to a variable. Below is my finished code (In ASP.NET VB)
It's a little bit sloppy, but it works and at this point, that's all that matters. =)

'***********************************

strSelect = "SELECT COUNT(*) AS listingNumber, cities.city, cities.cityID FROM (listings INNER JOIN cities ON cities.cityID = listings.cityID) WHERE EXISTS (SELECT * FROM listingtypelink WHERE listingtypelink.listingID = listings.listingID"

If objListingType <> "all" Then
strSelect &= " AND listingtypelink.listingtypeID IN (" & objListingType & ")"
End If
strSelect &= ") AND cities.stateID = @.stateID"
strSelect &= " GROUP BY cities.cityID, cities.city"
strSelect &= " ORDER BY cities.city ASC"

'***********************************

Thanks again!|||Ah, I see. I was trying to figure out what I might have broken somehow, and you were talking about the cleanup needed to make it work with your application in VB. Now we're on the same page again!

-PatP|||hah yeah for sure, sorry, sometimes I just type and don't even really pay attention to what I'm saying (that's the worst part about being a fast typer!)

Thanks so much again, I have since taken that snippet of code and used it on a few other pages for the site. Provided for much optimization as well with only single calls to the database (don't even ask what I was doing before, haha, bad practices for sure.)

Take care.

Need opinion about this design "Thread"

Hi all,

I am having problem with SQL connection at Godaddy where my pool connection gets MAX OUT. When it happens, I cannot access the database.
This is the thread about the problem:http://forums.asp.net/thread/1665023.aspx

I just created this with "THREAD". I hope someone who has experiences with thread can give me some advice about my design. This is my first time.

staticobject Locked =newobject();

publicobject ExecuteCommand(string queryStr,string type)
{
//*************************************************************************************//
// ExecuteCommand: Returns an object //
//*************************************************************************************//

Thread t =null;

lock(Locked)
{
SQLString = queryStr;

switch(type)
{
case"ExecuteNonQuery":
t =newThread(ExecuteNonQuery);
break;
case"ExecuteScalar":
t =newThread(ExecuteScalar);
break;
case"GetDataReader":
t =newThread(GetDataReader);
break;
}

t.Start();
t.Join();
}

returnnull;
}

First of, does this work at all? It runs, but is it a good design in term of Thread? Since I use LOCK, do I still need the t.Join() function? The switch with the three cases, is that OKAY? Basically, I'm clueless. If you read my other post, you will get an idea what I'm trying to do. Any feedback would be very very appreciated.

Thank you.

It looks a little odd to me.

You start a thread, and then immediately you do a "join" which effectively says "wait for the thread to finish before continuing". Why not just call a function instead of messing with a thread? The effect is the same and it is simpler.

Make sure you are Closing or Disposing any and all database connections when you are done using them. The Using statement is the most reliable way of doing this.

|||

SGWellen,

Thank you for the input. I use the "join" right after the "start" because I saw it on several tutorials. The "start" function automatically calls the function that is assign in the switch statement.
If I'm not wrong, I think I don't even need to "join" function there, because the whole script is inside the LOCK. Hmmmm I guess I need to dig in it further. Thank you SGWellen.

Need one Query to obtain results I can only get with two queries

I'm trying to devise a query for use on SQL Server 2000 that will do
what was previously done with one query in MS Access. The MS Access
query was like this:

SELECT Count(*) as [Opened],
Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and
Status not like 'Deleted'
Group By Year(DateOpened), Month(DateOpened)
Order By Year(DateOpened), Month(DateOpened)

Here were I'm at with SQL Server, TSQL

Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
Opened],
Count(Status) as [Opened]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and
Status not like 'Deleted'
Group By Year(DateOpened), Month(DateOpened) Order By
Year(DateOpened), Month(DateOpened)

Which yields

MonthOpened
======================
Aug 2004503
Sep 2004752
Oct 2004828
Nov 2004658
Dec 2004533
Jan 2005736
Feb 2005707
Mar 2005797
Apr 2005412

And

Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
Opened],
Count(Status) as [Cancelled]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and
Status like 'Cancelled%'
Group By Year(DateOpened), Month(DateOpened) Order By
Year(DateOpened), Month(DateOpened)

Which yields;

MonthCancelled
=========================
Aug 200478
Sep 2004105
Oct 2004121
Nov 2004106
Dec 200475
Jan 200582
Feb 200571
Mar 200594
Apr 200533

What is desired is

MonthOpenedCancelled
============================
Aug 200450378
Sep 2004752105
Oct 2004828121
Nov 2004658106
Dec 200453375
Jan 200573682
Feb 200570771
Mar 200579794
Apr 200541233

Any assistance would be appreciated.

Cheers;

BillI think that you need something like this:

Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
Opened],
Count(Status) as [Opened],
Sum(CASE WHEN [Status] Like 'Cancel%' THEN 1 ELSE 0 END) as
[Cancelled]
FROM Detail_Dir_LocV
Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and Status not like
'Deleted'
Group By Year(DateOpened), Month(DateOpened)
Order By Year(DateOpened), Month(DateOpened)

Razvan

PS. It would have been useful if you would have provided the DDL (in
form of "CREATE TABLE..." statements) and some sample data (in form of
"INSERT INTO ... VALUES ..." statements).|||"Razvan Socol" <rsocol@.gmail.com> wrote in message news:<1113473001.684266.112120@.o13g2000cwo.googlegroups. com>...
> I think that you need something like this:
> Select Right(Convert(Char (11), Min(DateOpened), 106), 8) as [Month
> Opened],
> Count(Status) as [Opened],
> Sum(CASE WHEN [Status] Like 'Cancel%' THEN 1 ELSE 0 END) as
> [Cancelled]
> FROM Detail_Dir_LocV
> Where (Detail_Dir_LocV.DateOpened > '2004-8-01') and Status not like
> 'Deleted'
> Group By Year(DateOpened), Month(DateOpened)
> Order By Year(DateOpened), Month(DateOpened)
> Razvan
> PS. It would have been useful if you would have provided the DDL (in
> form of "CREATE TABLE..." statements) and some sample data (in form of
> "INSERT INTO ... VALUES ..." statements).

Razvan;

Thank you for your assistance. It solved my problem.

Is the purpose of your request for a Create Table and Insert samples
so that you might build the table and populate it locally to test your
proposed solution? I think I know how to get the Create statement
since in fact the query is accessing a view and I can just grab it's
properties. The main table that the view is built upon was built
interactively. The table is also populated via an ASP html form so
building a set of insert into would be a manual process. Is there
anyway to have SQL Server build a sample for your intended purpose
based upon the existing data in the table or view?

Any way, you solved my problem and I appreciate your time.

Thank you.

Cheers;

Bill|||Hi Bill,

Yes, that is the purpose: to enable us to test the solution easily, but
also to make us sure that we properly understood the scenario, to make
all of those who respond use the same column names.

For generating insert scripts from the existing data, see:
http://vyaskn.tripod.com/code.htm#inserts
http://www.databasejournal.com/scri...cle.php/1493101

For in-depth considerations about DDL and sample data, see:
http://www.aspfaq.com/etiquette.asp?id=5006

Razvan

Need info about odbc and sql through access2k

I recently started with a company that uses access for a front end and sql2000 for the back. They have set up an ODBC connection though access2k to reach sql2k. This works fine but fouls up in regards to the record locking. You can set record locking in access but it has no effect. In the office help it says 'data in a form, report, or query from an Open Database Connectivity (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.'

Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.

So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?

any info would be great, thankshttp://advisor.com/doc/13043 for information about record locking in Access.

For SQL server http://www.sql-server-performance.com/reducing_locks.asp and
http://www.databasejournal.com/features/mssql/article.php/3289661
HTH

Need Help: For Cube data in Multi Languages.

Dear Friends..

I need help if any one can... My need is that...I am devloping my Cube using SSAS. I want to access my cube in multi languages e.g.: English, Japanese or other. At the movement I have two languages description in SQL Database like English and Japanese.

But In SSAS Translations, I can translate Measures Groups, Dimensions, Perspectives, KPIs, Actions, Named Sets & Calculated Members names. But there will be a need to Map Columns as per selected Lenguage so that I can display in proper language data.

Suppose I select Japanese the description should come from Column where Japanese description is stored.

OR any othere method by doing I can see my reports/data in any language.

Please Help...

Do you whant to display the description for the dimension members also translated?

In dimension editor you can specify data source feild for name translation of any dimension attribute. It this what you are looking for?

|||

Thanks for your reply.

What I'm understanding here that in dimension editor I will have to set NameColumn for another Lenguage Atrribute in Source. But doing that I will have to maintain two Dimension/Hierarchies for Two Languages e.g.: English and Japanese. I donot want like this. I want only based on lenguage selection data should come for that lenguage without duplicating hierarchies for the languages.

Or if I'm not understanding what you said please clear.

Looking positive response..

|||

Hi

I've got the solution in dimension editor in transalations we can do Attribute Data Transalation by clicking ellipsis.Thanks again.

But I've read out regarding Analysis Services Instance for Multi Languages - If you know about please tell me. Or in other words I would like to say any method so that all data in Cube should display in any lenguage based on lenguage selection, but we are not maitaing any manual mapping columns.

Thanks Again.

|||

Translations is the way for you to model your cube such that you can later on switch the language and present your users with translated data.

For that you got to specify where Analysis Server shoudl read translated data from.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Take a look at adventure works sample SSAS database. There you find an example of what you are looking for.

You don't have to maintain two dimensions\hierarchies. You will still have the same dimension with it's hierarchies but depending of LCID of connection you will see captions of dimensions and dimension member in corresponding language.

What you have to do is to supply you dimension tables with additional fields, containing translated names. It's pretty easy, without headache.

|||

Need Help

I tried with AdventureWorks db for Analysis Services - Translation. I made a Geography Dimention Attribute translation for Spanish & Franch having columns in DimGeography. It is working fine when browse SQL Browser. Then I made a SSRS Sample Report..given below a detail...

SELECT
NON EMPTY
{[Measures].[Reseller Sales-Sales Amount]} ON 0,
NON EMPTY
{
([Reseller Geography].[Geographies].[State-Province])
*
([Date].[Month Name].[Month Name])
} ON 1
FROM (SELECT(STRTOSET(@.DateMonthName, CONSTRAINED)) ON COLUMNS
FROM (SELECT(STRTOSET(@.ResellerGeographyGeographies, CONSTRAINED)) ON COLUMNS
FROM [Reseller Sales]))

AND DATASET FOR Geography

WITH
MEMBER [Measures].[ParameterCaption] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
{[Reseller Geography].[Geographies].[Country-Region]} ON ROWS
FROM [Reseller Sales]

AND DATASET FOR Date/Month

WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Month Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Month Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Month Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Month Name].ALLMEMBERS ON ROWS FROM [Reseller Sales]

Now Report is successfully deployed. But How to change the language. Here I am not sure but what I tried writting....I have opened Report Server and change the setting in Tools/Internet Settings/Lenguages -> and added Franch and move it to 1st place. After this Drop down list labels and it's data start to come in Franch but not same data comming in report itself. What to do.

Please Help.

|||

Moving to RS forum

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Need Help: For Cube data in Multi Languages.

Dear Friends..

I need help if any one can... My need is that...I am devloping my Cube using SSAS. I want to access my cube in multi languages e.g.: English, Japanese or other. At the movement I have two languages description in SQL Database like English and Japanese.

But In SSAS Translations, I can translate Measures Groups, Dimensions, Perspectives, KPIs, Actions, Named Sets & Calculated Members names. But there will be a need to Map Columns as per selected Lenguage so that I can display in proper language data.

Suppose I select Japanese the description should come from Column where Japanese description is stored.

OR any othere method by doing I can see my reports/data in any language.

Please Help...

Do you whant to display the description for the dimension members also translated?

In dimension editor you can specify data source feild for name translation of any dimension attribute. It this what you are looking for?

|||

Thanks for your reply.

What I'm understanding here that in dimension editor I will have to set NameColumn for another Lenguage Atrribute in Source. But doing that I will have to maintain two Dimension/Hierarchies for Two Languages e.g.: English and Japanese. I donot want like this. I want only based on lenguage selection data should come for that lenguage without duplicating hierarchies for the languages.

Or if I'm not understanding what you said please clear.

Looking positive response..

|||

Hi

I've got the solution in dimension editor in transalations we can do Attribute Data Transalation by clicking ellipsis.Thanks again.

But I've read out regarding Analysis Services Instance for Multi Languages - If you know about please tell me. Or in other words I would like to say any method so that all data in Cube should display in any lenguage based on lenguage selection, but we are not maitaing any manual mapping columns.

Thanks Again.

|||

Translations is the way for you to model your cube such that you can later on switch the language and present your users with translated data.

For that you got to specify where Analysis Server shoudl read translated data from.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Take a look at adventure works sample SSAS database. There you find an example of what you are looking for.

You don't have to maintain two dimensions\hierarchies. You will still have the same dimension with it's hierarchies but depending of LCID of connection you will see captions of dimensions and dimension member in corresponding language.

What you have to do is to supply you dimension tables with additional fields, containing translated names. It's pretty easy, without headache.

|||

Need Help

I tried with AdventureWorks db for Analysis Services - Translation. I made a Geography Dimention Attribute translation for Spanish & Franch having columns in DimGeography. It is working fine when browse SQL Browser. Then I made a SSRS Sample Report..given below a detail...

SELECT
NON EMPTY
{[Measures].[Reseller Sales-Sales Amount]} ON 0,
NON EMPTY
{
([Reseller Geography].[Geographies].[State-Province])
*
([Date].[Month Name].[Month Name])
} ON 1
FROM (SELECT(STRTOSET(@.DateMonthName, CONSTRAINED)) ON COLUMNS
FROM (SELECT(STRTOSET(@.ResellerGeographyGeographies, CONSTRAINED)) ON COLUMNS
FROM [Reseller Sales]))

AND DATASET FOR Geography

WITH
MEMBER [Measures].[ParameterCaption] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Reseller Geography].[Geographies].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS ,
{[Reseller Geography].[Geographies].[Country-Region]} ON ROWS
FROM [Reseller Sales]

AND DATASET FOR Date/Month

WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Month Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Month Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Month Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Month Name].ALLMEMBERS ON ROWS FROM [Reseller Sales]

Now Report is successfully deployed. But How to change the language. Here I am not sure but what I tried writting....I have opened Report Server and change the setting in Tools/Internet Settings/Lenguages -> and added Franch and move it to 1st place. After this Drop down list labels and it's data start to come in Franch but not same data comming in report itself. What to do.

Please Help.

|||

Moving to RS forum

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Need Help, DTS is Running extremely Slow!

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

GilTo maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the most advantageous time to execute jobs.

For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods.

Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Server Profiler or Windows NT Performance Monitor to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage.

Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule.

But test it before deploying on production.|||Thnx for replying Satya,

I am afraid this ahs nothing to do with CPU resources. Even when i start the job manualy when there is no activity at all an the server the DTS runs Very Very Slow.

I am prety sure that there is somthing else to be done , allthow i will try your advise.

Any suggestions?|||The are a number of thing you can do. It's specific to your job. First of all who has control. AS400 or the database(SQL). I don't know of a DTS package in AS400. So start there, next, you might want to look at the parameters manually vs job. Are there any environmental issues to look at. Such as: numbers of records to commit, indexes, maintenance and backups, buffers size, Look it up on books online SQL Server.|||AS400?

You mean DB2, no?

Have the DB2 dba set up an unload then copy the data to your box...

Then bcp the data in

I know that doesn't address your problem...

What's the execution step in the job look like?

One difference in your methods is the context of which ID is being used.

When you execute it from EM, it's under the context of your id, AND your location.

From the job, it's under the context of the agent AND the server drive mappings..

I'm guessing it's a network issue...

Did the job ever finish?|||Thnx alll 4 replying,

But i am afriad that that is not the problem...

I think i have solved it though...

The Client Access has an option to translate data by a DLL.
it was using one...dont know its name.

removing this dll solved the problem and the job is executing fine now.

thank you all for helping

Gil