(b60010eb08b2) in sp_lock

What does these values (b60010eb08b2) mean under the resource column for a
KEY type when one runs sp_lock ?Hassan
It's a hashed value of the key which is locked. There is no way to reverse
engineer to determine the value of the key.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e$Lt0gWDEHA.2908@.TK2MSFTNGP09.phx.gbl...
> What does these values (b60010eb08b2) mean under the resource column for a
> KEY type when one runs sp_lock ?
>|||Kalen,
I was going over through your e book on Locking and Blocking and was
confused on the % threshold before the lock escalates.
On page 46 , you said 40% and on page 40, you mentioned 3%. Am i not
following something here ? Please clarify
thanks
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eXhmYnXDEHA.712@.tk2msftngp13.phx.gbl...
> Hassan
> It's a hashed value of the key which is locked. There is no way to reverse
> engineer to determine the value of the key.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e$Lt0gWDEHA.2908@.TK2MSFTNGP09.phx.gbl...
a
>|||i also wanna reverse the key value by hashed value ,
because i wanna know the keys locked !

>--Original Message--
>Hassan
>It's a hashed value of the key which is locked. There is
no way to reverse
>engineer to determine the value of the key.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>news:e$Lt0gWDEHA.2908@.TK2MSFTNGP09.phx.gbl...
resource column for a
>
>.
>|||Not doable... sorry
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:b6bd01c40d8e$1df948d0$a001280a@.phx.gbl...
> i also wanna reverse the key value by hashed value ,
> because i wanna know the keys locked !
>
> no way to reverse
> resource column for a|||Hi Hassan
If you read the entries sentences again, you'll see they see two separate
things. On page 40, it says 3% of memory, on page 46, it says 40% of buffer
pool.
The 40% figure is much more exact. (The 3% figure is just one that a dev
tossed at me when I asked when lock esacation occurs, and he said it was
related to the total memory used, something like 3%. So on page 40 I did say
ABOUT 3%.)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uDT$S8XDEHA.3348@.TK2MSFTNGP11.phx.gbl...
> Kalen,
> I was going over through your e book on Locking and Blocking and was
> confused on the % threshold before the lock escalates.
> On page 46 , you said 40% and on page 40, you mentioned 3%. Am i not
> following something here ? Please clarify
> thanks
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eXhmYnXDEHA.712@.tk2msftngp13.phx.gbl...
reverse
for
> a
>

'ß' = 'ss' - are they equal?

