Indexes and Deadlocks: example

Showing posts with label example. Show all posts

Wednesday, March 28, 2012

Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
HerbertHi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:
> Hi,
> any one explain what information is available in log and what in data file.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert

InfoPath 2007 and endpoint yielding HTTP authentication failed.

I created a HelloWorld like example using InforPath 2007 and endpoint with SQL Server 2005. Very simple example.

I created a stored procedure that took in one parameter, a datetime field. This parameter is then inserted into a db table that has two columns, an id column and a datetime column.

I took the store procedure just discribed to create an endpoint. Cool. The WSDL is showing, I'm feeling good.

I go to InfoPath and make it so that submit a datetime to the webmethod associated with the endpoint. The data table shows one more row has been added and it is the datetime I sent from InfoPath. YEA!! I go to the Event View and see that I have an error message. The message is HTTP authenticaiton failed. [CLIENT: 10.8.2.26]. Bummer!!

This does not make sense to me. I am able to update the targeted database table but I get an exception. I do not like exceptions nor warnings without an explanation. Does anyone have an idea why this is happening?

The endpoint code looks like: <code snippet>

CREATE ENDPOINT [GeraldTesting_endpoint]
AUTHORIZATION
STATE=STARTED
AS HTTP (PATH=N'/testEndPoint',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'*',
CLEAR_PORT = 8080,
COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'testEndPoint'
( NAME=N'[geraldstest].[dbo].[Gerald_Test_Proc]'
, SCHEMA=STANDARD,
FORMAT=ALL_RESULTS),
BATCHES=ENABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60,
DATABASE=N'geraldstest', NAMESPACE=N'http://tempuri.org', SCHEMA=STANDARD, CHARACTER_SET=XML)

</code snippet>

I had to give up for the project I am currently working because of time constraints. But in the future, for out-of-the-box functionality for proof-of-concept/prototyping, I would like to use endpoints.

Hi Gerald,

If I understand your situation correctly, your InfoPath is able to connect to the SQL SOAP endpoint and submit the data correctly, but then you are seeing a HTTP authentication failure in the Event View. It sounds like InfoPath itself is not raising any errors or exceptions. Is this Event View part of InfoPath? Or, is this the SQL Errorlog?

I am assuming you've verified that the timestamp of the HTTP authentication failure entry is the same time when you submitted the data with InfoPath. Also, I am assuming that the connection setting to SQL Server in the InfoPath form is done through Web Services instead of a direct SQL connection.

If you are able to retrieve the WSDL document from SQL using InfoPath, the same user will have access to submit data to the endpoint. If the InfoPath form as opened by another user, then you will need to grant the enduser access.

It would be really helpful if you can provide the complete error message and the whether the user has been granted access to the endpoint.

From what you've mentioned, the insert did work, so there's either a major bug in the SQL product where we are incorrectly logging bad messages or else there is some other configuration issue.

Jimmy

Infopath

I am using SBS 2003 Premium with SQL Server installed and
updated to SP3a. When I connect to the example database
in infopath 2003 the "Submit to database" option is there
when I insert a submit button. When I created my own
database, the option is not there. Any ideas?
Thanks
I remember running into this issue a couple of months ago when I was
struggling to learn Infopath, but I forgot what the answer is -- it
has to do with Infopath, not SQL Server. I'd post on the
microsoft.public.infopath newsgroup, where you're more likely to get
responses from people who use Infopath regularly :-)
--Mary
On Wed, 12 May 2004 07:30:47 -0700, "Brent Goff"
<brentgoff@.goffcom.net> wrote:

>I am using SBS 2003 Premium with SQL Server installed and
>updated to SP3a. When I connect to the example database
>in infopath 2003 the "Submit to database" option is there
>when I insert a submit button. When I created my own
>database, the option is not there. Any ideas?
>Thanks

Friday, March 23, 2012

Inequality Comparision on Dimension members

Hi,

Take foodmart sales cube as an example, I want to find out store sizes that are bigger than 35000. I have tried.

1. select filter([Store Size in SQFT].[Store Sqft].members, STRTOVALUE([Store Size in SQFT].currentmember.name) > 35000) on columns from sales

Output: An MDX expression was expected. An empty expression was specified.

select filter([Store Size in SQFT].[Store Sqft].members, [Store Size in SQFT].currentmember.name > '35000')

on columns from sales

Output: lexicological comparison, not what I want.

select filter([Store Size in SQFT].[Store Sqft].members, [Store Size in SQFT].currentmember > 30000) on columns from sales

Output: comparing with default measure instead.

I guess I am still having the SQL mindset. Any help is really appreciated.

you should use function Val in order to convert string property into number.

select
filter([Store Size in SQFT].[Store Sqft].members, Val([Store Size in SQFT].currentmember.name) > 35000)
on columns
from sales

sql

