Problems Inserting Data- Am I doing something wrong?

I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine)

Insert

into customer(CustomerID, LastName, FirstName, BillingAddress, City,State, Country, Zipcode, PhoneNumber, EmailAddress)

select

CustomerID,(left(ContactName,charindex(' ', ContactName)-1))as LastName,

(right(

ContactName,charindex(' ',reverse(ContactName))-1))as FirstName,

Address

, City,Region,PostalCode,Country,Phonefrom Northwind.dbo.Customers

I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my application

Any help would be of great

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

|||

Hi Stew,

I corrected it in the actual query (when I executed) and it gives me this error message....any pointers??

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

Thank you

|||Look at the size constraints of the columns you are inserting too and see if they make sense against what is being inserted. You may have a string field going into a char field, or something of the sort.|||

Stew312:

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

Looks like the problem is this:

This is what my query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber

There is only one FROM COLUMN (ie., Contactname) but is repeated twice...I mean to say in

the Northwind customer database both the last name and first name are put in a single column

and I am trying to separate it into two colums (Lastname , Firstname).....Is it a wrong way of doing things.....I had been breaking my on this for couple of hours

your help is greatly appreciated

|||Too embrassing and a costly mistake (had to waste most of my time).........the order of columns I was trying to insert was wrong....though embrassing, I am sharing because some people (including me) tend to forget minor details while focusing on other major problems.....so moral of story....Pay attention to detail and don't forget basics.

Problems exporting Reports programatically

I am simply trying to use ActiveX to export a Report to another format, programatically, i.e. without the user having to select a format using the Export dialog.

I can set up all the properties in the ExportOptions object that is part of the Report Object itself, but these do not appear to have any effect when I call the the Export() method on the Report object; which simply brings up the Export dialog with default values.

In fact, even after entering different values through the Export dialog, these do not appear to change the properties that are found in the ExportOptions object. These properties are as what they were coded previously, i.e. unchanged

It's as though the IReport::Export() method and the ExportOptions object are totally separate... Can anyone help to see what I am missing?Try passing "False" to Export so that it does not prompt for the values - from memory, that's what I had to do to get it working.

problems executing a SELECT inside a TRAN against other computer

Hi
I have a problem executing a SELECT inside a TRAN against other computer

For example:
IN THE SQL Query Analizer of the COMPUTER2
1) this runs OK
BEGIN TRAN
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
COMMIT TRAN
2) this runs OK
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
3) this runs OK
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
4) this runs bad
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

The problem is that TABLE locks and it does not finish.

I've been looking for similar ERRORS in Microsoft Support but I found nothing
I've uninstall and install de SQL server 2000 SP4 and the problems continues the same

Please, someone could help me, thanks

Assanta:

A couple of things: (1) make sure that DTC is on and (2) maybe try using BEGIN DISTRIBUTED TRAN instead of BEGIN TRAN when you know you will be explicitly involving an external machine.

Dave

|||What do you mean when you say "this runs bad"? Does the statement take a long time to execute? Are you getting any errors? What is the exact behavior? Can you just isolate the case where it doesn't work and specify what you are doing? Are you running #4 from Computer2 and so on?|||

Hi

Thanks for your answers

MUGAMBO

Yes, I have the DTC STARTED

Yes, I've also try with de DISTRIBUTED and the problem is the same

UMACHANDAR

you have all the answers in my first mail

What do you mean when you say "this runs bad"? The problem is that TABLE locks and it does not finish.

Does the statement take a long time to execute? The problem is that TABLE locks and it does not finish.

Are you getting any errors? The problem is that TABLE locks and it does not finish, so the aren't ERRORS

What is the exact behavior? The problem is that TABLE locks and it does not finish.

Can you just isolate the case where it doesn't work and specify what you are doing?

4) IN THE SQL Query Analizer of the COMPUTER2
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

Are you running #4 from Computer2?

IN THE SQL Query Analizer of the COMPUTER2

BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

If I try a TRAN on other computer all is OK, so the problem is only on this two computers, there must have something diferent on the configuration, but I can't know what it is.

Any help please?

|||You are not changing data. There is no reason to run a transaction around a SELECT statement.|||

Hi Tom, thanks for the response

Yes, of course. But is for doing the statement more simple, what you use inside the TRAN is the same, always i use a TRAN it blocks, with a select inside, or with a insert, or......

|||

It is still not clear what you mean when you say the table locks. This can mean several things. One, the query you are trying is taking a long time or the query is getting blocked by other transactions on the server. Also, I was looking for the exact error message you are receiving from the server? Are you simply cancelling the command since it is taking a long time? Are you getting error# 7391? Did you look at sp_lock output for example to see if this is due to locks on the table due to other transactions in the server? Next, you need to check the waitstats using DBCC SQLPERF (see MSKB) to determine if the wait is related to MSDTC for example. Here the configuration of the MSDTC on computer1 needs to be checked (what is the coordinator, does it have network setting enabled, did you check the MSDTC statistics from the MSDTC console - this will show how long each distributed transaction is taking and if anything got committed or aborted). Lastly, your first query in your original post does not start a distributed transaction because the server part is a local server (you are using 4-part name on the same server - loopback) so you can't compare the two. Below is a KB article that has some steps for troubleshooting the MSDTC connection part:

http://support.microsoft.com/default.aspx/kb/306212

|||"Explicit" transactions, using BEGIN TRAN/COMMIT, are very special and should not be used "generically" for every command. They should only be used in the case where you have multiple update statements which depend on each other.

Bascially, what you are doing in the select statement is causing a table lock (for no reason). This is probably causing a deadlock situation. Look at your activity log and see if something is blocking.|||

Hi Umachandar, thanks for the reply

Nobody use this server, only me and i use for test, so there are no more transactions on the server

I mean with block that the table is "IX" during 30 minutes, then i cancel the command. Without the TRAN the select lasts 1 second