Hi,
I've set up an NVARCHAR table column having a UNIQUE constraint. This = UNIQUE constraint is supposed to keep double entries from being entered = into that column. So far so good...
In my new project I'm quite baffled about the fact that SQL Server 2000 = seems to believe that '=DF' and 'ss' are identical!
This assumption is only true for special cases. The German words = "Ma=DFe" and "Masse", for instance, have totally different meanings = ("measures", "mass"/"crowd").
How can I set up my table column definition (COLLATE perhaps?) in SQL = Server 2000 to have it regard '=DF' and 'ss' being distinct characters?
Your help is quite appreciated.
Best regards,
Axel DahmenAlex,
http://www.ureader.com/message/1343195.aspx has a conversation on this
subject you may find useful.
Most languages (especially English) have a fair number of homographs, which
are words spelled the same but with different pronunciations and meanings.
"Wind" can mean 'moving air' or 'tightening a spring', etc. I think that
you have probably run into this with your German words.
RLF
"Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
Hi,
I've set up an NVARCHAR table column having a UNIQUE constraint. This UNIQUE
constraint is supposed to keep double entries from being entered into that
column. So far so good...
In my new project I'm quite baffled about the fact that SQL Server 2000
seems to believe that 'ß' and 'ss' are identical!
This assumption is only true for special cases. The German words "Maße" and
"Masse", for instance, have totally different meanings ("measures",
"mass"/"crowd").
How can I set up my table column definition (COLLATE perhaps?) in SQL Server
2000 to have it regard 'ß' and 'ss' being distinct characters?
Your help is quite appreciated.
Best regards,
Axel Dahmen|||Great, Russel, thanks!
From the conversation you mentioned I've learned that according to DIN =and ANSI-SQL "=DF" equals to "ss". But in fact this isn't true anymore =since German has been updated to new spelling rules a few years ago.
With the spelling reform we've got they changed the meaning of "=DF". It =has now become a letter of its own and is no more a replacement for ="ss". They are even planning to introduce a new, "capital =DF" letter.
With the old spelling rules words like "Ma=DFe" were equal to "Masse". =But nowadays this isn't true anymore.
Are there any plans to update ANSI-SQL and SQL-Server collation rules =according to current German rules?
TIA,
Axel Dahmen
"Russell Fields" <russellfields@.nomail.com> schrieb im Newsbeitrag =news:e6VOWBxQIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Alex,
> > http://www.ureader.com/message/1343195.aspx has a conversation on this =
> subject you may find useful.
> > Most languages (especially English) have a fair number of homographs, =which > are words spelled the same but with different pronunciations and =meanings. > "Wind" can mean 'moving air' or 'tightening a spring', etc. I think =that > you have probably run into this with your German words.
> > RLF
> > > "Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message > news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> > I've set up an NVARCHAR table column having a UNIQUE constraint. This =UNIQUE > constraint is supposed to keep double entries from being entered into =that > column. So far so good...
> > In my new project I'm quite baffled about the fact that SQL Server =2000 > seems to believe that '=DF' and 'ss' are identical!
> > This assumption is only true for special cases. The German words ="Ma=DFe" and > "Masse", for instance, have totally different meanings ("measures", > "mass"/"crowd").
> > How can I set up my table column definition (COLLATE perhaps?) in SQL =Server > 2000 to have it regard '=DF' and 'ss' being distinct characters?
> > Your help is quite appreciated.
> > Best regards,
> Axel Dahmen
> > >=20|||Axel,
I imagine from the ANSI-SQL docuements that SQL is tracking with the ISO
standard on this. I see that the ISO collation standard was updated in
2007, but I am not willing to pay to find out what changed.
http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=44872
So, I don't know who to ask to get a good answer for you about what SQL is
doing. (Maybe Joe Celko has an update.)
In the meantime, if is worth the extra work for you, you can use this
workaround.
SELECT * FROM Composers
WHERE LastName = 'Strauß'
AND LastName COLLATE Latin1_General_BIN = 'Strauß'
The extra binary collation compare will ensure that you get 'Strauß' and do
not get ' Strauss'.
RLF
"Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
news:%23EJWJu4QIHA.5400@.TK2MSFTNGP04.phx.gbl...
Great, Russel, thanks!
From the conversation you mentioned I've learned that according to DIN and
ANSI-SQL "ß" equals to "ss". But in fact this isn't true anymore since
German has been updated to new spelling rules a few years ago.
With the spelling reform we've got they changed the meaning of "ß". It has
now become a letter of its own and is no more a replacement for "ss". They
are even planning to introduce a new, "capital ß" letter.
With the old spelling rules words like "Maße" were equal to "Masse". But
nowadays this isn't true anymore.
Are there any plans to update ANSI-SQL and SQL-Server collation rules
according to current German rules?
TIA,
Axel Dahmen
"Russell Fields" <russellfields@.nomail.com> schrieb im Newsbeitrag
news:e6VOWBxQIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Alex,
> http://www.ureader.com/message/1343195.aspx has a conversation on this
> subject you may find useful.
> Most languages (especially English) have a fair number of homographs,
> which
> are words spelled the same but with different pronunciations and meanings.
> "Wind" can mean 'moving air' or 'tightening a spring', etc. I think that
> you have probably run into this with your German words.
> RLF
>
> "Axel Dahmen" <KeenToKnow@.newsgroup.nospam> wrote in message
> news:OP2PAHwQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I've set up an NVARCHAR table column having a UNIQUE constraint. This
> UNIQUE
> constraint is supposed to keep double entries from being entered into that
> column. So far so good...
> In my new project I'm quite baffled about the fact that SQL Server 2000
> seems to believe that 'ß' and 'ss' are identical!
> This assumption is only true for special cases. The German words "Maße"
> and
> "Masse", for instance, have totally different meanings ("measures",
> "mass"/"crowd").
> How can I set up my table column definition (COLLATE perhaps?) in SQL
> Server
> 2000 to have it regard 'ß' and 'ss' being distinct characters?
> Your help is quite appreciated.
> Best regards,
> Axel Dahmen
>
>

"Table '?' could not be loaded. Column '?' does not exist"

Hi,

I am getting message while modifying one table in SSMS like "Table '?' could not be loaded. Column '?' does not exist" . Howevery particular column exist in table and even i am able to modify it with T-SQL.

Any Idea?

Please post the error message, including the stack trace from the error window.

Jens K. Suessmeyer.

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

Jens,

