Data Source question

How would be the best way to do this? We are creating reports (the development teams) that run against a Development SQL server (2005). We then send the rdl files to our QA team who then upload the files to their ReportServer (which we do not have access to and this is why we are not deploying the reports). They created their datasource on their ReportServer (using the report manager), but when they go into our reports and try to point to their datasource, they are getting errors.

This is the rdl:

<DataSources>

<DataSource Name="QADataSource">

<DataSourceReference>QADataSource</DataSourceReference>

<rd: DataSourceID>6a2ce78a-db44-4f00-8ec9-6658459ea989</rd: DataSourceID>

</DataSource>

</DataSources>

When I went in and deleted the <rd: DataSourceID> reference, this seemed to work.

Any suggestions?

That is because the dataset is referenced within the current project. If you open the WebBrowser and change the data source pointer to the data source on the report server, it should be fine. The changes to the reference is automatically changed during the VS deployment process.

Jens K. Suessmeyer.

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

So what I should be able to do is to supply a user with an rdl file and then they will set up their datasource on their report manager, upload the rdl file and then they should be able to set their own datasource that they created?

This is the functionality that we want.

|||Let them upload the rdl file you provide from Visual Studio and change them the data source to the actual olne on the server.

Jens K. Suessmeyer.

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

What we do is to create a set number of datasources on the report server that point to the various DB's we use.

E.g

Datasource 1

Datasource 2

Datasource 3

When creating a report, we set up a shared datasource with the same name as the datasource on the report server. This means when we upload the report, it will use the selected datasource and use the connection details that are stored within it. it doesnt matter what connection details we set in the shared data source within visual studio, it will use the details on the report server.

Data returned from .WriteXML is different than what is returned in query analyzer.

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.

Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).

Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.

If someone is able to help me figure this out, I would appreciate it.

1. public DataSet GetMarketList(string region, string marketRegion)
2. {
3. string sql = @."SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode";
5. DataSet dsMarketList = new DataSet();
6. SqlConnection sqlConn = new SqlConnection(intranetConnStr);

7. SqlCommand cmd = new SqlCommand(sql,sqlConn);
8. sqlConn.Open();
9. SqlDataAdapter adapter = new SqlDataAdapter(cmd);

10. try
11. {
12. adapter.Fill(dsMarketList);

13. String bling = adapter.SelectCommand.CommandText;//BRG
14. dsMarketList.DataSetName="RegionMarket";
15. dsMarketList.Tables[0].TableName = "MarketList";
16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4
18. // from the SQL query
19. }
20. catch(Exception e)
21. {
22. // Handle the exception (Code not shown)

I think that you need to look at what data isn't being returned in your app. Can you load the XML file into Excel? You could then paste the SQL Analyzer results into Excel and look at the difference.

Also, there isn't an order by clause in your SQL, so SQL Server is free to return the results in a different order between invocations of the same SQL. (It rarely happens, but it does occasionally.)

|||Ugh !!! I figured out what the problem is. Thanks !sql

Data replication issues: Thanks Paul

Paul,
Thanks for your reply. My distribution agents schedule is
set to run at 7:00 am everyday (similar to my snapshot
agent). I found that the distribution agent gave me an
error and the details are as follows:
Error msg: The process could not access
database 'LETS30_Repl' on server 'FTANT001'
Error details: Could not locate entry in sysdatabases for
database 'LETS30_Repl'. No entry found with that name.
make sure that the name is entered correctly. [SOurce:
FTANT001(Data Source): Error Number:911]
One interesting thing to note is even though I had created
the database 'LETS30_Repl' it does not show up
under 'Databases' in the server. Why is it so? Thanks,
Cheers,
Sabuj

>Sabuj,
>what is the distribution agent's schedule? My guess is
>that it hasn't
>matched its schedule time yet. Try running it manually to
>check that
>everything is OK.
>HTH,
>Paul Ibison
Sabuj,
can you query the sysdatabases table on the subscriber (FTANT001) to verify
if it is there:
select * from master.dbo.sysdatabases
Or alternatively just run sp_helpdb.
If it is not there then recreating it should allow the distribution agent to
succeed.
Regards,
Paul Ibison

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank you
Bringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you
|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my loca
l
machine it takes 14 minutes (slams my machine) to get back the whole dataset
.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such ma
ny rows.
Start by retrieving only the data you need, then tune the query by adding in
dexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:
> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data Processing Extensions - Parameters ?

Hi,
I am submitting a report to be run via sending the reportname and some
parameters via the RS WebService. Behind this I have a DPE. This all works
fine when there are no parameters. when I do pass params though, I have a
problem.
Trouble is that I cannot seem to lay my hands on the passed parameter values
(I presume a collection ?) when in my implementation class of the IDbCommand,
IDbCommandAnalysis interfaces
How can I obtain these params in the DPE ?
any help would be appreciated, thanks,The IDbCommandAnalysis interface serves the orthogonal purpose. It lets the
Report Designer know about the parameters that your query expects. During
runtime the Report Server calls IDataParameterCollection.Add to pass the
parameter values. This is where you will load them, e.g.:
public int Add(IDataParameter value)
{
Trace.WriteLine("DataSet Extension: Add(IDataParameter value");
if (((DsDataParameter)value).ParameterName != null)
{
return base.Add(value);
}
else
throw new ArgumentException("parameter must be named");
}
public DsDataParameter GetByName(string parameterName)
{
DsDataParameter parameter = null;
IEnumerator enumerator = this.GetEnumerator();
while (enumerator.MoveNext())
{
DsDataParameter tempParameter = (DsDataParameter) enumerator.Current;
if (tempParameter.ParameterName == parameterName)
{
parameter = tempParameter;
break;
}
}
return parameter;
}
Then, in your DataReader implementation you can get the parameters:
DsDataParameter parameter = m_parameters.GetByName(Util.DATA_SOURCE)
as DsDataParameter;
You can get a complete working DPE sample here:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"softgui" <softgui@.discussions.microsoft.com> wrote in message
news:FE3B6E92-4E95-42E7-8E1B-2868F9670CB0@.microsoft.com...
> Hi,
> I am submitting a report to be run via sending the reportname and some
> parameters via the RS WebService. Behind this I have a DPE. This all
works
> fine when there are no parameters. when I do pass params though, I have a
> problem.
> Trouble is that I cannot seem to lay my hands on the passed parameter
values
> (I presume a collection ?) when in my implementation class of the
IDbCommand,
> IDbCommandAnalysis interfaces
> How can I obtain these params in the DPE ?
> any help would be appreciated, thanks,
>|||Many thanks Teo.
- simon
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23OFyCBoqEHA.3712@.TK2MSFTNGP15.phx.gbl...
> The IDbCommandAnalysis interface serves the orthogonal purpose. It lets
> the
> Report Designer know about the parameters that your query expects. During
> runtime the Report Server calls IDataParameterCollection.Add to pass the
> parameter values. This is where you will load them, e.g.:
> public int Add(IDataParameter value)
> {
> Trace.WriteLine("DataSet Extension: Add(IDataParameter value");
> if (((DsDataParameter)value).ParameterName != null)
> {
> return base.Add(value);
> }
> else
> throw new ArgumentException("parameter must be named");
> }
> public DsDataParameter GetByName(string parameterName)
> {
> DsDataParameter parameter = null;
> IEnumerator enumerator = this.GetEnumerator();
> while (enumerator.MoveNext())
> {
> DsDataParameter tempParameter = (DsDataParameter) enumerator.Current;
> if (tempParameter.ParameterName == parameterName)
> {
> parameter = tempParameter;
> break;
> }
> }
> return parameter;
> }
> Then, in your DataReader implementation you can get the parameters:
> DsDataParameter parameter = m_parameters.GetByName(Util.DATA_SOURCE)
> as DsDataParameter;
> You can get a complete working DPE sample here:
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> --
> Hope this helps.
> ----
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "softgui" <softgui@.discussions.microsoft.com> wrote in message
> news:FE3B6E92-4E95-42E7-8E1B-2868F9670CB0@.microsoft.com...
>> Hi,
>> I am submitting a report to be run via sending the reportname and some
>> parameters via the RS WebService. Behind this I have a DPE. This all
> works
>> fine when there are no parameters. when I do pass params though, I have a
>> problem.
>> Trouble is that I cannot seem to lay my hands on the passed parameter
> values
>> (I presume a collection ?) when in my implementation class of the
> IDbCommand,
>> IDbCommandAnalysis interfaces
>> How can I obtain these params in the DPE ?
>> any help would be appreciated, thanks,
>>
>|||Hi Mr Softgui,
You can extend the DataParameter Extension to create your own types and then
cast them back into the (object) value property.
What do you think Teo?
Peter.
"softgui" wrote:
> Hi,
> I am submitting a report to be run via sending the reportname and some
> parameters via the RS WebService. Behind this I have a DPE. This all works
> fine when there are no parameters. when I do pass params though, I have a
> problem.
> Trouble is that I cannot seem to lay my hands on the passed parameter values
> (I presume a collection ?) when in my implementation class of the IDbCommand,
> IDbCommandAnalysis interfaces
> How can I obtain these params in the DPE ?
> any help would be appreciated, thanks,
>

Data not being displayed.

Hi,
I have just installed MS SQL in my computer and I am trying to run MSSQL and Visual studio for the first time. However I have run into aproblem that I can't solve. Basically the problem is that the data Ifetch from the database is not being displayed/bound to the ddl I haveon the page.
I have used VS to configure the sqlDataAdapter, DataSet, sqlConnection,and the sqlCommands. Basically the data is being fetched from theNorthwind sample database. From the preview option on thesqlDataAdapter I am able to preview the information from the database.So it seems to work. I have also configured the ddl settings so thatthe Categories table is the datasource. I have also set the ddl.DataTextField = "CategoryName"; ddl.DataValueField = "CategoryID";
When I compiled and run the page, no error messages are generated, butthe ddl is still completely blank and I can't understand why.
Does anybody know how I can solve this?? :s

Om Sri Sai Ram

Did you callDataBind method.

Thanks,

Ram

|||Hi,
I have just double-checked that I had called DataBind, and itturns out that I had. However, I had forgotten to fill the DataSet:
this.sqlDataAdapter1.Fill(this.dataSet11);
Now I get an error message:
Login failed for user 'DISABLED\ASPNET'|||Om Sri Sai Ram

Are u using windows authentication? If yes, check whether ASPNET account has pemission to access the table.
If you still have problems, post the code.
Thanks,
Ram

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.

> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.

> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.

> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.

> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.
> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data Mining Add-in error

Hello,

I am getting error messages when trying to run some of the features in the Data Mining addin for Excel. The error message says:

"Could not load file or assembly 'Microsoft.DataWarehouse, version=9.0.242.0, Culture-neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."

Some features work fine (ex: highlight exceptions), but others don't (ex: detect categories).

Any clue?

Configuration:

- SQL Server with SP2 and hot fix build 3054

- Excel 2007

- Windows XP all in one box

Regards,

Joao

Did you install a CTP version of SQL Server SP2? Probably the November 2006 or December 2006 CTP versions? If so, then this thread might contain the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1296235&SiteID=1

If this does not help, please let us know

data miner, i think?

hi not sure if i'm in right forum or not! but i have been getting a 'cannot find server' message for about 2 months now. i run AVG software and Ad-Aware SE and Spybot to try and combat viruses and adaware and spyware. usually what happens is i am using the internet and then this 'cannot find server' message, although the internet connection is still there. then i run all my software, find no viruses but Ad-Aware finds tracking cookies, data miners. so i quarantine them and restart the laptop and then everything seems to work ok, for a while anyway. i have restored the laptop a few times. although now, i am having the same problem and message but not finding anything on the system; i am very, very confused.

is there anything i can do to stop this happening, i feel like i'm going mad.
Unless you're trying to connect to SQL Server Analysis Services, this isn't the right forum. Sorry. The only way you can get our DM software is if you buy (or download a trial version of) SQL Server.

Data incorrectly repeating itself

I have run into a strange issue that I believe is a SQL Reporting Services issue.

I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.

The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.

When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.

What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.

I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.

If anyone has run into this and found a work around, let me know.

Thank you,

T.J.

Just bumping this thread on a Monday morning after posting late on a Friday afternoon.

I am wondering if anyone has run into this issue, and if so if there have been any resolutions or work arounds.

Thank you,

T.J.

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?
Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Derived Table

I am looking to run two different queries.

the first one returns about 6000 numbers. i.e 123454, 15432, 2343545 etc

My second query i want to use the results from the first query as a where clause?

i.e

where Number in ('123454','15432','2343545')

Clearly i dont want to type out 6000 numbers in a query. How do i use the data generated in the first query as the where criterea in the second query.

You can use the following query...(subquery)

Select Numbers From SomeTable -- Which returns 6000 numbers

on your second query..

..

..

Where Number in(Select Numbers From Sometable)