I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.
Why?Are you using a standard Microsoft Access database with links to the SQL Server tables, or are you using a Microsoft Access Data Project (.adp file extenstion)?
If you are linking to SQL server, MS Access will do it's best to formulate your queries as "pass through", meaning only the sql text is sent to the server and only the process results are returned. If it is unable to formulate it as a pass-through query than Access must temporarily transfer all the tables involved across your network and then process the results locally. You can imagine how this could be unimaginably slow. (?)
I recommend that you convert your application to an Access Data Project if possible.
blindman|||I am not sure about ADP, but Access2K does not use pass-through for linked objects. In fact, the only way you can use pass-through is by selecting it from the query builder.|||It is standard Access 97 VBA application with use of DLOOKUP function.
The long time answers for DLOOKUP queries are not the rule.
I can use DLOOKUP about 10 times with fast answer (under 1 second) and next time I have to wait 10 seconds.sql
Friday, March 30, 2012
MS Access "Upsizing" Tools in SQL Server
Dave,
I have tried the method that you described to import an Access database and
I keep getting the same error:
Could not set up data flow connections. The conection type OLEDB specified
for connection manager (then I see what looks like a registry entry) is not
recognized as a valid connection manager type.
I have also tried creating a new Integrated Services Project and run the
import from there. I get the same error. When I try to upsize in Access I
get a report that says that the tables were not converted.
Any suggestions?
"Dave Patrick" wrote:
> Good to hear. You're welcome.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Alex Maghen" wrote:
> | Thanks. Yeah, I get it jow.
> |
> | Alex
>
>
Looks like this should sort it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255347&SiteID=1
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rob" wrote:
> Dave,
> I have tried the method that you described to import an Access database
> and
> I keep getting the same error:
> Could not set up data flow connections. The conection type OLEDB
> specified
> for connection manager (then I see what looks like a registry entry) is
> not
> recognized as a valid connection manager type.
> I have also tried creating a new Integrated Services Project and run the
> import from there. I get the same error. When I try to upsize in Access
> I
> get a report that says that the tables were not converted.
> Any suggestions?
I have tried the method that you described to import an Access database and
I keep getting the same error:
Could not set up data flow connections. The conection type OLEDB specified
for connection manager (then I see what looks like a registry entry) is not
recognized as a valid connection manager type.
I have also tried creating a new Integrated Services Project and run the
import from there. I get the same error. When I try to upsize in Access I
get a report that says that the tables were not converted.
Any suggestions?
"Dave Patrick" wrote:
> Good to hear. You're welcome.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Alex Maghen" wrote:
> | Thanks. Yeah, I get it jow.
> |
> | Alex
>
>
Looks like this should sort it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255347&SiteID=1
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rob" wrote:
> Dave,
> I have tried the method that you described to import an Access database
> and
> I keep getting the same error:
> Could not set up data flow connections. The conection type OLEDB
> specified
> for connection manager (then I see what looks like a registry entry) is
> not
> recognized as a valid connection manager type.
> I have also tried creating a new Integrated Services Project and run the
> import from there. I get the same error. When I try to upsize in Access
> I
> get a report that says that the tables were not converted.
> Any suggestions?
MS Access - SQL Server Replication
Folks...
I have a client that is running MS Access 2002 for 10 people out in the field. The client wants to be able to replicate the data from MS Access 2002 to the SQL Server 2000. In addition, the client wants to have each field person only work on their own s
pecific data set during the replication. So the entire SQL Server database does not get replicated to the MS Access database. A good thing.
What would be a best practice approach to accomplish setting up the MS Access database and setting up the 'filters' only to pull the data that will be specifically worked on by the client.
I did some looking on the MS website and some other links...but of course the data is always fragemented.
Mephisto822
The easiest way of setting this up is using merge replication and dynamic
filters. For the dynamic filter there are options of HOST_NAME() and
SUSER_SNAME() functions and UDFs based on them, but I would suggest using
the -HOSTNAME agent parameter to easily partition the data according to
subscriber. This also gives you the option of using a business-related
partitioning value, rather than the computername. HTH,
Paul Ibison
I have a client that is running MS Access 2002 for 10 people out in the field. The client wants to be able to replicate the data from MS Access 2002 to the SQL Server 2000. In addition, the client wants to have each field person only work on their own s
pecific data set during the replication. So the entire SQL Server database does not get replicated to the MS Access database. A good thing.
What would be a best practice approach to accomplish setting up the MS Access database and setting up the 'filters' only to pull the data that will be specifically worked on by the client.
I did some looking on the MS website and some other links...but of course the data is always fragemented.
Mephisto822
The easiest way of setting this up is using merge replication and dynamic
filters. For the dynamic filter there are options of HOST_NAME() and
SUSER_SNAME() functions and UDFs based on them, but I would suggest using
the -HOSTNAME agent parameter to easily partition the data according to
subscriber. This also gives you the option of using a business-related
partitioning value, rather than the computername. HTH,
Paul Ibison
MS Access - Security Timestamp.
I need a simple way of capturing who updated what in an Access database.
User wants to know for each field in each table.
I think it's to much for Access to handle.
Any ideas?
A.
This is not an Access forum. Sorry, but you might find better luck on an newsgroup somewhere.However, you can set a default of "date()" in an Access Date/Time field. Just insert NULL into that field and you'll get the current date.
MS ACCESS - ON DELETE CASCADE Problem
I cannot execute my sql to create a table with ON DELETE CASCADE option.
Here is my sql:
Here is my sql:
CREATE TABLE Employees (Name Text(10) not null, Age number,
CONSTRAINT pkEmployees
PRIMARY KEY (Name)); <-- This is ok!
CREATE TABLE CanTake (Name Text(10) not null, Make Text(10) not null,
CONSTRAINT pkCanTake
PRIMARY KEY (Name, Make),
CONSTRAINT fkCanTake
FOREIGN KEY (Name) REFERENCES Employees
ON UPDATE CASCADE <-- MS-ACCESS says 'syntax error'
ON DELETE CASCADE
);
When I didn't include ON UPDATE CASCADE ON DELETE CASCADE in sql, it works
(but that's not what I want). Why? Could anyone suggest me?
Thank you.
--
Message posted via http://www.sqlmonster.comMSDN suggests that you need to use ALTER TABLE to add cascading DRI:
http://msdn.microsoft.com/library/d...ml/acintsql.asp
However, I know very little about Access, so I suggest you post in an
Access group to get a better response.
Simon|||Thank you.
--
Message posted via http://www.sqlmonster.com
Ms access
Basically I am a Oracle guy. Now I am developping some form in SQL server. I wana use SQL DB as back end and access as front end. I wanna all the tables in SQL server not as the Access DB. Is it possible.
I do not have knowledge of VB or VB script
What should I do? Please helpIn Access, select File, New, from the menu and choose to create a Project with either a new or existing database. A wizard will guide you through establishing a connetion with your SQL database, and you can even perform some administrative functions from Access ala Enterprise Manager. This means you will need to be carefull that you don't unintentionally alter your SQL database, and that you will need to lock down your final Access file so that users don't mess with your database.
The Access file will have a .adp extenstion (Access Data Project).|||[thanks mate it works|||No worries. G'day!
;)
I do not have knowledge of VB or VB script
What should I do? Please helpIn Access, select File, New, from the menu and choose to create a Project with either a new or existing database. A wizard will guide you through establishing a connetion with your SQL database, and you can even perform some administrative functions from Access ala Enterprise Manager. This means you will need to be carefull that you don't unintentionally alter your SQL database, and that you will need to lock down your final Access file so that users don't mess with your database.
The Access file will have a .adp extenstion (Access Data Project).|||[thanks mate it works|||No worries. G'day!
;)
MS Acces to SQL issues
Hello all - We have several small Access dBs that we want to move up to MS
SQL. We've had some success with the upsizing wizard but have issues. The
most pressing issue is that we can't add new records the the newly upsized
SQL dB. Its like the table or form becomes read-only. I understand that
manual data conversion is best but in the spirit of time, the upsizing wizard
sure is nice and our Access apps are very simple. Any thoughts or
suggestions? Thanks in advance.
Rob C, MCSE, CCA
Statesville, NC
Ensure you have primary keys defined on all tables you want to update.
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
|||Define "can't". Do you get an error message?
Jeff
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
|||I'm with Jeff. What do you mean "can't?" The upsizing wizard would not do
this sort of thing. Sounds more like a resource issue.
The main problem I have with the wizard is that it chooses nasty data types
that I wouldn't have if I'd done it manually. This and the transfer of
indexes, pks, and fk relationships is handled poorly by the Wizard.
Sincerely,
Anthony Thomas
"Rob C" wrote:
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues. The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
SQL. We've had some success with the upsizing wizard but have issues. The
most pressing issue is that we can't add new records the the newly upsized
SQL dB. Its like the table or form becomes read-only. I understand that
manual data conversion is best but in the spirit of time, the upsizing wizard
sure is nice and our Access apps are very simple. Any thoughts or
suggestions? Thanks in advance.
Rob C, MCSE, CCA
Statesville, NC
Ensure you have primary keys defined on all tables you want to update.
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
|||Define "can't". Do you get an error message?
Jeff
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
|||I'm with Jeff. What do you mean "can't?" The upsizing wizard would not do
this sort of thing. Sounds more like a resource issue.
The main problem I have with the wizard is that it chooses nasty data types
that I wouldn't have if I'd done it manually. This and the transfer of
indexes, pks, and fk relationships is handled poorly by the Wizard.
Sincerely,
Anthony Thomas
"Rob C" wrote:
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues. The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
MS Acces to SQL issues
Hello all - We have several small Access dBs that we want to move up to MS
SQL. We've had some success with the upsizing wizard but have issues. The
most pressing issue is that we can't add new records the the newly upsized
SQL dB. Its like the table or form becomes read-only. I understand that
manual data conversion is best but in the spirit of time, the upsizing wizard
sure is nice and our Access apps are very simple. Any thoughts or
suggestions? Thanks in advance.
--
Rob C, MCSE, CCA
Statesville, NCEnsure you have primary keys defined on all tables you want to update.
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||Define "can't". Do you get an error message?
Jeff
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||I'm with Jeff. What do you mean "can't?" The upsizing wizard would not do
this sort of thing. Sounds more like a resource issue.
The main problem I have with the wizard is that it chooses nasty data types
that I wouldn't have if I'd done it manually. This and the transfer of
indexes, pks, and fk relationships is handled poorly by the Wizard.
Sincerely,
Anthony Thomas
"Rob C" wrote:
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues. The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
SQL. We've had some success with the upsizing wizard but have issues. The
most pressing issue is that we can't add new records the the newly upsized
SQL dB. Its like the table or form becomes read-only. I understand that
manual data conversion is best but in the spirit of time, the upsizing wizard
sure is nice and our Access apps are very simple. Any thoughts or
suggestions? Thanks in advance.
--
Rob C, MCSE, CCA
Statesville, NCEnsure you have primary keys defined on all tables you want to update.
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||Define "can't". Do you get an error message?
Jeff
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||I'm with Jeff. What do you mean "can't?" The upsizing wizard would not do
this sort of thing. Sounds more like a resource issue.
The main problem I have with the wizard is that it chooses nasty data types
that I wouldn't have if I'd done it manually. This and the transfer of
indexes, pks, and fk relationships is handled poorly by the Wizard.
Sincerely,
Anthony Thomas
"Rob C" wrote:
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues. The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
MS Acces to SQL issues
Hello all - We have several small Access dBs that we want to move up to MS
SQL. We've had some success with the upsizing wizard but have issues. The
most pressing issue is that we can't add new records the the newly upsized
SQL dB. Its like the table or form becomes read-only. I understand that
manual data conversion is best but in the spirit of time, the upsizing wizar
d
sure is nice and our Access apps are very simple. Any thoughts or
suggestions? Thanks in advance.
--
Rob C, MCSE, CCA
Statesville, NCEnsure you have primary keys defined on all tables you want to update.
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||Define "can't". Do you get an error message?
Jeff
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||I'm with Jeff. What do you mean "can't?" The upsizing wizard would not do
this sort of thing. Sounds more like a resource issue.
The main problem I have with the wizard is that it chooses nasty data types
that I wouldn't have if I'd done it manually. This and the transfer of
indexes, pks, and fk relationships is handled poorly by the Wizard.
Sincerely,
Anthony Thomas
"Rob C" wrote:
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues. Th
e
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing wiz
ard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
SQL. We've had some success with the upsizing wizard but have issues. The
most pressing issue is that we can't add new records the the newly upsized
SQL dB. Its like the table or form becomes read-only. I understand that
manual data conversion is best but in the spirit of time, the upsizing wizar
d
sure is nice and our Access apps are very simple. Any thoughts or
suggestions? Thanks in advance.
--
Rob C, MCSE, CCA
Statesville, NCEnsure you have primary keys defined on all tables you want to update.
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||Define "can't". Do you get an error message?
Jeff
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:F4D0AF9E-5926-48FB-BFB4-C83345AA059F@.microsoft.com...
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues.
The
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing
wizard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC|||I'm with Jeff. What do you mean "can't?" The upsizing wizard would not do
this sort of thing. Sounds more like a resource issue.
The main problem I have with the wizard is that it chooses nasty data types
that I wouldn't have if I'd done it manually. This and the transfer of
indexes, pks, and fk relationships is handled poorly by the Wizard.
Sincerely,
Anthony Thomas
"Rob C" wrote:
> Hello all - We have several small Access dBs that we want to move up to MS
> SQL. We've had some success with the upsizing wizard but have issues. Th
e
> most pressing issue is that we can't add new records the the newly upsized
> SQL dB. Its like the table or form becomes read-only. I understand that
> manual data conversion is best but in the spirit of time, the upsizing wiz
ard
> sure is nice and our Access apps are very simple. Any thoughts or
> suggestions? Thanks in advance.
> --
> Rob C, MCSE, CCA
> Statesville, NC
Ms 2767withh DBCC show_statistics
Hello
One of my database is quite slow. The first action i did is check
statistics, using dbccshow_statistics.
But it seems statistics are not available (SQL server returns a 2767 msg,
saying it cannot find statistics)
Thus i used update statistics on one of the table. Query analysers says the
comand is complete. But reusing dbcc update statistics shows the same error
message (2767)
The server version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Is there any explanation ? Any ways to solve the problem ?
Thank you
XavierCan you show us:
result from
EXEC sp_helpindex 'tablename'
Your UPDATE STATISTICS command
Your DBCC SHOWSTATISTICS command
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> Hello
> One of my database is quite slow. The first action i did is check
> statistics, using dbccshow_statistics.
> But it seems statistics are not available (SQL server returns a 2767 msg,
> saying it cannot find statistics)
> Thus i used update statistics on one of the table. Query analysers says th
e
> comand is complete. But reusing dbcc update statistics shows the same erro
r
> message (2767)
> The server version is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windo
ws
> NT 5.2 (Build 3790: )
> Is there any explanation ? Any ways to solve the problem ?
> Thank you
> Xavier
>
>|||Thank you tibor for your quick answer.
I noticed yesterday, after sending my message, that the owner of the job
maintaining the databse was unkown in the security folder of EM. I switched
to 'sa' instead of this user (that was a Nt local account).
I let the job work last night, and now i have statistics on my table.
I suppose the owner of the job had insufficient rights in the first place,
but i cannot explain why using the sa account on a query analyser yesterday,
stats wouldn't be updated.
Here are the commande i used :
as you will see, the index on this table are, hm, usiong too much columns i
think (especially reagrding density of some columns). Any advice welcome by
the way :=) .
That is why i was using the dbcc show_statistics
exec sp_helpindex 'ValeursCAR'
IX_ValeursCAR nonclustered, unique located on PRIMARY Val_Montant, Val_Zon2,
Val_Zon3, Val_Zon4, Val_RefCheque
IX_ValeursCAR_1 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_10 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt,
Val_FlagReconc
IX_ValeursCar_11 nonclustered located on PRIMARY Val_Dt_Trt
IX_ValeursCAR_2 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_3 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_4 nonclustered located on PRIMARY Val_FlagReconc, Val_Hotesse,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_5 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_6 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_7 nonclustered located on PRIMARY Val_Dt_Reception,
Val_Dt_Vente, Val_Rmt_Numero, Val_Hotesse, Val_Zon8, Val_FlagReconc
IX_ValeursCAR_8 nonclustered located on PRIMARY Val_Dt_Reception,
Val_FlagReconc
IX_ValeursCAR_9 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt
PK_ValeursCAR clustered, unique, primary key located on PRIMARY Val_Identite
DBCC SHOW_statistics (ValeursCAR,IX_ValeursCAR_2)
Does return now correct values. so i cannot send the error message ...sorry
its number was 2767
"Tibor Karaszi" wrote:
> Can you show us:
> result from
> EXEC sp_helpindex 'tablename'
> Your UPDATE STATISTICS command
> Your DBCC SHOWSTATISTICS command
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in messa
ge
> news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
>sql
One of my database is quite slow. The first action i did is check
statistics, using dbccshow_statistics.
But it seems statistics are not available (SQL server returns a 2767 msg,
saying it cannot find statistics)
Thus i used update statistics on one of the table. Query analysers says the
comand is complete. But reusing dbcc update statistics shows the same error
message (2767)
The server version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Is there any explanation ? Any ways to solve the problem ?
Thank you
XavierCan you show us:
result from
EXEC sp_helpindex 'tablename'
Your UPDATE STATISTICS command
Your DBCC SHOWSTATISTICS command
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> Hello
> One of my database is quite slow. The first action i did is check
> statistics, using dbccshow_statistics.
> But it seems statistics are not available (SQL server returns a 2767 msg,
> saying it cannot find statistics)
> Thus i used update statistics on one of the table. Query analysers says th
e
> comand is complete. But reusing dbcc update statistics shows the same erro
r
> message (2767)
> The server version is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windo
ws
> NT 5.2 (Build 3790: )
> Is there any explanation ? Any ways to solve the problem ?
> Thank you
> Xavier
>
>|||Thank you tibor for your quick answer.
I noticed yesterday, after sending my message, that the owner of the job
maintaining the databse was unkown in the security folder of EM. I switched
to 'sa' instead of this user (that was a Nt local account).
I let the job work last night, and now i have statistics on my table.
I suppose the owner of the job had insufficient rights in the first place,
but i cannot explain why using the sa account on a query analyser yesterday,
stats wouldn't be updated.
Here are the commande i used :
as you will see, the index on this table are, hm, usiong too much columns i
think (especially reagrding density of some columns). Any advice welcome by
the way :=) .
That is why i was using the dbcc show_statistics
exec sp_helpindex 'ValeursCAR'
IX_ValeursCAR nonclustered, unique located on PRIMARY Val_Montant, Val_Zon2,
Val_Zon3, Val_Zon4, Val_RefCheque
IX_ValeursCAR_1 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_10 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt,
Val_FlagReconc
IX_ValeursCar_11 nonclustered located on PRIMARY Val_Dt_Trt
IX_ValeursCAR_2 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_3 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_4 nonclustered located on PRIMARY Val_FlagReconc, Val_Hotesse,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_5 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_6 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_7 nonclustered located on PRIMARY Val_Dt_Reception,
Val_Dt_Vente, Val_Rmt_Numero, Val_Hotesse, Val_Zon8, Val_FlagReconc
IX_ValeursCAR_8 nonclustered located on PRIMARY Val_Dt_Reception,
Val_FlagReconc
IX_ValeursCAR_9 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt
PK_ValeursCAR clustered, unique, primary key located on PRIMARY Val_Identite
DBCC SHOW_statistics (ValeursCAR,IX_ValeursCAR_2)
Does return now correct values. so i cannot send the error message ...sorry
its number was 2767
"Tibor Karaszi" wrote:
> Can you show us:
> result from
> EXEC sp_helpindex 'tablename'
> Your UPDATE STATISTICS command
> Your DBCC SHOWSTATISTICS command
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in messa
ge
> news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
>sql
Ms 2767withh DBCC show_statistics
Hello
One of my database is quite slow. The first action i did is check
statistics, using dbccshow_statistics.
But it seems statistics are not available (SQL server returns a 2767 msg,
saying it cannot find statistics)
Thus i used update statistics on one of the table. Query analysers says the
comand is complete. But reusing dbcc update statistics shows the same error
message (2767)
The server version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Is there any explanation ? Any ways to solve the problem ?
Thank you
Xavier
Can you show us:
result from
EXEC sp_helpindex 'tablename'
Your UPDATE STATISTICS command
Your DBCC SHOWSTATISTICS command
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> Hello
> One of my database is quite slow. The first action i did is check
> statistics, using dbccshow_statistics.
> But it seems statistics are not available (SQL server returns a 2767 msg,
> saying it cannot find statistics)
> Thus i used update statistics on one of the table. Query analysers says the
> comand is complete. But reusing dbcc update statistics shows the same error
> message (2767)
> The server version is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
> NT 5.2 (Build 3790: )
> Is there any explanation ? Any ways to solve the problem ?
> Thank you
> Xavier
>
>
|||Thank you tibor for your quick answer.
I noticed yesterday, after sending my message, that the owner of the job
maintaining the databse was unkown in the security folder of EM. I switched
to 'sa' instead of this user (that was a Nt local account).
I let the job work last night, and now i have statistics on my table.
I suppose the owner of the job had insufficient rights in the first place,
but i cannot explain why using the sa account on a query analyser yesterday,
stats wouldn't be updated.
Here are the commande i used :
as you will see, the index on this table are, hm, usiong too much columns i
think (especially reagrding density of some columns). Any advice welcome by
the way :=) .
That is why i was using the dbcc show_statistics
exec sp_helpindex 'ValeursCAR'
IX_ValeursCARnonclustered, unique located on PRIMARYVal_Montant, Val_Zon2,
Val_Zon3, Val_Zon4, Val_RefCheque
IX_ValeursCAR_1nonclustered located on PRIMARYVal_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_10nonclustered located on PRIMARYVal_Lot_Nulot, Val_Dt_Trt,
Val_FlagReconc
IX_ValeursCar_11nonclustered located on PRIMARYVal_Dt_Trt
IX_ValeursCAR_2nonclustered located on PRIMARYVal_FlagReconc,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_3nonclustered located on PRIMARYVal_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_4nonclustered located on PRIMARYVal_FlagReconc, Val_Hotesse,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_5nonclustered located on PRIMARYVal_FlagReconc,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_6nonclustered located on PRIMARYVal_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_7nonclustered located on PRIMARYVal_Dt_Reception,
Val_Dt_Vente, Val_Rmt_Numero, Val_Hotesse, Val_Zon8, Val_FlagReconc
IX_ValeursCAR_8nonclustered located on PRIMARYVal_Dt_Reception,
Val_FlagReconc
IX_ValeursCAR_9nonclustered located on PRIMARYVal_Lot_Nulot, Val_Dt_Trt
PK_ValeursCARclustered, unique, primary key located on PRIMARYVal_Identite
DBCC SHOW_statistics (ValeursCAR,IX_ValeursCAR_2)
Does return now correct values. so i cannot send the error message ...sorry
its number was 2767
"Tibor Karaszi" wrote:
> Can you show us:
> result from
> EXEC sp_helpindex 'tablename'
> Your UPDATE STATISTICS command
> Your DBCC SHOWSTATISTICS command
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
> news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
>
One of my database is quite slow. The first action i did is check
statistics, using dbccshow_statistics.
But it seems statistics are not available (SQL server returns a 2767 msg,
saying it cannot find statistics)
Thus i used update statistics on one of the table. Query analysers says the
comand is complete. But reusing dbcc update statistics shows the same error
message (2767)
The server version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Is there any explanation ? Any ways to solve the problem ?
Thank you
Xavier
Can you show us:
result from
EXEC sp_helpindex 'tablename'
Your UPDATE STATISTICS command
Your DBCC SHOWSTATISTICS command
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> Hello
> One of my database is quite slow. The first action i did is check
> statistics, using dbccshow_statistics.
> But it seems statistics are not available (SQL server returns a 2767 msg,
> saying it cannot find statistics)
> Thus i used update statistics on one of the table. Query analysers says the
> comand is complete. But reusing dbcc update statistics shows the same error
> message (2767)
> The server version is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
> NT 5.2 (Build 3790: )
> Is there any explanation ? Any ways to solve the problem ?
> Thank you
> Xavier
>
>
|||Thank you tibor for your quick answer.
I noticed yesterday, after sending my message, that the owner of the job
maintaining the databse was unkown in the security folder of EM. I switched
to 'sa' instead of this user (that was a Nt local account).
I let the job work last night, and now i have statistics on my table.
I suppose the owner of the job had insufficient rights in the first place,
but i cannot explain why using the sa account on a query analyser yesterday,
stats wouldn't be updated.
Here are the commande i used :
as you will see, the index on this table are, hm, usiong too much columns i
think (especially reagrding density of some columns). Any advice welcome by
the way :=) .
That is why i was using the dbcc show_statistics
exec sp_helpindex 'ValeursCAR'
IX_ValeursCARnonclustered, unique located on PRIMARYVal_Montant, Val_Zon2,
Val_Zon3, Val_Zon4, Val_RefCheque
IX_ValeursCAR_1nonclustered located on PRIMARYVal_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_10nonclustered located on PRIMARYVal_Lot_Nulot, Val_Dt_Trt,
Val_FlagReconc
IX_ValeursCar_11nonclustered located on PRIMARYVal_Dt_Trt
IX_ValeursCAR_2nonclustered located on PRIMARYVal_FlagReconc,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_3nonclustered located on PRIMARYVal_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_4nonclustered located on PRIMARYVal_FlagReconc, Val_Hotesse,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_5nonclustered located on PRIMARYVal_FlagReconc,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_6nonclustered located on PRIMARYVal_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_7nonclustered located on PRIMARYVal_Dt_Reception,
Val_Dt_Vente, Val_Rmt_Numero, Val_Hotesse, Val_Zon8, Val_FlagReconc
IX_ValeursCAR_8nonclustered located on PRIMARYVal_Dt_Reception,
Val_FlagReconc
IX_ValeursCAR_9nonclustered located on PRIMARYVal_Lot_Nulot, Val_Dt_Trt
PK_ValeursCARclustered, unique, primary key located on PRIMARYVal_Identite
DBCC SHOW_statistics (ValeursCAR,IX_ValeursCAR_2)
Does return now correct values. so i cannot send the error message ...sorry
its number was 2767
"Tibor Karaszi" wrote:
> Can you show us:
> result from
> EXEC sp_helpindex 'tablename'
> Your UPDATE STATISTICS command
> Your DBCC SHOWSTATISTICS command
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
> news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
>
Ms 2767withh DBCC show_statistics
Hello
One of my database is quite slow. The first action i did is check
statistics, using dbccshow_statistics.
But it seems statistics are not available (SQL server returns a 2767 msg,
saying it cannot find statistics)
Thus i used update statistics on one of the table. Query analysers says the
comand is complete. But reusing dbcc update statistics shows the same error
message (2767)
The server version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Is there any explanation ? Any ways to solve the problem ?
Thank you
XavierCan you show us:
result from
EXEC sp_helpindex 'tablename'
Your UPDATE STATISTICS command
Your DBCC SHOWSTATISTICS command
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> Hello
> One of my database is quite slow. The first action i did is check
> statistics, using dbccshow_statistics.
> But it seems statistics are not available (SQL server returns a 2767 msg,
> saying it cannot find statistics)
> Thus i used update statistics on one of the table. Query analysers says the
> comand is complete. But reusing dbcc update statistics shows the same error
> message (2767)
> The server version is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
> NT 5.2 (Build 3790: )
> Is there any explanation ? Any ways to solve the problem ?
> Thank you
> Xavier
>
>|||Thank you tibor for your quick answer.
I noticed yesterday, after sending my message, that the owner of the job
maintaining the databse was unkown in the security folder of EM. I switched
to 'sa' instead of this user (that was a Nt local account).
I let the job work last night, and now i have statistics on my table.
I suppose the owner of the job had insufficient rights in the first place,
but i cannot explain why using the sa account on a query analyser yesterday,
stats wouldn't be updated.
Here are the commande i used :
as you will see, the index on this table are, hm, usiong too much columns i
think (especially reagrding density of some columns). Any advice welcome by
the way :=) .
That is why i was using the dbcc show_statistics
exec sp_helpindex 'ValeursCAR'
IX_ValeursCAR nonclustered, unique located on PRIMARY Val_Montant, Val_Zon2,
Val_Zon3, Val_Zon4, Val_RefCheque
IX_ValeursCAR_1 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_10 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt,
Val_FlagReconc
IX_ValeursCar_11 nonclustered located on PRIMARY Val_Dt_Trt
IX_ValeursCAR_2 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_3 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_4 nonclustered located on PRIMARY Val_FlagReconc, Val_Hotesse,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_5 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_6 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_7 nonclustered located on PRIMARY Val_Dt_Reception,
Val_Dt_Vente, Val_Rmt_Numero, Val_Hotesse, Val_Zon8, Val_FlagReconc
IX_ValeursCAR_8 nonclustered located on PRIMARY Val_Dt_Reception,
Val_FlagReconc
IX_ValeursCAR_9 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt
PK_ValeursCAR clustered, unique, primary key located on PRIMARY Val_Identite
DBCC SHOW_statistics (ValeursCAR,IX_ValeursCAR_2)
Does return now correct values. so i cannot send the error message ...sorry
its number was 2767
"Tibor Karaszi" wrote:
> Can you show us:
> result from
> EXEC sp_helpindex 'tablename'
> Your UPDATE STATISTICS command
> Your DBCC SHOWSTATISTICS command
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
> news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> > Hello
> >
> > One of my database is quite slow. The first action i did is check
> > statistics, using dbccshow_statistics.
> >
> > But it seems statistics are not available (SQL server returns a 2767 msg,
> > saying it cannot find statistics)
> > Thus i used update statistics on one of the table. Query analysers says the
> > comand is complete. But reusing dbcc update statistics shows the same error
> > message (2767)
> >
> > The server version is
> > Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> > Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
> > NT 5.2 (Build 3790: )
> >
> > Is there any explanation ? Any ways to solve the problem ?
> >
> > Thank you
> >
> > Xavier
> >
> >
> >
> >
>
One of my database is quite slow. The first action i did is check
statistics, using dbccshow_statistics.
But it seems statistics are not available (SQL server returns a 2767 msg,
saying it cannot find statistics)
Thus i used update statistics on one of the table. Query analysers says the
comand is complete. But reusing dbcc update statistics shows the same error
message (2767)
The server version is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Is there any explanation ? Any ways to solve the problem ?
Thank you
XavierCan you show us:
result from
EXEC sp_helpindex 'tablename'
Your UPDATE STATISTICS command
Your DBCC SHOWSTATISTICS command
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> Hello
> One of my database is quite slow. The first action i did is check
> statistics, using dbccshow_statistics.
> But it seems statistics are not available (SQL server returns a 2767 msg,
> saying it cannot find statistics)
> Thus i used update statistics on one of the table. Query analysers says the
> comand is complete. But reusing dbcc update statistics shows the same error
> message (2767)
> The server version is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
> NT 5.2 (Build 3790: )
> Is there any explanation ? Any ways to solve the problem ?
> Thank you
> Xavier
>
>|||Thank you tibor for your quick answer.
I noticed yesterday, after sending my message, that the owner of the job
maintaining the databse was unkown in the security folder of EM. I switched
to 'sa' instead of this user (that was a Nt local account).
I let the job work last night, and now i have statistics on my table.
I suppose the owner of the job had insufficient rights in the first place,
but i cannot explain why using the sa account on a query analyser yesterday,
stats wouldn't be updated.
Here are the commande i used :
as you will see, the index on this table are, hm, usiong too much columns i
think (especially reagrding density of some columns). Any advice welcome by
the way :=) .
That is why i was using the dbcc show_statistics
exec sp_helpindex 'ValeursCAR'
IX_ValeursCAR nonclustered, unique located on PRIMARY Val_Montant, Val_Zon2,
Val_Zon3, Val_Zon4, Val_RefCheque
IX_ValeursCAR_1 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_10 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt,
Val_FlagReconc
IX_ValeursCar_11 nonclustered located on PRIMARY Val_Dt_Trt
IX_ValeursCAR_2 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_3 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_4 nonclustered located on PRIMARY Val_FlagReconc, Val_Hotesse,
Val_Dt_Vente, Val_Rmt_Numero
IX_ValeursCAR_5 nonclustered located on PRIMARY Val_FlagReconc,
Val_Dt_Reception, Val_Hotesse, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_6 nonclustered located on PRIMARY Val_FlagReconc, Val_Zon8,
Val_Dt_Reception, Val_Dt_VenteLot, Val_Rmt_Numero
IX_ValeursCAR_7 nonclustered located on PRIMARY Val_Dt_Reception,
Val_Dt_Vente, Val_Rmt_Numero, Val_Hotesse, Val_Zon8, Val_FlagReconc
IX_ValeursCAR_8 nonclustered located on PRIMARY Val_Dt_Reception,
Val_FlagReconc
IX_ValeursCAR_9 nonclustered located on PRIMARY Val_Lot_Nulot, Val_Dt_Trt
PK_ValeursCAR clustered, unique, primary key located on PRIMARY Val_Identite
DBCC SHOW_statistics (ValeursCAR,IX_ValeursCAR_2)
Does return now correct values. so i cannot send the error message ...sorry
its number was 2767
"Tibor Karaszi" wrote:
> Can you show us:
> result from
> EXEC sp_helpindex 'tablename'
> Your UPDATE STATISTICS command
> Your DBCC SHOWSTATISTICS command
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
> news:F82C47D3-42EF-421A-A5A3-979CA887FFCD@.microsoft.com...
> > Hello
> >
> > One of my database is quite slow. The first action i did is check
> > statistics, using dbccshow_statistics.
> >
> > But it seems statistics are not available (SQL server returns a 2767 msg,
> > saying it cannot find statistics)
> > Thus i used update statistics on one of the table. Query analysers says the
> > comand is complete. But reusing dbcc update statistics shows the same error
> > message (2767)
> >
> > The server version is
> > Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> > Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
> > NT 5.2 (Build 3790: )
> >
> > Is there any explanation ? Any ways to solve the problem ?
> >
> > Thank you
> >
> > Xavier
> >
> >
> >
> >
>
MS 2005 Reporting Services Matrix - % Change Issue ....Help!
Hello Everyone,
I have been tasked with creating a report using Reporting Services from an excel report that was wasting a lot of manual effort and time. I have searched boards and I am having the hardest time trying to find a way to do the following in Reporting Services within a Matrix:
Spring Summer Fall
2005 2006 2007 %Change 2005 2006 2007 % Change 2005 2006 2007 %Change
Reponses 219 310 410 100 100 200 300 100 2000 3000 200 -2800
FRC% 102% 103% 200% 97% 23% 25% 10% -15% 20% 32% 10% -22%
===========================================================================================
I can not find out how to do a percent change (the column in red) subtracting just the final or most right two columns of the resulting matrix columns, not an average or some other function over the entire row....
Please help, I know others have had the same issue...
Thank You,
I would think you would just need to incorporate this into your select statement. Select the difference between the two most recent years as percentChange. Then in your report, add a column with the percentChange field as the data value.|||Thank You for your reply...I will try that and post back...|||
Yep that was the answer...
Thanks...
MS - How About Some Answers?
Hey MS,
I realize that this a free forum, but given the number of people who have problems with SSRS configuration and the fact that there are virtually no solutions posted on MSDN, I would think that MS would be more responsive to these forum posts. The most recent post from someone who can't get SSRS working had 1,154 views, but no answers--similar to the other posts by those who can't get the product to work. There seem to be responses to the "easy" questions, like features and techniques.
Unlike the SQK2K version, this version has a GUI-based config tool. That should guarantee proper configuration, but it doesn't. The online knowledge base is not yet adequate to support installation and configuration. So I think that MS should be more responsive to post requesting configuration help.
Hi rlo,
Good post. I like your comments. RS configuration is a sore spot for both MS and for our customers. We have made and are continuing to make configuration better. The Configuration Tool in RS 2005 is a start, but not the panacea for configuration problems. Each version, we will definately work to make configuration easier. And sadly, there is no one solution to configuration problems.
In regards to getting answers for configuration problems via helper forums:
1. Clarify the problem in excrutiating detail. Little detail and most MS employees will move to the next post to help out.
2. Be polite. We have feelings too.
3. Bump the post if you do not get an answer within the first page.
The Reporting Services team takes great pride in helping customers. But do remember there are only a couple handfulls of us, while there are many thousands of you. Your help in responding to customers is as important as ours.
MRe: Cannot start SQLEXPRESS
I have just had to reinstall all my SQLServer instances (SQL2005 Standard, BCM2007, and SQLEXPRESS).
The first two have started fine, but I cannot get the SQLExpress instance to start. When I start in the Management Studio, the error I get is:-
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Loating Server/Instance Specified) (Microsoft SQL Server)."
Can anyone tell me what I have to do to correct this problem:?
I have looked in the Add or Remove Programs window and cannot see ANY instance relating to SQL Server Express 2005.
Hi,
First, if you have installed SQLExpress on your local machine then it should allow you to connect. If not try to configure it to allow remote connections. Following links will help you to do so.
And last, if you want to know about SQLEXpress instance, go to services in management console and there you can find if SQLExpress service is running or not and at the same location you can find the SQLExpress instance information.
Links are:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1712017&SiteID=1
http://techno-geeta.blogspot.com/2007/04/sql-server-2005-remote-connections.html
Mayur Tendulkar
Microsoft Student Partner
Asst. Manager: Pune User Group For Students
|||Unfortunately, none of these suggestions can be implemented. SQLEXPRESS does NOT appear in either the Surface Area Configuration menu, nor in the Configuration Manager module in MMC.
It does appear in the services tab, but when I attempt to start it, I get the response:-
"Could not start the SQL Server (SQLEXPRESS) service on local computer. Error 3: the system cannot find the path specified."
How do I reconfigure this to allow it to work?
|||Hi.
It seems that your SQLExpress installtion has problems. Please try to reinstall it. It may solve the problem.
Mayur Tendulkar
Microsoft Student Partner
Asst. Manager: Pune User Group For Students
|||Mayur
Thanks for that information.
I tried the installation again, and all tasks completed successfully other than the last one "SQL Server Database Services" where it reported an error:-
"The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "Howto view SQL Server setup log files" and "Starting SQL Server manually"."
This repeats when I retry. When I cancel, the install process finishes, but the SQLExpress instance does not appear.
This install generated an error report that is apparently sent to Microsoft. However, this does NOT help me with getting SQLExpress to actually WORK.
How do I rectify this issue? I need to have this instance running so that I can support my clients with another package that depends upon this working.
I am including the last part of the summary installation log which may help with your examination:-
Failed to install package
Fatal error during installation.
Setting package return code to: 29503
Complete: InstallSqlAction at: 2007/5/17 14:45:10, returned false
Error: Action "InstallSqlAction" failed during execution. Error information reported during run:
Target collection includes the local machine.
Invoking installPackage() on local machine.
Skipped: InstallToolsAction.10
Skipped: Action "InstallToolsAction.10" was not run. Information reported during analysis:
No install request found for package: "sqlxml4", referred by package: "tools", install will be skipped as a result.
Skipped: InstallASAction
Skipped: Action "InstallASAction" was not run. Information reported during analysis:
No install request found for package: "as", install will be skipped as a result.
Skipped: InstallRSAction
Skipped: Action "InstallRSAction" was not run. Information reported during analysis:
No install request found for package: "rs", install will be skipped as a result.
Skipped: InstallToolsAction
Skipped: Action "InstallToolsAction" was not run. Information reported during analysis:
No install request found for package: "tools", install will be skipped as a result.
Skipped: RepairForBackwardsCompatRedistAction
Skipped: Action "RepairForBackwardsCompatRedistAction" was not run. Information reported during analysis:
Action: "RepairForBackwardsCompatRedistAction" will be skipped due to the following condition:
Condition "sql was successfully upgraded." returned false. Condition context:
sql failed to upgrade and so the uninstall of the upgraded product will not occur.
Error: Action "UninstallForMSDE2000Action" failed during execution. Error information reported during run:
Action: "UninstallForMSDE2000Action" will be marked as failed due to the following condition:
Condition "sql was successfully upgraded." returned false. Condition context:
sql failed to upgrade and so the uninstall of the upgraded product will not occur.
Installation of package: "patchMSDE2000" failed due to a precondition.
Error: Action "UninstallForSQLAction" failed during execution. Error information reported during run:
Action: "UninstallForSQLAction" will be marked as failed due to the following condition:
Condition "sql was successfully upgraded." returned false. Condition context:
sql failed to upgrade and so the uninstall of the upgraded product will not occur.
Installation of package: "patchLibertySql" failed due to a precondition.
Skipped: UninstallForRS2000Action
Skipped: Action "UninstallForRS2000Action" was not run. Information reported during analysis:
Action: "UninstallForRS2000Action" will be skipped due to the following condition:
Condition "Action: InstallRSAction was skipped." returned true.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/5/17 14:45:10
Error: Action "ReportChainingResults" threw an exception during execution.
One or more packages failed to install. Refer to logs for error details. : 3
Error Code: 0x80070003 (3)
Windows Error Text: The system cannot find the path specified.
Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults:
erform
Source Line Number: 3521
- Context --
sqls::HostSetupPackageInstallerSynch:ostCommit
sqls::HighlyAvailablePackage:reInstall
led due to cancel code received from cancel source: 29539
Hi,
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=175249&SiteID=17
I'm bit confused too but this may solve your problem.
Mayur Tendulkar
Microsoft Student Partner
Asst. Manager: Pune User Group For Students
|||Mayur
This may have some bearing on my problem. Yesterday, because I was having problems updating SQLExpress with SP2, I decided to uninstall SQL Server entirely. However, I did NOT remove the various MSSQL* sub-directories. Could it be that SQL Express is getting confused with which of these it should instantiate? The sub-directories I have now are:-
MSSQL - containing what looks like my SQL Express databases - this was previously a MSDE2000 instance.
MSSQL.1 - containing what looks to be the SQL Standard edition databases. This has an OLAP sub-directory.
MSSQL.2 - does not contain any databases. This instance does have Reporting Services and OLAP sub-directories.
MSSQL.3 - also does not contain any databases.
MSSQL.4 - seems to be only Reporting Services.
MSSQL.5 - has some empty default databases.
Do you think that I should re-install SQL Server SE and SQL Server Express again, having removed the entire directory structure again? (clearly after backing up the databases I need - which I did do yesterday, of course).
|||Hi,
I too think that, it would be a great solution. Also remove all SQL Client tools, SQL-XML and other tools that gets installed with SQL. There is no specific order in which you can install SQL Server's diff editions. However, I would recommend from older to newer and Express edition first before SQL SE 2005.
I guess, this will solve your problem. 
Mayur Tendulkar
Microsoft Student Partner
Asst. Manager: Pune User Group For Students
sqlMr,Teo Lachev - Want your help
i got your code i think its in visual studio 2005, i am
using 2003. so can u help me further,
u were talking about customize dataset extension but i cant get how to use
this so plz plz help me its so urgentYou may find this sample useful.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:2A2A6B0B-E636-4F2A-98B6-B6FE33F6F8A5@.microsoft.com...
>i got your code i think its in visual studio 2005, i am
> using 2003. so can u help me further,
> u were talking about customize dataset extension but i cant get how to use
> this so plz plz help me its so urgent
>|||thx Sir,
i have read it. but here we are using only one xml file to read. but
as you know i am developing web application there may be several hits to
that, then how can i manage it through using one file name,|||Can you describe your requirements in more details? I am at lost about what
exactly you are trying to accomplish?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:417DA551-51C9-45A7-8EA7-FE1AA5D01A42@.microsoft.com...
> thx Sir,
> i have read it. but here we are using only one xml file to read.
> but
> as you know i am developing web application there may be several hits to
> that, then how can i manage it through using one file name,
>|||Thx Teo for replying,
i am making report for Tardus , a financial company. as per their
requirement they want some calculators with reports.
ex. in retirement report i have to show how long user's fund will
last. i have to show each year's amount. currently i using session id and do
the entry in database and for displaying report i pass session id as
parameter.
i fill this way very tedious. can you say how can i assign customize
ado.net dataset to report as we can do in crystal report
"Teo Lachev [MVP]" wrote:
> Can you describe your requirements in more details? I am at lost about what
> exactly you are trying to accomplish?
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
> news:417DA551-51C9-45A7-8EA7-FE1AA5D01A42@.microsoft.com...
> > thx Sir,
> >
> > i have read it. but here we are using only one xml file to read.
> > but
> > as you know i am developing web application there may be several hits to
> > that, then how can i manage it through using one file name,
> >
>
>|||NP. I've already did by pointing you out to a custom dataset extension that
gets the serialized dataset from a report parameter and deserialize it back
to an ADO.NET dataset. Can you explain why it doesn't meet your
requirements?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:805B028A-46F0-48FC-953E-0AFB517DE5A0@.microsoft.com...
> Thx Teo for replying,
> i am making report for Tardus , a financial company. as per their
> requirement they want some calculators with reports.
> ex. in retirement report i have to show how long user's fund will
> last. i have to show each year's amount. currently i using session id and
> do
> the entry in database and for displaying report i pass session id as
> parameter.
> i fill this way very tedious. can you say how can i assign customize
> ado.net dataset to report as we can do in crystal report
> "Teo Lachev [MVP]" wrote:
>> Can you describe your requirements in more details? I am at lost about
>> what
>> exactly you are trying to accomplish?
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
>> news:417DA551-51C9-45A7-8EA7-FE1AA5D01A42@.microsoft.com...
>> > thx Sir,
>> >
>> > i have read it. but here we are using only one xml file to read.
>> > but
>> > as you know i am developing web application there may be several hits
>> > to
>> > that, then how can i manage it through using one file name,
>> >
>>|||Thx Teo,
the code was awesome but i have a little problem.
we are passing xml file path as a parameter , the problem is the
site may have several hits at a time then i have to make a lots of xml file
and again i have to find a way to delete it so how can i come out this|||How are you getting the application dataset? And more importantly, why don't
you pass the serialized-to-XML datasets instead of the file path?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:AC3AB17D-31E8-4854-AE76-CD3C9EE0D010@.microsoft.com...
> Thx Teo,
> the code was awesome but i have a little problem.
> we are passing xml file path as a parameter , the problem is the
> site may have several hits at a time then i have to make a lots of xml
> file
> and again i have to find a way to delete it so how can i come out this
>|||Thx Teo,
actually i get the record of user that was entered by him,
ex. for retirement fund he has entered his initial balance, rate of interest
etc.
then i generate data for age, his balance, interest he gets etc each year .
i am displaying report in ascx control. i disable parameter field and
toolbar of report.
so|||This looks like a perfect scenario for using the Visual Studio 2005 ASP.NET
Report Viewer control in a disconnected mode because it will save you the
effort for creating and debugging a custom dataset extension. But you are
using VS.NET 2003 :-) This is what I would do in your case:
1. Use my custom dataset extension.
2. Generate an ADO.NET dataset
3. Serialize the dataset to XML and pass it as a hidden parameter in my
report via SOAP (Render SOAP API).
So, no XML files that linger around.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:A47889CE-A31F-4E88-8E62-80F5257B887D@.microsoft.com...
> Thx Teo,
> actually i get the record of user that was entered by him,
> ex. for retirement fund he has entered his initial balance, rate of
> interest
> etc.
> then i generate data for age, his balance, interest he gets etc each year
> .
> i am displaying report in ascx control. i disable parameter field and
> toolbar of report.
> so
>|||Thx Teo,
As i am busy with some documentation, i will do your suggestion
tomorrow, as soon i get it done or get some problem i will mail in
discussion, thx for your co- operation.
using 2003. so can u help me further,
u were talking about customize dataset extension but i cant get how to use
this so plz plz help me its so urgentYou may find this sample useful.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:2A2A6B0B-E636-4F2A-98B6-B6FE33F6F8A5@.microsoft.com...
>i got your code i think its in visual studio 2005, i am
> using 2003. so can u help me further,
> u were talking about customize dataset extension but i cant get how to use
> this so plz plz help me its so urgent
>|||thx Sir,
i have read it. but here we are using only one xml file to read. but
as you know i am developing web application there may be several hits to
that, then how can i manage it through using one file name,|||Can you describe your requirements in more details? I am at lost about what
exactly you are trying to accomplish?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:417DA551-51C9-45A7-8EA7-FE1AA5D01A42@.microsoft.com...
> thx Sir,
> i have read it. but here we are using only one xml file to read.
> but
> as you know i am developing web application there may be several hits to
> that, then how can i manage it through using one file name,
>|||Thx Teo for replying,
i am making report for Tardus , a financial company. as per their
requirement they want some calculators with reports.
ex. in retirement report i have to show how long user's fund will
last. i have to show each year's amount. currently i using session id and do
the entry in database and for displaying report i pass session id as
parameter.
i fill this way very tedious. can you say how can i assign customize
ado.net dataset to report as we can do in crystal report
"Teo Lachev [MVP]" wrote:
> Can you describe your requirements in more details? I am at lost about what
> exactly you are trying to accomplish?
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
> news:417DA551-51C9-45A7-8EA7-FE1AA5D01A42@.microsoft.com...
> > thx Sir,
> >
> > i have read it. but here we are using only one xml file to read.
> > but
> > as you know i am developing web application there may be several hits to
> > that, then how can i manage it through using one file name,
> >
>
>|||NP. I've already did by pointing you out to a custom dataset extension that
gets the serialized dataset from a report parameter and deserialize it back
to an ADO.NET dataset. Can you explain why it doesn't meet your
requirements?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:805B028A-46F0-48FC-953E-0AFB517DE5A0@.microsoft.com...
> Thx Teo for replying,
> i am making report for Tardus , a financial company. as per their
> requirement they want some calculators with reports.
> ex. in retirement report i have to show how long user's fund will
> last. i have to show each year's amount. currently i using session id and
> do
> the entry in database and for displaying report i pass session id as
> parameter.
> i fill this way very tedious. can you say how can i assign customize
> ado.net dataset to report as we can do in crystal report
> "Teo Lachev [MVP]" wrote:
>> Can you describe your requirements in more details? I am at lost about
>> what
>> exactly you are trying to accomplish?
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
>> news:417DA551-51C9-45A7-8EA7-FE1AA5D01A42@.microsoft.com...
>> > thx Sir,
>> >
>> > i have read it. but here we are using only one xml file to read.
>> > but
>> > as you know i am developing web application there may be several hits
>> > to
>> > that, then how can i manage it through using one file name,
>> >
>>|||Thx Teo,
the code was awesome but i have a little problem.
we are passing xml file path as a parameter , the problem is the
site may have several hits at a time then i have to make a lots of xml file
and again i have to find a way to delete it so how can i come out this|||How are you getting the application dataset? And more importantly, why don't
you pass the serialized-to-XML datasets instead of the file path?
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:AC3AB17D-31E8-4854-AE76-CD3C9EE0D010@.microsoft.com...
> Thx Teo,
> the code was awesome but i have a little problem.
> we are passing xml file path as a parameter , the problem is the
> site may have several hits at a time then i have to make a lots of xml
> file
> and again i have to find a way to delete it so how can i come out this
>|||Thx Teo,
actually i get the record of user that was entered by him,
ex. for retirement fund he has entered his initial balance, rate of interest
etc.
then i generate data for age, his balance, interest he gets etc each year .
i am displaying report in ascx control. i disable parameter field and
toolbar of report.
so|||This looks like a perfect scenario for using the Visual Studio 2005 ASP.NET
Report Viewer control in a disconnected mode because it will save you the
effort for creating and debugging a custom dataset extension. But you are
using VS.NET 2003 :-) This is what I would do in your case:
1. Use my custom dataset extension.
2. Generate an ADO.NET dataset
3. Serialize the dataset to XML and pass it as a hidden parameter in my
report via SOAP (Render SOAP API).
So, no XML files that linger around.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:A47889CE-A31F-4E88-8E62-80F5257B887D@.microsoft.com...
> Thx Teo,
> actually i get the record of user that was entered by him,
> ex. for retirement fund he has entered his initial balance, rate of
> interest
> etc.
> then i generate data for age, his balance, interest he gets etc each year
> .
> i am displaying report in ascx control. i disable parameter field and
> toolbar of report.
> so
>|||Thx Teo,
As i am busy with some documentation, i will do your suggestion
tomorrow, as soon i get it done or get some problem i will mail in
discussion, thx for your co- operation.
MQ Series Broker Service vs SQL Server Trigger
Hello
A rather frustrating trigger problem.
When I insert a record manually or even with an insert command, my trigger works fine and executes a job.
The trigger is supposed to work after a MQ Series broker service has added a row to the table but nothing happens.
Does this sound familiar?
The trigger:
CREATE TRIGGER CHECKFORNEWFILES
ON dbo.MQLog
AFTER INSERT
AS
SELECT 1
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @.Job_Name AS VARCHAR(50)
DECLARE @.Division AS VARCHAR(50)
DECLARE @.filename AS VARCHAR(50)
-- The bitmask is: power(2,(4-1)) = 8 => ((COLUMNS_UPDATED( )& 8 )> 0)
IF (COLUMNS_UPDATED() & 2 > 0)
BEGIN
UPDATE MQLOG
SET READY = GETDATE() WHERE Updated IS NULL
SELECT @.Division = CASE RTRIM(LTRIM(UPPER(LIBRARY)))
WHEN 'AEB' THEN 'SSS'
WHEN 'AIB' THEN 'ZZZ'
ELSE 'UNKNOWN'
END, @.filename = filename
FROM INSERTED
SELECT @.Job_Name= @.Division + '_' + @.filename
FROM INSERTED
-- Let's execute the job according to the file name which is ready for download.
EXEC msdb..sp_start_job_mq @.Job_Name
UPDATE MQLOG
SET PROCESSED = GETDATE(), UPDATED = 1 WHERE UPDATED IS NULL
END
Many thanks!!
Worf
Changing the AFTER INSERT into FOR INSERT did the trick. Whilst looking in SQL Profiler, we saw an exec sp_execute but not quite an insert, that's why an AFTER INSERT did not work.
regards,
Steve
MQ Series Broker Service vs SQL Server Trigger
Hello
A rather frustrating trigger problem.
When I insert a record manually or even with an insert command, my trigger works fine and executes a job.
The trigger is supposed to work after a MQ Series broker service has added a row to the table but nothing happens.
Does this sound familiar?
The trigger:
CREATE TRIGGER CHECKFORNEWFILES
ON dbo.MQLog
AFTER INSERT
AS
SELECT 1
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @.Job_Name AS VARCHAR(50)
DECLARE @.Division AS VARCHAR(50)
DECLARE @.filename AS VARCHAR(50)
-- The bitmask is: power(2,(4-1)) = 8 => ((COLUMNS_UPDATED( )& 8 )> 0)
IF (COLUMNS_UPDATED() & 2 > 0)
BEGIN
UPDATE MQLOG
SET READY = GETDATE() WHERE Updated IS NULL
SELECT @.Division = CASE RTRIM(LTRIM(UPPER(LIBRARY)))
WHEN 'AEB' THEN 'SSS'
WHEN 'AIB' THEN 'ZZZ'
ELSE 'UNKNOWN'
END, @.filename = filename
FROM INSERTED
SELECT @.Job_Name= @.Division + '_' + @.filename
FROM INSERTED
-- Let's execute the job according to the file name which is ready for download.
EXEC msdb..sp_start_job_mq @.Job_Name
UPDATE MQLOG
SET PROCESSED = GETDATE(), UPDATED = 1 WHERE UPDATED IS NULL
END
Many thanks!!
Worf
Changing the AFTER INSERT into FOR INSERT did the trick. Whilst looking in SQL Profiler, we saw an exec sp_execute but not quite an insert, that's why an AFTER INSERT did not work.
regards,
Steve
MPG calculation in CR 9, simple? not for me!
I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:
Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220
I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:
Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220
I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.
Format the Gals field. At the suppress formula: isnull (previous({galsfield}))
new formula, @.MPG: ({milesfield}-previous({milesfield}))/{galsfield)
new summary, grand total average @.MPG|||Jeffro308,
Thanks for responding to my post!
I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||I'm using 8.5 so there might be a difference. I need to try a couple things at work monday.
Jeffro308,
Thanks for responding to my post!
I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||bump|||Anybody else want to take a stab at this?|||I'll assume you're grouping by vehicle and ordering by odo, and that your 3 rows of sample data are for one vehicle.
I guess you want, per vehicle, the number of gals to be the sum of all records except the first, and the number of miles to be the last record minus the first.
So you could put a formula in the group header to subtract the gals and store the first odo, a formula in the details to add the gals, and a formula in the footer to subtract the stored first odo from the current odo.
e.g.
group header:
whileprintingrecords;
numbervar total_gals:= total_gals - {table.gals};
numbervar vehicle_gals := -{table.gals}; --or maybe {table.gals} * -1
numbervar first_vehicle_odo := {table.odo};
details:
whileprintingrecords;
numbervar total_gals := total_gals + {table.gals};
numbervar vehicle_gals := vehicle_gals + {table.gals};
group footer:
whileprintingrecords;
numbervar first_vehicle_odo;
numbervar vehicle_miles := {table.odo} - first_vehicle_odo;
numbervar total_miles := total_miles + vehicle_miles;
--and display miles/gallon for the vehicle
vehicle_miles / vehicle_gals --You should add a divide by zero check here for when there's only one record
report footer:
whileprintingrecords;
numbervar total_gals;
numbervar total_miles;
--and display miles/gallon for the report
total_miles / total_gals --divide by zero check again
By the way, I've not tried this explicitely but it might give you a start / ideas.sql
Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220
I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:
Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220
I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.
Format the Gals field. At the suppress formula: isnull (previous({galsfield}))
new formula, @.MPG: ({milesfield}-previous({milesfield}))/{galsfield)
new summary, grand total average @.MPG|||Jeffro308,
Thanks for responding to my post!
I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||I'm using 8.5 so there might be a difference. I need to try a couple things at work monday.
Jeffro308,
Thanks for responding to my post!
I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||bump|||Anybody else want to take a stab at this?|||I'll assume you're grouping by vehicle and ordering by odo, and that your 3 rows of sample data are for one vehicle.
I guess you want, per vehicle, the number of gals to be the sum of all records except the first, and the number of miles to be the last record minus the first.
So you could put a formula in the group header to subtract the gals and store the first odo, a formula in the details to add the gals, and a formula in the footer to subtract the stored first odo from the current odo.
e.g.
group header:
whileprintingrecords;
numbervar total_gals:= total_gals - {table.gals};
numbervar vehicle_gals := -{table.gals}; --or maybe {table.gals} * -1
numbervar first_vehicle_odo := {table.odo};
details:
whileprintingrecords;
numbervar total_gals := total_gals + {table.gals};
numbervar vehicle_gals := vehicle_gals + {table.gals};
group footer:
whileprintingrecords;
numbervar first_vehicle_odo;
numbervar vehicle_miles := {table.odo} - first_vehicle_odo;
numbervar total_miles := total_miles + vehicle_miles;
--and display miles/gallon for the vehicle
vehicle_miles / vehicle_gals --You should add a divide by zero check here for when there's only one record
report footer:
whileprintingrecords;
numbervar total_gals;
numbervar total_miles;
--and display miles/gallon for the report
total_miles / total_gals --divide by zero check again
By the way, I've not tried this explicitely but it might give you a start / ideas.sql
Mozilla Firefox and Report Viewer Print control
Has anyone gotten the print control in report viewer to work with Firefox?
One of our clients insists on using firefox and we would like to use
reporting services. I found and downloaded the Mozilla Activex control and
plugin support, added the CLSID for the RSClientPrint control, but have not
had any success in having the control show up in the report viewer.
Microsoft's knowledge base talks about what does and doesn't work in Firefox,
but mentions only that the print control does not work in Safari. Any help
would be greatly appreciated!Hello HK,
Based on my research, the RSClinetPrint ActiveX Control also did not
support for the FireFox browser.
As an alternate, you could export the Report to PDF or Excel and then print
it.
Also, you may print the report in the FireFox directly.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Fri, 6 Jul 2007 06:56:02 -0700, HK <hk@.newsgroup.nospam> wrote:
>Has anyone gotten the print control in report viewer to work with Firefox?
>One of our clients insists on using firefox and we would like to use
>reporting services. I found and downloaded the Mozilla Activex control and
>plugin support, added the CLSID for the RSClientPrint control, but have not
>had any success in having the control show up in the report viewer.
>Microsoft's knowledge base talks about what does and doesn't work in Firefox,
>but mentions only that the print control does not work in Safari. Any help
>would be greatly appreciated!
Just a thought. Perhaps you could check the IETab plugin?
B.|||Thank you for your response.
After searching through the knowledge base, we decided to programatically
render the reports in pdf through the web service. This works well in firefox
and provides us with printing capability without having a two step process to
print (using report viewer and then exporting to pdf).
"Wei Lu [MSFT]" wrote:
> Hello HK,
> Based on my research, the RSClinetPrint ActiveX Control also did not
> support for the FireFox browser.
> As an alternate, you could export the Report to PDF or Excel and then print
> it.
> Also, you may print the report in the FireFox directly.
> Hope this helps.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Thanks - we did. But since it runs IE and our clients may not have IE
installed, we couldn't use it.
"Brian Tkatch" wrote:
> On Fri, 6 Jul 2007 06:56:02 -0700, HK <hk@.newsgroup.nospam> wrote:
> >Has anyone gotten the print control in report viewer to work with Firefox?
> >One of our clients insists on using firefox and we would like to use
> >reporting services. I found and downloaded the Mozilla Activex control and
> >plugin support, added the CLSID for the RSClientPrint control, but have not
> >had any success in having the control show up in the report viewer.
> >Microsoft's knowledge base talks about what does and doesn't work in Firefox,
> >but mentions only that the print control does not work in Safari. Any help
> >would be greatly appreciated!
> Just a thought. Perhaps you could check the IETab plugin?
> B.
>
One of our clients insists on using firefox and we would like to use
reporting services. I found and downloaded the Mozilla Activex control and
plugin support, added the CLSID for the RSClientPrint control, but have not
had any success in having the control show up in the report viewer.
Microsoft's knowledge base talks about what does and doesn't work in Firefox,
but mentions only that the print control does not work in Safari. Any help
would be greatly appreciated!Hello HK,
Based on my research, the RSClinetPrint ActiveX Control also did not
support for the FireFox browser.
As an alternate, you could export the Report to PDF or Excel and then print
it.
Also, you may print the report in the FireFox directly.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Fri, 6 Jul 2007 06:56:02 -0700, HK <hk@.newsgroup.nospam> wrote:
>Has anyone gotten the print control in report viewer to work with Firefox?
>One of our clients insists on using firefox and we would like to use
>reporting services. I found and downloaded the Mozilla Activex control and
>plugin support, added the CLSID for the RSClientPrint control, but have not
>had any success in having the control show up in the report viewer.
>Microsoft's knowledge base talks about what does and doesn't work in Firefox,
>but mentions only that the print control does not work in Safari. Any help
>would be greatly appreciated!
Just a thought. Perhaps you could check the IETab plugin?
B.|||Thank you for your response.
After searching through the knowledge base, we decided to programatically
render the reports in pdf through the web service. This works well in firefox
and provides us with printing capability without having a two step process to
print (using report viewer and then exporting to pdf).
"Wei Lu [MSFT]" wrote:
> Hello HK,
> Based on my research, the RSClinetPrint ActiveX Control also did not
> support for the FireFox browser.
> As an alternate, you could export the Report to PDF or Excel and then print
> it.
> Also, you may print the report in the FireFox directly.
> Hope this helps.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Thanks - we did. But since it runs IE and our clients may not have IE
installed, we couldn't use it.
"Brian Tkatch" wrote:
> On Fri, 6 Jul 2007 06:56:02 -0700, HK <hk@.newsgroup.nospam> wrote:
> >Has anyone gotten the print control in report viewer to work with Firefox?
> >One of our clients insists on using firefox and we would like to use
> >reporting services. I found and downloaded the Mozilla Activex control and
> >plugin support, added the CLSID for the RSClientPrint control, but have not
> >had any success in having the control show up in the report viewer.
> >Microsoft's knowledge base talks about what does and doesn't work in Firefox,
> >but mentions only that the print control does not work in Safari. Any help
> >would be greatly appreciated!
> Just a thought. Perhaps you could check the IETab plugin?
> B.
>
Mozilla Firefox
The web interface for ReportServer does not work in Mozilla Firefox 1.0:
reports appear as blank pages.
Is this due to use of ActiveX controls in the browser, or is there some
other reason - and who's problem is it?
brianSomeone awhile back investigated this pretty extensively and I gather it has
to do with how the web interface was designed. It is not an active x
problem. If you want to provide support for Mozilla you will need to use
your own front end instead of the one provided by MS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Smith" <bsmith@.NO.SPAM.schemiotics.co.uk> wrote in message
news:uwkkBk5OFHA.580@.TK2MSFTNGP15.phx.gbl...
> The web interface for ReportServer does not work in Mozilla Firefox 1.0:
> reports appear as blank pages.
> Is this due to use of ActiveX controls in the browser, or is there some
> other reason - and who's problem is it?
> brian
reports appear as blank pages.
Is this due to use of ActiveX controls in the browser, or is there some
other reason - and who's problem is it?
brianSomeone awhile back investigated this pretty extensively and I gather it has
to do with how the web interface was designed. It is not an active x
problem. If you want to provide support for Mozilla you will need to use
your own front end instead of the one provided by MS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian Smith" <bsmith@.NO.SPAM.schemiotics.co.uk> wrote in message
news:uwkkBk5OFHA.580@.TK2MSFTNGP15.phx.gbl...
> The web interface for ReportServer does not work in Mozilla Firefox 1.0:
> reports appear as blank pages.
> Is this due to use of ActiveX controls in the browser, or is there some
> other reason - and who's problem is it?
> brian
Mozilla cannot run Reports ? Toolbar squishered?
I have most stuff configured to look good through Mozilla, and the reports
I'm working on themselves don't look bad in the browser.
However, the ReportViewer control Toolbar (with the export options, refresh
button etc) is compressed horizontally causing it to flow onto 3 or so
seperate horizontal lines, when it's rendered.
Now, I've tried running it in tables, divs, etc. I can't find anything on
the page which would cause this to happen. Does anyone know if this bar
does not display correctly through Mozilla?
I have a feeling it will not display correctly through Mozilla if it's made
up of mested tables (the ReportViewer toolbar) ?
Any light shone on the matter, appreciated.
Matt Swift"Matt Swift" wrote:
> I have a feeling it will not display correctly through Mozilla if it's made
> up of mested tables (the ReportViewer toolbar) ?
> Any light shone on the matter, appreciated.
http://validator.w3.org/ might help you, it will show you which particular
standards IE is set up to ignore here..|||How well does Mozilla handle other ActiveX controls? The ReportViewer
control Toolbar is an ActiveX control right?|||"veganopolis" wrote:
> How well does Mozilla handle other ActiveX controls?
Mozilla doesn't do ActiveX.
>The ReportViewer control Toolbar is an ActiveX control right?
No, it's ASP.NET which involves a lot of proprietary JavaScript-like
extensions, and some hidden form fields that contain gigantic strings that
look like this: dDw1MzgxO3Q8O2w8aTwxPjs+O2w8dDw7bDxp...etc etc.
Tim|||Although Mozilla doesn't support ActiveX out_of_the_box, there is an
ActiveX compatabe plugin: http://www.iol.ie/~locka/mozilla/plugin.htm
(there are probably many more open source implementations of this.
quick googling...)
I am new to RS so I do not know how MS implemented their ReportViewer
control Toolbar. I only remember the similar solution they have use
with their Office apps, which was an ActiveX control.
Matt Swift: If this control toolbar is JS, and written by MS, I
wouldn't expect it to work at all for another browser. But I am still
interested in how this pans out.
sam
I'm working on themselves don't look bad in the browser.
However, the ReportViewer control Toolbar (with the export options, refresh
button etc) is compressed horizontally causing it to flow onto 3 or so
seperate horizontal lines, when it's rendered.
Now, I've tried running it in tables, divs, etc. I can't find anything on
the page which would cause this to happen. Does anyone know if this bar
does not display correctly through Mozilla?
I have a feeling it will not display correctly through Mozilla if it's made
up of mested tables (the ReportViewer toolbar) ?
Any light shone on the matter, appreciated.
Matt Swift"Matt Swift" wrote:
> I have a feeling it will not display correctly through Mozilla if it's made
> up of mested tables (the ReportViewer toolbar) ?
> Any light shone on the matter, appreciated.
http://validator.w3.org/ might help you, it will show you which particular
standards IE is set up to ignore here..|||How well does Mozilla handle other ActiveX controls? The ReportViewer
control Toolbar is an ActiveX control right?|||"veganopolis" wrote:
> How well does Mozilla handle other ActiveX controls?
Mozilla doesn't do ActiveX.
>The ReportViewer control Toolbar is an ActiveX control right?
No, it's ASP.NET which involves a lot of proprietary JavaScript-like
extensions, and some hidden form fields that contain gigantic strings that
look like this: dDw1MzgxO3Q8O2w8aTwxPjs+O2w8dDw7bDxp...etc etc.
Tim|||Although Mozilla doesn't support ActiveX out_of_the_box, there is an
ActiveX compatabe plugin: http://www.iol.ie/~locka/mozilla/plugin.htm
(there are probably many more open source implementations of this.
quick googling...)
I am new to RS so I do not know how MS implemented their ReportViewer
control Toolbar. I only remember the similar solution they have use
with their Office apps, which was an ActiveX control.
Matt Swift: If this control toolbar is JS, and written by MS, I
wouldn't expect it to work at all for another browser. But I am still
interested in how this pans out.
sam
Mozilla allignment problem with reports
Hello,
I have generated reports in my web application using SQL Server Reporting services. Its working and displaying fine when I open the application in Internet Explorer. But with Firefox Mozilla, the display of report contents is not working proper. Please advise.
Thanks in advance,
Ambili.
Hi,
In Reporting Services, you use a Web browser to view reports and run Report Manager. Not all report functionality is supported by all browsers. The following table describes report functionality restrictions for the supported browsers.
So please make sure if your explorer type match the needs to run reporting service.
For details, see
http://msdn2.microsoft.com/en-us/library/ms156511.aspx
Thanks.
Hello,
Thanks for the prompt response.
As you have specified, I could see that Searching within thereport is not possible with Mozilla, which is possible in IE.
But my major issue is that the data inside the report is notdisplaying properly ( i mean, the formatting of the report contents). In IE, data inside the report is displaying properly. But in Mozilla, only first character of each column is displayed in each row. Any help on this issue will be appreciated.
Thanks in advance,
Ambili.
If the output is mushed height-wise, add this to the ReportServices.css file:
* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}
For output mushed width-wise, add an empty textbox the width of the expected output.
Hope that helps.
|||Hello,
Thanks a lot for the valuable information.It helped me to solve the problem in case of height-wise alignment in mozilla and I am working with the case of width-wise alignment issue using the idea which you have specified.
Thanks again.
-Ambili.
|||Hi Corwin:
Thank you for the code help in the ReportServices.css file. It worked for me using:
/* Fix report IFRAME height for Firefox */
.DocMapAndReportFrame
{
min-height: 860px;
}
I just had to put the forward slash ( / ) in front of * Fix report IFRAME height for Firefox */
moving/copying database
Hello,
Does somebody knows a complete solution for resolving the following issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. So i use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins.
I hope somebody has faced this problem before and can give me a solution.
Thnx
Jason
Here are links to some articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John
"Jason" wrote:
> Hello,
> Does somebody knows a complete solution for resolving the following issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. So i use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>
sql
Does somebody knows a complete solution for resolving the following issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. So i use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins.
I hope somebody has faced this problem before and can give me a solution.
Thnx
Jason
Here are links to some articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John
"Jason" wrote:
> Hello,
> Does somebody knows a complete solution for resolving the following issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. So i use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>
sql
moving/copying database
Hello,
Does somebody knows a complete solution for resolving the following issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. So i use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins.
I hope somebody has faced this problem before and can give me a solution.
ThnxJason
Here are links to some articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John
"Jason" wrote:
> Hello,
> Does somebody knows a complete solution for resolving the following issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. So i use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>
Does somebody knows a complete solution for resolving the following issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. So i use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins.
I hope somebody has faced this problem before and can give me a solution.
ThnxJason
Here are links to some articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John
"Jason" wrote:
> Hello,
> Does somebody knows a complete solution for resolving the following issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. So i use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>
moving/copying database
Hello,
Does somebody knows a complete solution for resolving the following issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. So i use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins.
I hope somebody has faced this problem before and can give me a solution.
ThnxJason
Here are links to some articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John
"Jason" wrote:
> Hello,
> Does somebody knows a complete solution for resolving the following issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. So i use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>
Does somebody knows a complete solution for resolving the following issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. So i use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins.
I hope somebody has faced this problem before and can give me a solution.
ThnxJason
Here are links to some articles that should help you resolve your problem.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Hope this helps
John
"Jason" wrote:
> Hello,
> Does somebody knows a complete solution for resolving the following issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. So i use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>
moving...
Hi ,
We have a database around 5GB in size, located in remote location . i want to get that database into my local machine. We cannot take back up in tape or any other media from our remote location ,so we have to depend on FTP .
Is there any better solution other than FTP, or DTS import/Export , to transfer whole database to my local machine ?
Please give me some suggestions !
Thanks,
HadleyIts better to take file backup and use FTP to transfer the file. Before than make sure to run DBCC checks against the database to keep it clean.
Even you can use Generate SQL Scripts and run those scripts on the local machine, then use DTS to transfer the data.
HTH
We have a database around 5GB in size, located in remote location . i want to get that database into my local machine. We cannot take back up in tape or any other media from our remote location ,so we have to depend on FTP .
Is there any better solution other than FTP, or DTS import/Export , to transfer whole database to my local machine ?
Please give me some suggestions !
Thanks,
HadleyIts better to take file backup and use FTP to transfer the file. Before than make sure to run DBCC checks against the database to keep it clean.
Even you can use Generate SQL Scripts and run those scripts on the local machine, then use DTS to transfer the data.
HTH
Moving XML Variable data to a SQL Table
Say I have the following T-SQL:
Declare @.Doc xml
Declare @.Table Table (ItemType nvarchar(10),UserType nvarchar(20), UserCount
int)
Set @.Doc = '
<row ItemType="UserCount" UserType="Corporate" UserCount="0"/>
<row ItemType="UserCount" UserType="External Billable" UserCount="0"/>
<row ItemType="UserCount" UserType="External Non-Billable" UserCount="9"/>
<row ItemType="UserCount" UserType="Internal Billable" UserCount="71"/>
<row ItemType="UserCount" UserType="Internal Non-Billable" UserCount="0"/>
<row ItemType="UserCount" UserType="Leadership Center" UserCount="0"/>'
Can someone show me the T-SQL statement needed to take the 6 rows in the XML
variable @.Doc and insert them into the Table variable @.Table?
Thanks very much - Amos."You have the following T-SQL"
Declare @.Doc xml
Declare @.Table Table (ItemType nvarchar(10),UserType nvarchar(20),UserCount
int)
Set @.Doc = '<row ItemType="UserCount" UserType="Corporate" UserCount="0"/><r
ow
ItemType="UserCount" UserType="External Billable" UserCount="0"/><row ItemTy
pe="UserCount"
UserType="External Non-Billable" UserCount="9"/><row ItemType="UserCount"
UserType="Internal Billable" UserCount="71"/><row ItemType="UserCount" UserT
ype="Internal
Non-Billable" UserCount="0"/> <row ItemType="UserCount" UserType="Leadership
Center" UserCount="0"/>'
insert into @.table
select t.c. value('data(@.ItemType)','nvarchar(10)'),
t.c. value('data(@.UserType)','nvarchar(20)'),
t.c.value('data(@.UserCount)','int')
from @.doc.nodes('row') as t(c)
select * from @.table
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent. That is exactly what I was looking for.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74f4048c8c82089568080@.news.microsoft.com...
> "You have the following T-SQL"
> Declare @.Doc xml
> Declare @.Table Table (ItemType nvarchar(10),UserType
> nvarchar(20),UserCount int)
> Set @.Doc = '<row ItemType="UserCount" UserType="Corporate"
> UserCount="0"/><row ItemType="UserCount" UserType="External Billable"
> UserCount="0"/><row ItemType="UserCount" UserType="External Non-Billable"
> UserCount="9"/><row ItemType="UserCount" UserType="Internal Billable"
> UserCount="71"/><row ItemType="UserCount" UserType="Internal Non-Billable"
> UserCount="0"/> <row ItemType="UserCount" UserType="Leadership Center"
> UserCount="0"/>'
> insert into @.table
> select t.c. value('data(@.ItemType)','nvarchar(10)'),
> t.c. value('data(@.UserType)','nvarchar(20)'),
> t.c.value('data(@.UserCount)','int')
> from @.doc.nodes('row') as t(c)
> select * from @.table
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
Declare @.Doc xml
Declare @.Table Table (ItemType nvarchar(10),UserType nvarchar(20), UserCount
int)
Set @.Doc = '
<row ItemType="UserCount" UserType="Corporate" UserCount="0"/>
<row ItemType="UserCount" UserType="External Billable" UserCount="0"/>
<row ItemType="UserCount" UserType="External Non-Billable" UserCount="9"/>
<row ItemType="UserCount" UserType="Internal Billable" UserCount="71"/>
<row ItemType="UserCount" UserType="Internal Non-Billable" UserCount="0"/>
<row ItemType="UserCount" UserType="Leadership Center" UserCount="0"/>'
Can someone show me the T-SQL statement needed to take the 6 rows in the XML
variable @.Doc and insert them into the Table variable @.Table?
Thanks very much - Amos."You have the following T-SQL"
Declare @.Doc xml
Declare @.Table Table (ItemType nvarchar(10),UserType nvarchar(20),UserCount
int)
Set @.Doc = '<row ItemType="UserCount" UserType="Corporate" UserCount="0"/><r
ow
ItemType="UserCount" UserType="External Billable" UserCount="0"/><row ItemTy
pe="UserCount"
UserType="External Non-Billable" UserCount="9"/><row ItemType="UserCount"
UserType="Internal Billable" UserCount="71"/><row ItemType="UserCount" UserT
ype="Internal
Non-Billable" UserCount="0"/> <row ItemType="UserCount" UserType="Leadership
Center" UserCount="0"/>'
insert into @.table
select t.c. value('data(@.ItemType)','nvarchar(10)'),
t.c. value('data(@.UserType)','nvarchar(20)'),
t.c.value('data(@.UserCount)','int')
from @.doc.nodes('row') as t(c)
select * from @.table
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent. That is exactly what I was looking for.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74f4048c8c82089568080@.news.microsoft.com...
> "You have the following T-SQL"
> Declare @.Doc xml
> Declare @.Table Table (ItemType nvarchar(10),UserType
> nvarchar(20),UserCount int)
> Set @.Doc = '<row ItemType="UserCount" UserType="Corporate"
> UserCount="0"/><row ItemType="UserCount" UserType="External Billable"
> UserCount="0"/><row ItemType="UserCount" UserType="External Non-Billable"
> UserCount="9"/><row ItemType="UserCount" UserType="Internal Billable"
> UserCount="71"/><row ItemType="UserCount" UserType="Internal Non-Billable"
> UserCount="0"/> <row ItemType="UserCount" UserType="Leadership Center"
> UserCount="0"/>'
> insert into @.table
> select t.c. value('data(@.ItemType)','nvarchar(10)'),
> t.c. value('data(@.UserType)','nvarchar(20)'),
> t.c.value('data(@.UserCount)','int')
> from @.doc.nodes('row') as t(c)
> select * from @.table
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
Moving whole rows in the script component
I am new to script components. I would like to make a simple filter that either passes a row through untouched or eliminates it. I have my input and output buffers set the same, and I have it set as asynchronous. Now these are big rows. Is there a painless way to copy all columns from the input to output buffer, or do I have to do a "Output0Buffer.Col1 = Row.Col1" for each column?
|||
No painless way that I am aware off.
A question: why are you doing this in a script component? This seems more suited to a conditional split. Just send the rows to be eliminated to an output that isn't connected to another component, and they are gone.
If you have to use a script component (perhaps because of extremely complex logic), you can still use synchronous outputs (which means all your columns will carry over). Just create two outputs (both synchronous) and send rows you want to one, rows to discard to another. See Jamie's post here for some info on this: http://blogs.conchango.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx
|||I did simplify some here. I want to sort removing duplicates of an ID column, but of the dupes, I want it to select the one with the earliest value in a date column. I tried sorting by the date, then by the ID (removing duplicates), but the date sorting was lost, of course. So the conditional split doesn't work because the decision is dependent on more than one row.I see a way to do this with SQL, making a few views, but it ended up being overly complicated. I also have it working synchronously as you suggested. That was an easy solution! That's the one I'll keep.
I guess I liked the idea of it working asynchronous more at first, but I didn't have a good reason why! I suppose it's faster to code and faster to process doing it synchronously.
Thanks for the quick response.
|||For future reference, here's all I had to do:
Sorted the dataflow coming in on the ID that I wanted to be unique (DocumentUniqueID).
Connected up a new Script Component.
Checked off all the Input Columns (and left them as ReadOnly).
Added an Output.
Set its SynchronousInputID to "Input 0".
Set its ExclusionGroup to 1.
Added this code to the script:
Code Snippet
Public Class ScriptMain
Inherits UserComponent
Private lastUniqueID As String
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.DocumentUniqueID = lastUniqueID Then
Row.DirectRowToOutput1()
End If
lastUniqueID = Row.DocumentUniqueID
End Sub
End Class
|||
If the source data is in Oracle, SQL Server 2005 or any other DB that has rank() function; you could solve the problem with plain SQL. You can generate an extra column called, let's say, MyRank that will sort each set of duplicate IDs using the given criteria; in you case date. Then you use the conditional split based on MyRank column. I talked about it in this post:
http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html
|||
Rafael Salas wrote:
If the source data is in Oracle, SQL Server 2005 or any other DB that has rank() function; you could solve the problem with plain SQL. You can generate an extra column called, let's say, MyRank that will sort each set of duplicate IDs using the given criteria; in you case date. Then you use the conditional split based on MyRank column. I talked about it in this post:
http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html
That's an even better solution. RANK() isn't a function I've used extensively, I overlooked it. Thanks for the tip.
sql
Moving virtual server and instance names to a new server
We currently have a (2) node active/active cluster with each node ideally
hosting (2) virtual servers and their instances. We want to split the
cluster into (2) active/passive clusters and move what normally resides on a
given node to it's own cluster. Here is the catch. We have cross
server/instances queries/procedures and Access database/queries with the
Virtual Server and instance name hard coded, and creating new names isn't
really an viable option.
Here is the current setup:
Current Cluster:
NODE01
VSQL1\sqlsrv01
VSQL1\sqlsrv02
NODE02
VSQL1\sqlsrv03
VSQL1\sqlsrv04
And here is the proposed setup::
New Cluster 1:
NODE01 - active
VSQL1\sqlsrv01
VSQL1\sqlsrv02
NODE02 - inactive
New Cluster 2:
NODE01 - active
VSQL1\sqlsrv03
VSQL1\sqlsrv04
NODE02 - inactive
Can we create the servers in a different domain, moving them to our
production domain when testing has been completed? And if so, how do we
handle the fact that the VSQLx and named instances already exists in Active
Directory? Can the old server just be turned off, the new one brought up and
added to the domain by resetting the current domain accounts and re-joining?
Of course. Does anyone have a good solution, I'm all ears. Thanks
> Here is the current setup:
> Current Cluster:
> NODE01
> VSQL1\sqlsrv01
> VSQL1\sqlsrv02
> NODE02
> VSQL1\sqlsrv03
> VSQL1\sqlsrv04
That can't be your current setup. Your current setup is probably more like:
NODE01
VSQL01\sqlsrv01
VSQL02\sqlsrv02
NODE02
VSQL03\sqlsrv03
VSQL04\sqlsrv04
I'd probably just do a switchover with the current cluster. First, get the
necessary database files to a staging server. Then, remove the cluster from
the network. But keep it as your rollback plan. Create two new clusters and
install SQL instances as follows:
New Cluster 1:
NODE01 - active
VSQL01\sqlsrv01
VSQL02\sqlsrv02
NODE02 - inactive
New Cluster 2:
NODE01 - active
VSQL03\sqlsrv03
VSQL04\sqlsrv04
And finally get the database files from the staging server to their
respective new servers.
Linchi
"randall_dh" wrote:
> We currently have a (2) node active/active cluster with each node ideally
> hosting (2) virtual servers and their instances. We want to split the
> cluster into (2) active/passive clusters and move what normally resides on a
> given node to it's own cluster. Here is the catch. We have cross
> server/instances queries/procedures and Access database/queries with the
> Virtual Server and instance name hard coded, and creating new names isn't
> really an viable option.
> Here is the current setup:
> Current Cluster:
> NODE01
> VSQL1\sqlsrv01
> VSQL1\sqlsrv02
> NODE02
> VSQL1\sqlsrv03
> VSQL1\sqlsrv04
>
> And here is the proposed setup::
> New Cluster 1:
> NODE01 - active
> VSQL1\sqlsrv01
> VSQL1\sqlsrv02
> NODE02 - inactive
> New Cluster 2:
> NODE01 - active
> VSQL1\sqlsrv03
> VSQL1\sqlsrv04
> NODE02 - inactive
> Can we create the servers in a different domain, moving them to our
> production domain when testing has been completed? And if so, how do we
> handle the fact that the VSQLx and named instances already exists in Active
> Directory? Can the old server just be turned off, the new one brought up and
> added to the domain by resetting the current domain accounts and re-joining?
> Of course. Does anyone have a good solution, I'm all ears. Thanks
|||Yes, you are correct regarding the Virtual Server naming (careless cutting
and pasting on my part). Ideally, we would like to test the new cluster for
several days prior to bringing it live. My main concern is that the reusing
of the names doesn't cause a problem. Am I correct in my assumtions that AD
isn't involved in the virtual server or instance naming and that the only
network ties are through the DNS entries?
ALso, would you expect that any issues with restoring the master and msdb to
the new cluster instances (any references to the old nodes etc...)?
Thank you.
"Linchi Shea" wrote:
[vbcol=seagreen]
> That can't be your current setup. Your current setup is probably more like:
> NODE01
> VSQL01\sqlsrv01
> VSQL02\sqlsrv02
> NODE02
> VSQL03\sqlsrv03
> VSQL04\sqlsrv04
> I'd probably just do a switchover with the current cluster. First, get the
> necessary database files to a staging server. Then, remove the cluster from
> the network. But keep it as your rollback plan. Create two new clusters and
> install SQL instances as follows:
> New Cluster 1:
> NODE01 - active
> VSQL01\sqlsrv01
> VSQL02\sqlsrv02
> NODE02 - inactive
> New Cluster 2:
> NODE01 - active
> VSQL03\sqlsrv03
> VSQL04\sqlsrv04
> And finally get the database files from the staging server to their
> respective new servers.
> Linchi
> "randall_dh" wrote:
hosting (2) virtual servers and their instances. We want to split the
cluster into (2) active/passive clusters and move what normally resides on a
given node to it's own cluster. Here is the catch. We have cross
server/instances queries/procedures and Access database/queries with the
Virtual Server and instance name hard coded, and creating new names isn't
really an viable option.
Here is the current setup:
Current Cluster:
NODE01
VSQL1\sqlsrv01
VSQL1\sqlsrv02
NODE02
VSQL1\sqlsrv03
VSQL1\sqlsrv04
And here is the proposed setup::
New Cluster 1:
NODE01 - active
VSQL1\sqlsrv01
VSQL1\sqlsrv02
NODE02 - inactive
New Cluster 2:
NODE01 - active
VSQL1\sqlsrv03
VSQL1\sqlsrv04
NODE02 - inactive
Can we create the servers in a different domain, moving them to our
production domain when testing has been completed? And if so, how do we
handle the fact that the VSQLx and named instances already exists in Active
Directory? Can the old server just be turned off, the new one brought up and
added to the domain by resetting the current domain accounts and re-joining?
Of course. Does anyone have a good solution, I'm all ears. Thanks
> Here is the current setup:
> Current Cluster:
> NODE01
> VSQL1\sqlsrv01
> VSQL1\sqlsrv02
> NODE02
> VSQL1\sqlsrv03
> VSQL1\sqlsrv04
That can't be your current setup. Your current setup is probably more like:
NODE01
VSQL01\sqlsrv01
VSQL02\sqlsrv02
NODE02
VSQL03\sqlsrv03
VSQL04\sqlsrv04
I'd probably just do a switchover with the current cluster. First, get the
necessary database files to a staging server. Then, remove the cluster from
the network. But keep it as your rollback plan. Create two new clusters and
install SQL instances as follows:
New Cluster 1:
NODE01 - active
VSQL01\sqlsrv01
VSQL02\sqlsrv02
NODE02 - inactive
New Cluster 2:
NODE01 - active
VSQL03\sqlsrv03
VSQL04\sqlsrv04
And finally get the database files from the staging server to their
respective new servers.
Linchi
"randall_dh" wrote:
> We currently have a (2) node active/active cluster with each node ideally
> hosting (2) virtual servers and their instances. We want to split the
> cluster into (2) active/passive clusters and move what normally resides on a
> given node to it's own cluster. Here is the catch. We have cross
> server/instances queries/procedures and Access database/queries with the
> Virtual Server and instance name hard coded, and creating new names isn't
> really an viable option.
> Here is the current setup:
> Current Cluster:
> NODE01
> VSQL1\sqlsrv01
> VSQL1\sqlsrv02
> NODE02
> VSQL1\sqlsrv03
> VSQL1\sqlsrv04
>
> And here is the proposed setup::
> New Cluster 1:
> NODE01 - active
> VSQL1\sqlsrv01
> VSQL1\sqlsrv02
> NODE02 - inactive
> New Cluster 2:
> NODE01 - active
> VSQL1\sqlsrv03
> VSQL1\sqlsrv04
> NODE02 - inactive
> Can we create the servers in a different domain, moving them to our
> production domain when testing has been completed? And if so, how do we
> handle the fact that the VSQLx and named instances already exists in Active
> Directory? Can the old server just be turned off, the new one brought up and
> added to the domain by resetting the current domain accounts and re-joining?
> Of course. Does anyone have a good solution, I'm all ears. Thanks
|||Yes, you are correct regarding the Virtual Server naming (careless cutting
and pasting on my part). Ideally, we would like to test the new cluster for
several days prior to bringing it live. My main concern is that the reusing
of the names doesn't cause a problem. Am I correct in my assumtions that AD
isn't involved in the virtual server or instance naming and that the only
network ties are through the DNS entries?
ALso, would you expect that any issues with restoring the master and msdb to
the new cluster instances (any references to the old nodes etc...)?
Thank you.
"Linchi Shea" wrote:
[vbcol=seagreen]
> That can't be your current setup. Your current setup is probably more like:
> NODE01
> VSQL01\sqlsrv01
> VSQL02\sqlsrv02
> NODE02
> VSQL03\sqlsrv03
> VSQL04\sqlsrv04
> I'd probably just do a switchover with the current cluster. First, get the
> necessary database files to a staging server. Then, remove the cluster from
> the network. But keep it as your rollback plan. Create two new clusters and
> install SQL instances as follows:
> New Cluster 1:
> NODE01 - active
> VSQL01\sqlsrv01
> VSQL02\sqlsrv02
> NODE02 - inactive
> New Cluster 2:
> NODE01 - active
> VSQL03\sqlsrv03
> VSQL04\sqlsrv04
> And finally get the database files from the staging server to their
> respective new servers.
> Linchi
> "randall_dh" wrote:
Moving Users
How can I export users from one SQL 2000 server and
recreate them on another, maintaining the same
permissions they have on he first server without having
to do it manually?
Thanks
EmmaEmma,
Create a DTS Package using the Transfer Logins Task object.
Norman|||http://support.microsoft.com/defaul...kb;en-us;246133
Ray Higdon MCSE, MCDBA, CCNA
--
"Emma" <eeemoe@.hotmail.com> wrote in message
news:92fe01c3ea6c$f7ae51f0$a001280a@.phx.gbl...
recreate them on another, maintaining the same
permissions they have on he first server without having
to do it manually?
Thanks
EmmaEmma,
Create a DTS Package using the Transfer Logins Task object.
Norman|||http://support.microsoft.com/defaul...kb;en-us;246133
Ray Higdon MCSE, MCDBA, CCNA
--
"Emma" <eeemoe@.hotmail.com> wrote in message
news:92fe01c3ea6c$f7ae51f0$a001280a@.phx.gbl...
quote:
> How can I export users from one SQL 2000 server and
> recreate them on another, maintaining the same
> permissions they have on he first server without having
> to do it manually?
> Thanks
> Emma
Moving Users
Is there an easy way of just moving users from another db along with all it's permissions?
Constraint = Sorry, working with SQL Server 6.5.
Thanks in advance!if you run instcat.sql on SQL 6.5, you would be abble to use DTS packages to move users
Constraint = Sorry, working with SQL Server 6.5.
Thanks in advance!if you run instcat.sql on SQL 6.5, you would be abble to use DTS packages to move users
moving user id and password from one server to another server
Hi,
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
Martin
You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison
|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login information
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed so
> that the above solution does not work? Or what should i do in order to move
> the user id and password from server A to server B without changing the user
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John
|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> John
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
Martin
You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison
|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login information
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed so
> that the above solution does not work? Or what should i do in order to move
> the user id and password from server A to server B without changing the user
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John
|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> John
moving user id and password from one server to another server
Hi,
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
----
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am going to migrate a db from server A to server B. The login
> information of the application are using SQL server authenication. From
> SQL help that user id and password can be export and move from server A to
> server B as following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
> so that the above solution does not work? Or what should i do in order to
> move the user id and password from server A to server B without changing
> the user password? Thanks you very much!
> Martin
>|||You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive.../03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login informatio
n
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
so
> that the above solution does not work? Or what should i do in order to mov
e
> the user id and password from server A to server B without changing the us
er
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
>
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> Johnsql
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
----
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am going to migrate a db from server A to server B. The login
> information of the application are using SQL server authenication. From
> SQL help that user id and password can be export and move from server A to
> server B as following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
> so that the above solution does not work? Or what should i do in order to
> move the user id and password from server A to server B without changing
> the user password? Thanks you very much!
> Martin
>|||You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive.../03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login informatio
n
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
so
> that the above solution does not work? Or what should i do in order to mov
e
> the user id and password from server A to server B without changing the us
er
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
>
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> Johnsql
Subscribe to:
Posts (Atom)