Thanks for your reply. I got the work around. I created another table with same schema with different name and also created all FKs and constrains with different name and it worked.

"Table ''?'' could not be loaded. Column ''?'' does not exist"

Hi,

I am getting message while modifying one table in SSMS like "Table '?' could not be loaded. Column '?' does not exist" . Howevery particular column exist in table and even i am able to modify it with T-SQL.

Any Idea?

Please post the error message, including the stack trace from the error window.

Jens K. Suessmeyer.

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

Jens,

Thanks for your reply. I got the work around. I created another table with same schema with different name and also created all FKs and constrains with different name and it worked.

"Row yielded no match during lookup" when using 2 columns in Lookup

I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail.

Why is this happening?

I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged:

07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0033.mdmp
07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No

Why am I getting this error with "Enable Memory Restriction"?

Yeah, well, also note that SSIS lookups are CaSE sensitive. Running a query in Management Studio may not be.|||I don't know why your are getting that error. Using partial cache won't fix the no-match error. By default, the lookup component treats no matches as errors; to get around taht you need to open the Lookup component and change error output to redirect-error; that way the no matches will go to the error output (red arrow)|||Your problem is probably that SQL uses different comparison rules than the SSIS Lookup does. SQL Server's default collation is case-insensitive, so it will match FooBar with FOOBAR. Lookup will see these as different strings and won't match them. Unfortunately, you can't control the Lookup behavior like you can SQL's. Best you can do is probably convert everything to upper or lower case to achieve consistency.
|||The case is exactly the same but I will try converting them all to same case all the same|||When I redirect to a flat file, ALL the records (about 900) of them get redirected. The column being looked up is a required field so I need to be able to lookup the value|||

newbie1a wrote:

When I redirect to a flat file, ALL the records (about 900) of them get redirected. The column being looked up is a required field so I need to be able to lookup the value

Watch out for trailing spaces too. Recommend you trim both the source rows and the reference rows.
|||It appears I had not trimmed everything. It works now after applying trims to the source data and the lookup. I do not need to enable memory restriction but any idea why I get "AdjustTokenPrivileges () failed (00000514)" when I do?|||Help!!! Now I am intermittently getting this "AdjustTokenPrivileges () failed (00000514)" error. What is causing this?|||

newbie1a wrote:

Help!!! Now I am intermittently getting this "AdjustTokenPrivileges () failed (00000514)" error. What is causing this?

I've never seen that error. First thing I do when a component goes goofy is to delete it and drop in a new one.

"Predictable Column Name" and "Predictable Value"

Hi,

I tried to utilize Mining Accuracy to analyze my models.

Mining Model Predictable Column Name Predictable Value
--
NaiveBayesModel
DecisionTreeModel

When I want to choose an option for "Predictable Column Name" on NaiveBayesModel row or DecisionTreeModel row, there is no option/value/choice on the drop box. There is also no option/value/choice for the "Predictable Value" column.

When I clicked "Lift Chart" tab to see the accuracy chart, it gave me this error message: "No mining models are selected for comparison."

The models are as follows:

(

[CustKey] KEY,

[Gender] TEXT DISCRETE,

[BikeModels] TABLE PREDICT_ONLY

(

[Model] TEXT KEY

)

)

Please assist!

Mary

The accuracy charts provided in the product only work for scalar predictable columns. In your model, the target is the BikeModels nested table, which cannot be handled by the tools|||This is because you don't have any predictable case level columns and the accuracy charts don't support nested table outputs. Since the prediction in your case is a nested table containing multiple bike models, there's no standard way to compare the accuracy of two models.|||

Ok,

If there is no standard way for predicted columns in a nested table, is there any non-standard way or perhaps using DMX query without relying the tool to measure the accuracy of the models? Is there any online resources that discuss more about this issue?

Mary

|||

I posted this response to a similar question - hope this helps. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=110238&SiteID=1

The issue in accuracy regarding nested tables is in the definition of the question. "Accuracy" means that the model predicts correctly what you are asking. With nested tables, it's not entirely clear what an "accurate" prediction would be. For instance, it could be that a recommendation occurred within the top n answers, or it could be that the predicted value for a particular nested key was correct, etc.

Let us know if the linked response helps, or if there are other areas of "accuracy" you want to explore.

Thanks

-Jamie

|||

Yeah, it helps.Thanks!

Mary

"Predictable Column Name" and "Predictable Value"

Hi,

I tried to utilize Mining Accuracy to analyze my models.