thanks for the article, but I continue with the same problem. also I have tried to Add Value TurnOffRpcSecurity (http://support.microsoft.com/default.aspx/kb/827805) although are in the same domain

Executing DBCC SQLPERF (waitstats) there are 77 types of names but noone about MSDTC, there is one lock, is the type "LCK_M_S" and has the values "Requests=1", "Wait time=78" and "Signal Wait Time=16"

Executing DBCC SQLPERF (SpinLockStats) "LOCK_HASH" with 2 Collisions and 132 Spins

More info I hadn't said until now:

The instalation is made from an image, after the instalation i changed the server and SQL name, now the SQL and the Server name is the same, and uniques in the domain, but maybe the image installation could have any problem with the name of any low level thing?

I've been looking for any soft to test the MSDTC communication between 2 machines, I've readed in a forum something about the "DTCPing.exe", I've executed the file and I thing the problem is this "RPC server is ready WARNING:the CID values for both test machines are the same while this problem won't stop DTCping test"

The solution like I read in a forum (http://cogitativemind.homeip.net/archive/2005/08/01/487.aspx) is "For enabling your MSDTC service, you go to command prompt type: msdtc -uninstall , you'll not get any feedback that this has completed so just wait for a bit, then type: msdtc -install, again wait for a bit. I then rebooted my server and everything came up roses"

but this didn't works ok, so I tried (http://forums.asp.net/thread/1192335.aspx)

Run MSDTC -uninstall

Go into the registry and remove the MSDTC keys in HKLM/Software/Microsoft/MSDTC and

HKLM/System/CurrentControlSet/Services/MSDTC

Reboot

Run MSDTC -install

An this yes, this makes my distributed transctions works ok!!!

|||Glad you got it working. You need to be careful using imaging software since there is lot of metadata used by various applications that can be incorrectly handled. And this can differ from different releases of Windows / Service Pack / Software too. For SQL Server, you said you changed the server and SQL name - so did you use sp_dropserver & sp_addserver with 'local' option. Apart from this one, there are other places in MSDB that can reference server names - like master servers in SQLAgent configuration and so on. And you should actually run SQL Server SETUP again to ensure that the machine change is handled correctly. I believe that SETUP detects the name change and performs some actions. Note that you still need to do the sp_dropserver/sp_addserver part since the @.@.SERVERNAME value is stored & obtained from the system catalog. Search in MSKB for articles that may help the imaging process or name change of machine.

Problems connection the SQL Server database

I build a small application using VS 2005. The application just builds a report using basic Select statement from SQL Server db. Here is my problem:

When I tried to run the application using VS 2005 by pressing F5 . The applicaiton launches using VS webserver and everything test fine. But when I put the site in IIS created website . I get an error "Login failed for user '<serverName>\<userName>

In my connection string I have Integrated Security = SSPI

Please suggest.

Thanks

Thismight be the problem (if the IIS created website you mentioned is on another machine).

On your machine, the "user" that logs into SQL Server is MYMACHINE\ASPNET.

When you deploy to another machine, the "user" that is trying to log into SQL Server is OTHERMACHINE\ASPNET. Maybe the other machine isn't aware of that "user"

Problems calling ::fn_helpcollations() on MS SQL 7.0

Hi.
I have som date/time inconsistency between a MS SQL 7.0 srv. database which
I built up on a MS SQL 2000 srv. database. I was told to use the SELECT *
FROM ::fn_helpcollations() to find out the settings on my (old) 7.0
database, but it fails (incorrect syntax near ':'.). What's the correct
syntax on MS SQL 7.0?
Regards
Mr. SmithHi
There is no correct syntax for SQL Server 7.0 and Functions as Functions are
not supported in SQL Server 7.0.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:Olby%23$CxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have som date/time inconsistency between a MS SQL 7.0 srv. database
> which I built up on a MS SQL 2000 srv. database. I was told to use the
> SELECT * FROM ::fn_helpcollations() to find out the settings on my (old)
> 7.0 database, but it fails (incorrect syntax near ':'.). What's the
> correct syntax on MS SQL 7.0?
> Regards
> Mr. Smith
>

Problems calling ::fn_helpcollations() on MS SQL 7.0

Hi.
I have som date/time inconsistency between a MS SQL 7.0 srv. database which
I built up on a MS SQL 2000 srv. database. I was told to use the SELECT *
FROM ::fn_helpcollations() to find out the settings on my (old) 7.0
database, but it fails (incorrect syntax near ':'.). What's the correct
syntax on MS SQL 7.0?
Regards
Mr. Smith
Hi
There is no correct syntax for SQL Server 7.0 and Functions as Functions are
not supported in SQL Server 7.0.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:Olby%23$CxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have som date/time inconsistency between a MS SQL 7.0 srv. database
> which I built up on a MS SQL 2000 srv. database. I was told to use the
> SELECT * FROM ::fn_helpcollations() to find out the settings on my (old)
> 7.0 database, but it fails (incorrect syntax near ':'.). What's the
> correct syntax on MS SQL 7.0?
> Regards
> Mr. Smith
>

Problems after installing SP2

I'm having some major problems with SSAS after installing SP2 (I never had an SP2 CTP installed, just SP1).

When I bring up BI Studio and select Open/Analysis Service Database, put in my server name (which is on the same machine that I'm running BI Studio), the click the "Database" drop down to select a database, it shows the following error in the bottom of the dialog:

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.
Errors in the metadata manager. An error occurred when loading the Sales measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.0.cub\Sales.1.det.xml'.
Errors in the metadata manager. An error occurred when loading the Trained Cube cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.1.cub.xml'.

At this point, there doesn't seem to be any way to open any database in BI Studio. I always get that error, even if I try typing in another database name.

SQL Management Studio seems slightly better - I can at least connect there. But it only shows a couple of the databases that were on the machine - a bunch are missing. Any attempt to process or browse any of the databases results in the same error as above.

Any ideas?

Quoting the SP2 Readme: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. Such invalid databases must be deleted.

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

|||Thanks Edward. RTFM, eh? Actually, I doubt it would have helped - this is a sample DB that I found somewhere, I wouldn't have realized it had this issue.

The problem is, I don't see a way to delete the database. The admin tools all barf with that error when I try to do anything, including deleting a DB. Is there some other way to nuke it?|||Unless you can go back to pre-SP2 build, you can stop the service, and manually delete all the files and folders in the Data directory. Then you will have to redeploy and reprocess the rest of your databases, unless you have backups in which case you would simply restore them.|||Thanks Mosha. I deleted the .0.db directory and the 0.db.xml file for the bad database (which I didn't need anyway, it was just for playing with), and have now regained access to my server. Any other files I should delete to clean things up?|||

This should be enough.

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

|||

I've recently installed SP2. All was fine initially but I'm now getting the error listed above having not changed anything significant (changed the membersname unique property of an attribute and then changed it back again). I'd been making changes all day to the attribute relationships and deploying without a problem.

I'm not using a linked measure group but the measure group it's reporting an error on has a different granularity key to the main measure group. Apart from changing the granularity key of the group that has a problem what can I do to fix this?

Mark

|||

I got the same error message which is posted in the first post here.

On a server which didn't had the foodmart database installed, I didn't get any problems after installing SP2. When I tossed the foodmart files away as described above, all problems vanished. It seems the problem here is the foodmart database. At the end of it Im glad I got my DB back, but "they" managed to scare the living daylight out of me with this, because I thought the whole AS had been turned into a wreck. Ironically the only database left in the management studio was the foodmart db. If you don't use it I would recommend to delete it before installing SP2.

Regards, Jeroen

Problems after installing SP2

I'm having some major problems with SSAS after installing SP2 (I never had an SP2 CTP installed, just SP1).

When I bring up BI Studio and select Open/Analysis Service Database, put in my server name (which is on the same machine that I'm running BI Studio), the click the "Database" drop down to select a database, it shows the following error in the bottom of the dialog:

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.
Errors in the metadata manager. An error occurred when loading the Sales measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.0.cub\Sales.1.det.xml'.
Errors in the metadata manager. An error occurred when loading the Trained Cube cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.1.cub.xml'.

At this point, there doesn't seem to be any way to open any database in BI Studio. I always get that error, even if I try typing in another database name.

SQL Management Studio seems slightly better - I can at least connect there. But it only shows a couple of the databases that were on the machine - a bunch are missing. Any attempt to process or browse any of the databases results in the same error as above.

Any ideas?

Quoting the SP2 Readme: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. Such invalid databases must be deleted.

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

|||Thanks Edward. RTFM, eh? Actually, I doubt it would have helped - this is a sample DB that I found somewhere, I wouldn't have realized it had this issue.

The problem is, I don't see a way to delete the database. The admin tools all barf with that error when I try to do anything, including deleting a DB. Is there some other way to nuke it?|||Unless you can go back to pre-SP2 build, you can stop the service, and manually delete all the files and folders in the Data directory. Then you will have to redeploy and reprocess the rest of your databases, unless you have backups in which case you would simply restore them.|||Thanks Mosha. I deleted the .0.db directory and the 0.db.xml file for the bad database (which I didn't need anyway, it was just for playing with), and have now regained access to my server. Any other files I should delete to clean things up?|||

This should be enough.

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

|||

I've recently installed SP2. All was fine initially but I'm now getting the error listed above having not changed anything significant (changed the membersname unique property of an attribute and then changed it back again). I'd been making changes all day to the attribute relationships and deploying without a problem.

I'm not using a linked measure group but the measure group it's reporting an error on has a different granularity key to the main measure group. Apart from changing the granularity key of the group that has a problem what can I do to fix this?

Mark

|||

I got the same error message which is posted in the first post here.

On a server which didn't had the foodmart database installed, I didn't get any problems after installing SP2. When I tossed the foodmart files away as described above, all problems vanished. It seems the problem here is the foodmart database. At the end of it Im glad I got my DB back, but "they" managed to scare the living daylight out of me with this, because I thought the whole AS had been turned into a wreck. Ironically the only database left in the management studio was the foodmart db. If you don't use it I would recommend to delete it before installing SP2.

Regards, Jeroen

Problems after installing SP2

I'm having some major problems with SSAS after installing SP2 (I never had an SP2 CTP installed, just SP1).

When I bring up BI Studio and select Open/Analysis Service Database, put in my server name (which is on the same machine that I'm running BI Studio), the click the "Database" drop down to select a database, it shows the following error in the bottom of the dialog:

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.
Errors in the metadata manager. An error occurred when loading the Sales measure group, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.0.cub\Sales.1.det.xml'.
Errors in the metadata manager. An error occurred when loading the Trained Cube cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Data\FoodMart.0.db\Trained Cube.1.cub.xml'.

At this point, there doesn't seem to be any way to open any database in BI Studio. I always get that error, even if I try typing in another database name.

SQL Management Studio seems slightly better - I can at least connect there. But it only shows a couple of the databases that were on the machine - a bunch are missing. Any attempt to process or browse any of the databases results in the same error as above.

Any ideas?

Quoting the SP2 Readme: http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade

Introduced in SQL Server 2005 SP2.

Analysis Services now requires that all dimensions contained in a linked measure group have the same granularity as the dimension in the source measure group. For example, if a time dimension in the source measure group has the day attribute as the granularity attribute, the linked measure group must also use the day attribute as the granularity attribute. The most common scenario for an Analysis Services database that violates this rule is a SQL Server 2000 Analysis Services database that was migrated by using a pre-release version of SQL Server 2005.

If you have a database that violates this granularity rule, you must modify the database containing the linked measure group before applying SP2. If you don't, the database will become invalid after SP2 is applied. Such invalid databases must be deleted.

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

|||Thanks Edward. RTFM, eh? Actually, I doubt it would have helped - this is a sample DB that I found somewhere, I wouldn't have realized it had this issue.

The problem is, I don't see a way to delete the database. The admin tools all barf with that error when I try to do anything, including deleting a DB. Is there some other way to nuke it?
|||Unless you can go back to pre-SP2 build, you can stop the service, and manually delete all the files and folders in the Data directory. Then you will have to redeploy and reprocess the rest of your databases, unless you have backups in which case you would simply restore them.|||Thanks Mosha. I deleted the .0.db directory and the 0.db.xml file for the bad database (which I didn't need anyway, it was just for playing with), and have now regained access to my server. Any other files I should delete to clean things up?
|||

This should be enough.

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

|||

I've recently installed SP2. All was fine initially but I'm now getting the error listed above having not changed anything significant (changed the membersname unique property of an attribute and then changed it back again). I'd been making changes all day to the attribute relationships and deploying without a problem.

I'm not using a linked measure group but the measure group it's reporting an error on has a different granularity key to the main measure group. Apart from changing the granularity key of the group that has a problem what can I do to fix this?

Mark

|||

I got the same error message which is posted in the first post here.

On a server which didn't had the foodmart database installed, I didn't get any problems after installing SP2. When I tossed the foodmart files away as described above, all problems vanished. It seems the problem here is the foodmart database. At the end of it Im glad I got my DB back, but "they" managed to scare the living daylight out of me with this, because I thought the whole AS had been turned into a wreck. Ironically the only database left in the management studio was the foodmart db. If you don't use it I would recommend to delete it before installing SP2.

Regards, Jeroen

Probleme with plusiers value of a field

Hello to all:

I have this request:

SELECT code_statusfac FROM factures WHERE code_statusfac in (:FILTRE)

While :FILTRE that can equal to some value, example:

' DIS02 ', ' DIS03 ', ' DIS04 ', etc.

When I execute the request and I replace:FILTRE by DIS04 that works well, but when I want to allocate him some value at the same moment it costs not.

What is what you have ideas PLEASE.

Thank you in advance.

Simple answer is use dynmaic query...

But be cautious about sql injection & other security concerns (it needs SELECT permission the table object).

Code Snippet

Exec ('SELECT code_statusfac FROM factures WHERE code_statusfac in (' + @.ListOfValues + ')')

problema con la funzione except

qualcuno sa dirmi cosa c' di sbagliata in questa query:
EXCEPT
({ SELECT idAttach
FROM tbAttachs }, {
SELECT idAttach
FROM tbAttachsForPages
WHERE (idPage = 2) })
graziealidicera <samarcanda@.nospambbs.olografix.org> wrote in message news:<16hyw7zwzctkz.ei7eq6l4y1wn$.dlg@.40tude.net>...
> qualcuno sa dirmi cosa c' di sbagliata in questa query:
> EXCEPT
> ({ SELECT idAttach
> FROM tbAttachs }, {
> SELECT idAttach
> FROM tbAttachsForPages
> WHERE (idPage = 2) })
> grazie

You might want to post this in microsoft.public.sqlserver.olap,
assuming that EXECPT() in this case is the Analysis Services function.
But you should provide some more information - what does the complete
query look like, how are you executing it, what error or problem do
you get, etc.

Simon

Problem: What's that sql script doing ?

Hello !
Due to a lack of SQL server i am wondering, what that sql script is
doing. It it is part of a VB macro.
UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
ZIP<>A.ZIP)
tnx in advance, Guido StepkenGuido Stepken (stepken@.little-idiot.de) writes:

quote:

---

> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)

---

I guess it depends on which engine you run it on. On SQL Server this
happens:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 195, Level 15, State 1, Line 1
'FIRST' is not a recognized function name.
I would guess that it intended for Access.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no such concept as "FIRST" in SQL Server. Did you mean MIN() or
MAX()? Or something else altogether?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Guido Stepken" <stepken@.little-idiot.de> wrote in message
news:bts9rc$umu$06$1@.news.t-online.com...

quote:

---

> Hello !
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
> tnx in advance, Guido Stepken
>

---

|||FIRST is an Access thing. The closest thing in T-SQL is
UPDATE address SET
ZIP = (
SELECT TOP 1 ZIP
FROM address
WHERE ZIP <> A.ZIP
)
But TOP 1 without ORDER BY is not often a good idea, and this is a
rather bizarre update query: change every ZIP in the address table to
some random other ZIP?
SK
Guido Stepken wrote:

quote:

---

> Hello !
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
> tnx in advance, Guido Stepken
>

---

|||Steve Kass wrote:

quote:

---

> FIRST is an Access thing. The closest thing in T-SQL is

---

Interesting...

quote:

---

> UPDATE address SET
> ZIP = (
> SELECT TOP 1 ZIP
> FROM address
> WHERE ZIP <> A.ZIP
> )
>

---

You rather mean:
UPDATE address AS A SET
ZIP = (
SELECT TOP 1 ZIP
FROM address
WHERE ZIP <> A.ZIP)
does it work on T-SQL ?

quote:

---

> But TOP 1 without ORDER BY is not often a good idea, and this is a
> rather bizarre update query: change every ZIP in the address table to
> some random other ZIP?

---

Bizarre ... a SQL attack on data warehouse, ERP, CRM, - programs,
address books, outlook ... ?

quote:

---

> SK
> Guido Stepken wrote:
>
>

---

|||Guido Stepken wrote:

quote:

---

> Steve Kass wrote:
>
>
> Interesting...
>
> You rather mean:
> UPDATE address AS A SET
> ZIP = (
> SELECT TOP 1 ZIP
> FROM address
> WHERE ZIP <> A.ZIP)
> does it work on T-SQL ?

---

Guido,
T-SQL does not support an alias on the target table of an UPDATE
query, and I should have written
UPDATE address SET
ZIP = (
SELECT TOP 1 ZIP
FROM address A
WHERE A.ZIP <> ZIP
)
SK

quote:

---

>
>
> Bizarre ... a SQL attack on data warehouse, ERP, CRM, - programs,
> address books, outlook ... ?
>
>

---

Problem: What's that sql script doing ?

Hello !
Due to a lack of SQL server i am wondering, what that sql script is
doing. It it is part of a VB macro.
UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
ZIP<>A.ZIP)
tnx in advance, Guido StepkenGuido Stepken (stepken@.little-idiot.de) writes:
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
I guess it depends on which engine you run it on. On SQL Server this
happens:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 195, Level 15, State 1, Line 1
'FIRST' is not a recognized function name.
I would guess that it intended for Access.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||There is no such concept as "FIRST" in SQL Server. Did you mean MIN() or
MAX()? Or something else altogether?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Guido Stepken" <stepken@.little-idiot.de> wrote in message
news:bts9rc$umu$06$1@.news.t-online.com...
> Hello !
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
> tnx in advance, Guido Stepken
>|||FIRST is an Access thing. The closest thing in T-SQL is
UPDATE address SET
ZIP = (
SELECT TOP 1 ZIP
FROM address
WHERE ZIP <> A.ZIP
)
But TOP 1 without ORDER BY is not often a good idea, and this is a
rather bizarre update query: change every ZIP in the address table to
some random other ZIP?
SK
Guido Stepken wrote:
> Hello !
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
> tnx in advance, Guido Stepken
>|||Steve Kass wrote:
> FIRST is an Access thing. The closest thing in T-SQL is
Interesting...
> UPDATE address SET
> ZIP = (
> SELECT TOP 1 ZIP
> FROM address
> WHERE ZIP <> A.ZIP
> )
>
You rather mean:
UPDATE address AS A SET
ZIP = (
SELECT TOP 1 ZIP
FROM address
WHERE ZIP <> A.ZIP)
does it work on T-SQL ?
> But TOP 1 without ORDER BY is not often a good idea, and this is a
> rather bizarre update query: change every ZIP in the address table to
> some random other ZIP?
Bizarre ... a SQL attack on data warehouse, ERP, CRM, - programs,
address books, outlook ... ?
> SK
> Guido Stepken wrote:
>> Hello !
>> Due to a lack of SQL server i am wondering, what that sql script is
>> doing. It it is part of a VB macro.
>> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
>> ZIP<>A.ZIP)
>> tnx in advance, Guido Stepken
>|||Guido Stepken wrote:
> Steve Kass wrote:
>> FIRST is an Access thing. The closest thing in T-SQL is
>
> Interesting...
>> UPDATE address SET
>> ZIP = (
>> SELECT TOP 1 ZIP
>> FROM address
>> WHERE ZIP <> A.ZIP
>> )
> You rather mean:
> UPDATE address AS A SET
> ZIP = (
> SELECT TOP 1 ZIP
> FROM address
> WHERE ZIP <> A.ZIP)
> does it work on T-SQL ?
Guido,
T-SQL does not support an alias on the target table of an UPDATE
query, and I should have written
UPDATE address SET
ZIP = (
SELECT TOP 1 ZIP
FROM address A
WHERE A.ZIP <> ZIP
)
SK
>> But TOP 1 without ORDER BY is not often a good idea, and this is a
>> rather bizarre update query: change every ZIP in the address table to
>> some random other ZIP?
>
> Bizarre ... a SQL attack on data warehouse, ERP, CRM, - programs,
> address books, outlook ... ?
>> SK
>> Guido Stepken wrote:
>> Hello !
>> Due to a lack of SQL server i am wondering, what that sql script is
>> doing. It it is part of a VB macro.
>> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
>> ZIP<>A.ZIP)
>> tnx in advance, Guido Stepken
>>
>

