Online Data Transform: February 2012

Saturday, February 25, 2012

Only one case table allowed for Time series algortihm?

Hi, all experts here,

Thank you very much for your kind attention.

I just encountered the problem that with Microsoft Time Series algorithm, no more than one case table is allowed? Mining structure is not allowed to contain any nested table?

Or did I miss out anything there? Thank you very much in advance for your kind advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Just found that we can actually use nested table as input column for Time Series algorithm? So what is the problem then?

Thanks again and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

|||You can use a case table and a nested table for the TS algorithm. Can you please explain in more details what the schema of your mining structure (case table, nested table) looks like.

Only members of sysadmin role are allowed to update or delete jobs owned by a different login

Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below

by the way I am a member of the sysadmin group

thanks in advance

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() End of inner exception stack trace

my bad

I need to move this over to SSRS

|||

Moved to RS as requested.

The error though is more general than RS, in fact it is a standard SQL Server Agent message. Unless there is a bug, I'd say you really are not a member of sysadmins, or at least not in the context of the RS WS call that is doing the work on yoru behalf. That may be the issue, the security context used by the WS itself. Perhaps run a profiler trace and see what login is being used, and also what job it is trying to delete. You can then verify the job owner. There should not be a problem as you are managing a RS created job, so RS should be able to delete its own jobs... maybe it is a RS thing?

|||Thanks
your info did the trick-- the account used was not a member of sysadmin-- my bad
thanks again
kam
|||

hi kam,

how did you solved the issue? i've added myself as the sysadmin on the server, but i'm still getting the same error. is there any other sysadmin than the sqladmin? thanks.

|||actually, i've found the answer. whatever account used on the app pool need to have sysadmin, as this is the account that is updating/deleting the report. thanks.

Only members of sysadmin role are allowed to update or delete jobs owned by a different login

by the way I am a member of the sysadmin group

thanks in advance

my bad

I need to move this over to SSRS

|||

Moved to RS as requested.

|||Thanks
your info did the trick-- the account used was not a member of sysadmin-- my bad
thanks again
kam|||

hi kam,

how did you solved the issue? i've added myself as the sysadmin on the server, but i'm still getting the same error. is there any other sysadmin than the sqladmin? thanks.

|||actually, i've found the answer. whatever account used on the app pool need to have sysadmin, as this is the account that is updating/deleting the report. thanks.

only Japanese got error from WebSphere to SQL 2000

Dear all:
Please help.
I have the java code to insert multi-language data from WebSphere in
Unix to MS SQL 2000 by using MS JDBC driver Service Pack 1 Version
2.2.0029.
All other language are ok but not Japanese.
I am sure that I input the correct Japanese in the statement before I
send the insert statement because I put the string into log file to
double check.
Does any one have a clue?
Thanks in advance
dennis_chen_canada@.hotmail.com
Dear all:
It is too bad.
I download and installed the MS SQL JDBC driver SP2.
The result is the same.
The traditional Chinese, simple Chinese, German, Franch are OK.
Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
more info:
- I log the sql statement before calling JDBC and it is correct Japanese letter
- I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
then run the cold fusion page, the result is OK in table
more question:
- How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
It is kind of urgent.
Any help is welcome
|||dennis_chen_canada@.hotmail.com wrote:

> Dear all:
> It is too bad.
> I download and installed the MS SQL JDBC driver SP2.
> The result is the same.
> The traditional Chinese, simple Chinese, German, Franch are OK.
> Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
> more info:
> - I log the sql statement before calling JDBC and it is correct Japanese letter
> - I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
> then run the cold fusion page, the result is OK in table
> more question:
> - How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
> It is kind of urgent.
> Any help is welcome
>
Hi. You would only get support from MS by paying for support. They rarely
answer posts here unless they can identify the poster as a paying support
customer... I suggest you try a commercial driver. Try the DataDirect driver,
because that is likely to indicate how quickly MS will provide a fixed
driver. DataDiarect made MS's free driver and will likely make the next version.
Their commercial driver is their most advanced product, so if that works, it will
show that they know the problem already. If you do buy a commercial driver you
will likely get faster support.
Joe
|||From: Dennis Chen <dennis_chen_canada@.hotmail.com>
To: joeNOSPAM@.bea.com
Subject: Re: only Japanese got error from WebSphere to SQL 2000
Dear Joe:
Thanks for your email.
I will try to get it later on.
Currrently, we are facing the urgent issue that comes from customers.
Do you have any other suggestion.
rgds,
Dennis Chen
Techinical Manager, E-Commerce Div.
www.ulead.com
|||dennis_chen_canada wrote:

> From: Dennis Chen <dennis_chen_canada@.hotmail.com>
> To: joeNOSPAM@.bea.com
> Subject: Re: only Japanese got error from WebSphere to SQL 2000
> Dear Joe:
> Thanks for your email.
> I will try to get it later on.
> Currrently, we are facing the urgent issue that comes from customers.
> Do you have any other suggestion.
> rgds,
> Dennis Chen
> Techinical Manager, E-Commerce Div.
> www.ulead.com
Call Microsoft technical support and pay them to take your case,
but don't expect rapid response because typical MS support only
has a vague idea of what Java/JDBC is, so they would eventually
pass the case on to the folks in MS who talk to the external company
that makes the driver...
I would create a standalone jdbc program that demonstrates the problem
if possible. I would suggest also trying to get support from IBM
Websphere folks. They should have some MS/JDBC experts. If you had
been using BEA's Weblogic, I would have been able to do more, and
would have solved your problem...
Joe Weinstein at BEA

>

Only getting one record in preview mode

I've created a new simple report and I am trying to figure out why I am only getting one page of the report when I am expecting a page per db record.

1. I have a simple query that returns 7 rows of data, 1 column 'name'. They query test good in the Data tab.

2. I have dragged and drop that column to my page on the Design tab

3. I then go to the Preview tab and I only get one page with the name on it.

What am I doing wrong - I expected one page per record from my query.

Thanks,

Patrick

Obvious question first - have you checked your group by on your report (NOT data tab)?|||Where would I find this?|||Assuming you are displaying your data in a table, then just select the table. Right click on the selected table, and check out the 'Groups' tab...

Only get dates

Can anyone help me create an expression for a column in a query that holds
only the date portion of a smalldatetime column? I've tried DatePart and had
a look around for other functions but I couldn't find anything. Thanks in
advance,
On Thu, 26 Aug 2004 22:19:04 -0700, Gary K wrote:

>Can anyone help me create an expression for a column in a query that holds
>only the date portion of a smalldatetime column? I've tried DatePart and had
>a look around for other functions but I couldn't find anything. Thanks in
>advance,
Hi Gary,
Use CONVERT to convert it to character, using a format without time (or
using a character column short enough that the time won't fit). If you
need it in datetime format without the time (you'll get the default time:
midnight), cast the result back to datetime format.
SELECT getdate()
SELECT CONVERT (varchar(8), GETDATE(), 112)
SELECT CAST(CONVERT (varchar(8), GETDATE(), 112) AS datetime)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo, that takes care of the main problem I had. I still have to
convert it back into a date variable but thats easy now.
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 22:19:04 -0700, Gary K wrote:
>
> Hi Gary,
> Use CONVERT to convert it to character, using a format without time (or
> using a character column short enough that the time won't fit). If you
> need it in datetime format without the time (you'll get the default time:
> midnight), cast the result back to datetime format.
> SELECT getdate()
> SELECT CONVERT (varchar(8), GETDATE(), 112)
> SELECT CAST(CONVERT (varchar(8), GETDATE(), 112) AS datetime)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Only functions and extended stored procedures can be executed from within a function

Hi everybody,

When i try to excute a function i got the following error message:
" Only functions and extended stored procedures can be executed from within a function."

I wrote the following stored procedure wich update the sequence id :
---------------------
CREATE PROCEDURE dbo.pr_get_id_sequence
(@.p_nom_sequence varchar(100),
@.p_nom_table varchar(100),
@.p_id_sequence numeric OUTPUT ) AS

-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @.v_id_sequence numeric

SET @.v_id_sequence = -1

UPDATE GST_SEQUENCE
SET @.v_id_sequence = ID_SEQUENCE = ID_SEQUENCE + 1
WHERE NOM_SEQUENCE = @.p_nom_sequence
AND NOM_TABLE = @.p_nom_table

SET @.p_id_sequence = @.v_id_sequence
RETURN @.p_id_sequence

Then, i wrote the following function which return the last value of sequence id:
--------------------
CREATE FUNCTION dbo.fu_get_id_sequence
(@.p_nom_sequence varchar(100),
@.p_nom_table varchar(100)
) returns int AS

BEGIN
DECLARE @.v_id_sequence numeric

Execute dbo.pr_get_id_sequence @.p_nom_sequence, @.p_nom_table, @.v_id_sequence

RETURN @.v_id_sequence

END

And, when i try to call the function, i got the error message:
-------------------
select dbo.fu_get_id_sequence ('SEQ', 'TABLE')

By the way i'm using SQL Server 2005. Also, I tried to create the function by incliding the UPDATE statement but it didn't work.

Can anyone help me ?
Thank you

PaulThe error messages clearly point to the source of error: you cannot call stored procedures, as well as perform updates, deletes and inserts in T-SQL functions. That's why you cannot execute your function. You'll have to create stored procedure instead of function to be able to call another stored procedure. Also, all output parameters should be explicitly marked as output when you call a stored procedure:

Execute dbo.pr_get_id_sequence @.p_nom_sequence, @.p_nom_table, @.v_id_sequence output

Only for SQL GURUS - Calling external procedures from Functions

I am using SQL Server 2005 Developer Edition. I am working on a conversion project from Oracle to SQL Server 2005.

As part of this, in order to replicate Oracle sequence functionality in SQL Server, the design called for writing a function that would accept a parameter , say sequence name. There will be a user table, say, SEQUENCE_GENERATOR with the following structure.

Column Name Data Type Nullability
-- -
Sequence_Name varchar(50) N
Last_Number int N

Everytime before generating the next sequence this function would be called. It would do the following : -

-
Select @.Last_Number = Last_Number
from SEQUENCE_GENERATOR WITH (UPDLOCK)
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

SET @.Last_Number = @.Last_Number +1

UPDATE SEQUENCE_GENERATOR
SET Last_Number = @.Last_Number
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

RETURN @.Last_Number
Since we are fetching the value of Last_Number(by calling the function) before inserting into our tables, we do not want users simultaneously accessing the same sequence to end up with PK violations and hence the use of UPDLOCK hint.

Now the real problem: -

Since the above three steps are a DML operation excepting the RETURN clause, we cannot do it in a function. However we can call an external stored procedure that does the above UML operations to accept the parameter and then return an output value. This output value would then be returned by the function.

Now I would like to know if the above approach is possible with external sps as I have no prior experience in this. Also, can someone point important resources where I can code the above thing in C++...

Thanks & Regards
Imtiaz

Before I get to the answer...

1. In practice sequence tables are a huge headache in any database design, I find they're more common in the Oracle world than any other, especially in large database designs that required a federated/distributed/replication model. If you can avoid a sequence table, consider using uniqueidentifier columns for PKs instead, they will remove a lot of headache (read: labor hours) and simplify software design at all levels.

2. Locking (table or otherwise) just creates artificial contention, if 1000 clients all decided to create a record at the same time they'd all have to stand in line. *owe*

3. In your sequence table you might consider using a seeded identity column for the PK, and leave the 'sequence name' as a regular data value. You could then write a UDF (function) in sql server that simply inserted the sequence name into the sequence table, and upon completion you acquire the generated number via @.@.IDENTITY (look this up in Books Online if you're not familiar with it already). This will help avoid the locking (which is hell on performance) and still get everything contained within sql server (no need to go the xp route). This is an option so long as the rest of the solution doesn't rely on a contigious set of numbers for a given sequence name (table?).
To answer your question, though, an extended stored procedure (xp/xsp) is implemented as a standard C dll, so you could implement it in C++ so long as you had cdecl exports sql server could consume.

Also, the registration process is tacky (look up sp_addextendedproc) since it requires access to the master table, but yes you can do what you want using an xp and a little elbow grease.

See: Creating an Extended Stored Procedure (MSDN)

Hope that helps,
Shaun

|||The logic that you have below is not quite correct unless you enclose the statements in a transaction. Without a transaction, the UPDLOCK will be released after the SELECT statement completes.

Select @.Last_Number = Last_Number
from SEQUENCE_GENERATOR WITH (UPDLOCK)
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

SET @.Last_Number = @.Last_Number +1

UPDATE SEQUENCE_GENERATOR
SET Last_Number = @.Last_Number
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

RETURN @.Last_Number

A more concise way of writing this in SQL Server is to use the UPDATE statement extension like:

UPDATE SEQUENCE_GENERATOR
SET @.Last_Number = Last_Number = Last_Number + 1
WHERE Sequence_Name = <sequence name>

Having said this, it is still not possible to perform data modifying operations from within UDFs. You can write an extended stored procedure. But that is more trouble actually. It can easily lead to concurrency issues, poor performance etc. You will also have to use bound connections. You could still go this route but be aware of the risks especially in a OLTP environment. You can use Visutal Studio templates to create the stubs required for implementing extended stored procedures. In any case, I would recommend against converting Oracle code verbatim. Identity provides a much faster and efficient mechanism to generate sequence numbers. If you need the sequence mechanism then why not implement it as a stored procedure and call it from within the proc requiring it. This is much more scalaeble way to implement it in TSQL.|||Shaun and Uma

The solutions you have given me instead of using external stored procedures I am aware of that. The application would run on Oracle or SQL Server backedn and the idea is to have most of the things as close as possible. I know this can be achieved by SPs but we desperately need functions to do that.

Uma
The reason we have UPDLOCK hint is for concurrency issues.

Now how can we write T-SQL code in C++ is my question. where do I start with?|||I am still waiting for some inputs on this...The problem is how do I start a xaction, access the database in extended procedure.

Regards
Imtiaz|||

Shaun Wilson wrote:

See: Creating an Extended Stored Procedure (MSDN)

Also

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_22sz.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_22sz.asp

Sync the toc on their website, all the docs you need are there. If all else fails you might consider using MSDTC as a transaction coordinator between the 'client', your xp and sql server.

Only for Gurus -- Analysis MDX Query help needed

I need a expression to get the name of the Member of a dimension.
For example .. if I have a Time Dimension ... and Child in hierarchy are Months.. I would like the result as
January
February
.....
December
Any Ideas ?http://www.winnetmag.com/Article/ArticleID/16311/16311.html

HTH

Only first row appearing

New to Reporting services, I am trying to create a simple header/
detail report. I am not using subreports due to problems with
mysterious pagebreaking before a lengthy subreport. However, my
problem is now everything looks good in the preview screen, but in a
print or print layout I am only seeing the first header in the group
and the first row for that header.
-wylieOn Jun 21, 1:36 pm, Wylie <wyli...@.gmail.com> wrote:
> New to Reporting services, I am trying to create a simple header/
> detail report. I am not using subreports due to problems with
> mysterious pagebreaking before a lengthy subreport. However, my
> problem is now everything looks good in the preview screen, but in a
> print or print layout I am only seeing the first header in the group
> and the first row for that header.
> -wylie
It sounds kind-of like you have possibly set 'Page break at end' as
part of your grouping (if applicable). Right-click the table control
(if applicable) and select Properties. Select the Groups tab, then
select the 'Edit...' button and uncheck 'Page break at end.' Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 21, 9:39 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 21, 1:36 pm, Wylie <wyli...@.gmail.com> wrote:
> > New to Reporting services, I am trying to create a simple header/
> > detail report. I am not using subreports due to problems with
> > mysterious pagebreaking before a lengthy subreport. However, my
> > problem is now everything looks good in the preview screen, but in a
> > print or print layout I am only seeing the first header in the group
> > and the first row for that header.
> > -wylie
> It sounds kind-of like you have possibly set 'Page break at end' as
> part of your grouping (if applicable). Right-click the table control
> (if applicable) and select Properties. Select the Groups tab, then
> select the 'Edit...' button and uncheck 'Page break at end.' Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I do not have any page breaks set. I could see that happening, but
I'm not seeing any other records. In preview I have 4 groups with
2-30 records in each group. On print layout ALL I get is the header
and first record of the first group.
-wylie

Only enable the string truncation prevention of ANSI_WARNINGS

I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.

I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.

What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?

Cheers
Dave"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0401080924.40dd7da1@.posting.google.c om...
> I'm working with some long standing VB/SQL Server applications and for
> the second time we've suffered from having the parameters to a stored
> procedure call get silently truncated now that the data field has got
> much larger than when the code was developed all those years ago. This
> is always very hard to debug and I'd really like SQL Server to throw
> an error when this happens.
> I don't feel confident enablying the full ANSI_WARNINGS as it is
> likely to affect lots of functionality in the database in
> unanticipated ways.
> What I'd like to be able to do is enable only the ANSI check for the
> string data getting truncated but haven't been able to find a way to
> do this. Is it possible?
> Cheers
> Dave

There's no 'subset' of ANSI_WARNINGS which will only raise an error on
string truncation, so your best bet is to fix your application, either by
making the stored proc parameter longer, or by validating the input in the
front end.

If neither of those are possible, then there aren't many options left,
except perhaps to raise an error if the parameter value is the same size as
the maximum possible size of the parameter data type. So if you have
char(20), assume that only values up to 19 characters are valid. But it
would be much better to fix the problem at the source, and validate the
input.

Simon|||David Sharp (dave@.daveandcaz.freeserve.co.uk) writes:
> I'm working with some long standing VB/SQL Server applications and for
> the second time we've suffered from having the parameters to a stored
> procedure call get silently truncated now that the data field has got
> much larger than when the code was developed all those years ago. This
> is always very hard to debug and I'd really like SQL Server to throw
> an error when this happens.
> I don't feel confident enablying the full ANSI_WARNINGS as it is
> likely to affect lots of functionality in the database in
> unanticipated ways.
> What I'd like to be able to do is enable only the ANSI check for the
> string data getting truncated but haven't been able to find a way to
> do this. Is it possible?

To add to what Simon said, you would probably have any use for ANSI_WARNINGS
anyway. ANSI_WARNINGS produces an error if you try to assign a column
a value which is too long. However, variable and parameter assignment
still truncates silently, even with ANSI_WARNINGS ON.

I would however encourage you to switch to ANSI_WARNINGS for other reasons.
This setting is required is some contexts, more precisely in distributed
queries and when you used indexed views and indexed computed columns.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946B48FD1863Yazorman@.127.0.0.1>...
> I would however encourage you to switch to ANSI_WARNINGS for other reasons.
> This setting is required is some contexts, more precisely in distributed
> queries and when you used indexed views and indexed computed columns.

Thanks for your help. Sounds like there's no short cut to identify
when this happens. We'll have to physically go through and make sure
it is checked for in each sproc.

Cheers
Dave

only each first item from a select with group ....

i want to get only the first (max sum ) value of each grouped item after a
select
example of the returned records where: two columns (grouped value company
and sum sell price)
in the table there are a nr of entrys of each company
i calculate the sum for the sell price in each company
i want to display only the best of a region
now i get somethink like
nord company33 145678,44
nord company2 34578,44
nord company44 478,44
nord company6 78,44
west company77 645678,44
west company8 35678,44
sud company9 995678,44
sud company4 678,44
but i want to display only the first (max) entry of each group - like
nord company33 145678,44 (the best from region nord)
west company77 645678,44
sud company9 995678,44
thanks
XavierTry,
To make it readable, let us create a view.
create view v1
as
here goes the select statement that does the grouping
go
select *
from v1 as a
where not exists (
select *
from v1 as b
where b.region = a.region
and b.sum_sell_price > a.sum_sell_price
)
-- or
select a.*
from v1 as a
inner join
(
select region, max(sum_sell_price ) as max_sum_sell_price
from v1
group by region
) as b
on a.region = b.region
and a.sum_sell_price = b.max_sum_sell_price
AMB
"Xavier" wrote:

> i want to get only the first (max sum ) value of each grouped item after
a
> select
> example of the returned records where: two columns (grouped value company
> and sum sell price)
> in the table there are a nr of entrys of each company
> i calculate the sum for the sell price in each company
> i want to display only the best of a region
> now i get somethink like
> nord company33 145678,44
> nord company2 34578,44
> nord company44 478,44
> nord company6 78,44
> west company77 645678,44
> west company8 35678,44
> sud company9 995678,44
> sud company4 678,44
> but i want to display only the first (max) entry of each group - like
> nord company33 145678,44 (the best from region nord)
> west company77 645678,44
> sud company9 995678,44
> thanks
> Xavier
>|||it works, thanks for your help
Xavier
"Alejandro Mesa" wrote:
> Try,
> To make it readable, let us create a view.
> create view v1
> as
> here goes the select statement that does the grouping
> go
> select *
> from v1 as a
> where not exists (
> select *
> from v1 as b
> where b.region = a.region
> and b.sum_sell_price > a.sum_sell_price
> )
> -- or
> select a.*
> from v1 as a
> inner join
> (
> select region, max(sum_sell_price ) as max_sum_sell_price
> from v1
> group by region
> ) as b
> on a.region = b.region
> and a.sum_sell_price = b.max_sum_sell_price
>
> AMB
> "Xavier" wrote:
>

Only displaying the subtotal value in a matrix report.

Is there a way to make it so that a data column in a matrix only displays a value for the subtotal of a field?

If you add a toggle to the matrix column/row group, it will enable to switch between the detailed group instances (when expanded) or show the subtotals only (when collapsed). Take a look at the "Company Sales" sample report in the Adventure Works sample project that comes with RS.

-- Robert

Only Date

Hey,

how can i only save the date in the SQL server? there is are only TimeDate and SmallDateTime Types, both save the date as mm-dd-yyyy hh:mm:ss
i only need the need. and not the time.

ThanxSorry. If you want to save a date as a date, you gotta take that time part as well.

You could save it as a varchar, but I would advise against it...|||You can save the date and time (with time always being 0:00:00), and then just decide to show only the date when formatting the value from the database.|||In fact, to take this a step further:

When inserting a date, if you specify no time value, the time will default to 0:00:00.

Only data for Yesterday

How do i get only the data for the last date in the query. I just want the most current date. Which is yesterdays information. But on monday, its fridays information. So is there a code for getting the most Current Data. If not whats the code for getting the last business day such as friday for today. But for tommorow it will be monday. My time_log field has the date converted into mm-dd-yyyy. Any help?

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_6_Daily]

(@.Region_Key int=null)

BEGIN

SELECT dbo.Qry_Visits.Status,

dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],

B.VisitsTotal

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status

FROM dbo.Qry_Visits

WHERE Qry_Visits.Status=2

GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status

HAVING SUM(Visits) < 6)B

ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND

CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2 and CONVERT(varchar, dbo.Qry_Visits.time_log, 110)=GETDATE()

ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

END

SET NOCOUNT OFF

Can you use something like:

Code Snippet

declare @.date datetime

if DATENAME(dw, getdate()) = 'MONDAY'
SET @.date = getdate()-3
else
SET @.date = getdate()-1

SELECT ....
FROM ......
WHERE DATEDIFF(D,Time_Log, @.Date) = 0

HTH!

|||

Do i have to make the date a parameter. I tried to just doing this:

Code Snippet

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2 and

(CONVERT(varchar, dbo.Qry_Visits.time_log, 110))=getdate()-3

but nothing shows up.

|||

Or, you might try this one:

Code Snippet

where REGION_KEY=@.Region_Key and Qry_Visits.Status=2

and DateDiff(dd,0,dbo.Qry_Visits.time_log) =

case

when DATENAME(dw, getdate()) = 'MONDAY' then datediff(dd,0,GetDate()) - 3

else datediff(dd,0,GetDate()) - 1

end

ORDER BY dbo.Qry_Sales_Group.SR_Name, dbo.Qry_Date_Dim.Date_Dimension_Date

|||that works perfectly THANKS!!

Only counting values that appear more than once

Hi guys
I have a table containing serial numbers, some of which occur more than
once. What I want to do is to only count serial number that only occur
more than once. How would I go about doing this?
Many thanksandystob wrote:
> Hi guys
> I have a table containing serial numbers, some of which occur more than
> once. What I want to do is to only count serial number that only occur
> more than once. How would I go about doing this?
> Many thanks
SELECT
SUM(cnt) AS row_cnt,
COUNT(*) AS serial_number_cnt
FROM
(SELECT COUNT(*) AS cnt
FROM your_table
GROUP BY serial_number
HAVING COUNT(*)>1) T ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If I understand your problem correctly,
select sum(Num) from
(select count(*)
from sometable
group by SerialNumber
having count(*)>1) X(Num)|||This will give you your list of duplicate serial numbers...
select serialnumber
from yourtable
group by serialnumber
having count(*) > 1
You can then use that as a join to do your other query...
select ..
from (
select serialnumber
from yourtable
group by serialnumber
having count(*) > 1 ) as dups
inner join yourtable yt on yt.yourid = dups.yourid
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"andystob" <sql@.evilscience.co.uk> wrote in message
news:1138793435.757970.264220@.o13g2000cwo.googlegroups.com...
> Hi guys
> I have a table containing serial numbers, some of which occur more than
> once. What I want to do is to only count serial number that only occur
> more than once. How would I go about doing this?
> Many thanks
>|||I've tried that code, and I'm getting the following error
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near ')'.|||Ah - forgot to add an alias after the last bracket. All working now.
Thanks very much for your help, chaps.|||Hi
SELECT DATEADD(MONTH,DATEDIFF(MONTH,'19000101',
GETDATE()),'19000101')-1
"andystob" <sql@.evilscience.co.uk> wrote in message
news:1138793435.757970.264220@.o13g2000cwo.googlegroups.com...
> Hi guys
> I have a table containing serial numbers, some of which occur more than
> once. What I want to do is to only count serial number that only occur
> more than once. How would I go about doing this?
> Many thanks
>|||Lol Uri - right answer - wrong thread, you want the one before this one ;)
All the best,
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238oncbyJGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi
> SELECT DATEADD(MONTH,DATEDIFF(MONTH,'19000101',
GETDATE()),'19000101')-1
>
>
> "andystob" <sql@.evilscience.co.uk> wrote in message
> news:1138793435.757970.264220@.o13g2000cwo.googlegroups.com...
>|||Yep, you are right, I was still thinking about a table variables get
recompiled in our another thread
I 'm going to take a coffee , it is time to relax :-))))
Have a nice day Tony
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUimJkyJGHA.4068@.TK2MSFTNGP10.phx.gbl...
> Lol Uri - right answer - wrong thread, you want the one before this one ;)
> All the best,
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238oncbyJGHA.2012@.TK2MSFTNGP14.phx.gbl...
>

Only copy missing rows from one database to another

Hello everyone,

I'm trying to create a performant script to copy records from a table in a source database, to an identical table in a destination database.

In SQL 2000, I used to create a little lookup which did a count using certain fields. If the record was missing, I executed an INSERT query, otherwise an UPDATE query. The result was that the table on the destination side was always up to date. Duplicate rows were out of the question.

This was, if I'm not mistaking, a Data Transformation, using a bit of custom VBA code to govern the transfer. For each source row, the custom code was executed. Depending on the result of the custom code, a different query was launched.

Now I'm trying to do the same using SSIS in SQL 2005. Is there a task which does this for me, or do I have to script again? In the latter case, which type of task would I use?

(I thought of the Script Task, but then I would need to set up quite a bit myself.)

Thank you,

Bram

Look at the "checking to see if a record exists if so update else insert" post on the main page.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

Only Configuration tool downloaded, why?

Hi,

First of all, I am very new on SQL server express.

Today I downloaded and installed the SQL server express (from www.microsoft.com/downloads), but what I got is only: Configuration tool, (I can see from: Start -> Programms -> Microsoft SQL server 2005->Configuration tool).

I didn't get SQL Server management Studio express, SAL Server Business Intelligence Development Studio, and Documentation and Tutorials.

I don't know where I did wrong, please help.

Thank you.

James

You need to download the version with 'Advanced Services' in order to get the client tools. And you should also download the Books Online reference materials.

SQL Server 2005 Express Edition (Advanced/SSMS)
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
http://msdn2.microsoft.com/en-us/library/ms365247.aspx
http://tinyurl.com/yelwr9

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx

SQL Server 2005 SSMS Express Tutorial
http://tinyurl.com/nqsca

Only bringing in select columns from a text file

Hi all,

I have some bad data that i'm trying to do a workaround for. I have a comma delimited text file with a column which contains a few records that has, gasp, a comma in it. Thus creating an extra column and pushing out NULL values in an empty column that isn't recognized by my DTS package.
Can i fix this?

And if I'm correct in my assumption that I can't, how do I import only the fields preceding the bad data field? When i attempt to do that, I get the following message

'Too many columns found in the current row; non white-spaced characters found after the last defined column's data.'

Blindman, you probably know exactly what to do, huh?Look up BCP and Format file in BOL.|||Exactly what to do?

Shoot the jackass who created the datafile.

If you are in luck (which you probably aren't) the string fields are enclosed by quotes, in which case commas won't cause a problem. But you are having a problem, so forget that...

Check you datafile. Commas embedded in text are followed by spaces, while comma delimiters may not be, depending on the system that generated the file. If this is true with your data, then try this trick:

1) Using a text editor, replace all occurrences of ", " with another string, such as "azbycx".
2) Replace all occurrences of "," with the pipe character "|".
3) Replace all occurrences of "azbycx" with your origianl ", ".

Voila! If all goes well you now have a pipe delimited file.

If your data is too messy for this, then you may have to load it into a staging table as a single column and write a procedure for parsing it yourself, based upon its characteristics.

...or you could just ask the jackass to give you a better file before you shoot him.|||I was hoping you would help!!! I know all about bcp and format file. Well, not all about it, but you know what I'm trying to say.

No such luck for me today. Primarily because I can't shoot the jackass (laws, you see), and I can't send the data back. Fortunately, I don't NEED that particular troublesome column, so how can I bring in only the columns preceding the bad data filled one?

I attempted a hideiously clumsy but successful workaround involving opening said text file in excel, copying and pasting, deleting bad columsn, creating array, and importing back into SQL. Obviously, this just won't work once the file gets to be over 65,536 rows.

And I've been told, this is something we will be doing a lot of. I have a boss that doesn't accept the "bad data" excuse, electing rather to spit out, "Get creative, make it work."

Am I just totally screwed??

OH, and the comma delimiters are accompanied by different spacing , because it's really a fixed width comma delimited field. And columns without values are given a random fixed width (blank of course) ie. ,' ',' ',

Nice, eh?|||I was hoping you would help!!! I know all about bcp and format file. Well, not all about it, but you know what I'm trying to say.

Relax..

First, you should have books online open at all times...sometimes it's hard to navigate, but it's all in there.

Second, There are MANY ways to do this...like load the whole thing into a single column varchar(8000) column, and use t-sql to audit the crap.

Third, you could use a format card, like

7.0
18
1 SQLCHAR 0 14 "" 1 EmployeeID
2 SQLCHAR 0 40 "" 2 LastName
3 SQLCHAR 0 20 "" 3 FirstName
4 SQLCHAR 0 60 "" 4 Title
5 SQLCHAR 0 50 "" 5 TitleOfCourtesy
6 SQLCHAR 0 26 "" 6 BirthDate
7 SQLCHAR 0 26 "" 7 HireDate
8 SQLCHAR 0 120 "" 8 Address
9 SQLCHAR 0 30 "" 9 City
10 SQLCHAR 0 30 "" 10 Region
11 SQLCHAR 0 20 "" 11 PostalCode
12 SQLCHAR 0 30 "" 12 Country
13 SQLCHAR 0 48 "" 13 HomePhone
14 SQLCHAR 0 8 "" 14 Extension
15 SQLCHAR 0 0 "" 0 Photo
16 SQLCHAR 0 0 "" 0 Notes
17 SQLCHAR 0 14 "" 17 ReportsTo
18 SQLCHAR 0 510 "\r\n" 18 PhotoPath

This is from the Northwind Employees table...notice the zeroes?|||One other thing...you may be worrying about column (I don't know why if they're not image or text)...but I would get a sense that as soon as you figure this out, you're gonna have to start worrying about rows, more than columns...

Did you ever think to create a staging table and make everything varchar?

EDIT: Oh, and I like your bosses 'tude|||Well, if your data file was really "fixed width" you'd have no problems importing it, so I assume you just mean that empty values are deliniated by spaces.

I do wonder, though, how you were able to load it into Excel and delete the bad columns. Not sure why you could do this in Excel and not in SQL Server.

I have to ask: who is sending you this data on a regular basis that cannot send it in a standard format? (The boss's nephew?)

Can you post one or two of the records that contain embedded commas, just so we can see what we are dealing with?

Here is an option: load the record into a single large varchar. Write a User Defined Function that takes a string and parses off everything to the left of the first comma, truncating the rest of the string. Cycle through your staging table once for each column in your production table, using this function to populate the data.|||First of all, thanks so much for the help. I wish I could relax, but I'm under major time constraints and I'm running a 102 fever.

Okay, I uploaded the txt.file. A few new developments...
When I open the file in Excel, I can see the record with the embedded comma. (Do a search on Policy_Number WRA1227). The comma is in one of the 'address lines' near the back of the file. When I so a search for that same Policy_Number in the txt file, I can't find that policy. ??

Also, the other reason why I can't use bulk insert task to do this: Error 229. Don't have the rights.

Other details, I work for a VERY small consulting company. My boss is an actuary, knows nothing about SQL. He programs in APL, and has no troubles do any of this stuff.

I'm the only one here. So alone... :o

This dump used to work before new columns were added. And I am importing everything into a staging table as varchar using Transform Data Properties using the text souce icon, and then playing with the data during later steps in my dts.

Also should mention: I'm not a DBA. So I have a bit of trouble with the dynamic sql. Trying to learn it, but while under massive deadlines, I just do what I know how to do. Which isn't a whole lot. :(

And to answer your question blindman, we are testing a new program for a developer for a fraction of the cost of purchasing a maintstream life insurance processing program. But we will have to be dealing with "bad data" all the time, so that's why he's being such a hardass.|||Please cut and paste some sample rows of data that you have..

Also post the DDL of the final destination table

With that, I'm sure I can get you a solution quickly

Make sure the sample data has good and bad rows.

What does he want doen with the bad data|||I know I should eta, but I noticed my insert didn't work. File too big... so I just replicated the problem on a similar record. Policy Number: T58I30

Product Type,Plan,Policy Number,ID Number,Agent,Group,Issue State,Issue Date,Mode,Specified Amount,Benefit,Base Premium,Rider Premium,Contract Value,Loan Balance,Surrender Charge,Surrender Value,PUA Dividends,OYT Dividends,Dividend Cash Balance,Dividend Cash Value,Status Date,Status Code,Paid To Date,Date Last Bill,APL Option,NFO Option,DIVD Option,District,Next Premium Due Date,Next Premium Due Amount,Scheduled Premiums,Premiums Paid,Insured IssueAge,Insured Sex,Insured Class,Insured Birthdate,2nd Insured IssueAge,2nd Insured Sex,2nd Insured Class,2nd Insured Birthdate,Riders Count,Rider1 Type,Rider1 Code,Rider1 IssDate,Rider1 ExpDate,Rider1 Benefit,Rider1 Premium,Rider1 Period,Rider1 Status,Rider1 TermDate,Rider2 Type,Rider2 Code,Rider2 IssDate,Rider2 ExpDate,Rider2 Benefit,Rider2 Premium,Rider2 Period,Rider2 Status,Rider2 TermDate,Rider3 Type,Rider3 Code,Rider3 IssDate,Rider3 ExpDate,Rider3 Benefit,Rider3 Premium,Rider3 Period,Rider3 Status,Rider3 TermDate,Rider4 Type,Rider4 Code,Rider4 IssDate,Rider4 ExpDate,Rider4 Benefit,Rider4 Premium,Rider4 Period,Rider4 Status,Rider4 TermDate,Rider5 Type,Rider5 Code,Rider5 IssDate,Rider5 ExpDate,Rider5 Benefit,Rider5 Premium,Rider5 Period,Rider5 Status,Rider5 TermDate,Rider6 Type,Rider6 Code,Rider6 IssDate,Rider6 ExpDate,Rider6 Benefit,Rider6 Premium,Rider6 Period,Rider6 Status,Rider6 TermDate,Rider7 Type,Rider7 Code,Rider7 IssDate,Rider7 ExpDate,Rider7 Benefit,Rider7 Premium,Rider7 Period,Rider7 Status,Rider7 TermDate,Rider8 Type,Rider8 Code,Rider8 IssDate,Rider8 ExpDate,Rider8 Benefit,Rider8 Premium,Rider8 Period,Rider8 Status,Rider8 TermDate,Rider9 Type,Rider9 Code,Rider9 IssDate,Rider9 ExpDate,Rider9 Benefit,Rider9 Premium,Rider9 Period,Rider9 Status,Rider9 TermDate,Rider10 Type,Rider10 Code,Rider10 IssDate,Rider10 ExpDate,Rider10 Benefit,Rider10 Premium,Rider10 Period,Rider10 Status,Rider10 TermDate,Rider11 Type,Rider11 Code,Rider11 IssDate,Rider11 ExpDate,Rider11 Benefit,Rider11 Premium,Rider11 Period,Rider11 Status,Rider11 TermDate,Rider12 Type,Rider12 Code,Rider12 IssDate,Rider12 ExpDate,Rider12 Benefit,Rider12 Premium,Rider12 Period,Rider12 Status,Rider12 TermDate,Rider13 Type,Rider13 Code,Rider13 IssDate,Rider13 ExpDate,Rider13 Benefit,Rider13 Premium,Rider13 Period,Rider13 Status,Rider13 TermDate,Rider14 Type,Rider14 Code,Rider14 IssDate,Rider14 ExpDate,Rider14 Benefit,Rider14 Premium,Rider14 Period,Rider14 Status,Rider14 TermDate,Rider15 Type,Rider15 Code,Rider15 IssDate,Rider15 ExpDate,Rider15 Benefit,Rider15 Premium,Rider15 Period,Rider15 Status,Rider15 TermDate,Rider16 Type,Rider16 Code,Rider16 IssDate,Rider16 ExpDate,Rider16 Benefit,Rider16 Premium,Rider16 Period,Rider16 Status,Rider16 TermDate,Next Rider Premium Date,Next Rider Premium Amount,Rider Premiums Scheduled,Rider Premiums Paid,Expiry Date,Insured Last Name,Insured First Name,Insured SSN,Billing Option,Bill Address1,Bill Address2,Bill City,Bill State,Bill Zip,Bill Phone
S,DV583A, 56, 1, HO, 0,AL,06241963,A, 5560.21, 5560.21, 396.20, 0.00, 3345.99, 0.00, 0.00, 3345.99, 0.00, 0.00, 97.37, 97.37,20041201,52,20470624,00000000,Y,E,A, ,00000000, 0.00, 15055.60, 15055.60, 16,F,C,12051947, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20460624,'HOEHN ','MARY ','421-64-8104',D,'C/O FRED M HOEHN ','525 22ND AVENUE S ','BIRMINGHAM ','AL','35205-6429',' '
S,T58I30, 72, 2, HO, 0,FL,12271972,A, 265.99, 265.99, 0.05, 0.00, 186.22, 0.00, 0.00, 186.22, 0.00, 0.00, 0.00, 0.00,20041201,52,20381227,00000000,Y,E,C, ,00000000, 0.00, 0.10, 0.08, 34,M,C,02201939, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20371227,'POWELL ','RANDALL ','000-00-0000',D,'%FAITH,INVESTMENT CO ','NO CURRENT ADDRESS ','PENSACOLA ','FL','32505 ',' '
S,T58I30, 73, 3, HO, 0,FL,12271972,A, 197.98, 197.98, 0.04, 0.00, 113.28, 0.00, 0.00, 113.28, 0.00, 0.00, 0.00, 0.00,20041201,52,20491227,00000000,Y,E,C, ,00000000, 0.00, 0.08, 0.06, 23,M,C,07071949, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20481227,'THOMAS ','HARVEY ','000-00-0000',D,'%FAITH INVESTMENT CO ','NO CURRENT ADDRESS ','PENSACOLA ','FL','32505 ',' '
S,121101, 055, 4, HO, 0,WA,12121967,A, 10540.00, 10540.00, 0.00, 0.00, 9247.54, 0.00, 0.00, 9247.54, 0.00, 0.00, 300.80, 300.80,20041201,50,20181212,00000000,Y,E,C, ,00000000, 0.00, 7727.00, 7727.00, 49,F,C,11211918, 0, ,C,00000000, 0, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000, , ,00000000,00000000, 0.00, 0.00, 0, ,00000000,00000000, 0.00, 0.00, 0.00,20171212,'HINZE ','PEARL ','479-22-7580',D,'148 102ND SE APT 21 ',' ','BELLEVUE ','WA','98004 ',' '

Excuse my ignorance, but DDL? Is that just the create table logic?

And thank you. :)|||I need the DDL as well

CREATE TABLE tablename(Col1 int, ect

Do you know how to script that in Enterprise Manager?|||Also I noticed that some of the character data has quotes (ie 'Brett Kaiser')

And some of it doesn't...is this true?

Does the data normally get loaded with quotes?|||Do you know how to script that in Enterprise Manager?

I have a DTS package in Enterprise Manager that calls a stored procedure that creates the table:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_PolDump_113004_test

AS

IF OBJECT_ID('MEC.mcopper.Pol_Dump113004_test') IS NOT NULL
DROP TABLE MEC.mcopper.Pol_Dump113004_test

CREATE TABLE [Pol_Dump113004_test]
(
[Product_Type] varchar (12) NULL,
[Plan_Number] varchar (12) NULL,
[Policy_Number] varchar (12) NULL,
[ID_Number] varchar (12) NULL,
[Agent] varchar (12) NULL,
[Group_Number] varchar (12) NULL,
[Issue_State] varchar (12) NULL,
[Issue_Date] varchar (12) NULL,
[Mode] varchar (12) NULL,
[Specified_Amount] varchar (12) NULL,
[Benefit] varchar (12) NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] varchar (12) NULL,
[Contract_Value] varchar (12) NULL,
[Loan_Balance] varchar (12) NULL,
[Surrender_Charge] varchar (12) NULL,
[Surrender_Value] varchar (12) NULL,
[PUA_Dividends] varchar (12) NULL,
[OYT_Dividends] varchar (12) NULL,
[Dividend_Cash_Balance] varchar (12) NULL,
[Dividend_Cash_Value] varchar (12) NULL,
[Status_Date] varchar (12) NULL,
[Status_Code] varchar (12) NULL,
[Paid_To_Date] varchar (12) NULL,
[Date_Last_Bill] varchar (12) NULL,
[APL_Option] varchar (12) NULL,
[NFO_Option] varchar (12) NULL,
[DIVD_Option] varchar (12) NULL,
[District] varchar (12) NULL,
[Next_Premium_Due_Date] varchar (12) NULL,
[Next_Premium_Due_Amount] varchar (12) NULL,
[Scheduled_Premiums] varchar (12) NULL,
[Premiums_Paid] varchar (12) NULL,
[Insured_IssueAge] varchar (12) NULL,
[Insured_Sex] varchar (12) NULL,
[Insured_Class] varchar (12) NULL,
[Insured_Birthdate] varchar (12) NULL,
[2nd_Insured_IssueAge] varchar (12) NULL,
[2nd_Insured_Sex] varchar (12) NULL,
[2nd_Insured_Class] varchar (12) NULL,
[2nd_Insured_Birthdate] varchar (12) NULL,
[Riders_Count] varchar (12) NULL,
[Rider1_Type] varchar (12) NULL,
[Rider1_Code] varchar (12) NULL,
[Rider1_IssDate] varchar (12) NULL,
[Rider1_ExpDate] varchar (12) NULL,
[Rider1_Benefit] varchar (12) NULL,
[Rider1_Premium] varchar (12) NULL,
[Rider1_Period] varchar (12) NULL,
[Rider1_Status] varchar (12) NULL,
[Rider1_TermDate] varchar (12) NULL,
[Rider2_Type] varchar (12) NULL,
[Rider2_Code] varchar (12) NULL,
[Rider2_IssDate] varchar (12) NULL,
[Rider2_ExpDate] varchar (12) NULL,
[Rider2_Benefit] varchar (12) NULL,
[Rider2_Premium] varchar (12) NULL,
[Rider2_Period] varchar (12) NULL,
[Rider2_Status] varchar (12) NULL,
[Rider2_TermDate] varchar (12) NULL,
[Rider3_Type] varchar (12) NULL,
[Rider3_Code] varchar (12) NULL,
[Rider3_IssDate] varchar (12) NULL,
[Rider3_ExpDate] varchar (12) NULL,
[Rider3_Benefit] varchar (12) NULL,
[Rider3_Premium] varchar (12) NULL,
[Rider3_Period] varchar (12) NULL,
[Rider3_Status] varchar (12) NULL,
[Rider3_TermDate] varchar (12) NULL,
[Rider4_Type] varchar (12) NULL,
[Rider4_Code] varchar (12) NULL,
[Rider4_IssDate] varchar (12) NULL,
[Rider4_ExpDate] varchar (12) NULL,
[Rider4_Benefit] varchar (12) NULL,
[Rider4_Premium] varchar (12) NULL,
[Rider4_Period] varchar (12) NULL,
[Rider4_Status] varchar (12) NULL,
[Rider4_TermDate] varchar (12) NULL,
[Rider5_Type] varchar (12) NULL,
[Rider5_Code] varchar (12) NULL,
[Rider5_IssDate] varchar (12) NULL,
[Rider5_ExpDate] varchar (12) NULL,
[Rider5_Benefit] varchar (12) NULL,
[Rider5_Premium] varchar (12) NULL,
[Rider5_Period] varchar (12) NULL,
[Rider5_Status] varchar (12) NULL,
[Rider5_TermDate] varchar (12) NULL,
[Rider6_Type] varchar (12) NULL,
[Rider6_Code] varchar (12) NULL,
[Rider6_IssDate] varchar (12) NULL,
[Rider6_ExpDate] varchar (12) NULL,
[Rider6_Benefit] varchar (12) NULL,
[Rider6_Premium] varchar (12) NULL,
[Rider6_Period] varchar (12) NULL,
[Rider6_Status] varchar (12) NULL,
[Rider6_TermDate] varchar (12) NULL,
[Rider7_Type] varchar (12) NULL,
[Rider7_Code] varchar (12) NULL,
[Rider7_IssDate] varchar (12) NULL,
[Rider7_ExpDate] varchar (12) NULL,
[Rider7_Benefit] varchar (12) NULL,
[Rider7_Premium] varchar (12) NULL,
[Rider7_Period] varchar (12) NULL,
[Rider7_Status] varchar (12) NULL,
[Rider7_TermDate] varchar (12) NULL,
[Rider8_Type] varchar (12) NULL,
[Rider8_Code] varchar (12) NULL,
[Rider8_IssDate] varchar (12) NULL,
[Rider8_ExpDate] varchar (12) NULL,
[Rider8_Benefit] varchar (12) NULL,
[Rider8_Premium] varchar (12) NULL,
[Rider8_Period] varchar (12) NULL,
[Rider8_Status] varchar (12) NULL,
[Rider8_TermDate] varchar (12) NULL,
[Rider9_Type] varchar (12) NULL,
[Rider9_Code] varchar (12) NULL,
[Rider9_IssDate] varchar (12) NULL,
[Rider9_ExpDate] varchar (12) NULL,
[Rider9_Benefit] varchar (12) NULL,
[Rider9_Premium] varchar (12) NULL,
[Rider9_Period] varchar (12) NULL,
[Rider9_Status] varchar (12) NULL,
[Rider9_TermDate] varchar (12) NULL,
[Rider10_Type] varchar (12) NULL,
[Rider10_Code] varchar (12) NULL,
[Rider10_IssDate] varchar (12) NULL,
[Rider10_ExpDate] varchar (12) NULL,
[Rider10_Benefit] varchar (12) NULL,
[Rider10_Premium] varchar (12) NULL,
[Rider10_Period] varchar (12) NULL,
[Rider10_Status] varchar (12) NULL,
[Rider10_TermDate] varchar (12) NULL,
[Rider11_Type] varchar (12) NULL,
[Rider11_Code] varchar (12) NULL,
[Rider11_IssDate] varchar (12) NULL,
[Rider11_ExpDate] varchar (12) NULL,
[Rider11_Benefit] varchar (12) NULL,
[Rider11_Premium] varchar (12) NULL,
[Rider11_Period] varchar (12) NULL,
[Rider11_Status] varchar (12) NULL,
[Rider11_TermDate] varchar (12) NULL,
[Rider12_Type] varchar (12) NULL,
[Rider12_Code] varchar (12) NULL,
[Rider12_IssDate] varchar (12) NULL,
[Rider12_ExpDate] varchar (12) NULL,
[Rider12_Benefit] varchar (12) NULL,
[Rider12_Premium] varchar (12) NULL,
[Rider12_Period] varchar (12) NULL,
[Rider12_Status] varchar (12) NULL,
[Rider12_TermDate] varchar (12) NULL,
[Rider13_Type] varchar (12) NULL,
[Rider13_Code] varchar (12) NULL,
[Rider13_IssDate] varchar (12) NULL,
[Rider13_ExpDate] varchar (12) NULL,
[Rider13_Benefit] varchar (12) NULL,
[Rider13_Premium] varchar (12) NULL,
[Rider13_Period] varchar (12) NULL,
[Rider13_Status] varchar (12) NULL,
[Rider13_TermDate] varchar (12) NULL,
[Rider14_Type] varchar (12) NULL,
[Rider14_Code] varchar (12) NULL,
[Rider14_IssDate] varchar (12) NULL,
[Rider14_ExpDate] varchar (12) NULL,
[Rider14_Benefit] varchar (12) NULL,
[Rider14_Premium] varchar (12) NULL,
[Rider14_Period] varchar (12) NULL,
[Rider14_Status] varchar (12) NULL,
[Rider14_TermDate] varchar (12) NULL,
[Rider15_Type] varchar (12) NULL,
[Rider15_Code] varchar (12) NULL,
[Rider15_IssDate] varchar (12) NULL,
[Rider15_ExpDate] varchar (12) NULL,
[Rider15_Benefit] varchar (12) NULL,
[Rider15_Premium] varchar (12) NULL,
[Rider15_Period] varchar (12) NULL,
[Rider15_Status] varchar (12) NULL,
[Rider15_TermDate] varchar (12) NULL,
[Rider16_Type] varchar (12) NULL,
[Rider16_Code] varchar (12) NULL,
[Rider16_IssDate] varchar (12) NULL,
[Rider16_ExpDate] varchar (12) NULL,
[Rider16_Benefit] varchar (12) NULL,
[Rider16_Premium] varchar (12) NULL,
[Rider16_Period] varchar (12) NULL,
[Rider16_Status] varchar (12) NULL,
[Rider16_TermDate] varchar (12) NULL,
[Next_Rider_Premium_Date] varchar (12) NULL,
[Next_Rider_Premium_Amount] varchar (12) NULL,
[Rider_Premiums_Scheduled] varchar (12) NULL,
[Rider_Premiums_Paid] varchar (12) NULL,
[Expiry_Date] varchar (12) NULL,
[Insured_Last_Name] varchar (288) NULL,
[Insured_First_Name] varchar (288) NULL,
[Insured_SSN] varchar (288) NULL,
[Billing_Option] varchar (288) NULL,
[Bill_Address1] varchar (288) NULL,
[Bill_Address2] varchar (288) NULL,
[Bill_City] varchar (288) NULL,
[Bill_State] varchar (288) NULL,
[Bill_Zip] varchar (288) NULL,
[Bill_Phone] varchar (288) NULL
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Also I noticed that some of the character data has quotes (ie 'Brett Kaiser')

And some of it doesn't...is this true?

Does the data normally get loaded with quotes?

Yes, it's true. That was the 'developer's' version of a quick fix. To text delimit the add'l columns that possibly had bad data. He originally had double quotes, but I changed them to single quotes.|||OK, at a certain point (now would be good) you and your boss have to ask yourselves this question:
"How good is the software this guy is developing going to be if he can't perform such a simple task as exporting a standard text file?"

I, (and probably anybody else on this forum with any experience) can tell you that this turd is just going to stink more and more as the months wear on. It's going to cost you a helluva lot in maintenance costs if it turns out to be crappy software.

At least, that is what MY crystal ball is telling me...

As for your data, if you scrap the first line (headers) the rest is pure fixed-width with some superfluous commas tossed in. BCP should be able to handle it, but load it into a staging table and then strip off the trailing commas when you move it to your production table.

Want another quick and dirty solution? MS Access's wizard will import fixed width with ease, (you can even set it up to ignore the comma delimiters). For best results use an Access ADP project linked to your SQL Server, or just use an Access MDB database and link your server tables to it.|||EDIT: I attached the sample file to load from

After you build it you might need to ISDATE and ISNUMERIC to filter out garbage and or REPLACE to get rid of quotes...

Where in the world are you doing this, and take 5 advil

USE Northwind
GO

master..xp_cmdshell ' DIR D:\Tax\m*.*'

CREATE TABLE myTable99(Col1 varchar(8000))
GO

master..xp_cmdshell 'bcp Northwind.dbo.myTable99 in D:\Tax\melaticTestData.txt -SNJROS1D151\NJROS1D151DEV -Usa -P -c'
GO

SELECT LEN(Col1) FROM myTable99

INSERT INTO myTable99(Col1)
SELECT REPLICATE('1234567890',800) UNION ALL

SELECT REPLICATE( SPACE(9)+'1'+SPACE(9)+'2'+SPACE(9)+'3'+SPACE(9)+'4 '+SPACE(9)+'5'
+ SPACE(9)+'6'+SPACE(9)+'7'+SPACE(9)+'8'+SPACE(9)+'9 '+SPACE(9)+'0',80) UNION ALL

SELECT REPLICATE( SPACE(99)+'1'+SPACE(99)+'2'+SPACE(99)+'3'+SPACE(99 )+'4'+SPACE(99)+'5'
+ SPACE(99)+'6'+SPACE(99)+'7'+SPACE(99)+'8'+SPACE(99 )+'9'+SPACE(99)+'0',8) UNION ALL

SELECT SPACE(999)+'1'+SPACE(999)+'2'+SPACE(999)+'3'+SPACE (999)+'4'+SPACE(999)+'5'
+ SPACE(999)+'6'+SPACE(999)+'7'+SPACE(999)+'8'

SELECT * FROM myTable99

-- Then you could build a select statement

SELECT SUBSTRING(Col1,0001,001) AS [Product Type]
, SUBSTRING(Col1,0003,006) AS [Plan]
, SUBSTRING(Col1,0010,010) AS [Policay Number]
, SUBSTRING(Col1,0021,010) AS [ID]
-- ect
FROM myTable99
WHERE SUBSTRING(Col1,1,1) <> '1'
GO

DROP TABLE myTable99
GO

--Use The Column list (Hopefully the match the names of the table

-- Cut and paste the select for reference doen above after to help build the select|||Sorry, i was out sick. SICK I TELL YOU!!! :)

I'm happy now though, because I got that developer to start using vertical bars as column delimiters instead of commas. I persuaded him it was just better, and he happily obliged. Aaah, the power of communication.

So, thanks blindman and brett kaiser for you help. I adore both of you to pieces.|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".
A pipe-delimited developer ... I know the answer to this one!!!
Kris Kringle;)|||I prefer tab delimited|||Funny, you struck me as someone who would prefer Diet Coke delimited over Tab delimited...|||Funny, you struck me as someone who would prefer Diet Coke delimited over Tab delimited...Heck, I'll take Tab every time! It is one of the few Coca-Cola products that I like!

-PatP|||If it's not ta kill ya, then it's Poland spring....

I actually pass it on the way to and from Sunday River|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".

I called it pipe delimited, and he calls it vertical bar delimited. He's a C++ developer, I don't know if that matters. Allz I know is, I'm a happy gal.

Again, thanks so much for your help. I would be a stressball if it weren't for the comfort of my expert friends on dbforums.

only authorized users can access the database in enterprise manager in MSSQL Server

hello,

I want to give password when we open the enterprise manager .So that only the authorised users can access the database & see the tables .

Could u help me for solving this problem.
& Also tell me is it posible or not.

very urgent.........reply soonIf user's rights are restricted - he can't do much through Enterprise Manager.
Revoke all unwanted rights from users.|||tell me is it possible that first we enter the login name & password
if it is valid , only that user can see the database.

I want to do...........

If possible then how will be done.

Only apply border to left and right of stacked column bar chart

Hi.

Is it in anyway possible to only add borders to left and right sides of a column in a bar chart.

I can see in the xsl definition schema that style does allow left, right, top, bottom tags. But if i replace the <Default>4.5pt</Default> with left, right, top and bottom and accordingly 4.5pt and None - the result doesnt seem to be any different.

What i need to do is to create columns like this where y is for instance Yellow and b is black, 1 and 2 are to Values (DataPoint in rdl)

2 YYY
2 YYY
2 YYY
2 YYY
2 YYY
1 YBY
1 YBY
1 YBY
1 YBY
1 YBY
1 YBY
1 YBY

I've created this using yellow borders on datapoint 1. But if one is very small - i wont see any of the black part because borders fills entire space.

What i would like is the borders to only be left and right and black fill all space from top to bottom.

Hope there is a way to do this.

Best Regards
Anders

The Dundas chart control only supports borders on all edges, while RDL reportitems support borders on individual edges. The closest you can get in the chart would be to determine the border width based on an expression. (e.g. 3pt vs. 2pt vs. 1pt border width)

-- Robert

|||Ok, thanx for the answer..

I've done it opposite of my first suggestion - i've just put a small dotted black border around the "used" colomn and then the fill color inside.

This way i can make the destinction visually, and get the marked section to actually fill the percentage it should.

But its nice to now that it's not possible to do.

Once again thanks :-)

Only administrators can connect

We have been having a problem recently where all of a sudden sql starts
denying connections. The errors in the sql log say "login failed for
<user>. only administrators may connect at this time". I've been unable to
find any documentation on the net for what exactly this is and I can't seem
to figure out what's causing it. Any suggestions?Did you by any chance start the server with -m? If so, remove -m will allow
non administrator to connect.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent Mills" <*bcm*@.hotmail.com> wrote in message
news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> We have been having a problem recently where all of a sudden sql starts
> denying connections. The errors in the sql log say "login failed for
> <user>. only administrators may connect at this time". I've been unable
to
> find any documentation on the net for what exactly this is and I can't
seem
> to figure out what's causing it. Any suggestions?
>|||No, that's the weird thing. It works fine for about a week or two and then
all of a sudden it just starts denying connections.
"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:OPZBCl$$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Did you by any chance start the server with -m? If so, remove -m will
allow
> non administrator to connect.
> --
> Gang He
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brent Mills" <*bcm*@.hotmail.com> wrote in message
> news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> > We have been having a problem recently where all of a sudden sql starts
> > denying connections. The errors in the sql log say "login failed for
> > <user>. only administrators may connect at this time". I've been
unable
> to
> > find any documentation on the net for what exactly this is and I can't
> seem
> > to figure out what's causing it. Any suggestions?
> >
> >
>

Only administrators can connect

We have been having a problem recently where all of a sudden sql starts
denying connections. The errors in the sql log say "login failed for
<user>. only administrators may connect at this time". I've been unable to
find any documentation on the net for what exactly this is and I can't seem
to figure out what's causing it. Any suggestions?Did you by any chance start the server with -m? If so, remove -m will allow
non administrator to connect.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent Mills" <*bcm*@.hotmail.com> wrote in message
news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> We have been having a problem recently where all of a sudden sql starts
> denying connections. The errors in the sql log say "login failed for
> <user>. only administrators may connect at this time". I've been unable
to
> find any documentation on the net for what exactly this is and I can't
seem
> to figure out what's causing it. Any suggestions?
>|||No, that's the weird thing. It works fine for about a week or two and then
all of a sudden it just starts denying connections.
"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:OPZBCl$$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Did you by any chance start the server with -m? If so, remove -m will
allow
> non administrator to connect.
> --
> Gang He
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brent Mills" <*bcm*@.hotmail.com> wrote in message
> news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
unable
> to
> seem
>

Only Administrator May Connect

What would cause a SQL Server 2000 Clustered server to
output the error message listed below. This is a Windows
2000 Advanced Server. The error was pulled out of the SQL
Server Errorlog.
Error Message:
Login failed for user 'DALLAS\BLKSDRRT'. Only
administrators may connect at this time.
Thank You,
Dan
You may have the database access restricted to members of db_owner, dbcreator or sysadmins. Administrators can set this using either SQL Enterprise Manager or the sp_dboptions '[database name]','dbo use only' TSQL command. If you run the previous comman
d (and you are in the sysadmin role or db_owner role) it will tell you if it is "ON" or "OFF".
|||This can also occur if SQL Server it self has been set to single user mode.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

However, I want to add a new group so that non-administrators can access Report Manager. However, whatever new item I add in the permissions appears to have no effect. For instance, if I add a permission for Users, i.e. :

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

Hi Andy! Reporting Services is Active Directory (AD) aware. If an AD group has been created (ex: reportusers), you can grant access to that group by going to the Home Folder in Report Manager and clicking on properties and then "New Role Assignment" where you would put something like this: DomainName\GroupID (ex: mydomain\reportusers).

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

What is interesting is that if I allow this group System Administrator access in Man. Studio, and I then log on as that user, I appear to have full access as I do for actual administrator users, but only within Management Studio. I still get the standard blank page (i.e. just "Home" bar but no visible reports) in Internet Explorer when connecting to Report Manager!

So there would appear to be some mismatch between what permissions I have within Report Manager versus Management Studio. My guess (and it's only a guess) is that there's either some IIS configuration that's wrong, or something in the security setup is blocking access via a web interface.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

2) Anonymous access (only suggested if used with security extensions). Anonymous access limites your ability to vary role assignment because all users will access the Report Server under the Anonymous user account.

3) Basic Authentification, which should only be used with a Secure Sockets Layer (SSL) connection because Basic Authentification sends username and passwords to the server in clear text, which could be picked up with a network sniffer.

The Integrated Windows Authentification method is preferred and widely used. Your network administrator can setup Active Directory groups on the Domain Controller, which you can then grant those groups access to Reporting Services. If you network users have Unix, Mac, Novell or Linux accounts (non-Windows accounts) then you will need to go with Forms Authentification. Forms Authentification directs a request from an unauthenticated user to an HTML form, where they are prompted to enter a username and password. I have not used this method but many in this forum have and a quick search of the forum should return several useful posts.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

I can understand that my problem is to do with the security setup, but I can't get my head around what the difference is between accessing from Internet Explorer and accessing via Management Studio. It must, surely, be something to do with security for IIS rather than Reporting Services itself?

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Andy, I'm sorry I didn't suggest to verify the users had rights to the home page properties section first as this is required before giving them rights to any sub-folders you may create. I'm glad you figured this out and hope you enjoy reporting services as much as I have.

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Only able to access Report Manager as Administrator

Hi,

I have Reporting Services set up with the permissions (via Right Click Server/Permisions in SSMS or System Role Assignments in Report Manager) set as follows:

BUILTIN\Administrators - System Administrator

When logged in as an administrator I am able to access Report Manager, no problem.

BUILTIN\Administrators - System Administrator

BUILTIN\Users - System Administrator

I can still only access Report Manager as an administrator.. Any other user just gets the blank page.

I have tried creating a custom group, Report Users, with the same result.

I checked IIS to ensure anonymous access was turned off (this was an issue I had earlier on in my setup - it was turned on, but is now firmly off).

I don't know much about IIS, but I guess the problem is there somewhere. Does anybody have any idea what the problem could be and how I get around it?

Thanks very much in advance

Andy

If you are not using Active Directory, then please explain where (Report Manager or SQL Server Management Studio) you are creating your groups and how you have added security and we'll go from there.

|||

Hi Chuck,

Thank you so much for your reply.

I have to confess to not being an expert at Windows Server configuration.. and I'm not sure whether the server I am using employs Active Directory.. How can I tell?

I am creating the users in Admin Tools/Computer Management/Local Users and Groups.

I now have a user (MyUser) which is a member of a group (Report Users).

I have added this group in Management Studio (Right Click server/Permissions) as a System User. I am also able to add it via Report Manager/Site Security, it has the same effect.

Hope this sheds some light on things.

Thanks again for your interest

Andy

|||

There are three types of Authentification in Reporting Services:

1) Integrated Windows Authentification (Active Directory aware) is the default authentification method for the Report Server and Report Manager virtual directories.

|||

Hi Chuck,

Thanks very much for the information,

I am using Integrated Windows Authentication. The users I am dealing with are local to this server, it does not appear to be a domain server or to be part of a domain.

If so, can you give me any clues as to which permissions I should check and where? I can't tell whether it's withing IIS or some local policy on the server that is blocking the access.

Thanks again for your help and interest.

Regards,

Andy

|||

I eventually found out what this was..

It was nothing to do with Windows authentication - as I suspected because the access I granted worked in SSMS, but not via the web interface.

It was just that I had to add Browser access for my users via the Report Manager web interface .. on the home page/properties/New Role Assignment .. Once I did that everything worked as I had hoped.

A previous installation I carried out didn't require this.. I suspect that all users were granted Browser access by default, which wasn't the case here.

Thanks for your interest Chuck!

Andy

|||

Saturday, February 25, 2012

Only one case table allowed for Time series algortihm?

Only members of sysadmin role are allowed to update or delete jobs owned by a different login

Only members of sysadmin role are allowed to update or delete jobs owned by a different login

only Japanese got error from WebSphere to SQL 2000

Only getting one record in preview mode

Only get dates

Only functions and extended stored procedures can be executed from within a function

Only for SQL GURUS - Calling external procedures from Functions

Only for Gurus -- Analysis MDX Query help needed

Only first row appearing

Only enable the string truncation prevention of ANSI_WARNINGS

only each first item from a select with group ....

Only displaying the subtotal value in a matrix report.

Only Date

Only data for Yesterday

Only counting values that appear more than once

Only copy missing rows from one database to another

Only Configuration tool downloaded, why?

Only bringing in select columns from a text file

only authorized users can access the database in enterprise manager in MSSQL Server

Only apply border to left and right of stacked column bar chart

Only administrators can connect

Only administrators can connect

Only Administrator May Connect

Only able to access Report Manager as Administrator

Only able to access Report Manager as Administrator

Only able to access Report Manager as Administrator

Online Data Transform

Blog Archive

About Me