Mining Model Predictable Column Name Predictable Value
--
NaiveBayesModel
DecisionTreeModel

When I want to choose an option for "Predictable Column Name" on NaiveBayesModel row or DecisionTreeModel row, there is no option/value/choice on the drop box. There is also no option/value/choice for the "Predictable Value" column.

When I clicked "Lift Chart" tab to see the accuracy chart, it gave me this error message: "No mining models are selected for comparison."

The models are as follows:

(

[CustKey] KEY,

[Gender] TEXT DISCRETE,

[BikeModels] TABLE PREDICT_ONLY

(

[Model] TEXT KEY

)

)

Please assist!

Mary

The accuracy charts provided in the product only work for scalar predictable columns. In your model, the target is the BikeModels nested table, which cannot be handled by the tools|||This is because you don't have any predictable case level columns and the accuracy charts don't support nested table outputs. Since the prediction in your case is a nested table containing multiple bike models, there's no standard way to compare the accuracy of two models.|||

Ok,

If there is no standard way for predicted columns in a nested table, is there any non-standard way or perhaps using DMX query without relying the tool to measure the accuracy of the models? Is there any online resources that discuss more about this issue?

Mary

|||

I posted this response to a similar question - hope this helps. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=110238&SiteID=1

The issue in accuracy regarding nested tables is in the definition of the question. "Accuracy" means that the model predicts correctly what you are asking. With nested tables, it's not entirely clear what an "accurate" prediction would be. For instance, it could be that a recommendation occurred within the top n answers, or it could be that the predicted value for a particular nested key was correct, etc.

Let us know if the linked response helps, or if there are other areas of "accuracy" you want to explore.

Thanks

-Jamie

|||

Yeah, it helps.Thanks!

Mary

"Pending" status for Data-driven Subscription

An issue with the 'Status' column of the 'Subscription' tab in the Report Manager.
I created Data-driven Subscriptions for some of the reports, and I scheduled the reports using shared schedule. The scheduling is working fine and all the reports are getting generated daily.
The issue is with the status of the report subscriptions under subscription tab, it shows as PENDING even after the report is generated.
I restarted the ReportServer database, it didn't served my purpose. The status never changes .

When do the subscription status changes to PENDING?
What needs to be done to change the PENDING status?
Is this the bug with the Report Manager?

Appreciate your timely help. TIA
Did you find a solution for this? I'm having the same issue.|||I didn't find any solution yet. If you find any, do share the same .
|||

There seems to be a SOAP Solution, But I've never used SOAP (Except in the Shower..LOL..sorry lame joke) , I'm having a hard time getting the MSDN Example to work. I can't get vb to recognize the ReportingService2005 class. heres the link if you want to take a look. http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.listsubscriptions(SQL.90).aspx

Code Snippet

Dim rs As New ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim extSettings As ExtensionSettings
Dim desc As String
Dim active As ActiveState
Dim status As String
Dim eventType As String
Dim matchData As String
Dim values As ParameterValue() = Nothing
Dim subscriptions As Subscription() = Nothing
Dim extensionParams As ParameterValueOrFieldReference() = Nothing

Try
subscriptions = rs.ListSubscriptions("/SampleReports/Employee Sales Summary", "myDomain\myUserName")

If Not (subscriptions Is Nothing) Then
' Retrieve properties for the first subscription in the list.
rs.GetSubscriptionProperties(subscriptions(0).SubscriptionID, extSettings, desc, active, status, eventType, matchData, values)

Console.WriteLine("Description: {0}", desc)
Console.WriteLine("Status: {0}", status)
Console.WriteLine("EventType: {0}", eventType)
Console.WriteLine("matchData: {0}", matchData)
Console.WriteLine("Extension: {0}", extSettings.Extension)

extensionParams = extSettings.ParameterValues

If Not (extensionParams Is Nothing) Then
Dim extensionParam As ParameterValueOrFieldReference
For Each extensionParam In extensionParams
Console.WriteLine((CType(extensionParam, ParameterValue).Name + ": " + CType(extensionParam, ParameterValue).Value))
Next extensionParam
End If

If Not (values Is Nothing) Then
Dim pv As ParameterValue
For Each pv In values
Console.WriteLine("Name: {0}", pv.Name)
Console.WriteLine("Value: {0}", pv.Value)
Next pv
End If
End If

Catch e As SoapException
Console.WriteLine(e.Detail.OuterXml)
End Try

|||

Any help on this issue?