problem: select Max(date) which is less than actual Max(date)

based on the data at the beginning of this thread I use this query and get
this result
SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06'
--gives the correct result for this query but......
recID curDate expDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
I am selecting only rows where the Max(expDate) is '1/25/06'. That would
include rows with recID of 1, 4, and 5 since recID's 2, 3, and 6 have a
Max(expDate) of '2/1/06' which is greater than '1/25/06'.
Well, I need to write another query that selects not only the rows with a
Max(expDate) of '1/25/06', but will also select rows that contain an expDate
of '1/25/06' -- IF -- the corresponding curDate for that row and recID i
s
the Max(curDate) for that recID for the corresponding expDate of '1/25/06'.
recID 6 contains a row with expDate of '1/25/06' which is not its
Max(expDate). However, the corresponding curDate for that row is '1/5/06'
OK. I see your problem. I screwed up. The curDate was supposed to be
'1/5/06' which would make it the Max(curDate) for recID 6 for the
corresponding expdate of '1/25/06'. So I need to pick up that row in
addition to the rows that have a Max(expDate) of '1/25/06', even though for
recID 6 expDate of 1/25/06 is not the Max(expDate), but curDate is
Max(curDate) for recID 6 expDate 1/25/06.
Sorry for the goof up. Any suggestions how I could write such a query?
"Roy Harvey" wrote:

> Rich,
> This is just a wee bit hard for me to sort out. One part in
> particular has me especially .
> The total set of data for recID = 6 is:
>
> Your desired result includes:
> I simply do not see where a curDate in 2006 came from that input data.
> Your specs seem to talk about using a different value for expDate
> under specific circumstances, but I was unable to see anything about
> using any different calculation for curDate than a value of curDate.
> Roy Harvey
> Beacon Falls, CT
>Here is what I came up with, including the last couple of lines of
data load with the correction. The basic idea is to write it as two
queries with a UNION. The first part is what you already have
working. The second part tries to get the row that meets the
secondary criteria. Note that the first query is pretty much
duplicated in the second for the NOT IN test.
While it appears to return the result you wanted from the data
provided, I do not guarantee it will do as well when you have the real
data.
select 6, '1/5/06', '1/25/06' union -- corrected curDate
select 6, '11/27/05', '2/1/06'
SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06'
UNION ALL
SELECT *
FROM #temp as X
WHERE recid NOT IN --the list of recid from beginning
(SELECT recid
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06')
AND expDate = '1/25/06'
AND curDate >=
(select curDate from #temp as Y
where X.recid = Y.recid
and Y.expDate =
(select max(expDate) from #temp as Z
where X.recid = Z.recid))
Roy Harvey
Beacon Falls, CT|||Thanks very much. The Union query was the route I was going to go, but I
thought that maybe there a more sophisticated way to go. I guess I need to
have a little more confidence in myself.
Actually, the way you did it is fairly sophisticated. My idea of using a
Union query was way more verbose, and thus, I was trying to avoid it. But
you nailed it
thanks very much for your help.
Rich
"Roy Harvey" wrote:

> Here is what I came up with, including the last couple of lines of
> data load with the correction. The basic idea is to write it as two
> queries with a UNION. The first part is what you already have
> working. The second part tries to get the row that meets the
> secondary criteria. Note that the first query is pretty much
> duplicated in the second for the NOT IN test.
> While it appears to return the result you wanted from the data
> provided, I do not guarantee it will do as well when you have the real
> data.
> select 6, '1/5/06', '1/25/06' union -- corrected curDate
> select 6, '11/27/05', '2/1/06'
>
> SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06'
> UNION ALL
> SELECT *
> FROM #temp as X
> WHERE recid NOT IN --the list of recid from beginning
> (SELECT recid
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06')
> AND expDate = '1/25/06'
> AND curDate >=
> (select curDate from #temp as Y
> where X.recid = Y.recid
> and Y.expDate =
> (select max(expDate) from #temp as Z
> where X.recid = Z.recid))
> Roy Harvey
> Beacon Falls, CT
>|||If I may, I added one more twist to this query. I added one more row to
#temp where recID 2 now also has an expDate of 1/25/06 which is less than it
s
max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that correspond
s
to the expDate of 1/25/06 - same situation at recID 6 where the curDate that
corresponds to recID 2 expDate of 2/1/06 is less than the recID 2
Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks
up recID 6.
So what I need to do is modify your query to see if I can pick up recID 2 in
addition to recID 6 along with the original recID's of 1, 4, 5.
This is the part where I was afraid of getting verbose. I will experiment
The real scenario has hundreds of thousands of records and there are rows
that match my sample. The pain.
"Rich" wrote:
> Thanks very much. The Union query was the route I was going to go, but I
> thought that maybe there a more sophisticated way to go. I guess I need t
o
> have a little more confidence in myself.
> Actually, the way you did it is fairly sophisticated. My idea of using a
> Union query was way more verbose, and thus, I was trying to avoid it. But
> you nailed it
> thanks very much for your help.
> Rich
> "Roy Harvey" wrote:
>|||I must be trying too hard. Your query works fine with the additional new ro
w
for recID 2. Sorry bout that.
"Rich" wrote:
> If I may, I added one more twist to this query. I added one more row to
> #temp where recID 2 now also has an expDate of 1/25/06 which is less than
its
> max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that correspo
nds
> to the expDate of 1/25/06 - same situation at recID 6 where the curDate th
at
> corresponds to recID 2 expDate of 2/1/06 is less than the recID 2
> Max(curDate) of 1/7/06. The 2nd part of the current Union query only pic
ks
> up recID 6.
> So what I need to do is modify your query to see if I can pick up recID 2
in
> addition to recID 6 along with the original recID's of 1, 4, 5.
> This is the part where I was afraid of getting verbose. I will experiment
> The real scenario has hundreds of thousands of records and there are rows
> that match my sample. The pain.
>
> "Rich" wrote:
>|||so, taking this to the nth level (for posterity incase I need to refer to
this again), I added yet one more row to recID 2. This additional row is a
2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06.
One of these rows contains a Max(curDate) for recID 2.
create table #temp(
recID int, curDate datetime, expDate datetime)
insert into #temp
select 1, '11/1/05', '1/5/06' union
select 1, '11/5/05', '1/13/06' union
select 1, '11/12/05', '1/25/06' union
select 1, '11/28/05', '1/25/06' union
select 2, '11/7/05', '1/7/06' union
select 2, '11/13/05', '1/12/06' union
select 2, '11/27/05', '1/15/06' union
select 2, '1/7/06', '1/25/06' union
select 2, '1/8/06', '1/25/06' union
select 2, '12/1/05', '2/1/06' union
select 3, '11/3/05', '1/7/06' union
select 3, '11/8/05', '1/12/06' union
select 3, '11/17/05', '1/23/06' union
select 3, '12/1/05', '2/1/06' union
select 4, '11/5/05', '1/3/06' union
select 4, '11/9/05', '1/7/06' union
select 4, '11/19/05', '1/14/06' union
select 4, '11/23/05', '1/25/06' union
select 5, '11/5/05', '1/3/06' union
select 5, '11/9/05', '1/7/06' union
select 5, '11/19/05', '1/25/06' union
select 5, '11/27/05', '1/25/06' union
select 6, '11/5/05', '1/3/06' union
select 6, '11/9/05', '1/7/06' union
select 6, '11/19/05', '1/25/06' union
select 6, '1/5/06', '1/25/06' union
select 6, '11/27/05', '2/1/06'
The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and to
also retrieve rows that contain an expdate of 1/25/06 that is not the
Max(expDate) but these additional rows contain a curdate in the same row as
the expDate of 1/25/06 which is the Max(curDate) for that recID. In this
sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of thos
e
2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The
goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of
1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate) fo
r
the rows that contain an expDate of 1/25/06.
What I did was to modify Roy's query as follows:
SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
FROM #temp
GROUP BY recid
HAVING MAX(expDate)='1/25/06'
UNION ALL
select recID, Max(curDate) m, expdate from --I added this line
(SELECT * FROM #temp as X
WHERE
recid NOT IN --the list of recid from beginning
(SELECT recid FROM #temp GROUP BY recid
HAVING MAX(expDate)='1/25/06')
AND
expDate = '1/25/06'
AND curDate >
(select curDate from #temp as Y
where X.recid = Y.recid
and Y.expDate =
(select max(expDate) from #temp as Z
where X.recid = Z.recid))) t1
Group By recid, expdate
--this gives
recID curDate expDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000
6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000
"Rich" wrote:
> I must be trying too hard. Your query works fine with the additional new
row
> for recID 2. Sorry bout that.
> "Rich" wrote:
>|||This gives the same result:
select recid,max(curDate) curDate, expDate
from #temp
where expDate='1/25/06'
group by recid,expDate
-oj
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:7634C35E-CD6B-4E14-8D6E-7DBAE3861BAC@.microsoft.com...
> so, taking this to the nth level (for posterity incase I need to refer to
> this again), I added yet one more row to recID 2. This additional row is
> a
> 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of
> 1/25/06.
> One of these rows contains a Max(curDate) for recID 2.
>
> create table #temp(
> recID int, curDate datetime, expDate datetime)
> insert into #temp
> select 1, '11/1/05', '1/5/06' union
> select 1, '11/5/05', '1/13/06' union
> select 1, '11/12/05', '1/25/06' union
> select 1, '11/28/05', '1/25/06' union
> select 2, '11/7/05', '1/7/06' union
> select 2, '11/13/05', '1/12/06' union
> select 2, '11/27/05', '1/15/06' union
> select 2, '1/7/06', '1/25/06' union
> select 2, '1/8/06', '1/25/06' union
> select 2, '12/1/05', '2/1/06' union
> select 3, '11/3/05', '1/7/06' union
> select 3, '11/8/05', '1/12/06' union
> select 3, '11/17/05', '1/23/06' union
> select 3, '12/1/05', '2/1/06' union
> select 4, '11/5/05', '1/3/06' union
> select 4, '11/9/05', '1/7/06' union
> select 4, '11/19/05', '1/14/06' union
> select 4, '11/23/05', '1/25/06' union
> select 5, '11/5/05', '1/3/06' union
> select 5, '11/9/05', '1/7/06' union
> select 5, '11/19/05', '1/25/06' union
> select 5, '11/27/05', '1/25/06' union
> select 6, '11/5/05', '1/3/06' union
> select 6, '11/9/05', '1/7/06' union
> select 6, '11/19/05', '1/25/06' union
> select 6, '1/5/06', '1/25/06' union
> select 6, '11/27/05', '2/1/06'
>
> The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and
> to
> also retrieve rows that contain an expdate of 1/25/06 that is not the
> Max(expDate) but these additional rows contain a curdate in the same row
> as
> the expDate of 1/25/06 which is the Max(curDate) for that recID. In this
> sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of
> those
> 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The
> goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of
> 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate)
> for
> the rows that contain an expDate of 1/25/06.
> What I did was to modify Roy's query as follows:
> SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06'
> UNION ALL
> select recID, Max(curDate) m, expdate from --I added this line
> (SELECT * FROM #temp as X
> WHERE
> recid NOT IN --the list of recid from beginning
> (SELECT recid FROM #temp GROUP BY recid
> HAVING MAX(expDate)='1/25/06')
> AND
> expDate = '1/25/06'
> AND curDate >
> (select curDate from #temp as Y
> where X.recid = Y.recid
> and Y.expDate =
> (select max(expDate) from #temp as Z
> where X.recid = Z.recid))) t1
> Group By recid, expdate
> --this gives
> recID curDate expDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000
> 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000
>
> "Rich" wrote:
>|||On Thu, 2 Mar 2006 22:56:26 -0800, Rich
<Rich@.discussions.microsoft.com> wrote:

>so, taking this to the nth level (for posterity incase I need to refer to
>this again)....
Sounds like you have things well in hand. (I really don't have time
to get into the logic any further.) My only thought, in light of oj's
reply, is to add more variety to the test data. Make it a LOT more
complicated. I'm sure you know about the problem of programmers only
testing part of their logic because they use the application the say
they think it should be used. Well, us DB folks can suffer from the
same problem, testing with test cases that are only a subset of what
the real world might hit us with.
Good luck!
Roy Harvey
Beacon Falls, CT|||Thanks all for your replies. OJ's solution is less verbose than the one I
was working with, but at least we all got the idea what I was trying to do -
at least I am starting to get the idea. And Roy is correct on making the
data more complex to cover more twists. Believe me, the actual data is
quite a bit more complex, but at least now I am chiseling down the queries
that I need. Start out with verbose till I understand what I am looking for
and then drill down to the less verbose.
"Rich" wrote:
> so, taking this to the nth level (for posterity incase I need to refer to
> this again), I added yet one more row to recID 2. This additional row is
a
> 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06
.
> One of these rows contains a Max(curDate) for recID 2.
>
> create table #temp(
> recID int, curDate datetime, expDate datetime)
> insert into #temp
> select 1, '11/1/05', '1/5/06' union
> select 1, '11/5/05', '1/13/06' union
> select 1, '11/12/05', '1/25/06' union
> select 1, '11/28/05', '1/25/06' union
> select 2, '11/7/05', '1/7/06' union
> select 2, '11/13/05', '1/12/06' union
> select 2, '11/27/05', '1/15/06' union
> select 2, '1/7/06', '1/25/06' union
> select 2, '1/8/06', '1/25/06' union
> select 2, '12/1/05', '2/1/06' union
> select 3, '11/3/05', '1/7/06' union
> select 3, '11/8/05', '1/12/06' union
> select 3, '11/17/05', '1/23/06' union
> select 3, '12/1/05', '2/1/06' union
> select 4, '11/5/05', '1/3/06' union
> select 4, '11/9/05', '1/7/06' union
> select 4, '11/19/05', '1/14/06' union
> select 4, '11/23/05', '1/25/06' union
> select 5, '11/5/05', '1/3/06' union
> select 5, '11/9/05', '1/7/06' union
> select 5, '11/19/05', '1/25/06' union
> select 5, '11/27/05', '1/25/06' union
> select 6, '11/5/05', '1/3/06' union
> select 6, '11/9/05', '1/7/06' union
> select 6, '11/19/05', '1/25/06' union
> select 6, '1/5/06', '1/25/06' union
> select 6, '11/27/05', '2/1/06'
>
> The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and t
o
> also retrieve rows that contain an expdate of 1/25/06 that is not the
> Max(expDate) but these additional rows contain a curdate in the same row a
s
> the expDate of 1/25/06 which is the Max(curDate) for that recID. In this
> sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of th
ose
> 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The
> goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of
> 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate)
for
> the rows that contain an expDate of 1/25/06.
> What I did was to modify Roy's query as follows:
> SELECT recid, Max(curDate) curDate, MAX(expDate) expDate
> FROM #temp
> GROUP BY recid
> HAVING MAX(expDate)='1/25/06'
> UNION ALL
> select recID, Max(curDate) m, expdate from --I added this line
> (SELECT * FROM #temp as X
> WHERE
> recid NOT IN --the list of recid from beginning
> (SELECT recid FROM #temp GROUP BY recid
> HAVING MAX(expDate)='1/25/06')
> AND
> expDate = '1/25/06'
> AND curDate >
> (select curDate from #temp as Y
> where X.recid = Y.recid
> and Y.expDate =
> (select max(expDate) from #temp as Z
> where X.recid = Z.recid))) t1
> Group By recid, expdate
> --this gives
> recID curDate expDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000
> 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000
>
> "Rich" wrote:
>|||Well, OJ's less verbose query isn't working on my actual data because I am
picking up rows with expDates that are not Max(expDate) for a given recID
where the curDate is not the Max(curDate) for the given recID and expDate.
I
need to pick up rows where the Max(expDate) is 1/25/06 or if a recID
contains a newer Max(expDate) than 1/25/06, I pick that row/recID up if the
corresponding curDate for the row with expdate = 1/25/06 is greater than th
e
curdate for the row containing the newer expDate for the given recID. So it
looks like I will have to go with the more verbose query. I was hoping it
would be a simple query, but no such luck. Oh well, at least one of the
solutions will work for my data.
"Rich" wrote:
> Thanks all for your replies. OJ's solution is less verbose than the one I
> was working with, but at least we all got the idea what I was trying to do
-
> at least I am starting to get the idea. And Roy is correct on making the
> data more complex to cover more twists. Believe me, the actual data is
> quite a bit more complex, but at least now I am chiseling down the queries
> that I need. Start out with verbose till I understand what I am looking f
or
> and then drill down to the less verbose.
> "Rich" wrote:
>