Wednesday, March 21, 2012

INDEXTYPE equivalent ?

Hi,

There is a concept called INDEXTYPE in Oracle, Is there any equivallent for the same in SQL Server ?

Oracle example :

CREATE INDEX index1 ON test_table(col1) INDEXTYPE IS CTXSYS.CONTEXT

What is the SQL Server equivalent query for the above ?

Thanks,
SamI'm not enirely sure as to what exactly indextypes in Oracle are. As far as I know its a more narrow definition of how the column/s in the index could be used in when/like clauses and is build to that. Searches such as when client = 'Kaiowas', like 'Kai%s' or 'K%w%s'. Ms SQLServer does not have this kind of performance tuning as far as I know, perhaps Ms SQLServer depends more on it's internal query-plans.sql

Indexing XML

I have an table-valuated function with XML as parameter. I generate table from that XML. Here is an example of parameter:

<root>
<an>
<anid>5433</anid>
<tix>64</tix>
<dataid>43</dataid>
<datavalue>bc84</datavalue>
</an>
...
</root>

function body begins with:
WITH X(anid, tix, dataid, datavalue, cnt) AS
(
SELECT
new.col.query('./anid').value('.', 'nvarchar(100)') as anid,
new.col.query('./tix').value('.', 'nvarchar(100)') as tix,
new.col.query('./dataid').value('.', 'nvarchar(100)') as dataid,
new.col.query('./datavalue').value('.', 'nvarchar(100)') as datavalue,
cnt = (
SELECT count(*) as cnt
FROM @.xmldata.nodes('/root/an') new(col)
)
FROM
@.xmldata.nodes('/root/an') new(col)
)
....

after what I use X table in joins. The problem is that the X table has no indexes and joins are very slow :[. Is there any possibility to improve performance? That is function and I cannot create index.

thanks a lot

if ur using sqlserver2005 , there is an option for xml index there, though u need to have a primary index defined before u can do it...try it out...

reply if it works out ..

Friday, February 24, 2012

Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the information using some
of the new dynamic management views (dm_db_missing_indexes_%).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and release some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and
>> release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>|||You are in for a lot more work, then and the question is whether you want to do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse the text for the
execution plans to see what indexes were used, compare that to the indexes in the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for 2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribió en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such information, so you'd have
>> to do it using a Profiler trace or similar. 2005 does, and you can look at the information using
>> some of the new dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> I'm sorry...
>> I'm using SQL Server 2000...
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> escribió en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server? SQL Server 2000 doesn't keep track of such
>> information, so you'd have to do it using a Profiler trace or similar.
>> 2005 does, and you can look at the information using some of the new
>> dynamic management views (dm_db_missing_indexes_%).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eduardo Sicouret" <esicouret> wrote in message
>> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> Anyone knows how to determine if an index is being used.
>> For example: I have several tables which have multiple indexes.
>> I want to know which of them are not being used so I can delete them
>> and release some space.
>> Is there a stored procedure to get these statistics?
>> TIA
>> Regards,
>> Eduardo Sicouret
>>
>>
>

Sunday, February 19, 2012

indexes

where can i find a example on how to
build a script to reindex all my tables
Tks
DavePDaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

where can i find a example on how to
build a script to reindex all my tables

http://www.google.se/search?hl=sv&q...=S%C3%B6k&meta=
You could also look in Books Online for the definition of sysindexes and sysobjects if you are on SQL 2000 or sys.objects and sys.indexes if you
are on SQL 2005.

One thing to be careful with is that if fragmentation is below 30%, there is
not so much to gain with reindexing, so adding some intelligence to handle
this may be worth it. I don't have any example at hand, but the hits on
Google may give you something.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thank you much erland
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99AAF398D854AYazorman@.127.0.0.1...

Quote:

Originally Posted by

DaveP (dvs_bis@.sbcglobal.net) writes:
>

Quote:

Originally Posted by

>where can i find a example on how to
>build a script to reindex all my tables

>
http://www.google.se/search?hl=sv&q...=S%C3%B6k&meta=
>
You could also look in Books Online for the definition of sysindexes and
sysobjects if you are on SQL 2000 or sys.objects and sys.indexes if you
are on SQL 2005.
>
One thing to be careful with is that if fragmentation is below 30%, there
is
not so much to gain with reindexing, so adding some intelligence to handle
this may be worth it. I don't have any example at hand, but the hits on
Google may give you something.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 28, 2012

Information in Log and Data file

InfoPath 2007 and endpoint yielding HTTP authentication failed.

Infopath

Friday, March 23, 2012

Inequality Comparision on Dimension members

Wednesday, March 21, 2012

INDEXTYPE equivalent ?

Indexing XML

Friday, February 24, 2012

Indexes

Indexes

Sunday, February 19, 2012

indexes

Indexes and Deadlocks

Blog Archive

About Me