|||

Are you sure that this isn't a client-side caching issue in your browser? That would potentially out-last restarting the reportserver database.

IAC if you are seeing "Pending" In your browser, whether it is browser-cached or webapp-cached this would not have much to do with restarting the database. Even server-side it could be the ReportManager webapp that is caching the value. IOW, if it is a server-side cache, and if you restart the RS instance (rather than the database) you would see the change.

>L<

"Pending" status for Data-driven Subscription

An issue with the 'Status' column of the 'Subscription' tab in the Report Manager.
I created Data-driven Subscriptions for some of the reports, and I scheduled the reports using shared schedule. The scheduling is working fine and all the reports are getting generated daily.
The issue is with the status of the report subscriptions under subscription tab, it shows as PENDING even after the report is generated.
I restarted the ReportServer database, it didn't served my purpose. The status never changes .

When do the subscription status changes to PENDING?
What needs to be done to change the PENDING status?
Is this the bug with the Report Manager?

Appreciate your timely help. TIA
Did you find a solution for this? I'm having the same issue.|||I didn't find any solution yet. If you find any, do share the same .
|||

There seems to be a SOAP Solution, But I've never used SOAP (Except in the Shower..LOL..sorry lame joke) , I'm having a hard time getting the MSDN Example to work. I can't get vb to recognize the ReportingService2005 class. heres the link if you want to take a look. http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.listsubscriptions(SQL.90).aspx

Code Snippet

Dim rs As New ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim extSettings As ExtensionSettings
Dim desc As String
Dim active As ActiveState
Dim status As String
Dim eventType As String
Dim matchData As String
Dim values As ParameterValue() = Nothing
Dim subscriptions As Subscription() = Nothing
Dim extensionParams As ParameterValueOrFieldReference() = Nothing

Try
subscriptions = rs.ListSubscriptions("/SampleReports/Employee Sales Summary", "myDomain\myUserName")

If Not (subscriptions Is Nothing) Then
' Retrieve properties for the first subscription in the list.
rs.GetSubscriptionProperties(subscriptions(0).SubscriptionID, extSettings, desc, active, status, eventType, matchData, values)

Console.WriteLine("Description: {0}", desc)
Console.WriteLine("Status: {0}", status)
Console.WriteLine("EventType: {0}", eventType)
Console.WriteLine("matchData: {0}", matchData)
Console.WriteLine("Extension: {0}", extSettings.Extension)

extensionParams = extSettings.ParameterValues

If Not (extensionParams Is Nothing) Then
Dim extensionParam As ParameterValueOrFieldReference
For Each extensionParam In extensionParams
Console.WriteLine((CType(extensionParam, ParameterValue).Name + ": " + CType(extensionParam, ParameterValue).Value))
Next extensionParam
End If

If Not (values Is Nothing) Then
Dim pv As ParameterValue
For Each pv In values
Console.WriteLine("Name: {0}", pv.Name)
Console.WriteLine("Value: {0}", pv.Value)
Next pv
End If
End If

Catch e As SoapException
Console.WriteLine(e.Detail.OuterXml)
End Try

|||

Any help on this issue?

|||

Are you sure that this isn't a client-side caching issue in your browser? That would potentially out-last restarting the reportserver database.

IAC if you are seeing "Pending" In your browser, whether it is browser-cached or webapp-cached this would not have much to do with restarting the database. Even server-side it could be the ReportManager webapp that is caching the value. IOW, if it is a server-side cache, and if you restart the RS instance (rather than the database) you would see the change.

>L<

"Pending" status for Data-driven Subscription

An issue with the 'Status' column of the 'Subscription' tab in the Report Manager.
I created Data-driven Subscriptions for some of the reports, and I scheduled the reports using shared schedule. The scheduling is working fine and all the reports are getting generated daily.
The issue is with the status of the report subscriptions under subscription tab, it shows as PENDING even after the report is generated.
I restarted the ReportServer database, it didn't served my purpose. The status never changes .

When do the subscription status changes to PENDING?
What needs to be done to change the PENDING status?
Is this the bug with the Report Manager?

Appreciate your timely help. TIA
Did you find a solution for this? I'm having the same issue.|||I didn't find any solution yet. If you find any, do share the same .
|||

There seems to be a SOAP Solution, But I've never used SOAP (Except in the Shower..LOL..sorry lame joke) , I'm having a hard time getting the MSDN Example to work. I can't get vb to recognize the ReportingService2005 class. heres the link if you want to take a look. http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.listsubscriptions(SQL.90).aspx