Problem: Non Cascading Params causing report reload on each select

When running a report I am noticing the following behaviour:
As each parameter is selected, the report is doing some kind of postback or
refresh despite the fact that none of the parameters are linked
(cascading/dependent parameters).
Why is this occuring?
It is very annoying to the user as a report with 8 params is reloading 8
times even before they run the report.
Virtually all the params are query based with a default of query based.
config
RS2005 sp1I think this is by design, no way to change, unless write custom code which
is time consuming... may be MS will improve in the furture versions. :-)
Amarnath, MCTS
"adolf garlic" wrote:
> When running a report I am noticing the following behaviour:
> As each parameter is selected, the report is doing some kind of postback or
> refresh despite the fact that none of the parameters are linked
> (cascading/dependent parameters).
> Why is this occuring?
> It is very annoying to the user as a report with 8 params is reloading 8
> times even before they run the report.
> Virtually all the params are query based with a default of query based.
>
> config
> RS2005 sp1

PROBLEM: Database Mainence plan "All user Databases" missing database

If I select "All user Databases" and click T-SQL, 3 our of 4
databases are listed- and the missing database has not been backed up
via the plan. All databases are listed, however, when I select
"Selected Databases"- they are shown in the drop downbox.
This is for SQL 2005 Service Pack 2 with Hotfix 3175.
My guess: The missing database was restored and not restored to
overwrite an existing database. Since the CREATE DATABASE command was
not executed, maybe there is system info in master that doesn't get
set'
Help please. Thanks
Erik> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
No, that should not be the reason. The same meta-data would exist regardless of how the database was
created (CREATE DATABASE, attach or by RESTORE). Perhaps the database is in 70 compatibility mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:25261d77-880b-4b83-9e9a-94866e14af6e@.e6g2000prf.googlegroups.com...
> If I select "All user Databases" and click T-SQL, 3 our of 4
> databases are listed- and the missing database has not been backed up
> via the plan. All databases are listed, however, when I select
> "Selected Databases"- they are shown in the drop downbox.
> This is for SQL 2005 Service Pack 2 with Hotfix 3175.
> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
> Help please. Thanks
> Erik|||Thanks. No, i've seent that 7.0 Compat mode issue before, it is
actually set to 9.0.|||Do the plan do both db and log backup? Is this missing backup a db or a log backup? Perhaps there's
a difference in the recovery model setting.
If not, then I'd check other database options, just doing a quick visual compare:
SELECT * FROM sys.databases
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:42c70b00-ac98-4d82-b150-69d1dbc3bf29@.i29g2000prf.googlegroups.com...
> Thanks. No, i've seent that 7.0 Compat mode issue before, it is
> actually set to 9.0.|||Thanks for suggstions. I found that Auto Update Statistics was
different and I turned it on and also set to FULL RECOVER MODE and it
got listed in View t-SQL preview !!
THAN I turned everything back to the previous settings for my missing
databses and the All Databases support still was fixed and in tact! t-
SQL Preview showed my previously missing database.
Updating sys.databases seems to fix the problem. maybe there is some
trigger in the background or something.
Thank you for all of your help.
Erik

