Do regular stored procedures still run when Agent XPs are disabled?

Gurus,
I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
following:
"SQL Server Agent (Agent XPs disabled)"
Agent XPs are extended stored procedures.
http://msdn2.microsoft.com/en-us/library/ms178127.aspx
My question is do regular stored procedures still run?
--
SpinSpin,
Yes, they do. Agent XPs disable really means just the SQL Agent procedures
that SQL Agent uses to get its job done.
RLF
"Spin" <Spin@.invalid.com> wrote in message
news:647jmvF2ae2t8U1@.mid.individual.net...
> Gurus,
> I noticed when my SQL Server Agent is stopped, what appears in SSMS is the
> following:
> "SQL Server Agent (Agent XPs disabled)"
> Agent XPs are extended stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms178127.aspx
> My question is do regular stored procedures still run?
> --
> Spin
>

Do Queries Support Regular Expressions?

If I had the following string in a table how could I find all records that
contained the term 'Business' for example? Would Regular Expressions be
supported in this context? Your comments regarding performance of a category
schema such as this?
// Example
PrivateSector_Business_Services
<%= Clinton GallagherClinton,
You could use the LIKE operator with the % wildcard character as:
WHERE COLUMN LIKE '%Business%'
Optionally you could use a Full-Text index as well.
HTH
Jerry
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>|||Thank you Jerry. That was simple :-)
<%= Clinton Gallagher
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OZqoPlczFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Clinton,
> You could use the LIKE operator with the % wildcard character as:
> WHERE COLUMN LIKE '%Business%'
> Optionally you could use a Full-Text index as well.
> HTH
> Jerry
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||For what it's worth, you can make real regular expression query searches via
calls to external COM libraries.
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
However, if using the native LIKE operator satisfies your requirement, then
use that instead.
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>

Do not display the result of my long text string, approx about 400 characters

I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
......If you are using the MS Query Analyzer from the SQL 2000 Client Tools, you can set the maximum column length. Select Tools | Options | Results and the length control is near the middle of the sheet.

-PatPsql

Do lots of COUNTs

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULLsome ddl and sample data would help...read the hint sticky at the top of the forum...but I'll give it a shot

bit, would you like a result set of many rows or a single row

Also, why don't you use a sproc?|||Apologies:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

Sample data attached.

I'd like the results as a single row with 6 columns: one for each of the queries.

I'm not using a sproc because I'm lazy and haven't got round to taking it out of my ASP and putting it into one yet. And I don't know how to put all those SQL queries into one proc.|||Something like

select
sum(case when(id like'123%')then 1 else 0 end) Count1
,sum(case when([name] like'sys%')then 1 else 0 end) Count2
from sysobjects|||Do you want something like this....BTW I am not sure...

CREATE PROCEDURE CountTot

AS

SELECT
(SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years') as Totq3,
(SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL) as Totq4,
(SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy')as Totq5,
(SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes') as Totq6,
(SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes') as ToTq7,
(SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz|||This will scan the table only once
SELECT
sum(case when q3='5 years +' OR q3='2 - 4 years' then 1 else 0 end) as Totq3
,sum(case when q4<>'' AND q4 IS NOT NULL then 1 else 0 end) as Totq4
,sum(case when q5='Unhappy' then 1 else 0 end) as Totq5
,sum(case when q6='Yes' then 1 else 0 end) as Totq6
,sum(case when q7='Yes' then 1 else 0 end) as ToTq7
,sum(case when q8<>'' AND q8 IS NOT NULL then 1 else 0 end) as Totq8
FROM tblQuiz

That's exactly what I want, thankyou :) Now to try and figure out how it works :confused: :D|||Was that not equivalent to what I had posted ? :(|||Was that not equivalent to what I had posted ? :(nope, quite different :)

Do I use case or coalesce or something else?

Hi all!
I'm runnnig the following query:
declare @.Action int
set @.Action = 2
SELECT * FROM estates
WHERE
((@.Action!=1)OR(
est_ZipCode BETWEEN 12000 AND 12999 OR
est_ZipCode BETWEEN 14000 AND 14999
))AND
((@.Action!=2)OR(
est_ZipCode BETWEEN 16000 AND 16999
))AND
((@.Action!=3)OR(
est_ZipCode BETWEEN 11000 AND 11999 OR
est_ZipCode BETWEEN 13000 AND 13999 OR
est_ZipCode BETWEEN 15000 AND 15999
))AND
((@.Action!=4)OR(
est_ZipCode BETWEEN 17000 AND 19999
))
and it runs much, much slower then:
SELECT * FROM estates
WHERE est_ZipCode BETWEEN 16000 AND 16999
/*equivalent to action 2*/
Why is that? Is there a better way of solving this, perhaps with case
or coalesce?
Any help appreciated,
NiclasIn order to get the best plan you will either need to use dynamic sql or a
number of if else statements.
Your problem stems from the optimiser not knowing the value of @.Action so it
must optimiser the whole query.
Have you tried UNION ALL, not experimented with that; the dynamic sql would
be the neatest in my opinion but has some requirements for security on the
base tables rather than just exec permission on the stored proc.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137422736.659781.271560@.o13g2000cwo.googlegroups.com...
> Hi all!
> I'm runnnig the following query:
> declare @.Action int
> set @.Action = 2
> SELECT * FROM estates
> WHERE
> ((@.Action!=1)OR(
> est_ZipCode BETWEEN 12000 AND 12999 OR
> est_ZipCode BETWEEN 14000 AND 14999
> ))AND
> ((@.Action!=2)OR(
> est_ZipCode BETWEEN 16000 AND 16999
> ))AND
> ((@.Action!=3)OR(
> est_ZipCode BETWEEN 11000 AND 11999 OR
> est_ZipCode BETWEEN 13000 AND 13999 OR
> est_ZipCode BETWEEN 15000 AND 15999
> ))AND
> ((@.Action!=4)OR(
> est_ZipCode BETWEEN 17000 AND 19999
> ))
> and it runs much, much slower then:
> SELECT * FROM estates
> WHERE est_ZipCode BETWEEN 16000 AND 16999
> /*equivalent to action 2*/
> Why is that? Is there a better way of solving this, perhaps with case
> or coalesce?
> Any help appreciated,
> Niclas
>|||"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137422736.659781.271560@.o13g2000cwo.googlegroups.com...
> Hi all!
> I'm runnnig the following query:
> declare @.Action int
> set @.Action = 2
> SELECT * FROM estates
> WHERE
> ((@.Action!=1)OR(
> est_ZipCode BETWEEN 12000 AND 12999 OR
> est_ZipCode BETWEEN 14000 AND 14999
> ))AND
> ((@.Action!=2)OR(
> est_ZipCode BETWEEN 16000 AND 16999
> ))AND
> ((@.Action!=3)OR(
> est_ZipCode BETWEEN 11000 AND 11999 OR
> est_ZipCode BETWEEN 13000 AND 13999 OR
> est_ZipCode BETWEEN 15000 AND 15999
> ))AND
> ((@.Action!=4)OR(
> est_ZipCode BETWEEN 17000 AND 19999
> ))
> and it runs much, much slower then:
> SELECT * FROM estates
> WHERE est_ZipCode BETWEEN 16000 AND 16999
> /*equivalent to action 2*/
> Why is that? Is there a better way of solving this, perhaps with case
> or coalesce?
> Any help appreciated,
> Niclas
I agree with Tony in part.
I had problems with one query (that contained a few ANDs and ORs) that was
solved by spliting it in 2 (or more) and using Union (or Union All). Try
this first. If this doesn't work, try writing 4 separate queries handling
each value of @.Action and use a series of IFs.
I would not go the dynamic SQL way.|||Thanks for your responses,
since the query contains about 20 actions and some other conditions
also, I think dynamic SQL sounds like the best solution.
Thanks again,
Niclas
Is there no way to build a switch statement inside the where-clause?|||"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137426449.243655.33600@.g49g2000cwa.googlegroups.com...
> Thanks for your responses,
> since the query contains about 20 actions and some other conditions
> also, I think dynamic SQL sounds like the best solution.
> Thanks again,
> Niclas
> Is there no way to build a switch statement inside the where-clause?
No control-of-flow inside a Select statement.
CASE is possible, of course.|||It's a common problem with UI that have a filter option, you need to change
your query according to the parameters the user has filtered on.
Doing it in one big SELECT with CASE or OR's won't give a very good query
plan, often very poor and general.
This is where we need to use either IF ELSE or dynamic SQL, the IF ELSE
route has the problem of duplicate code and if there are lots of filters
then you could end up with 20, 30 or more IF ELSE's which is a lot to
maintain.
There is help at hand with the old dynamic SQL security on base table
requirement in SQL Server 2005 now.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eU7tHJsGGHA.1312@.TK2MSFTNGP09.phx.gbl...
> "tonicvodka" <tonicvodka@.hotmail.com> wrote in message
> news:1137426449.243655.33600@.g49g2000cwa.googlegroups.com...
> No control-of-flow inside a Select statement.
> CASE is possible, of course.
>|||The standard syntax is <> instead of the stolen C syntax != for
non-equal. Your code and the sample you gave do not quite match. I
think that you want somethign like this.
SELECT * -- production code should have column names
FROM Estates
WHERE CASE @.action
WHEN 1
THEN CASE WHEN est_zipcode BETWEEN 12000 AND 12999
THEN 'T'
WHEN est_zipcode BETWEEN 14000 AND 14999
THEN 'T' ELSE 'F' END
WHEN 2
THEN CASE WHEN est_zipcode BETWEEN 16000 AND 16999
THEN 'T' ELSE 'F' END
WHEN 3
THEN CASE WHEN est_zipcode BETWEEN 11000 AND 11999
THEN 'T'
WHEN est_zipcode BETWEEN 13000 AND 13999
THEN 'T'
WHEN est_zipcode BETWEEN 15000 AND 15999
THEN 'T' ELSE 'F' END
WHEN 4
THEN CASE WHEN est_zipcode BETWEEN 17000 AND 19999
THEN 'T' ELSE 'F' END
etc.
ELSE 'F' END = 'T';
The CASE expression checks the WHEN clauses in order, so put the most
likely case first. This applies to the intermost WHENs also. I have
used both forms of the CASE expression and nested them, but you might
want to look up the syntax so you feel comfortable with it. The 'T'
and 'F' stand for True and False. It is a common SQL porgramming trick
for complex logic. You might also want to look at Logic Gem or another
decision table tool that will help with this kind of problem.
This lets the optimizer do its job on a single query, without having to
kludge dynamic SQL, use UNIONs or resort to procedural code.|||>> In order to get the best plan you will either need to use dynamic SQL or
a number of if else statements. <<
You really are trapped in an OO and 3GL mindset and cannot think
declaratively!
I used to tell students that it takes a year of SQL programming to have
the"declarative revelation" so that you stop thinking in terms of
dynamic on-the-fly coding, IF-THEN_ELSE control flows, etc. and just
write a single query.
I am not sure how good SQL-2005 is with CASE expressions, but DB2 does
a fine job.|||> I am not sure how good SQL-2005 is with CASE expressions, but DB2 does
> a fine job.
No it does not, and you just don't get it. In the real world "one size
fits all" approach (just write a single query) may perform very poorly.
Just think: consider a simple query
select <some columns> from some_table where some_date between
@.date_from and @.date_to
If there is a ( non-clustered for SQL Server) index on some_date, which
plan should the optimizer choose? It could scan the table (tablespace
scan in DB2 parlance), or it could access the table through the index.
Neither plan is the best for all the cases. Recompiling the query makes
perfect sense if teh table is big.
just think: consider a search form with several search conditions for
the user to fill, like zip, last name, fisrt name, whatever. Dynamic
SQL is most likely to run much better that generic stufff like
where zip=@.zip or @.zip is null
and last_name=@.last_name or @.last_name is null
and the reason is simple: all real life major RDBMS are well capable of
choosing different plans for diffrent parameter values, DB2 included.
For big tables the performance price of choosing only one plan for all
the cases may be way too steep.
So yes, in simpler cases we have if statements, in more complex ones we
just go for dynamic SQL.|||On 16 Jan 2006 10:31:21 -0800, --CELKO-- wrote:

>The standard syntax is <> instead of the stolen C syntax != for
>non-equal. Your code and the sample you gave do not quite match. I
>think that you want somethign like this.
>SELECT * -- production code should have column names
> FROM Estates
> WHERE CASE @.action
> WHEN 1
> THEN CASE WHEN est_zipcode BETWEEN 12000 AND 12999
> THEN 'T'
> WHEN est_zipcode BETWEEN 14000 AND 14999
> THEN 'T' ELSE 'F' END
> WHEN 2
> THEN CASE WHEN est_zipcode BETWEEN 16000 AND 16999
> THEN 'T' ELSE 'F' END
> WHEN 3
> THEN CASE WHEN est_zipcode BETWEEN 11000 AND 11999
> THEN 'T'
> WHEN est_zipcode BETWEEN 13000 AND 13999
> THEN 'T'
> WHEN est_zipcode BETWEEN 15000 AND 15999
> THEN 'T' ELSE 'F' END
> WHEN 4
> THEN CASE WHEN est_zipcode BETWEEN 17000 AND 19999
> THEN 'T' ELSE 'F' END
> etc.
> ELSE 'F' END = 'T';
>The CASE expression checks the WHEN clauses in order, so put the most
>likely case first. This applies to the intermost WHENs also. I have
>used both forms of the CASE expression and nested them, but you might
>want to look up the syntax so you feel comfortable with it. The 'T'
>and 'F' stand for True and False. It is a common SQL porgramming trick
>for complex logic. You might also want to look at Logic Gem or another
>decision table tool that will help with this kind of problem.
>This lets the optimizer do its job on a single query, without having to
>kludge dynamic SQL, use UNIONs or resort to procedural code.
Hi Joe,
But the single query tha the optimizer gets to do its job on doesn't
have any comparison predicate of the form
"<column name> <comp op> <expression>"
or "<expression> <comp op> <column name>".
The only thing the optimizer can do is to sigh and settle for a complete
table scan.
Using 4 SELECT statements and an IF ELSE tree or (shudder) dynamic SQL
would enable the optimizer to check if an index on est_zipcode can be
sed. That would result in faster execution.
Hugo Kornelis, SQL Server MVPsql

Do I need these OLEDB properties when opening a database?

Howdy folks!

If I set the following three properties when creating a database, do I need to set the same properties when I open the same database later?

sscedbprop[0].dwPropertyID = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_I4;
sscedbprop[0].vValue.intVal = 100;

sscedbprop[1].dwPropertyID = DBPROP_SSCE_MAX_DATABASE_SIZE;
sscedbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[1].vValue.vt = VT_I4;
sscedbprop[1].vValue.intVal = 4091;

sscedbprop[2].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
sscedbprop[2].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[2].vValue.vt = VT_BOOL;
sscedbprop[2].vValue.boolVal = (a_bEncrypted==TRUE)?VARIANT_TRUE:VARIANT_FALSE;

Thanks!

BTW, why is the max size not 4096?

The first two properties can vary from session to session, but not the third. When you create a database with encryption, it must always be opened with that property (AFAIK). The auto shrink threshold and max database size have no impact on the database file format and thus don't persist.

The 4096 vs 4091 question also baffles me...

|||Awesome, thanks Joao.

Do I need DatePart or similar

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @.dt_src_date) AND
(src_terrier.Areacode = @.chr_div) AND
(src_centre_list.Portfolio_no = @.vch_portfolio_no) AND
(src_centre_list.propcat = @.vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @.dt_src_date) AND (src_date < DATEADD(month, 1, @.dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@.dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

|||

Problem Resolved by using

WHERE (src_date >= CONVERT(varchar(6), @.dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @.dt_src_date, 112) + '01') - 1)

Regards

sql

Do I need DatePart or similar

I have a function that uses the following statement in it

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref

WHERE (src_terrier.datadate = @.dt_src_date) AND
(src_terrier.Areacode = @.chr_div) AND
(src_centre_list.Portfolio_no = @.vch_portfolio_no) AND
(src_centre_list.propcat = @.vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)

The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows

src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.

How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.

Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.

Anybody confused by that , cause I am!

Regards

yes, one way is using DatePart to compare the day,month and year separately.
Then you do not have to change the input format of mmddyyyy to ddmmyyyy.

Ex:

SET DATEFORMAT mdy
DECLARE @.Inputdate datetime
SET @.Inputdate = '04/28/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate1 datetime
SET @.Tabledate1 = '28/04/2006'

SET DATEFORMAT dmy
DECLARE @.Tabledate2 datetime
SET @.Tabledate2 = '01/04/2006'

if ( datepart(dd,@.Inputdate)= datepart(dd,@.Tabledate1) AND
datepart(mm,@.Inputdate)= datepart(mm,@.Tabledate1) AND
datepart(yyyy,@.Inputdate)= datepart(yyyy,@.Tabledate1) )
PRINT 'SAME DATES'
ELSE
PRINT 'DIFFERENT DATES'

If ( datepart(mm,@.Tabledate1)= datepart(mm,@.Tabledate2) AND datepart(yyyy,@.Tabledate1)= datepart(yyyy,@.Tabledate2) )
PRINT 'SAME MONTH AND YEAR'
ELSE
PRINT 'DIFF MONTH/YEAR'

GO

|||

The query search condition (WHERE clause) doesn't quite match what you are explaining. If you want to ignore the day part in the datetime value then you can use an expression like below:

select dateadd(day, 1 - day(CURRENT_TIMESTAMP), CURRENT_TIMESTAMP )

Please substitute CURRENT_TIMESTAMP with the date column or variable as required. Note that if you use functions or expressions on columns then the index if any on that column cannot be used to efficiently seek to the value. So performance of the query might suffer if your primary search/join condition is based on the date column. For example, you can modify search condition below:

(src_terrier.datadate = @.dt_src_date)

to:

(dateadd(day, 1 - day(src_terrier.datadate), src_terrier.datadate) = @.dt_src_date)

Assuming that @.dt_src_date is specified as YYYYMM01 then it will match fine. Another way to optimize the search is to do the following:

(src_terrier.datadate >= @.dt_src_date and

src_terrier.datadate < dateadd(month, 1, @.dt_src_date) - 1)

In this search condition, we use a lower and upper bound for the date values which is month start and month end. So it will match any date value in "datadate" column for the specific YYYYMM combination in @.dt_src_date.

|||

In addition to my previous question relating to datepart

I have two entries in a table

site_ref src_date budget_rent budget_occ

AE 01/04/2006 123456.0000 69

AE 01/05/2006 371540.2432 74

When I run the following query

SELECT site_ref, budgeted_net_rent, budgeted_occupancy
FROM src_tbl_rental
WHERE (src_date >= @.dt_src_date) AND (src_date < DATEADD(month, 1, @.dt_src_date) - 1)

I get a result set that includes both of the above lines, when what I was trying to achieve was just the value for the first one only. I know that the date is entered in the database as 01/04/2006 as part of an import from excel. Not sure wther it is actually yyyy-mm-dd etc in reality, but from a visual point of view it certainly is dd/mm/yyyyy.

If I manually enter the (@.dt_src_date) parameter of 01/04/2006 then it returns only the one line. The problem I have is that the parameter that I mentioned actually needs to provide dd/mm/yyyy data for the bulk of the other functions etc, I want this query to only return a value that will always the mm/yyyy element of the parameter value and prefixed by 01/.

Anybody help me out here?

Regards

|||

Problem Resolved by using

WHERE (src_date >= CONVERT(varchar(6), @.dt_src_date, 112) + '01') AND (src_date < DATEADD(month, 1, CONVERT(varchar(6), @.dt_src_date, 112) + '01') - 1)

Regards

Do I need an actual mailbox for SQLServerAgent (2000)

I'm following a book and trying to set up MSSQL to email me upon failures
and successful backups. MSSQL and SQLAgent are installed and running under
a network service user account I call "SqlServerService" which is infact a
network user.
In Ent Mgr, I'm going to Management >> Operators and I'm trying to add a new
operator. I add the email (me) account to be emailed and click Test to send
a test message. It returns an error of "error 22022: SqlServerAgent Error:
The SQLServerAgent mail session is not running; check the mail profile
and/or the SqlServerAgent service start up account..."
The SqlServerAgent is indeed running and the Agent Properties is running
under this network account I created.
Any ideas? The account "SqlServerService" doesn't have an actual Exchange
Server mailbox. I just thought it would be possible (as shown in the book)
to send FROM this account. Do I actually need to create the mailbox account
too?
Short answer: Yes.
Long answer and how: [How to configure SQL Mail]
http://support.microsoft.com/default...b;en-us;263556
HTH, Jens Suessmeyer.

Do I need an actual mailbox for SQLServerAgent (2000)

I'm following a book and trying to set up MSSQL to email me upon failures
and successful backups. MSSQL and SQLAgent are installed and running under
a network service user account I call "SqlServerService" which is infact a
network user.
In Ent Mgr, I'm going to Management >> Operators and I'm trying to add a new
operator. I add the email (me) account to be emailed and click Test to send
a test message. It returns an error of "error 22022: SqlServerAgent Error:
The SQLServerAgent mail session is not running; check the mail profile
and/or the SqlServerAgent service start up account..."
The SqlServerAgent is indeed running and the Agent Properties is running
under this network account I created.
Any ideas? The account "SqlServerService" doesn't have an actual Exchange
Server mailbox. I just thought it would be possible (as shown in the book)
to send FROM this account. Do I actually need to create the mailbox account
too?Short answer: Yes.
Long answer and how: [How to configure SQL Mail]
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556
HTH, Jens Suessmeyer.

Do I need an actual mailbox for SQLServerAgent (2000)

I'm following a book and trying to set up MSSQL to email me upon failures
and successful backups. MSSQL and SQLAgent are installed and running under
a network service user account I call "SqlServerService" which is infact a
network user.
In Ent Mgr, I'm going to Management >> Operators and I'm trying to add a new
operator. I add the email (me) account to be emailed and click Test to send
a test message. It returns an error of "error 22022: SqlServerAgent Error:
The SQLServerAgent mail session is not running; check the mail profile
and/or the SqlServerAgent service start up account..."
The SqlServerAgent is indeed running and the Agent Properties is running
under this network account I created.
Any ideas? The account "SqlServerService" doesn't have an actual Exchange
Server mailbox. I just thought it would be possible (as shown in the book)
to send FROM this account. Do I actually need to create the mailbox account
too?Short answer: Yes.
Long answer and how: [How to configure SQL Mail]
http://support.microsoft.com/defaul...kb;en-us;263556
HTH, Jens Suessmeyer.sql

Do I need a warehouse?

Hi chaps,

I have the following scenario and aren't sure of what approach I need to take to solve it. Any advice would be welcome (im very new to warehousing).

Scenario: I have a high transaction OLTP database (SQL Server) containing 20+ inter-related tables. It is all highly normalised. Over a year I am looking to accumulate something like 10+ million rows of information (most likely more).

The database is used extensively by an application (ASP.NET) which allows users to view (and in some cases update) the information in the database. The majority of the data is inserted by BizTalk.

Once an entire transaction is completed (which may take for example 2 weeks), it will not be changed in the future, but will still need to be reported on. I.e. View All Orders in March 07

Problem: As my data volumes grow, the performance of this database will undoubtedly begin to crawl, for both selects and inserts/updates. To solve this, I would like to move the completed transactions into an achieve (i.e. my datawarehouse) that will store the information in a denormalised state which the application can continue to use to perform queries (which I hope will be very fast).

I understand this isn't a typical use for a warehouse, namely I am not summing numerical figures or interesting in aggregating information based on measures/hierarchies.

Is what I want to do a correct approach, or can someone provide a better solution?

Any help would be greatly appreciated,
TM

Hello. You can build your data warehouse as highly normalized as you prefer. You can control the aggregation of information in one or several data marts that you can build from views or in tables in a new database.

Data warehouse and data marts(aggregated denormalized) informations do not have to be the same.

In the data warehouse you will normally remove some tables in order to keep the number of relations down to a minimum. You will also translate OLTP-columns to more business like names.

Still, if you build a copy of the source system in the data warehouse you will be able to remove historical transactions from the source. That will help performance in the source system.

Also, analytical queries will not compete for resources with transaction registration.

HTH

Thomas Ivarsson

|||

So Thomas,

If I were to build a denormalized warehouse could I then build a UDM cube on top of it without building an OALP fact/dimension type structure?

|||

Hello JohnSLG. Yes, I think you can. You can build cubes on top of OLTP-systems.

You can have all information like dimensions, hierarchies and measures in one table and build a cube on that.

Best practice, however, points to a data mart on top of a data warehouse.

Kind Regards

Thomas Ivarsson

|||

Thank you for your response, greatly appreciated.

Could you also point me to where I can get a grounding in general warehousing theory? Then build onto this with Analysis Serviecs.

Kind regards

TM

|||

Here is a good link: www.kimballgroup.com

Regards

Thomas Ivarsson

do I have a torn page?

Ive been getting alerts like the following all morning:
DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
COMMENT: (None)
JOB RUN: (None)
What do I need to do here?
TIA, ChrisRHi
Look like corruption.
Run DBCC CHECKDB on the database and get last nights backup out, you might
need to restore it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <noemail@.bla.com> wrote in message
news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
> file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>|||Here's some additional info about those situations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in file
> 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>

do I have a torn page?

Ive been getting alerts like the following all morning:
DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
COMMENT: (None)
JOB RUN: (None)
What do I need to do here?
TIA, ChrisRHi
Look like corruption.
Run DBCC CHECKDB on the database and get last nights backup out, you might
need to restore it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <noemail@.bla.com> wrote in message
news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
> file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>|||Here's some additional info about those situations:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file
> 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>[/vbcol]

do I have a torn page?

Ive been getting alerts like the following all morning:
DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
COMMENT: (None)
JOB RUN: (None)
What do I need to do here?
TIA, ChrisR
Hi
Look like corruption.
Run DBCC CHECKDB on the database and get last nights backup out, you might
need to restore it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <noemail@.bla.com> wrote in message
news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in
> file 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>
|||Here's some additional info about those situations:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:Ow4eB1ZTFHA.2680@.tk2msftngp13.phx.gbl...
> Ive been getting alerts like the following all morning:
> DESCRIPTION: Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset 0x0000111c264000 in file
> 'D:\MSSQL\MSSQL\Data\production_Data.mdf'.
> COMMENT: (None)
> JOB RUN: (None)
>
> What do I need to do here?
> TIA, ChrisR
>
sql

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:

>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:

>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

DNS required ?

Hi All!
I facing the following problem:
Sometimes i get error while connecting to my SQLServer instance from client
machine "Timeout expired". I found it occurs when my DNS server is not reach
eable from client machine. But i connecting through alias, created by Client
network utility, where i s
pecify directly IP-address, NOT DNS-name ! I tried to connect through QA, os
ql - resuls the same.
Why it requires DNS ? What parameter i missing ?
Telnet on IP-address port works normally.
Server - NT 4.0 SP6 PDC, SQLServer 2000 Std Ed
Client - Windows 98 or NT 4.0IF you try to connect to a server named London, the name London needs to be
resolved to the IP address. This is the function that DNS provides...
However if you use theIP address yourself, DNS does not have to do anything.
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Slava Melnikov" <vasya000@.yandex.ru> wrote in message
news:F6CBFA12-2F13-452B-B848-5BA82228D0A1@.microsoft.com...
> Hi All!
> I facing the following problem:
> Sometimes i get error while connecting to my SQLServer instance from
client machine "Timeout expired". I found it occurs when my DNS server is
not reacheable from client machine. But i connecting through alias, created
by Client network utility, where i specify directly IP-address, NOT DNS-name
! I tried to connect through QA, osql - resuls the same.
> Why it requires DNS ? What parameter i missing ?
> Telnet on IP-address port works normally.
> Server - NT 4.0 SP6 PDC, SQLServer 2000 Std Ed
> Client - Windows 98 or NT 4.0|||What version of the SQL Server ODBC driver (sqlsrv32.dll) is installed on
the client. There is a issue with wsome of the earlier MDAC 2.6 SQL Server
ODBC drivers. If your driver version is 2000.8.0.194 you may be running
into this problem. It also occurs on some later versions as well. If you
install one of the more recent versions of MDAC, such as MDAC 2.7 then the
problem I am describing will not occur. Of course this assumes that you are
using the version of MDAC that has the problem.
When you ping the ip address does the response come back quickly or dies it
take some time?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||O-oh!
sqlsrv32.dll version is really 2000.8.0.194.
Thanks for help, Rand !
I should install latest service pack or new MDAC it's
enough ?
ping works normally

>--Original Message--
>What version of the SQL Server ODBC driver
(sqlsrv32.dll) is installed on
>the client. There is a issue with wsome of the earlier
MDAC 2.6 SQL Server
>ODBC drivers. If your driver version is 2000.8.0.194 you
may be running
>into this problem. It also occurs on some later versions
as well. If you
>install one of the more recent versions of MDAC, such as
MDAC 2.7 then the
>problem I am describing will not occur. Of course this
assumes that you are
>using the version of MDAC that has the problem.
>When you ping the ip address does the response come back
quickly or dies it
>take some time?
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

DMX Query examples

Can you give an example on how exactly to write each of the following? (I am clearly not a programmer :))

TopCount ( <table expr>,<rank expr>,<n-items>) =

TopPercent ( <table expr>,<rank expr>,<percent>) =

PredictTimeSeries ( <table expr>,<n1>,<n2>) =

PredictAssociation ( <table expr>,<n>) =

Thanks,

Carrie

SELECT TopCount(PredictHistogram(MyAttribute),$Probability,3), // returns top 3 values by probability
TopPercent(PredictHistogram(MyAttribute),$Support,0.20) // returns top values that contain at least 20% of the total support
FROM MyModel
PREDICTION JOIN
...

SELECT PredictTimeSeries(MyNestedTableTimeSeriesColumn, 2, 5) // predicts steps 2-5 in the series
FROM MyTimeSeriesModel

SELECT PredictAssociation(MyNestedTable, 5) // returns top 5 associated items
FROM My Model
PREDICTION JOIN
...

|||

So I did.....

select TopCount(PredictHistogram(Returnwithplay),$Support,.20)

From [REC FT All Cube DT]

PREDICTION JOIN

And received the following ( I think the query completed with errors):

Executing the query ...

Parser: The end of the input was reached.

Execution complete

|||And I tried

select PredictTimeSeries([Slot Theo Win],2,5) FROM [Player Market]

and received the following error:

Executing the query ...

Error (Data mining): The specified DMX column was not found in the context at line 1, column 26.

Execution complete

My time series is set up as the following.......

Player Market:

Date Dim Predict

Day Key

Rated Slot Theo Predict Only

Rated Table Theo Predict Only

Player Market Dim Key

|||

PredictTimeSeries only works on models using the Microsoft_Time_Series algorithm. Also, it doesn't seem that you have a column called "Slot Theo Win" in your data set.

It seems you would need a model somewhat like

CREATE MINING MODEL [Player Market]
(
DateDim DATE KEY TIME,
PlayerMarketDim TEXT KEY,
RatedSlotTheo DOUBLE CONTINUOUS PREDICT_ONLY,
RatedSlotTheo DOUBLE CONTINUOUS PREDICT_ONLY
) USING Microsoft_Time_Series

sql

DMO SQLServer->Connect failed for user name with special characters

Hi,
If I give a user name or password having special character (mainly
semi-colon) then I cannot connect to the SQL Server. Following is the error:
Login failed for user 'a`~!@.#$%^&*()_+|-=[]'.
TCP Provider: An existing connection was forcibly closed by the
remote host.
Communication link failure
Invalid connection string attribute
However, this error was coming in my Cpp code. I tried with VB, I
works. Also, I got same error from Enterprise Manager.
Is this a defect or known behavior in DMO?
TIA,
AjeyActual user name was a`~!@.#$%^&*()_+|-=[];',./{}:"<>?
"Ajey" <ajey5@.hotmail.com> wrote in message
news:ujnaqXcjFHA.1428@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If I give a user name or password having special character (mainly
> semi-colon) then I cannot connect to the SQL Server. Following is the
> error:
> Login failed for user 'a`~!@.#$%^&*()_+|-=[]'.
> TCP Provider: An existing connection was forcibly closed by the
> remote host.
> Communication link failure
> Invalid connection string attribute
> However, this error was coming in my Cpp code. I tried with VB, I
> works. Also, I got same error from Enterprise Manager.
> Is this a defect or known behavior in DMO?
>
> TIA,
> Ajey
>|||/ is an escape character in cpp, you have to replace / with // in your
string for it to work properly.
hth
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Ajey" <ajey5@.hotmail.com> wrote in message
news:OqwRXccjFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Actual user name was a`~!@.#$%^&*()_+|-=[];',./{}:"<>?
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:ujnaqXcjFHA.1428@.TK2MSFTNGP10.phx.gbl...
>|||I think it's mainly due to semi-colon. The error message displays the user
names till the character before the semi-colon. Also, same username &
password does not work while registering the sql server using Enterprise
Manager or connecting through Query Analyser.
- Ajey
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:uMKZSCkjFHA.3900@.TK2MSFTNGP10.phx.gbl...
>/ is an escape character in cpp, you have to replace / with // in your
>string for it to work properly.
> hth
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:OqwRXccjFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||Can you post your cpp code that can reproduce the issue?
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Ajey" <ajey5@.hotmail.com> wrote in message
news:uZXxuFojFHA.2644@.TK2MSFTNGP09.phx.gbl...
>I think it's mainly due to semi-colon. The error message displays the user
>names till the character before the semi-colon. Also, same username &
>password does not work while registering the sql server using Enterprise
>Manager or connecting through Query Analyser.
> - Ajey
> "Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
> news:uMKZSCkjFHA.3900@.TK2MSFTNGP10.phx.gbl...
>|||Also try to put quotes around the user name (try both single quotes and
double quotes, one of them should be able to handle special characters)
hth
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Ajey" <ajey5@.hotmail.com> wrote in message
news:uZXxuFojFHA.2644@.TK2MSFTNGP09.phx.gbl...
>I think it's mainly due to semi-colon. The error message displays the user
>names till the character before the semi-colon. Also, same username &
>password does not work while registering the sql server using Enterprise
>Manager or connecting through Query Analyser.
> - Ajey
> "Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
> news:uMKZSCkjFHA.3900@.TK2MSFTNGP10.phx.gbl...
>|||strSQLLoginName = L"abc;xyz"
strSQLLoginPassword = L"abc"
HRESULT hrConnect = spServer->Connect( rkstrCurrentServerName.c_str(),
strSQLLoginName.c_str(), strSQLLoginPassword.c_str() );
This code also gives the same error. Also, if i paste user name as abc;xyz
in Enterprise Manager or Query Analyzer I get the same error.
The same thing works with ADO. I had to just escape the special characters
in the connection string. While can pass the username & password directly to
the ADOConnection->Connect() method. But same is not working on DMO.
Thanks.
- Ajey
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:OrYsYoujFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Also try to put quotes around the user name (try both single quotes and
> double quotes, one of them should be able to handle special characters)
> hth
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:uZXxuFojFHA.2644@.TK2MSFTNGP09.phx.gbl...
>