Code Snippet

Dim rs As New ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim extSettings As ExtensionSettings
Dim desc As String
Dim active As ActiveState
Dim status As String
Dim eventType As String
Dim matchData As String
Dim values As ParameterValue() = Nothing
Dim subscriptions As Subscription() = Nothing
Dim extensionParams As ParameterValueOrFieldReference() = Nothing

Try
subscriptions = rs.ListSubscriptions("/SampleReports/Employee Sales Summary", "myDomain\myUserName")

If Not (subscriptions Is Nothing) Then
' Retrieve properties for the first subscription in the list.
rs.GetSubscriptionProperties(subscriptions(0).SubscriptionID, extSettings, desc, active, status, eventType, matchData, values)

Console.WriteLine("Description: {0}", desc)
Console.WriteLine("Status: {0}", status)
Console.WriteLine("EventType: {0}", eventType)
Console.WriteLine("matchData: {0}", matchData)
Console.WriteLine("Extension: {0}", extSettings.Extension)

extensionParams = extSettings.ParameterValues

If Not (extensionParams Is Nothing) Then
Dim extensionParam As ParameterValueOrFieldReference
For Each extensionParam In extensionParams
Console.WriteLine((CType(extensionParam, ParameterValue).Name + ": " + CType(extensionParam, ParameterValue).Value))
Next extensionParam
End If

If Not (values Is Nothing) Then
Dim pv As ParameterValue
For Each pv In values
Console.WriteLine("Name: {0}", pv.Name)
Console.WriteLine("Value: {0}", pv.Value)
Next pv
End If
End If

Catch e As SoapException
Console.WriteLine(e.Detail.OuterXml)
End Try

|||

Any help on this issue?

|||

Are you sure that this isn't a client-side caching issue in your browser? That would potentially out-last restarting the reportserver database.

IAC if you are seeing "Pending" In your browser, whether it is browser-cached or webapp-cached this would not have much to do with restarting the database. Even server-side it could be the ReportManager webapp that is caching the value. IOW, if it is a server-side cache, and if you restart the RS instance (rather than the database) you would see the change.

>L<

"Multiselect" way for delete invalid references?

Hi everyone,

When you've got -for example- a derived column task linked with a Flat file and then you change any field and come back to derived column task again you have select that field

with two possibilities:

1-‘Leave as invalid column reference’ /

2-‘Delete invalid column reference’

It’s easy when just one is affected but when you have eight or ten is very tedious do the same one by one. Any way for to do same but selecting more than one?

Thanks for any input,

Yes, select all columns and then in the dropdown box at the bottom of the dialog select which option you want to apply. It will apply that to all the selected columns.

-Jamie

|||thanx

"large value" data type(bcp)

I want to store some binary things(pic and so on), so I create a table which contain a a "varbinary" data-type column.

but 1. I used OPENROWSET to insert the large file in this table. 2. I used master..xp_cmdshell to retrieve data out as a file. One strange thing happened: the size of the input and output is really different(output is 1k bigger than the input file).

and it seems that the file is broken with different file format......

I really don't know why....

Any help would be appriciated.....

kavin

Could you please post a simple repro script? What command or utility are you using with xp_cmdshell to create the file? Is it BCP or OSQL with SELECT or BCP with queryout option and so on?|||

yes, sure.

EXEC master..xp_cmdshell 'bcp " SELECT column1 FROM Products where id =1111111117 " queryout fileName -n -U sa -P -S yourserver.

Is it any problem?

Anyway, thanks.

BR,

kavin

|||and when I compare the two files, I found that it just the 8-bytes at the beginning of file are extra filled. So I'm afraid something wrong with the created file?

"Jump to Report" hyperlink only if there is data?

I basically want to disable the "Jump to Report" ability in my report if the column has a sum of 0. If the sum is greater than 0 I want it to allow hyperlinking.

Here is what I have in the textbox "Jump to Report" section (where you select the sub report from the dropdown)

=Iif(Fields!Not_Patched.Value > 0, "subRPT_Enterprise Vulnerability Report By PLUS", False)

This works, however I get the following Warning when running the report...

[rsInvalidExpressionDataType] The DrillthroughReportName expression used in chart ‘chart1.DataPoint.ActionInfo.Action’ returned a data type that is not valid.
Preview complete -- 0 errors, 1 warnings

Can anyone assist in why I'm getting this warning? I have tried to search this forum and google but no answers...