Problem with WMI tasks : waiting for files in parallel in a SSIS package

Hi,

I have a problem with the task "event watcher".

I've made a query like the one in msdn (SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"e:\\\\temp\""). I have 20 similar tasks for watching in different folders, but when there are too much tasks in parallel, it doesn't work anymore. I change the numbers of executables to 128 (in the general properties of the package (to test)) but it doesn't seems to work.

I don't understand why it works when there are only 1 or 2 (6 seems to be the maximum) tasks and not if there are more than 6.

Could you help me with this issue?

Configuration : Windows Server 2003, SQL Server 2005, SSIS, Sql Server Agent

Thanks a lot.

Julien.

I forgot to tell you that the package work when I execute it on debug, on the local machine but not on the server with a sql job.

Thanks.

|||

I don't know anything about WMI, but it looks like you're waiting for files to be created. Have you looked at the File Watcher Task that's available for download from SQLIS.com?

http://www.sqlis.com/23.aspx

Hopefully someone will have an idea about the WMI problem, but this might get you around the problem in the interim...

|||

Hi,

I don't really want to add tasks that are not supported by Microsoft, that's why I tried this WMI stuff.

Any idea?

Thanks.

Julien.

Problem with updating only one row in a SQL 2005 database

hi,

I have a problem with updating a row in my database. I don't know how to update and i need help.

I have a form where the users select a category and a sub category to enter a message.

I have two dropdown lists, one is for selecting the category and the other is for the sub-category.

In my database for the categories and the sub-categories, i have a variable (Quantity) where i keep the quantity for each table.

I just want to be able to increment this variable by 1 (for the selected category and sub-category) when a user enter a new message...

The insert procedure is working well and have no problem with it.

I use VWD 2005 Express with SQL 2005 express and i code in VB.

HELP!!!!!

Please post your table structure, some sample data and the update you are expecting to happen. Also the query you currently have would help.

|||

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SousCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SousCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

With corrections!

----------

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SubCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SubCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||So any ideas?|||

Hi,

