Request: Help creating a difficult view.

Hello all.

I have a table defined in sql server as follows:

ROW_ID (identity)
DEPTH_FROM Number (8,3)
DEPTH_TO Number (8,3)
COLOUR Char(10)

With typical data like:
ROW_ID DEPTH_FROM DEPTH_TO COLOUR
-------------------
1 0 5
BLUE
2 5 8
BLUE
3 8 10
RED
4 10 12
GREEN
5 12 16
GREEN

I want to create a view that will 'compress/roll up' the data so
it appears like:

DEPTH_FROM DEPTH_TO COLOUR
------------------
0 8 BLUE
8 10 RED
10 16 GREEN

I have been working on this for several days, with no luck,
any help would be appreciated. BTW: there are no overlaps
allowed in the depth_from, depth_to values.

Thanks in advance.I'll assume that the colours don't always occur at consecutive depths
otherwise you could just do this:

SELECT MIN(depth_from), MAX(depth_to), colour
FROM ColDepths
GROUP BY colour

Here's the (assumed) DDL and sample data. It helps if you include this with
posts.

CREATE TABLE ColDepths (row_id INTEGER NOT NULL UNIQUE, depth_from INTEGER
NOT NULL, depth_to INTEGER NOT NULL, colour CHAR(10) NOT NULL,
CHECK(depth_from<depth_to), PRIMARY KEY (depth_from,depth_to))

INSERT INTO ColDepths VALUES (1,0,5, 'BLUE')
INSERT INTO ColDepths VALUES (2,5,8, 'BLUE')
INSERT INTO ColDepths VALUES (3,8,10, 'RED')
INSERT INTO ColDepths VALUES (4,10,12, 'GREEN')
INSERT INTO ColDepths VALUES (5,12,16, 'GREEN')

Here's my query.

SELECT MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM ColDepths AS A
JOIN
(SELECT c1.row_id, MIN(C2.depth_to) AS next_depth
FROM ColDepths AS C1
LEFT JOIN ColDepths AS C2
ON C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY c1.row_id) AS B
ON A.row_id = B.row_id
GROUP BY A.colour, B.next_depth

--
David Portas
----
Please reply only to the newsgroup
--|||Is this what you have in mind?

create table foo
(ROW_ID int, /* my datatypes vary from yours for my convenience
*/
DEPTH_FROM int,
DEPTH_TO int,
COLOUR Char(10))
go
insert foo values (1,0,5,'blue')
insert foo values (2,5,8,'blue')
insert foo values (3,8,10,'red')
insert foo values (4,10,12,'green')
insert foo values (5,12,16,'green')
go
select min(depth_from) as depth_from, max(depth_to) as depth_to, colour
from foo
group by colour

depth_from depth_to colour
---- ---- ----
0 8 blue
10 16 green
8 10 red

(3 row(s) affected)

You may want an ORDER BY clause, too. Order of rows returned with GROUP BY
is not guaranteed/predictable.

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||"Dave Pylatuk" <davep@.centurysystems.net> wrote...

> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN

I don't use SqlServer but... wouldn't this work?

select min(depth_from), max(depth_to), colour from <tablename> group by
colour|||Testing these suggestions right now, thanks to all

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||Hi Dave,

If there is no overlap but there can be gaps between intervals, this is the
query you want:

select DEPTH_FROM,
DEPTH_TO = (select min(DEPTH_TO)
from T T3
where DEPTH_TO not in (select DEPTH_FROM
from T T4
where T3.COLOUR = T4.COLOUR
and T3.DEPTH_FROM <>
T4.DEPTH_FROM
)
and T3.DEPTH_TO > T1.DEPTH_FROM
),
COLOUR
from T T1
where DEPTH_FROM not in (select DEPTH_TO
from T T2
where T1.COLOUR = T2.COLOUR
and T1.DEPTH_FROM <> T2.DEPTH_FROM
)
order by DEPTH_FROM

Good Luck,
Shervin

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.

This will also handle gaps between consecutive depth intervals.

CREATE TABLE ColorDepths
(
depth_from INT NOT NULL PRIMARY KEY,
depth_to INT NOT NULL,
color CHAR(10) NOT NULL,
CHECK (depth_from <= depth_to)
)

-- Your sample data augmented to better exercise code
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (0,5, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (5,8, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (8,10, 'RED')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (11,12, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (12,15, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (16, 18, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (18, 24, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (26, 30, 'BLUE')

-- Associate consecutive depth intervals with natural numbers
CREATE VIEW OrderedColorDepths (depth_from, depth_to, color, seq)
AS
SELECT D1.depth_from, D1.depth_to, D1.color, COUNT(*)
FROM ColorDepths AS D1
INNER JOIN
ColorDepths AS D2
ON D2.depth_from <= D1.depth_from
GROUP BY D1.depth_from, D1.depth_to, D1.color

-- Using above natural numbers, find endpoints
CREATE VIEW ColorDepthEnds (color, seq)
AS
SELECT OD1.color, OD1.seq
FROM OrderedColorDepths AS OD1
LEFT OUTER JOIN
OrderedColorDepths AS OD2
ON OD2.seq = OD1.seq + 1
WHERE OD2.color <> OD1.color OR -- consecutive depths w/ diff. colors
OD2.color IS NULL OR -- last (greatest) depth
OD2.depth_from > OD1.depth_to -- gap between consecutive depths

SELECT color,
MIN(depth_from) AS depth_from , MAX(depth_to) AS depth_to
FROM (SELECT OD.color, OD.depth_from, OD.depth_to,
MIN(DE.seq) AS seq
FROM OrderedColorDepths AS OD
INNER JOIN
ColorDepthEnds AS DE
ON DE.seq >= OD.seq AND
DE.color = OD.color
GROUP BY OD.depth_from, OD.depth_to, OD.color) AS R
GROUP BY seq, color
ORDER BY depth_from

color depth_from depth_to
BLUE 0 8
RED 8 10
GREEN 11 15
BLUE 16 24
BLUE 26 30

Regards,
jag

request/response style profiling

don't laugh,
is there some utility that enables a request/response style profiling on SQL
server.
this is really needed.
I mean not only seeing the SQL request like the SQL server profiler does,
but seeing also the response in some way, lets say first 10 rows etc.
it can really ease debugging
if there is no such way but you know how to do this using some profiling API
to SQL server it can also help.
TIA.
z f wrote:
> don't laugh,
> is there some utility that enables a request/response style profiling
> on SQL server.
> this is really needed.
> I mean not only seeing the SQL request like the SQL server profiler
> does, but seeing also the response in some way, lets say first 10
> rows etc.
> it can really ease debugging
> if there is no such way but you know how to do this using some
> profiling API to SQL server it can also help.
>
> TIA.
Profiler shows this is as the Duration, which is the time SQL Server takes
to execute and the client to fetch all results. You can see this easily with
a large enough table from SQL EM (do not do this in production). Start a
Profiler trace (RPC:Starting/Completed and SQL:BatchStarting/Completed
events). Open a table from SQL EM (All Rows). You'll see the starting event.
Hit CTR+END to fetch all rows. You'll then see the Completed event. Is this
what you are looking for? If not, it may be something you'll have to program
from the data access layer in your source code.
David Gugick
Quest Software
|||There's no way to get at the responses with SQL Profiler.
The only way I'm aware of to get this information without changing your app
is to use a protocol analyser such as Ethereal. Good news is that Ethereal
is free & conveniently exports its captures to xml files which are then
fairly easy to work with.
It wouldn't be too hard to write an xml parser to extract the first 10 rows
from the output..
Regards,
Greg Linwood
SQL Server MVP
"z f" <dont@.send.mails> wrote in message
news:eg0Uage6FHA.4076@.tk2msftngp13.phx.gbl...
> don't laugh,
> is there some utility that enables a request/response style profiling on
> SQL server.
> this is really needed.
> I mean not only seeing the SQL request like the SQL server profiler does,
> but seeing also the response in some way, lets say first 10 rows etc.
> it can really ease debugging
> if there is no such way but you know how to do this using some profiling
> API to SQL server it can also help.
>
> TIA.
>
>
|||does the protocol SQL server used is being parsed by ethereal (like HTTP )
to not show only the binary data?
is it public like HTTP?
because if not there is no way to parse the data.
thanks
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23fysOpi6FHA.2608@.tk2msftngp13.phx.gbl...
> There's no way to get at the responses with SQL Profiler.
> The only way I'm aware of to get this information without changing your
> app is to use a protocol analyser such as Ethereal. Good news is that
> Ethereal is free & conveniently exports its captures to xml files which
> are then fairly easy to work with.
> It wouldn't be too hard to write an xml parser to extract the first 10
> rows from the output..
> Regards,
> Greg Linwood
> SQL Server MVP
> "z f" <dont@.send.mails> wrote in message
> news:eg0Uage6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||Not having a public protocol certainly does NOT mean that there's no way to
parse the binary data. For example, some client access libraries such as ADO
have documented & even supported de-serialization methods which you can use
to reverse a byte stream to an instance of an object. This was a very common
technique amongst MSMQ developers years ago ( & even today ) as they would
often serialise an ADO recordset to a byte stream, pack it into a MSMQ
message, transmit it somewhere, then de-serialize it at the other end of the
wire. I don't have that code handy right now but can definitely dig it up if
you're looking to unpack older ADO stuff.
SQL Server's TDS protocol is documented to a reasonable extend here:
http://www.freetds.org. How useful this is to you will depend on which
version of SQL Server you're using (which you haven't stated) & also what
you're actually intending to do (eg script something for your own ad-hoc use
or build a toolset that you're planning to sell which requires significantly
more efffort).
The real trick here is to know what protocols you're intending to work with
& target them individually. Trying to build an all-encompassing solution
that covers all client access protocols probably isn't feasible, given the
number & versions of apis available.
I didn't mean to trivialise this when I suggested the idea. Whether this is
worth your while reallyy depends on how badly you need a solution to this.
If you're dealing with your own application, it may well be easier to simply
insert some extra code in your data layer & pluck the results from within
whatever library you're using.
Regards,
Greg Linwood
SQL Server MVP
"z f" <dont@.send.mails> wrote in message
news:eI50ahn6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> does the protocol SQL server used is being parsed by ethereal (like HTTP )
> to not show only the binary data?
> is it public like HTTP?
> because if not there is no way to parse the data.
> thanks
>
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:%23fysOpi6FHA.2608@.tk2msftngp13.phx.gbl...
>

Request to IIS failed

hi

i having having an error request to IIs failed evertime.i have even uinstall my firewall but it is still not wking.here is my sql server diagnotics

SQL Server Mobile Server Agent Diagnostics

2006/07/15 15:57:03

General Information Item Value Server Name nisha URL /SQLMobile/sqlcesa30.dll Authentication Type Anonymous Server Port 80 HTTPS off Server Software Microsoft-IIS/5.1 Replication Allowed RDA Allowed Logging Level 1

Impersonation and Access Tests Action Status ErrorCode Impersonate User SUCCESS 0x0 ReadWriteDeleteMessageFile SUCCESS 0x0

SQL Server Mobile Modules Test Module Status ErrorCode Version SQLCERP30.DLL SUCCESS 0x0 3.0.5207.0 SQLCESA30.DLL SUCCESS 0x0 3.0.5207.0

Reconciler Test Reconciler Status ErrorCode 9.0 Database Reconciler SUCCESS 0x0 8.0 Database Reconciler FAILURE 0x80004005

SQL Server Module Versions Module Version sqloledb.dll 2000.85.1117.0 9.0 replrec.dll 2005.90.1399.0 9.0 replprov.dll 2005.90.1399.0 9.0 msgprox.dll 2005.90.1399.0

i dont kno how to solve the database reconciler.i am not able to synchronize.my coding is as follows:

Dim repl As New SqlCeReplication()

Dim strpath As String

strpath = "\Program Files\Flight_PPC\SQLFlight.sdf"

repl.InternetUrl = "http://nisha/SQLMobile/sqlcesa30.dll"

repl.Publisher = "NISHA"

repl.PublisherDatabase = "flightDatabase"

repl.PublisherSecurityMode = SecurityType.DBAuthentication

repl.PublisherLogin = "sa"

repl.PublisherPassword = "pradha"

repl.Publication = "SQLMobile"

repl.Subscriber = "SQLMobile"

repl.SubscriberConnectionString = "Data Source=" + strpath

Try

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

End Try

Try increasing your default timeout value on the IIS server and also the timeout from the client and see if that helps.|||Hi, I'm having the same problem...

The timeout of my IIS is set to 120 seconds, so I don't think that is the problem. I have the same exact display page as posted above. Any other idea?

Thanks
|||

Hi, I still have the same issue. Any other suggestions will be appreciated. Thanks.

|||

Can you post the error message? You can see the log within "Replication Monitor". Open up SQL Server Managment Studio. Right click on "Replication" and choose "Launch Replication Monitor".

Request to IIS failed

hi

i having having an error request to IIs failed evertime.i have even uinstall my firewall but it is still not wking.here is my sql server diagnotics

SQL Server Mobile Server Agent Diagnostics

2006/07/15 15:57:03

General Information Item Value Server Name nisha URL /SQLMobile/sqlcesa30.dll Authentication Type Anonymous Server Port 80 HTTPS off Server Software Microsoft-IIS/5.1 Replication Allowed RDA Allowed Logging Level 1

Impersonation and Access Tests Action Status ErrorCode Impersonate User SUCCESS 0x0 ReadWriteDeleteMessageFile SUCCESS 0x0

SQL Server Mobile Modules Test Module Status ErrorCode Version SQLCERP30.DLL SUCCESS 0x0 3.0.5207.0 SQLCESA30.DLL SUCCESS 0x0 3.0.5207.0

Reconciler Test Reconciler Status ErrorCode 9.0 Database Reconciler SUCCESS 0x0 8.0 Database Reconciler FAILURE 0x80004005

SQL Server Module Versions Module Version sqloledb.dll 2000.85.1117.0 9.0 replrec.dll 2005.90.1399.0 9.0 replprov.dll 2005.90.1399.0 9.0 msgprox.dll 2005.90.1399.0

i dont kno how to solve the database reconciler.i am not able to synchronize.my coding is as follows:

Dim repl AsNew SqlCeReplication()

Dim strpath AsString

strpath = "\Program Files\Flight_PPC\SQLFlight.sdf"

repl.InternetUrl = "http://nisha/SQLMobile/sqlcesa30.dll"

repl.Publisher = "NISHA"

repl.PublisherDatabase = "flightDatabase"

repl.PublisherSecurityMode = SecurityType.DBAuthentication

repl.PublisherLogin = "sa"

repl.PublisherPassword = "pradha"

repl.Publication = "SQLMobile"

repl.Subscriber = "SQLMobile"

repl.SubscriberConnectionString = "Data Source=" + strpath

Try

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

EndTry

Try increasing your default timeout value on the IIS server and also the timeout from the client and see if that helps.|||Hi, I'm having the same problem...

The timeout of my IIS is set to 120 seconds, so I don't think that is the problem. I have the same exact display page as posted above. Any other idea?

Thanks
|||

Hi, I still have the same issue. Any other suggestions will be appreciated. Thanks.

|||

Can you post the error message? You can see the log within "Replication Monitor". Open up SQL Server Managment Studio. Right click on "Replication" and choose "Launch Replication Monitor".

Request to IIS failed

hi

i having having an error request to IIs failed evertime.i have even uinstall my firewall but it is still not wking.here is my sql server diagnotics

SQL Server Mobile Server Agent Diagnostics

2006/07/15 15:57:03

General Information

Item

Value

Server Name

nisha

URL

/SQLMobile/sqlcesa30.dll

Authentication Type

Anonymous

Server Port

80

HTTPS

off

Server Software

Microsoft-IIS/5.1

Replication

Allowed

RDA

Allowed

Logging Level

1

Impersonation and Access Tests

Action

Status

ErrorCode

Impersonate User

SUCCESS

0x0

ReadWriteDeleteMessageFile

SUCCESS

0x0

SQL Server Mobile Modules Test

Module

Status

ErrorCode

Version

SQLCERP30.DLL

SUCCESS

0x0

3.0.5207.0

SQLCESA30.DLL

SUCCESS

0x0

3.0.5207.0

Reconciler Test

Reconciler

Status

ErrorCode

9.0 Database Reconciler

SUCCESS

0x0

8.0 Database Reconciler

FAILURE

0x80004005

SQL Server Module Versions

Module

Version

sqloledb.dll

2000.85.1117.0

9.0 replrec.dll

2005.90.1399.0

9.0 replprov.dll

2005.90.1399.0

9.0 msgprox.dll

2005.90.1399.0

i dont kno how to solve the database reconciler.i am not able to synchronize.my coding is as follows:

Dim repl As New SqlCeReplication()

Dim strpath As String

strpath = "\Program Files\Flight_PPC\SQLFlight.sdf"

repl.InternetUrl = "http://nisha/SQLMobile/sqlcesa30.dll"

repl.Publisher = "NISHA"

repl.PublisherDatabase = "flightDatabase"

repl.PublisherSecurityMode = SecurityType.DBAuthentication

repl.PublisherLogin = "sa"

repl.PublisherPassword = "pradha"

repl.Publication = "SQLMobile"

repl.Subscriber = "SQLMobile"

repl.SubscriberConnectionString = "Data Source=" + strpath

Try

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

End Try

Try increasing your default timeout value on the IIS server and also the timeout from the client and see if that helps.|||Hi, I'm having the same problem...

The timeout of my IIS is set to 120 seconds, so I don't think that is the problem. I have the same exact display page as posted above. Any other idea?

Thanks|||

Hi, I still have the same issue. Any other suggestions will be appreciated. Thanks.

|||

Can you post the error message? You can see the log within "Replication Monitor". Open up SQL Server Managment Studio. Right click on "Replication" and choose "Launch Replication Monitor".

Request timeout problem

Hi everybody.

Got a nice little problem here. I have a accessdatabase containing 100 000 rows. Im fetching these rows to a dataset and then inserting them, row by row, to a MSSQL dB. The dB and IIS is running on the same server. I also have full control over this webserver, so I pushed theServer.ScriptTimeout valueup to 3600 sec (both in IIS and in the c# code) but when executing this query (witch aprox. take me 8 minutes) I recieve aError Code: 408. The operation timed out. The remote server did not respond within the set time allowederror.

Someone got a clue for me? :)

-Thomas

Perhaps retrieving 100,000 rows and inserting them one at a time into another server, from a front end is not a good idea. Try looking into other options like (1) creating a text file from access and doing a BULK INSERT into SQL server or (2) creating a DTS package or (3) check if OPENROWSET works. Read up books on line for each of these options and see which works best for you.

|||

Ok, I will do :) Thanks for the quick reply!

Got a tip on how to generate custom reports on this as well? Have a select statement based on a BETWEEN two DATETIME stamps, it take some time, but seems to work ok, but I'm always looking for a way to improve this :)

Appreciate it!

request Stored procedure filtered by Today

I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date

In SqlServer, today's function is GetDate()|||

Yes in T-SQL we use GETDATE() to get today'date, but remember that the GETDATE() function will return a DATETIME value which also contains time. So if you just want to compare date, you may need something like this (suppose the EXPDATE column is also DATETIME data type):

select * fromyourTable
WHERE DATEDIFF(d,GETDATE(),EXPDATE)>0

Request only the required parameters

Hello,
I have a rdl document with 1 DataSource and many dataSets (all MDX
type). For each dataSet I have one or more parameters (any of them
optional).
This rdl is clean in design terms (in layout mode there aren't any
objects). The goal is to give this file to other user(client) and he
will decide what data he want to see.
The problem that I have, simulating what the end user will do, is
after choosing some columns of data, when previewing, the Visual
Studio asks for all parameters of the rdl, even if I'm only asking to
see data of a particular dataset.
Is there a way to request only the required parameters for the data
that I want to see?
I already thought to divide this rdl document in many documents (one
for each dataset) but the client doesn't want that.
Thanks,
Sidhartagive each parameter a default value in report
"Sidharta" wrote:
> Hello,
> I have a rdl document with 1 DataSource and many dataSets (all MDX
> type). For each dataSet I have one or more parameters (any of them
> optional).
> This rdl is clean in design terms (in layout mode there aren't any
> objects). The goal is to give this file to other user(client) and he
> will decide what data he want to see.
> The problem that I have, simulating what the end user will do, is
> after choosing some columns of data, when previewing, the Visual
> Studio asks for all parameters of the rdl, even if I'm only asking to
> see data of a particular dataset.
> Is there a way to request only the required parameters for the data
> that I want to see?
> I already thought to divide this rdl document in many documents (one
> for each dataset) but the client doesn't want that.
> Thanks,
> Sidharta
>

Request Length Exceeded

I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
I am working in Visual Studio 2005. I have a Report Model project with a
data source, a data source view which contains all of the tables in my
database (SQL Server 2005), and a Report Model that contains the items that I
want the users to be able to work with.
When I deploy the project, I get the following error:
Error 2 System.Web.Services.Protocols.SoapException: There was an exception
running the extensions specified in the config file. -->
System.Web.HttpException: Maximum request length exceeded. at
System.Web.HttpRequest.GetEntireRawContent() at
System.Web.HttpRequest.get_InputStream() at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
inner exception stack trace -- at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
HttpContext context, HttpRequest request, HttpResponse response, Boolean&
abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
How do I fix it?
Thank you,
Mark LauserI second that. Ran into the same problem today.
Don Olsen
"Mark Lauser" wrote:
> I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
>
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser
>|||After re-reading this error and looking at the smdl file I suspected that the
problem was the XML being sent to the SOAP service was too long. Started
deleting some unused tables and viola it worked after deleting about 6
tables. Your milage may vary.
Hope that helps.
-Don
"Mark Lauser" wrote:
> I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
>
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser
>|||Thanks for your input Don. I tried deleting all I could stand to, but the
problem persisted. I'm hoping for a different solution that will allow a
larger request length.
Best Regards,
Mark Lauser
"Don Olsen" wrote:
> After re-reading this error and looking at the smdl file I suspected that the
> problem was the XML being sent to the SOAP service was too long. Started
> deleting some unused tables and viola it worked after deleting about 6
> tables. Your milage may vary.
> Hope that helps.
> -Don
> "Mark Lauser" wrote:
> > I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
> >
> >
> > I am working in Visual Studio 2005. I have a Report Model project with a
> > data source, a data source view which contains all of the tables in my
> > database (SQL Server 2005), and a Report Model that contains the items that I
> > want the users to be able to work with.
> >
> > When I deploy the project, I get the following error:
> >
> > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > running the extensions specified in the config file. -->
> > System.Web.HttpException: Maximum request length exceeded. at
> > System.Web.HttpRequest.GetEntireRawContent() at
> > System.Web.HttpRequest.get_InputStream() at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > inner exception stack trace -- at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> >
> > How do I fix it?
> >
> > Thank you,
> > Mark Lauser
> >|||Hey, Mark
I think I just replied to your original post, so see if you can dig back a
couple of posts and read the answer...but it has to do with the <httpRuntime
maxRequestLength="" /> element in the web.config file of the ReportServer and
Report Manager apps.
--
Regards,
Thiago Silva
"Mark Lauser" wrote:
> Thanks for your input Don. I tried deleting all I could stand to, but the
> problem persisted. I'm hoping for a different solution that will allow a
> larger request length.
> Best Regards,
> Mark Lauser
>
> "Don Olsen" wrote:
> > After re-reading this error and looking at the smdl file I suspected that the
> > problem was the XML being sent to the SOAP service was too long. Started
> > deleting some unused tables and viola it worked after deleting about 6
> > tables. Your milage may vary.
> >
> > Hope that helps.
> >
> > -Don
> >
> > "Mark Lauser" wrote:
> >
> > > I'm re-posting under an updated subscriber ID so Microsoft techs will answer...
> > >
> > >
> > > I am working in Visual Studio 2005. I have a Report Model project with a
> > > data source, a data source view which contains all of the tables in my
> > > database (SQL Server 2005), and a Report Model that contains the items that I
> > > want the users to be able to work with.
> > >
> > > When I deploy the project, I get the following error:
> > >
> > > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > > running the extensions specified in the config file. -->
> > > System.Web.HttpException: Maximum request length exceeded. at
> > > System.Web.HttpRequest.GetEntireRawContent() at
> > > System.Web.HttpRequest.get_InputStream() at
> > > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > > inner exception stack trace -- at
> > > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> > >
> > > How do I fix it?
> > >
> > > Thank you,
> > > Mark Lauser
> > >

Request Length Exceeded

I am working in Visual Studio 2005. I have a Report Model project with a
data source, a data source view which contains all of the tables in my
database (SQL Server 2005), and a Report Model that contains the items that I
want the users to be able to work with.
When I deploy the project, I get the following error:
Error 2 System.Web.Services.Protocols.SoapException: There was an exception
running the extensions specified in the config file. -->
System.Web.HttpException: Maximum request length exceeded. at
System.Web.HttpRequest.GetEntireRawContent() at
System.Web.HttpRequest.get_InputStream() at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
inner exception stack trace -- at
System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
HttpContext context, HttpRequest request, HttpResponse response, Boolean&
abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
How do I fix it?
Thank you,
Mark LauserHi, Mark
It sounds like your report model file is kinda large so when you
upload/deploy it to the server, the asp.net http request times out before the
file is fully uploaded (it uses the web service).
You are gonna have to change the value in the maxRequestLength attribute of
the httpRuntime element in the web.config file of your ReportServer and
Report Manager so it doesn't time out. The default size is 4096 KB (4 MB),
but I bet your file is much larger, so change it to something like:
<httpRuntime ... maxRequestLength="151200" .../>
Hope this helps you!
--
Regards,
Thiago Silva
"Mark Lauser" wrote:
> I am working in Visual Studio 2005. I have a Report Model project with a
> data source, a data source view which contains all of the tables in my
> database (SQL Server 2005), and a Report Model that contains the items that I
> want the users to be able to work with.
> When I deploy the project, I get the following error:
> Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> running the extensions specified in the config file. -->
> System.Web.HttpException: Maximum request length exceeded. at
> System.Web.HttpRequest.GetEntireRawContent() at
> System.Web.HttpRequest.get_InputStream() at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> inner exception stack trace -- at
> System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> How do I fix it?
> Thank you,
> Mark Lauser|||Thanks, that worked. My problem was that my web.config file didn't have a
maxRequestLength attribute for the httpRuntime element, so my 'Find' didn't
find anything. Now that I know where it is supposed to be, I added the
attribute and it worked fine.
There must be a default that is used when the attribute is not present.
Thanks again!
Mark Lauser
"HC" wrote:
> Hi, Mark
> It sounds like your report model file is kinda large so when you
> upload/deploy it to the server, the asp.net http request times out before the
> file is fully uploaded (it uses the web service).
> You are gonna have to change the value in the maxRequestLength attribute of
> the httpRuntime element in the web.config file of your ReportServer and
> Report Manager so it doesn't time out. The default size is 4096 KB (4 MB),
> but I bet your file is much larger, so change it to something like:
> <httpRuntime ... maxRequestLength="151200" .../>
> Hope this helps you!
> --
> Regards,
> Thiago Silva
> "Mark Lauser" wrote:
> > I am working in Visual Studio 2005. I have a Report Model project with a
> > data source, a data source view which contains all of the tables in my
> > database (SQL Server 2005), and a Report Model that contains the items that I
> > want the users to be able to work with.
> >
> > When I deploy the project, I get the following error:
> >
> > Error 2 System.Web.Services.Protocols.SoapException: There was an exception
> > running the extensions specified in the config file. -->
> > System.Web.HttpException: Maximum request length exceeded. at
> > System.Web.HttpRequest.GetEntireRawContent() at
> > System.Web.HttpRequest.get_InputStream() at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() -- End of
> > inner exception stack trace -- at
> > System.Web.Services.Protocols.SoapServerProtocol.Initialize() at
> > System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type,
> > HttpContext context, HttpRequest request, HttpResponse response, Boolean&
> > abortProcessing) c:\crimson\wmsdev\vbapps2005\desktopweb\wmsreportmodels\CrimsonWMS.smdl 0 0
> >
> > How do I fix it?
> >
> > Thank you,
> > Mark Lauser|||Yeah, the default is in the machine.config file
(C:\WINDOWS\Microsoft.NET\Framework\[VERSION_NUMBER]\CONFIG).
The aspnet engine follows a hierarchy of config files. If it doesn't
find a setting in the inner-most config, it will keep searching up the
hierarchy until it finds it.
Most settings are defined with a default in the machine.config file.
So if you don't override a setting in your application folder's
web.config, it will use whatever is defined in the machine.config file.
Regards,
Thiago Silva|||Thanks, that makes sense.
My problem was further complicated by the fact that there was no
maxRequestLength in my machine.config for the version of the .net framework
that I am working in. It must have defaulted all the way up the hierarchy.
I noticed that the notes in the file say to only include a setting if you
want to override the default (for better performance).
Best Regards,
Mark Lauser
"tafs7" wrote:
> Yeah, the default is in the machine.config file
> (C:\WINDOWS\Microsoft.NET\Framework\[VERSION_NUMBER]\CONFIG).
> The aspnet engine follows a hierarchy of config files. If it doesn't
> find a setting in the inner-most config, it will keep searching up the
> hierarchy until it finds it.
> Most settings are defined with a default in the machine.config file.
> So if you don't override a setting in your application folder's
> web.config, it will use whatever is defined in the machine.config file.
> Regards,
> Thiago Silva
>

request issue

Hi,
I've got two primary keys in a table:

Constraint(QueryId, ConstraintName)

In a stored procedure I select {QueryId, ConstraintName} couples that
match some criteria, and what I want to do is specifying in my a SELECT
statement that I want all of the {QueryId, ConstraintName} that are not
in my stored procedure result. With only one field, it would be easy :

Select * from Constraint where QueryId not in (Select QueryId from
OtherTable)

My explanations are not great but I think it's enough to understand
what I want.

Select * from Constraint where QueryId and ConstraintName not in
(select QueryId ,ConstraintName from OtherTable)
--> of course not correct, but then how can I do that ?

ThxI've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||I've tried this, but it doesn't work.

CREATE PROCEDURE pr_Admin_GetConstraintMessages
AS
SELECT CM.QueryId, Message, Type, Q.QueryName, Q.RootTable,
ConstraintName
FROM ConstraintMessages CM JOIN Queries Q ON CM.QueryId = Q.QueryId
WHERE (CM.QueryId, ConstraintName)
NOT IN (SELECT QueryId, ConstraintName from
fn_Admin_GetOrphanedMessages)
GO

fn_Admin_GetOrphanedMessages returns (queryid, constraintName) couples.

Error message : Incorrect syntax near ','
I guess it is my WHERE statement...|||See this thread:

http://groups.google.ch/group/comp...62403e78dd78646

Simon|||Use NOT EXISTS rather than NOT IN:

SELECT *
FROM [Constraint] AS T
WHERE NOT EXISTS
(SELECT *
FROM OtherTable
WHERE queryid = T.queryid
AND constraintname = T.constraintname)

CONSTRAINT is a reserved word and therefore not a good choice for a
table name.

--
David Portas
SQL Server MVP
--|||Try using a LEFT JOIN:

SELECT a.columnList --don't use *, explicitly name your columns
FROM TableA a LEFT JOIN TableB b ON a.Col1 =b.Col1 AND a.Col2 =b.Col2
WHERE b.Col1 IS NULL

HTH,
Stu|||I've actually solved this problem yesterday. I've done it the way David
suggested, using NOT EXISTS and it works just fine.
David, actually my table is called ConstraintMessages :) I wrote
Constraint as it's quicker to type!

Thx

Request for the permission of type System.Net.Mail.SmtpPermission

Hi all

I have some problems with sending Mail over System.Net.Mail ...
I have made a C# Class which sends mail, and it works fine.
Now I have added this assembly to SQL Server 2005,
I made the SQL function and so on.

When I try to run it I get the following message:
Msg 50000, Level 16, State 1, Procedure SendMail, Line 114
Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Would be very very happy for any comments !!

Thanks and best regards
Frank UrayI just found out what to do ... :-)
Add "External access assembly" to the Login or role .|||

Hi,

how did you register the assembly in SQL Server (which security settings / safe/unsafe/external Access ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Can you send how i can do it? Please.

Request for the permission of type System.Net.Mail.SmtpPermission

Hi all

I have some problems with sending Mail over System.Net.Mail ...
I have made a C# Class which sends mail, and it works fine.
Now I have added this assembly to SQL Server 2005,
I made the SQL function and so on.

When I try to run it I get the following message:
Msg 50000, Level 16, State 1, Procedure SendMail, Line 114
Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Would be very very happy for any comments !!

Thanks and best regards
Frank UrayI just found out what to do ... :-)
Add "External access assembly" to the Login or role .|||

Hi,

how did you register the assembly in SQL Server (which security settings / safe/unsafe/external Access ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Can you send how i can do it? Please.

Request for the permission of type System.Data.SqlClient.SqlClient

Environment:
The report server, report service and report designer are all installed on
the same box. The data is read from the sql database on another sql server
box.
Issue:
Am sure somebody might have already asked or experienced or come across the
below issue in this newsgroup. I have a very basic knowledge of dot net
security.
I have a custom data extension build that retrives dataset from the data
access application block. Data Access application block is placed in GAC. The
reports are able to retrieve dataset successfully on preview mode from visual
studio.
After I deploy the reports on to the report server and try to run the report
using start or from report manager am getting
Reporting service error
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Query execution failed for data set 'DS_PAGENO02_PAGEBODYNO1'.
(rsErrorExecutingCommand) Get Online Help
Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
Any guide or help in resolving this issue is highly appreciated.
--
kvsAs per the recommendations from the other post, I tried the following code,
but still getting the same error.
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
'open connection explicitly
Dim cn As SqlConnection = New SqlConnection(cns)
cn.Open()
Try
Return SqlDataAccess.ExecuteDataSet(cn, sql, arp)
Finally
cn.Dispose()
End Try
--
kvs
"kvs" wrote:
> Environment:
> The report server, report service and report designer are all installed on
> the same box. The data is read from the sql database on another sql server
> box.
> Issue:
> Am sure somebody might have already asked or experienced or come across the
> below issue in this newsgroup. I have a very basic knowledge of dot net
> security.
> I have a custom data extension build that retrives dataset from the data
> access application block. Data Access application block is placed in GAC. The
> reports are able to retrieve dataset successfully on preview mode from visual
> studio.
> After I deploy the reports on to the report server and try to run the report
> using start or from report manager am getting
> Reporting service error
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Query execution failed for data set 'DS_PAGENO02_PAGEBODYNO1'.
> (rsErrorExecutingCommand) Get Online Help
> Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> Any guide or help in resolving this issue is highly appreciated.
> --
> kvs

Request for the permission of type System.Data.SqlClien

I got this message when running the same project at my work which I worked on
at home without encountering it. Thanks for the help.
Request for the permission of type System.Data.SqlClienAdditional
information: Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
bic
Seems that sou didnt approve SQLServer Client to run in the special context
you are using it (wheter you use it with Biztalk or Sharepoint or etc..). It
depends on the type of underlying application you are using, in sharepoint
you have to configure a configfile, in other enviroments you have to set
..NET security for that. (Perhaps at home you have assigned Fulltrust --> To
all and in the comapany there is a domain policy which dont allow any
assemblies to run until they are aproced by some security administrator)
If you have further questions just raise a hand ;-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"bic" <bic@.discussions.microsoft.com> schrieb im Newsbeitrag
news:825EDB65-0534-4A1C-B343-DAE786D8E601@.microsoft.com...
>I got this message when running the same project at my work which I worked
>on
> at home without encountering it. Thanks for the help.
> Request for the permission of type System.Data.SqlClienAdditional
> information: Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> --
> bic

Request for the permission of type System.Data.SqlClien

I got this message when running the same project at my work which I worked o
n
at home without encountering it. Thanks for the help.
Request for the permission of type System.Data.SqlClienAdditional
information: Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
--
bicSeems that sou didnt approve SQLServer Client to run in the special context
you are using it (wheter you use it with Biztalk or Sharepoint or etc..). It
depends on the type of underlying application you are using, in sharepoint
you have to configure a configfile, in other enviroments you have to set
.NET security for that. (Perhaps at home you have assigned Fulltrust --> To
all and in the comapany there is a domain policy which dont allow any
assemblies to run until they are aproced by some security administrator)
If you have further questions just raise a hand ;-)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"bic" <bic@.discussions.microsoft.com> schrieb im Newsbeitrag
news:825EDB65-0534-4A1C-B343-DAE786D8E601@.microsoft.com...
>I got this message when running the same project at my work which I worked
>on
> at home without encountering it. Thanks for the help.
> Request for the permission of type System.Data.SqlClienAdditional
> information: Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0,
> Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
> --
> bic

Request for SqlCeEngine.Exists(), .Delete() methods

Given a connection string, I can create a database using a SqlCeEngine object and call engine.CreateDatabase().

However, there doesn't seem to be a way to determine whether a database exists, given a connection string. I would need to interpret the connection string myself and extract the file name to use File.Exists() etc.

There should be SqlCeEngine methods to test whether a database exists, test whether it's accessible, and to delete it, given a connection string.

Cheers, Oli

It is unlikely that would be added as you can easily do that with simple string manipulations and classes from System.IO – you already know how.

In compact world (where keeping size down is very important) functionality is usually added only if it’s not possible (or very hard) to do or if it’s some very common task. One example would be multiple connections support added in SQL Mobile. Your task is easy to do with existing functionality and it’s not that common – usually connection string is constructed from file name.

In any case you’re welcome to submit a request via Product feedback site: http://connect.microsoft.com/Main/content/content.aspx?ContentID=2220

|||

Hmm...I can get you a work around if you are taking connection string as input.

This should help you!

conn = new SqlCeConnection(inputConnectionString);

if (File.Exists(conn.DataSource))

File.Delete(conn.DataSource);

The important thing to note here is that, Connection Object parses the connection string the moment you assign it (need not call Open). And DataSource property will return the Database File Path.

Thanks,

Laxmi

request for sample program for fulltext search

hi...
i'm newbie here... can i have a sample program for fulltext search.
thanx!
VpUser,
Well, could you be more specific? SQL Server 2000 Full-text Search (FTS)
uses pure T-SQL such as CONTAINS or FREETEXT, for example:
select * from pub_info where CONTAINS(*,'books')
or if you want a SQL-DMO sample program, you can checkout SQL 2000 BOL title
"SQL-DMO Examples: Full-text Indexing"
More info would be helpful. What are you looking to do with the sample
program?
Regards,
John
"VpUser" <vpuser@.someone.com> wrote in message
news:OGwpRiEoEHA.2764@.TK2MSFTNGP11.phx.gbl...
> hi...
> i'm newbie here... can i have a sample program for fulltext search.
> thanx!
>
|||John,
Thanks John! It's very great example. I'm using SQL Server 2000 FTS.
1. May i know what different between CONTAINS and FREETEXT?
eg.
select * from pub_info where CONTAINS(title,'booktitle')
and
select * from pub_info where FREETEXT(title,'booktitle')
Its returns same result.
2. When i try this query:
==> select * from pub_info where CONTAINS(title,'book and title')
It's giving me the error. Error abt noisy words...
May i enter noisy word in the search? how do i know the noisy word by using
SQL statement(not from text file).
regards,
VpUser
|||You're welcome, VpUser,
1. May i know what different between CONTAINS and FREETEXT?
A. CONTAINS and CONTAINSTABLE will return rows that contain the exact word
that you are searching for while FREETEXT or FREETEXTTABLE will return the
exact word and if present, words that "match the meaning and not the exact
wording of the words in the search condition" (from SQL 2000 BOL). So while
in the small table pub_info, the results would be the same for your search
word booktitle, while in larger tables with more diverse text, FREETEXT
would often return more rows than CONTAINS when searching for the same word.
2. The correct sntax for the table pub_info than does not have a column
called "title", would be:
select * from pub_info where CONTAINS(*,'book and title')
When I execute the above query on Win2003, it returns 0 rows and no errors.
However, if I change title in the search condition, to "between" a
US_English noise word:
select * from pub_info where CONTAINS(*,'book and between')
This returns error Msg 7619 ".. A clause of the query contained only
ignored words". This can be avoided by removing "between" from the
US_English noise word file noise.enu that is located under
\FTDATA\SQLServer\Config where you have SQL Server 2000 installed. You can
open and edit this file with notepad.exe, but to save it you will need to
stop the "Microsoft Search" service and then run a Full Population.
Additionally, you can alter the query using quotes or parse the noise word
out via pre-processing before passing it to a SQL Server contains query. I'd
recommend that you review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well as KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
3. May i enter noisy word in the search? how do i know the noisy word by
using SQL statement(not from text file).
A. Yes, but you will need to parse the noise word into phrases or remove it
to avoid the error. You can also use BULK INSERT and import the noise word
file (noise.enu text file) into a SQL table and then use that table as a
lookup table to determine if the searcher enters a noise word.
Regards,
John
"VpUser" <vpuser@.someone.com> wrote in message
news:#JunAhFoEHA.3464@.TK2MSFTNGP14.phx.gbl...
> John,
> Thanks John! It's very great example. I'm using SQL Server 2000 FTS.
> 1. May i know what different between CONTAINS and FREETEXT?
> eg.
> select * from pub_info where CONTAINS(title,'booktitle')
> and
> select * from pub_info where FREETEXT(title,'booktitle')
> Its returns same result.
> 2. When i try this query:
> ==> select * from pub_info where CONTAINS(title,'book and title')
> It's giving me the error. Error abt noisy words...
> May i enter noisy word in the search? how do i know the noisy word by
using
> SQL statement(not from text file).
> regards,
> VpUser
>
|||John,
Thanks your clear explanation. It's very clear and "user friendly", easy for
me to pick up.
Well... i still have one question abt Contains.
I try to run this query in SQL server it giving me the error.
Error ==> select * from pub_info where CONTAINS(*,'yellow book')
it return me the error...
If I enter
OK==> select * from pub_info where CONTAINS(*,'yellow and book')
How do i the query if i want to find "yellow book" exact word in the query?
best regards,
VpUser
|||wrap your search phrase in double quotes
select * from pub_info where CONTAINS(*,'"yellow book"')
that's a single quote, followed by a double quote, followed by yellow book
followed by a double quote followed by a single quote
"VpUser" <vpuser@.someone.com> wrote in message
news:uEs%23$IIoEHA.3968@.TK2MSFTNGP11.phx.gbl...
> John,
> Thanks your clear explanation. It's very clear and "user friendly", easy
> for
> me to pick up.
> Well... i still have one question abt Contains.
> I try to run this query in SQL server it giving me the error.
> Error ==> select * from pub_info where CONTAINS(*,'yellow book')
> it return me the error...
> If I enter
> OK==> select * from pub_info where CONTAINS(*,'yellow and book')
> How do i the query if i want to find "yellow book" exact word in the
> query?
>
> best regards,
> VpUser
>
>
|||I am trying to use Bulk insert to populate a table from a text file.it works
fine on localhost but while running on network it gives the following error.
"Could not bulk insert because file 'E:\far\farextracts\DEGREE.txt' could
not be opened. Operating system error code 5(Access is denied.)."
Any Suggestions or solutions.
Thanks
"VpUser" wrote:

> John,
> Thanks John! It's very great example. I'm using SQL Server 2000 FTS.
> 1. May i know what different between CONTAINS and FREETEXT?
> eg.
> select * from pub_info where CONTAINS(title,'booktitle')
> and
> select * from pub_info where FREETEXT(title,'booktitle')
> Its returns same result.
> 2. When i try this query:
> ==> select * from pub_info where CONTAINS(title,'book and title')
> It's giving me the error. Error abt noisy words...
> May i enter noisy word in the search? how do i know the noisy word by using
> SQL statement(not from text file).
> regards,
> VpUser
>
>
|||MMSqlserver,
Yes. This is a permissions issue related to accessing the file
(E:\far\farextracts\DEGREE.txt) and the account (DOMAIN\account or "Local
System"/LocalSystem) that is use to start the SQL Server (MSSQLServer)
service most likely does not have the correct permission to access the
server and share where this file exists. You should also ensure that the
share (\far\farextracts) has the correct permissions, such as the Everyone
group with Read access.
While not directly related to uploading a file via BULK INSERT, but still
related to the access denied error, you should review the following KB
article "PRB: Unable to Back Up Database to a Network Drive Without
Permissions" at:
http://support.microsoft.com/default...b;EN-US;207187
Regards,
John
"MMSqlserver" <sqlservermn@.discussions.microsoft.com> wrote in message
news:609CDD9A-DA01-4CBE-9170-502990189403@.microsoft.com...
> I am trying to use Bulk insert to populate a table from a text file.it
works
> fine on localhost but while running on network it gives the following
error.[vbcol=seagreen]
> "Could not bulk insert because file 'E:\far\farextracts\DEGREE.txt' could
> not be opened. Operating system error code 5(Access is denied.)."
> Any Suggestions or solutions.
> Thanks
> "VpUser" wrote:
using[vbcol=seagreen]

Request for info - detecting DB changes and batching for e-mail

I have a .NET app recently ported from 1.1 to 2.0 with a pending feature request. I'd first like to state that I'm not asking anyone for the programmatic answer, unless of course, you'd like to provide it. I'm simply asking the best way to accomplish this task with the tools I have available to me (VS 2005, SQL 2000 and 2005). I do not consider myself a professional .NET or T-SQL programmer but I do have a good understanding of the technologies so that I can find what I need to know once I know I'm going about something the right way.

The application I wrote, from scratch, manages News, Featured Connections, and FAQs for a web site. It was designed in a multi-layer approach where there are:

items -> assigned to categories -> assigned to users

So if user Fred is granted rights to category A, when he authenticates to the app he can Add/Change/Delete any item in category A. Simple enough. But also what Fred can do is see all the other items other users have created and *schedule* any relevant item related to his web page to appear within his own items even tho he has no rights to change that item in the admin interface.

The feature request is a notification service, so that Fred gets an e-mail when someone has added a new item or edited an existing one. I don't want Fred to get an e-mail *every* time an item is created or edited, that would be big bother and would generate lots of e-mails. What I'd like to do is send Fred an e-mail sometime in the middle of the night that informs him of the items added or changed since his last login, so that at his choosing he can go into the admin interface and schedule one or more of those items for his page.

Should I just write a new console app that does this, compile it and schedule the .EXE to run at midnight? Or is there a better way to leverage the new features in SQL 2005 (extended strored procs, triggers) and .NET to do this a better way? Currently, the DB is in SQL 2000 but I have no problems moving it to SQL 2005 if that helps me in some way. Thanks in advance for your comments.

...Bill

Table UserLastNotify

ID (useridtype - int/uid/whatever)

LastChangeSeen int

Create trigger on your categories table, recording a changeid, time,categoryid that changed to an auditCategories table.

Create a stored procedure (or cursor) that checks the last changeid in the auditCategories table to their entry in the UserLastNotify table, and then filter based on whatever criteria you want them to be notified about. Then generate the email, and update the UserLastNotify table with the current max of auditCategories (alternatively you can also use the max of the auditCategories id that you generated an email for them - since any higher you either haven't seen, or don't care about).

Create an sql job that runs at midnight every night that calls your stored procedure.

Request for Feedback: Usage of xs:date and xs:dateTime and negative dates in SQL Server XM

Hi all
As part of the next version of SQL Server, we have to stop supporting
negative
years in the two types xs:dateTime and xs:date in the XML data type for
reasons I currently
cannot go into details.
The SQL Server product team would like your help in deciding on what upgrade
experience is acceptable. We would appreciate if you could do the following
if you are storing XML in your database that is constrained with an XML
schema collection (if you have customers, we would love to hear from their
databases, but please ask them first whether you can run the script):
1.. Consider the three upgrade options:
a. Database upgrade without size of data upgrade, affected tables, XML
Schema Collections and XML indexes will be taken offline. A built-in system
stored procedure will have to be run, that will perform the size of data
upgrade, change negative dates to a user-provided date that needs to be
positive and then recreates the indices and re-enables the tables and XML
Schema collections.
b. Database upgrade without size of data upgrade, affected XML indexes
will be taken offline. Creating an XML index and XQueries will fail if the
data queried/indexed contains a negative date or dateTime value. A built-in
system stored procedure will be provided, that for a given column will
perform the size of data upgrade, and change negative dates to a
user-provided date that needs to be positive. After this the query and index
creation can be run.
c. Database upgrade with size of data upgrade for every XML column that
can contain an xs:date or xs:dateTime value, affected XML indexes will be
taken offline. As part of the upgrade process, negative dates are mapped to
the smallest date 0001-01-01T00:00:00Z and the previous negative value will
be logged. Users then can recreate the XML indices.
2. run the attached script on your SQL Server database instance. Please
run it under an account that has the most data visibility. You can run it in
one of two ways:
A. run it to only gather metadata information. This is a short query
looking at the catalog views and does not impact the system performance in a
big way):
-- Just get the dependent columns per affected XML Schema Collection
exec DateTimeInvestigation 0
B. run it to gather metadata and data information. This may run a long
time
on the data and will most likely impact the system's performance, so please
run it only after making sure that this cost is acceptable:
-- Get the dependent columns per affected XML Schema Collection and
-- the number of affected values
exec DateTimeInvestigation 1
3. Please reply to this questionnaire by Monday, May 7 lunch time
providing your preferential ordering of 1.a to 1.c and attach the resulting
XML document from run of 2. Note that we are of course still taking
information from later runs but they may not be used for making this
decision if they arrive too late.
I and the rest of the SQL Server team highly appreciate your help with this.
Best regards
Michael Rys
Principal Program Manager
SQL Server Engine Team
(please remove the online. from the email address)
and here is the attached script.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ObKsOUgjHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all
> As part of the next version of SQL Server, we have to stop supporting
> negative
> years in the two types xs:dateTime and xs:date in the XML data type for
> reasons I currently
> cannot go into details.
> The SQL Server product team would like your help in deciding on what
> upgrade
> experience is acceptable. We would appreciate if you could do the
> following
> if you are storing XML in your database that is constrained with an XML
> schema collection (if you have customers, we would love to hear from their
> databases, but please ask them first whether you can run the script):
> 1.. Consider the three upgrade options:
> a. Database upgrade without size of data upgrade, affected tables, XML
> Schema Collections and XML indexes will be taken offline. A built-in
> system
> stored procedure will have to be run, that will perform the size of data
> upgrade, change negative dates to a user-provided date that needs to be
> positive and then recreates the indices and re-enables the tables and XML
> Schema collections.
> b. Database upgrade without size of data upgrade, affected XML indexes
> will be taken offline. Creating an XML index and XQueries will fail if the
> data queried/indexed contains a negative date or dateTime value. A
> built-in
> system stored procedure will be provided, that for a given column will
> perform the size of data upgrade, and change negative dates to a
> user-provided date that needs to be positive. After this the query and
> index
> creation can be run.
> c. Database upgrade with size of data upgrade for every XML column that
> can contain an xs:date or xs:dateTime value, affected XML indexes will be
> taken offline. As part of the upgrade process, negative dates are mapped
> to
> the smallest date 0001-01-01T00:00:00Z and the previous negative value
> will
> be logged. Users then can recreate the XML indices.
> 2. run the attached script on your SQL Server database instance. Please
> run it under an account that has the most data visibility. You can run it
> in
> one of two ways:
> A. run it to only gather metadata information. This is a short query
> looking at the catalog views and does not impact the system performance in
> a
> big way):
> -- Just get the dependent columns per affected XML Schema Collection
> exec DateTimeInvestigation 0
> B. run it to gather metadata and data information. This may run a long
> time
> on the data and will most likely impact the system's performance, so
> please
> run it only after making sure that this cost is acceptable:
> -- Get the dependent columns per affected XML Schema Collection and
> -- the number of affected values
> exec DateTimeInvestigation 1
> 3. Please reply to this questionnaire by Monday, May 7 lunch time
> providing your preferential ordering of 1.a to 1.c and attach the
> resulting
> XML document from run of 2. Note that we are of course still taking
> information from later runs but they may not be used for making this
> decision if they arrive too late.
> I and the rest of the SQL Server team highly appreciate your help with
> this.
> Best regards
> Michael Rys
> Principal Program Manager
> SQL Server Engine Team
> (please remove the online. from the email address)
>