Instead of False use Nothing:

=Iif(Fields!Not_Patched.Value > 0, "subRPT_Enterprise Vulnerability Report By PLUS", Nothing)

Thanks, Donovan.

"Error converting data type nvarchar to real" using LIKE operator on CHAR column

View1 has a CHAR column of CharCol1. While using LIKE operator on this column, I receive below error.

Select * from View1 Where CharCol1 Like '%77%'

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to real.

But all of below syntax works:

Select * from View1 Where CharCol1 like N'%77%'

Select * from View1 Where CharCol1 Like '77'

Select * from View1 Where CharCol1 = '77'

Why does SQL 2005 treat '%77%' as real? This used to be working in SQL 2000.

It works for me in SQL 2005. Are you sure your view's CharCol1 is really a character column, and hasn't been casted as real?|||

Actually the view was being used in SQL 2000 and after SQL 2005 upgrade, this error has started.

Last view used a view and that view also uses another view. There are three levels. But one of the views used in the first view is then joined in the last view which gives this error. Of course, this is not good programming but it was working in the previous version. Now, I have removed this join and carried the necessary columns from the first level thus I eliminated double joining of same view at the last level. This solved the problem.

Actually, the problem is that, I had tested this view and they were ok. But once I give a Where clause using a column from the double joined view, I get this error. Unfortunately, we encountered this error after upgrade and on the live system we had to fix it.

What I understand is that somehow in SQL 2005, we can get errors if the coding is not good and the views should be tested by giving "Where" clauses using all possible columns.

Hope this is clear.

Does anyone have comment on this?

<Long Text> errors on a 'text char(16)' column

Our SQL Server database is being utilized by many Applix/iHelpdesk
forms. On a cloumn with a 'text char(16)' datatype, we recently found
2 <Long Text> errors on a table. The Applix developers said their
application was locked up. My DBArtisan can disply only two records.
The third row showed a <Long Text> error in the Enterprise Manager.
Was my DBArtisan also locked up?
What kinds of Applix data format caused the the <Long Test> error?
How to solve this problem?
TIA.
JeffreyThat's not an error; that's Enterprise Manager not displaying TEXT/NTEXT
properly. Use a better tool (e.g. Query Analyzer) and that problem goes
away.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeffrey" <cjeffwang@.yahoo.com> wrote in message
news:c61d4674.0411221528.487ebc2@.posting.google.com...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> forms. On a cloumn with a 'text char(16)' datatype, we recently found
> 2 <Long Text> errors on a table. The Applix developers said their
> application was locked up. My DBArtisan can disply only two records.
> The third row showed a <Long Text> error in the Enterprise Manager.
> Was my DBArtisan also locked up?
> What kinds of Applix data format caused the the <Long Test> error?
> How to solve this problem?
> TIA.
> Jeffrey

<Long Text> errors on a 'text char(16)' column

Our SQL Server database is being utilized by many Applix/iHelpdesk
forms. On a cloumn with a 'text char(16)' datatype, we recently found
2 <Long Text> errors on a table. The Applix developers said their
application was locked up. My DBArtisan can disply only two records.
The third row showed a <Long Text> error in the Enterprise Manager.
Was my DBArtisan also locked up?
What kinds of Applix data format caused the the <Long Test> error?
How to solve this problem?
TIA.
Jeffrey
That's not an error; that's Enterprise Manager not displaying TEXT/NTEXT
properly. Use a better tool (e.g. Query Analyzer) and that problem goes
away.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jeffrey" <cjeffwang@.yahoo.com> wrote in message
news:c61d4674.0411221528.487ebc2@.posting.google.co m...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> forms. On a cloumn with a 'text char(16)' datatype, we recently found
> 2 <Long Text> errors on a table. The Applix developers said their
> application was locked up. My DBArtisan can disply only two records.
> The third row showed a <Long Text> error in the Enterprise Manager.
> Was my DBArtisan also locked up?
> What kinds of Applix data format caused the the <Long Test> error?
> How to solve this problem?
> TIA.
> Jeffrey

<Long Text> errors on a 'text char(16)' column

Our SQL Server database is being utilized by many Applix/iHelpdesk
forms. On a cloumn with a 'text char(16)' datatype, we recently found
2 <Long Text> errors on a table. The Applix developers said their
application was locked up. My DBArtisan can disply only two records.
The third row showed a <Long Text> error in the Enterprise Manager.
Was my DBArtisan also locked up?
What kinds of Applix data format caused the the <Long Test> error?
How to solve this problem?
TIA.
Jeffrey
Can you script out this table?
There is no text char(16) data type. There is a text data type and a char
datatype, but no text char(16) data type.
"Jeffrey" <cjeffwang@.yahoo.com> wrote in message
news:c61d4674.0411221515.474c4231@.posting.google.c om...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> forms. On a cloumn with a 'text char(16)' datatype, we recently found
> 2 <Long Text> errors on a table. The Applix developers said their
> application was locked up. My DBArtisan can disply only two records.
> The third row showed a <Long Text> error in the Enterprise Manager.
> Was my DBArtisan also locked up?
> What kinds of Applix data format caused the the <Long Test> error?
> How to solve this problem?
> TIA.
> Jeffrey

<Long Text> display on 'text' datatype (16-byte pointer) column

Our SQL Server database is being utilized by many Applix/iHelpdesk
devlopers and users.
On a cloumn with a 'text'datatype (16-byte pointer), we recently were
informed by the developers that their application was locked up. In
Enterprise Manager, I found 2 <Long Text> displays on a table. My
DBArtisan can disply only two records. The third row showed a <Long
Text> in the Enterprise Manager.
Was my DBArtisan also locked up?
How to solve this problem from Applix side?
TIA.
Jeffrey
Jeffrey,
Yes, what you have in your table is a column defined with the TEXT datatype
that shows up as a 16-byte textpointer. Most likely the cause is a implied
UPDATE statement is being held on the TEXT data by you application Applix
while your application is open and this is causing blocking or "locking up"
the data from access by other applications. You can use SQL Profiler to
confirm exactly what your application is doing and then alter the code.
Regards,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0411222006.571cfacb@.posting.google.c om...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> devlopers and users.
> On a cloumn with a 'text'datatype (16-byte pointer), we recently were
> informed by the developers that their application was locked up. In
> Enterprise Manager, I found 2 <Long Text> displays on a table. My
> DBArtisan can disply only two records. The third row showed a <Long
> Text> in the Enterprise Manager.
> Was my DBArtisan also locked up?
> How to solve this problem from Applix side?
> TIA.
> Jeffrey

<= and comparing strings

Hi,

I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707

What I want to to is to search for log posts that are in between two dates.

I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.

How would I solve this

Best /M

Well, I would suggest you convert that data into a datetime value and you will get a lot more value. But, the problem you are having is that 'A' comes before 'AA' in a sort order. So, 070422 is before any value in your table that has a time portion. So use < 070423 and you will get the values you need:

drop table test

go

create table test

(

timecreated char(16)

)

go

insert into test

select '0704212112538707'

union all

select '0704223112538707'

union all

select '0704212112338707'

union all

select '0704242112338707'

go

select *

from test

where timeCreated >= '070421'

and timeCreated < '070423'

timecreated
-
0704212112538707
0704223112538707
0704212112338707

'0704242112338707' was not returned.

|||You cannot compare strings in this manner and get teh results you are wanting. You will need to convert the field to a datetime for comparisons.
|||

What if you sum 1 day to the final timecreated?. I mean, of you want the '070421' to '070422' you can probe with the '070422' + 1 day -> '070421' to '070423'

<<identity field>>

hi friends,
i have an identity column in my table.(e.x : id )
and it's identity increment is 1.
when i insert a row the id field is 1, and in next record the field is 2....
now , i delete second record(id=2)
and now when i insert a record again , the id column is 3.
i want to record be 2 instead 3.

plz help me.
thanks

This cannot be accomplished "as standard"

When you delete a record, add the id to a "secondhand id" table, then, when you want to insert a new record, see if there is a secondhand id.

The rationale for this approach is that there are no fast methods to iterate the table with id's that may be free when there are large numbers of records

|||

well you can do it but it would not be the best way to do programming......... there can be 2 ways.

Method 1 :

Forget about identity columns.
insert the record by yourself using insert command.
before using insert command retrieve the id (as in your case) of the last record of the table. you can do it like this.
str=select id from <tablename> orderby id
get the results by using this command into a datareader and store the last value in a variable
dim last_id as integer
while dr.read
last_id=dr(0)
end while.
now insert the last_id as the id in your insert statement...

Method 2:

you can reseed(reset) the identity value when you delete a record. i mean to say when you delete a record just reset the identity column to that id(or might be one previous id) for reseting the coulmn see the link below.
http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

hope this could be helpful