You can create a button_click event in your code-behind page. The following code (c#) is just a sample which can achieve your purpose.

protected void Button1_Click(object sender, EventArgs e) {/// Get the CategoryName value from DropDownList1string CategoryName =this.DropDownList1.SelectedItem.Value;/// Get the CategoryName value from DropDownList2string SubCategoryName =this.DropDownList2.SelectedItem.Value;/// in this sample,we get the message and the username value from textboxesstring Message =this.TextBox1.Text;string User=this.TextBox2.Text;/// connstr is the name of connection string you set in Web.configstring conn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; SqlConnection myconn =new SqlConnection(conn);/// write the message which user submit into YourTablestring insert_command ="Insert into YourTable (CategoryName,SubCategoryName,Message,Users) values('"+CategoryName+"','"+SubCategoryName+"','"+Message+"','"+User+"')"; myconn.Open(); SqlCommand mycomm =new SqlCommand(insert_command, myconn); mycomm.ExecuteNonQuery(); mycomm.Dispose();/// sql for updating the Quantity number in the Categories tablestring up_cate ="update Categories set Quantity = (Quantity+1) where CategoryName = '" + CategoryName +"'";/// sql for updating the Quantity number in the SubCategories tablestring up_subcate ="update SubCategories set Quantity = (Quantity+1) where SubCategoryName ='" + SubCategoryName +"'"; SqlCommand mycomm_1 =new SqlCommand(up_cate, myconn); SqlCommand mycomm_2 =new SqlCommand(up_subcate, myconn); mycomm_1.ExecuteNonQuery(); mycomm_2.ExecuteNonQuery(); mycomm_1.Dispose(); mycomm_2.Dispose(); myconn.Close(); }

Hope it helps. Thanks.|||

Sorry i code in VB...

I tried that and it's not working (on a click event) :

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("FouinerDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
Dim myDA As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity=10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)

|||

(1) Please use parameterized queries to prevent SQL injeection attacks.

I would recommend you do it via stored procedure as follows:

Update CategoriesSET Quantity = Quantity + 1WHERE Categoryid = @.CategoryidUpdate SubCategoriesSET Quantity = Quantity + 1WHERE SubCategories = @.SubCategoriesand Categoryid = @.Categoryid

If you had to do it via adhoc T-SQL, you'd be making 2 trips once for each Update. Through a stored proc, you can do it all in one trip.

|||Ok, but how do you call the stored procedure in the vb code.

I dont know nothing about it... ;-(|||I tired that for stored procedure and when i try it (execute), it doesn't change anything in my database (i also tried to change @.Categoryid and @.SousCategoriesID by values that i know are valid ) :

ALTER PROCEDURE

dbo.UpdateQuantity/*(@.parameter1 int = 5,@.parameter2 datatype OUTPUT)*/

AS

/* SET NOCOUNT ON */RETURN

BEGIN

DECLARE@.CategoryidintDECLARE@.SousCategoriesIDint

UPDATE

CategoriesSETQuantity = Quantity + 1WHERECategoryID = @.Categoryid

UPDATE

SousCategoriesSETQuantity = Quantity + 1WHERESousCategoriesID = @.SousCategoriesID

END

|||I don't understand why aSelectCommand works in my page and not anUpdateCommand...

Works!!!
myDA.SelectCommand =

New Data.SqlClient.SqlCommand("SELECT * FROM Annonces WHERE SousCategoryID = " & SubCatID.ToString(), myConnectionDB)

Dont works!!!
myDA.UpdateCommand =New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = 10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
|||

Ok i finally got it!!!

It's theUpdateCommand.ExecuteNonQuery() that i missed. ;-)

I will also try with a Stored Procedure... See you soon!
-----------

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DatabaseDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
myConnectionDB.Open()
Dim myDA1 As New Data.SqlClient.SqlDataAdapter()
Dim myDA2 As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA1.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = Quantity + 1 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
myDA2.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE SousCategories SET Quantity = Quantity + 1 WHERE SousCategoriesID = " & SubCatID.ToString(), myConnectionDB)
myDA1.UpdateCommand.ExecuteNonQuery()
myDA2.UpdateCommand.ExecuteNonQuery()
myConnectionDB.Close()

|||

It would be more like this: The parameters values would come from your VB application. For your update on SubCategories I also added the CategoryId= @.CategoryID condition because I assumed that the same subcategory could be present for multiple categories. So its important to also specify the Category level.

ALTER PROCEDURE dbo.UpdateQuantity ( @.Categoryidint , @.SousCategoriesIDinT)ASBeginSET NOCOUNT ON UPDATE CategoriesSET Quantity = Quantity + 1WHERE CategoryID = @.CategoryidUPDATE SousCategoriesSET Quantity = Quantity + 1WHERE SousCategoriesID = @.SousCategoriesIDAnd CategoryID = @.CategoryidSET NOCOUNT OFFEND

Your VB code could look like this:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="updateQuantity"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.Categoryid",SqlDbType.int))myCommand.Parameters("@.Categoryid").Value = 10myCommand.Parameters.Add(New SqlParameter("@.SousCategoriesID",SqlDbType.int))myCommand.Parameters("@.SousCategoriesID").Value = 1TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try

|||Hi,

In terms of process speed, wich one is the fastest?

Hard coding or Stored Procedure?|||

Whether you use T-SQL/stored proc the actual statements are executed at the DB. IF you had multiple T-SQL statements to be executed you are better off using stored procs as you can do it all in one trip. Also, if you have T-SQL all over your app it becomes a maintenance nightmare if your underlying schema changes in future. Stored procs provide a channelized way of accesssing the data. Also, for security reasons procs are a better choice. You'd have to give SELECT/UPDATE/DELETE rights to the userid you are using on the actual table if you use adhoc queries. If you do the same thing via procs, you just give the user permission to execute the proc. There are tons of other pros vs cons. You can also google (adhoc query vs stored proc) and see some good debate about this.

Problem with update query based on subquery

Hello all!
I have created a update query, which looks like this:
UPDATE NMR.dbo.NMR_wpisy
INNER JOIN
(SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
[Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
= [Confirmed_Production_Labor]
Could you modify these query to be right, because there is the error? I
would be very grateful for it...
Please have a look what I would achieve:
I want to sum double or more values from the table using such query
(let`s call it query1):
SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
This query works fine. After that I want to update the values of
NMR_wpisy table which are in relations with nr_NMR field from the above
query1 and there (in query1) are the values which I would like to to
update the fields. I don`t know how to do that. I was trying to do it on
MS Access and copy this whole query into the MS SQL but if the subquery
exists I cannot do that.
Please help
I would be very grateful for it
Best regards
Marcin
*** Sent via Developersdex http://www.examnotes.net ***Try,
UPDATE a
SET
a.czas_machine_sortowanie = b.[Confirmed_Production_Machine],
a.czas_labour_sortowanie = b.[Confirmed_Production_Labor]
from
NMR.dbo.NMR_wpisy as a
INNER JOIN
(
SELECT
N.nr_NMR,
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
GROUP BY
N.nr_NMR
) as b
ON b.nr_NMR = a.nr_NMR
-- or
UPDATE NMR.dbo.NMR_wpisy
SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0),
NMR.dbo.NMR_wpisy.czas_labour_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Labor])
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0)
where
exists(
select
*
from
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
)
go
AMB
"Marcin Zmyslowski" wrote:

> Hello all!
> I have created a update query, which looks like this:
> UPDATE NMR.dbo.NMR_wpisy
> INNER JOIN
> (SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
> ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
> NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
> [Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
> = [Confirmed_Production_Labor]
> Could you modify these query to be right, because there is the error? I
> would be very grateful for it...
> Please have a look what I would achieve:
> I want to sum double or more values from the table using such query
> (let`s call it query1):
> SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
> This query works fine. After that I want to update the values of
> NMR_wpisy table which are in relations with nr_NMR field from the above
> query1 and there (in query1) are the values which I would like to to
> update the fields. I don`t know how to do that. I was trying to do it on
> MS Access and copy this whole query into the MS SQL but if the subquery
> exists I cannot do that.
> Please help
> I would be very grateful for it
> Best regards
> Marcin
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Problem with UNION ALL

Hi,
I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
Among them one SELECT query is making more time.
If the same query is run seperately, it works fine in much less time
and the row count in the execution plan is small with a inner loop
join.
But when used with other select statements using UNION ALL,
the row count goes in thousands resulting in a HASH join.
Could u suggest any possible reason?
Thanks,
Dutt.Dutt
Do the SELECTs have WHERE conditions? How big is the output? Can you post
am example here?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> Hi,
> I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> Among them one SELECT query is making more time.
> If the same query is run seperately, it works fine in much less time
> and the row count in the execution plan is small with a inner loop
> join.
> But when used with other select statements using UNION ALL,
> the row count goes in thousands resulting in a HASH join.
> Could u suggest any possible reason?
> Thanks,
> Dutt.
>|||Hi Uri,
Yeah, some SELECTs have WHERE conditions and other SELECTs are just
SELECT NULLs.
Output is 4line XML.(17 rows without XML).
XML option has nothing to do with it.
Thanks,
Dutt.
On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt
> Do the SELECTs have WHERE conditions? How big is the output? Can you pos
t
> am example here?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>
>
> - Show quoted text -|||Dutt
As I see here you also have some JOIN within UNION ALL statement, have you
tried to created a nonclusterd indexes
on referenced tables (FK)?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...[vbcol=seagreen]
> Hi Uri,
> Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> SELECT NULLs.
> Output is 4line XML.(17 rows without XML).
> XML option has nothing to do with it.
> Thanks,
> Dutt.
>
> On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||Yeah, Uri,
Here the joining key is Primary Key itself and no other key is
available between those tables.
And one doubt, is there any problem if we have too many UNION ALLs.
Thanks
Dutt
On Apr 10, 2:07 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt>> > the row count goes in thousands resulting in a HASH join.
> As I see here you also have some JOIN within UNION ALL statement, have you
> tried to created a nonclusterd indexes
> on referenced tables (FK)?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -