Empty Column: 2012

Thursday, March 29, 2012

ENDPOINTs in SQL Server 2005 April CTP

I've been happily using VS2005 Beta 1 to access ENDPOINTs on SQL Server 2005 Beta 2 for a while now with no problems. Having just upgraded to VS2005 Beta 2 and SQL Server 2005 April CTP I now get this when I make a call to the ENDPOINT web service:

"The request failed with HTTP status 505: HTTP Version not supported."

Also, if I try to access the WSDL just through Internet Explorer by putting "?wsdl" on the end of the url, I get:

"Error 501/505 - Not implemented or not supported"

It all worked fine in previous beta's. Any idea if some new features\security are causing me this problem, or does it just not work anymore ?

Thanks for any help.
Colin.

Hmm, this works fine for me. If you go into the SQL Server Surface Area Configuration tool and check the Native Web services feature; do you see your endpoints then? If you do, make sure they are started.

Also, what OS, do you run under; XP or Win2K3? If you run under XP make sure that the native httplistener is started; "net start httplistener".

Niels

|||Hi Niels

Thankyou for your reply.

I've just looked under Native Web Services and both
endpoints I use are Started. I'm using Win2k3. Still
having no luck with it unfortunately. Did you unistall
any previous beta's before you installed your current
version? I did, so I'm just wondering if thats were my
problem is.

Colin.
|||Hi Colin

We recently (since the last beta, but a few IDW's ago) dropped support for HTTP 1.0. In other words we reject a request from a HTTP 1.0 client. We now require that the incoming request be a HTTP 1.1 request. This was done because we were sending back chunked responses and chunking isn’t allowed in HTTP/1.0. Is it possible to upgrade your client (IE) to use the newer HTTP protocol. When we made the decision we didn't anticipate that there would be too many applications out there using HTTP 1.0.

Srik|||Hello Colin,
Is it possible to get a netmon sniff of the client/server communication to confirm that this is indeed a HTTP/1.0 request that SQL Server is rejecting?

Thanks,
Anu|||Thanks for your help guys.

Discovered that the endpoint does work properly when using machine names in the url (so thats request are limited to within our network). But when I put a internet domain name in, I get this 'not supported' problem.

It turns out that you are correct in that the sql server is recieving a HTTP 1.0 request, which I found quite odd, because my client is xp with all the latest patches and updates.

Anyway, after further thought I guessed our firewall could be doing something (it is Microsoft ;) ) and it turns out that is where the problem is. As detailed in the TechNet article :

http://www.microsoft.com/technet/prodtechnol/isa/2000/plan/isahttp.mspx

Microsoft ISA Server will forward 1.1 requests as 1.0

Well done Microsoft. Seems you have to pay for the new Micro$oft ISA 2004 version that get the desired functionality.

Colin.|||

I have one similar trouble, I made one webservice with sql server 2005 create endpoint. In my office that we have a domain controller I needed to start my sql server in several machines with the same domain user and all works fine. In customer office they don't have a domain, the machines are connected in a Lan but there are not common users within the machines. When I try to invoke the webservice It always shows LOGIN ERROR. I make one user in both machines (same name, same password) and I cannot access web service.

Some help ?

ENDPOINTs in SQL Server 2005 April CTP

"The request failed with HTTP status 505: HTTP Version not supported."

Also, if I try to access the WSDL just through Internet Explorer by putting "?wsdl" on the end of the url, I get:

"Error 501/505 - Not implemented or not supported"

It all worked fine in previous beta's. Any idea if some new features\security are causing me this problem, or does it just not work anymore ?

Thanks for any help.
Colin.

Also, what OS, do you run under; XP or Win2K3? If you run under XP make sure that the native httplistener is started; "net start httplistener".

Niels

|||Hi Niels

Thankyou for your reply.

Colin.|||Hi Colin

Srik|||Hello Colin,
Is it possible to get a netmon sniff of the client/server communication to confirm that this is indeed a HTTP/1.0 request that SQL Server is rejecting?

Thanks,
Anu|||Thanks for your help guys.

It turns out that you are correct in that the sql server is recieving a HTTP 1.0 request, which I found quite odd, because my client is xp with all the latest patches and updates.

Anyway, after further thought I guessed our firewall could be doing something (it is Microsoft ;) ) and it turns out that is where the problem is. As detailed in the TechNet article :

http://www.microsoft.com/technet/prodtechnol/isa/2000/plan/isahttp.mspx

Microsoft ISA Server will forward 1.1 requests as 1.0

Well done Microsoft. Seems you have to pay for the new Micro$oft ISA 2004 version that get the desired functionality.

Colin.|||

Some help ?

ENDPOINTs in SQL Server 2005 April CTP

"The request failed with HTTP status 505: HTTP Version not supported."

Also, if I try to access the WSDL just through Internet Explorer by putting "?wsdl" on the end of the url, I get:

"Error 501/505 - Not implemented or not supported"

It all worked fine in previous beta's. Any idea if some new features\security are causing me this problem, or does it just not work anymore ?

Thanks for any help.
Colin.

Also, what OS, do you run under; XP or Win2K3? If you run under XP make sure that the native httplistener is started; "net start httplistener".

Niels

|||Hi Niels

Thankyou for your reply.

Colin.|||Hi Colin

Srik|||Hello Colin,
Is it possible to get a netmon sniff of the client/server communication to confirm that this is indeed a HTTP/1.0 request that SQL Server is rejecting?

Thanks,
Anu|||Thanks for your help guys.

It turns out that you are correct in that the sql server is recieving a HTTP 1.0 request, which I found quite odd, because my client is xp with all the latest patches and updates.

Anyway, after further thought I guessed our firewall could be doing something (it is Microsoft ;) ) and it turns out that is where the problem is. As detailed in the TechNet article :

http://www.microsoft.com/technet/prodtechnol/isa/2000/plan/isahttp.mspx

Microsoft ISA Server will forward 1.1 requests as 1.0

Well done Microsoft. Seems you have to pay for the new Micro$oft ISA 2004 version that get the desired functionality.

Colin.

Endpoints and authentication

I tried asking a similar question over at the asp.net, but I'm not getting any replies.

I created an endpoint in SS 2005 using DIGEST authentication, and I was successful in adding the web service to my project and getting results from a call to it.

However, the production environment does not exist in a domain environment, which eliminates even DIGEST (which requires a valid windows domain logon).

But, when I create the endpoint using BASIC authentication, I can no longer "find" the service. SS says the command(s) completed successfully after the Create Endpoint command. As a test, the documentation says that you can enter the http site into IE and the WSDL will display. And that works in digest mode. However, I've tried both:
http://<server>/path?WSDL and
https://<server>/path?WSDL
And neither returns the WSDL in IE (nor can it be added to my project as a web service).

I'm hoping someone has some ideas on how I can resolve this problem.

TIA,
DaveI suspect that my SSL problem has to do with how the server is setup. I just tried using DIGEST authentication with a LOGIN_TYPE=MIXED. This combination requires PORTS=SSL also.

I go the same messages when I tried to attach. Firefox reports the error as "The connection was interrupted" IE says "Internet Explorer cannot display the webpage"

Could someone give a few hints on what to check on my server?

TIA,
Dave

endpoints

I am trying to find info / examples of how to send information to a webservice using sql server is that at all possible.

the following code might help you,

Code Snippet

Declare @.Object as Int;

Declare @.ResponseText as Varchar(8000);

Exec sp_OACreate 'MSXML2.XMLHTTP', @.Object OUT;

Exec sp_OAMethod @.Object, 'open', NULL, 'get',

'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Url (invoked)

'false'

Exec sp_OAMethod @.Object, 'send'

Exec sp_OAMethod @.Object, 'responseText', @.ResponseText OUTPUT

Select @.ResponseText

Exec sp_OADestroy @.Object

|||Thanks Manivannan

so i am assuming that if i wanted to send an xml file to a url i would use method with 'post'

Exec sp_OAMethod @.Object, 'open', NULL, 'post', 'myurl', 'false'

and then send right?
|||I using the the example you gave me and trying to post this xml file

Declare @.Object as Int;

Declare @.ResponseText as Varchar(8000);
Declare @.xmlData xml

select @.xmlData = c
FROM OPENROWSET (
BULK 'E:\StockQuote.xml',SINGLE_BLOB) as TEMP(C)

Exec sp_OACreate 'MSXML2.XMLHTTP', @.Object OUT;

Exec sp_OAMethod @.Object, 'open', NULL, 'post',

'http://www.webservicex.com/stockquote.asmx/GetQuote', --Your Web Url (invoked)

'false'

Exec sp_OAMethod @.Object, 'send', NULL, @.xmlData

Exec sp_OAMethod @.Object, 'responseText', @.ResponseText OUTPUT
Select @.ResponseText

Exec sp_OADestroy @.Object

I am not getting back a response just like the example you gave insteadi get null what am i doing wrong i am trying to figure out how to post some XML file here is the xml i am trying to post

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetQuote xmlns="http://www.webserviceX.NET/">
<symbol>MSFT</symbol>
</GetQuote>
</soap:Body>
</soap:Envelope>

sql

Endpoint wont deploy

im just playing with endpoints, and i have created this one below:

create endpoint my_Endpoint

state = started

as http(path='/sql',

authentication=(INTEGRATED),

PORTS=(CLEAR),

SITE='endpointTest')

For SOAP

(

webmethod 'GetSprocData'(name='adventureworksdw.dbo.testEndPointSproc'),

webmethod 'GetFunctionData'(name='adventureworksdw.dbo.endpointFunctionTest'),

wsdl=default,

schema=standard,

database ='adventureworksdw',

namespace = 'myNamespace'

)

The problem here is that although the script executes successfully, the site is not created within IIS and thus the endpoint is not deployed. Im using windows vista, and have IIS installed. does anyone know what im doing wrong?

The SQL Server 2005 SOAP/HTTP endpoints do not require IIS; therefore, they will not show up on the IIS management tool. You do not need IIS installed to create these endpoints.

There are couple of ways to verify that the endpoint has been successfully created. Since you have enabled WSDL generation on the endpoint, you can use an Internet browser (such as IE) to retrieve the WSDL document. In your specific scenario the URL will be http://endpointTest/sql?wsdl. Alternatively, a bit more complicated way is open a command prompt and run "tasklist" and you should see an entry for SQL Server example:

Image Name PID Session Name
========================= ======== ================

sqlservr.exe 3104 Services

Then you can run "netstat -ano" and look for the set of ports that SQL Server is listening on:

>netstat -ano

Active Connections

Proto Local Address Foreign Address State PID
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING 3104

I recommend reading up on the implications of the "SITE" keyword value setting on the endpoint. Detailed information is available on MSDN (http://msdn2.microsoft.com/en-us/library/ms181591.aspx)

For testing purposes, I recommend setting the "SITE" keyword value to "*".

Jimmy

Endpoint Security ?

Hi There

I am trying to grasp endpoint security, or actually more security/certiicates in general, at the moment i am trying to write a distributed service broker app, all the examples i have seen use certificates for the endpoint authentication.

Why must one create a certificate at each endpoint? Why can i not create a single certificate and let all endpoints use it ?

As you can imagine of this app gets distributed to hundreds of places creating a certificate at each one is a mission?

So can i use a single certificate for all endpoints authentication?

Thanx

The certificates based authentication (in general, not only for Service Broker) relies on the fact that the private key is a well guarded secret. Being a secret, proof of possesion of the private key (e.g. a cryptographic signature) can be used as proof of identity. The moment you're talking about multiple copies of the same private key, it's value as a identity proof is greatly diminished. It is impossible in practice to ensure the secrecy of the private key while is deployed at hundreds of sites.

What you can consider is to allow public connectivity to your server. That is, grant CONNECT to [Public] on the broker endpoint and grant SEND to [Public] on the target service. This allows anybody to connect ot the endpoint and anybody to send a message to the service (using anonymous dialog security).

If public connectivity is not acceptable, then you must create hundreds odf certificates and manage them. Using one certificate instead and copying the private key hundreds of time doesn't give you any real security: since your private key is very likely to be leaked outside your control, all you have is just a false sense of security.

HTH,
~ Remus

Endpoint Authentication

Hi,
I have setup a basic endpoint that exposes a sp that when given a few
parameters, should go and update a record on the database:
/****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
15:01:04 ******/
CREATE ENDPOINT [ep_UpdateAddressDetails]
AUTHORIZATION [mydomainname\tgriffiths]
STATE=STARTED
AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'UpdateAddress'(
NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
, SCHEMA=STANDARD
, FORMAT=ALL_RESULTS), BATCHES=ENABLED,
WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
I can see the wsdl from a web browser, however when I go and setup a HTTP
Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then press
test and it comes back with "the remote server returned an error: (401)
Unauthorized."
So I presume this is just a permissions issue? However I am unsure what I
need to apply permissions on, as you can see from the statement above, I have
given AUTHORIZATION to my username "tgriffiths". I have also run a seperate
grant connect priviledges for me - but still not difference in the response
from VS2005.
I am a local admin on this machine and my user is authorized in the above
statement, along with me running a specific grant connect on this endpoint. I
am also a db_owner of this database, not to mention being part of the
sysadmin group.
Can anyone assist in getting this going as I am not sure where to look from
here.
Thanks in advance
Troy
Hi Troy,
Please make sure in your Visual Studio application that you are setting the
user credentials to use for the connection.
Example:
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
For additional details or options, please refer to the following MSDN article:
http://msdn2.microsoft.com/en-us/library/ms175929.aspx
Jimmy
"Troy" wrote:

> Hi,
> I have setup a basic endpoint that exposes a sp that when given a few
> parameters, should go and update a record on the database:
> /****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
> 15:01:04 ******/
> CREATE ENDPOINT [ep_UpdateAddressDetails]
> AUTHORIZATION [mydomainname\tgriffiths]
> STATE=STARTED
> AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
> SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
> FOR SOAP (
> WEBMETHOD 'UpdateAddress'(
> NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
> , SCHEMA=STANDARD
> , FORMAT=ALL_RESULTS), BATCHES=ENABLED,
> WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
> SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
> NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
>
> I can see the wsdl from a web browser, however when I go and setup a HTTP
> Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then press
> test and it comes back with "the remote server returned an error: (401)
> Unauthorized."
> So I presume this is just a permissions issue? However I am unsure what I
> need to apply permissions on, as you can see from the statement above, I have
> given AUTHORIZATION to my username "tgriffiths". I have also run a seperate
> grant connect priviledges for me - but still not difference in the response
> from VS2005.
> I am a local admin on this machine and my user is authorized in the above
> statement, along with me running a specific grant connect on this endpoint. I
> am also a db_owner of this database, not to mention being part of the
> sysadmin group.
> Can anyone assist in getting this going as I am not sure where to look from
> here.
> Thanks in advance
> Troy
>
|||Hi, Thanks Jimmy for your response.
I had already confirmed the proxy userdetails etc, but same error.
After playing around a little more I have managed to get it working - and
realise now that the Endpoints within SQL only allow HTTP Get's rather than
Posts - and this is most likely where this error is coming from *maybe*
Anyhow, I can at least select my web service now!
Thanks
Troy
"Jimmy Wu [MSFT]" wrote:
[vbcol=seagreen]
> Hi Troy,
> Please make sure in your Visual Studio application that you are setting the
> user credentials to use for the connection.
> Example:
> proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
> For additional details or options, please refer to the following MSDN article:
> http://msdn2.microsoft.com/en-us/library/ms175929.aspx
> Jimmy
> "Troy" wrote:

Endpoint Authentication

min group.
Can anyone assist in getting this going as I am not sure where to look from
here.
Thanks in advance
TroyHi Troy,
Please make sure in your Visual Studio application that you are setting the
user credentials to use for the connection.
Example:
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
For additional details or options, please refer to the following MSDN articl
e:
http://msdn2.microsoft.com/en-us/library/ms175929.aspx
Jimmy
"Troy" wrote:

> Hi,
> I have setup a basic endpoint that exposes a sp that when given a few
> parameters, should go and update a record on the database:
> /****** Object: Endpoint [ep_UpdateAddressDetails] Script Date: 02/22/2007
> 15:01:04 ******/
> CREATE ENDPOINT [ep_UpdateAddressDetails]
> AUTHORIZATION [mydomainname\tgriffiths]
> STATE=STARTED
> AS HTTP (PATH=N'/sql', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED),
> SITE=N'lfxakl13', CLEAR_PORT = 80, COMPRESSION=DISABLED)
> FOR SOAP (
> WEBMETHOD 'UpdateAddress'(
> NAME=N'[testDb].[dbo].[p_tTest_UpdateAddressDetails]'
> , SCHEMA=STANDARD
> , FORMAT=ALL_RESULTS), BATCHES=ENABLED,
> WSDL=N'[master].[sys]. [sp_http_generate_wsdl_defaultcomplexors
imple]',
> SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'testDb',
> NAMESPACE=N'http://lfxakl13/sql/', SCHEMA=STANDARD, CHARACTER_SET=XML)
>
> I can see the wsdl from a web browser, however when I go and setup a HTTP
> Connection in VS2005, I put in my URL as : http://lfxakl13/sql and then pr
ess
> test and it comes back with "the remote server returned an error: (401)
> Unauthorized."
> So I presume this is just a permissions issue? However I am unsure what I
> need to apply permissions on, as you can see from the statement above, I h
ave
> given AUTHORIZATION to my username "tgriffiths". I have also run a seperat
e
> grant connect priviledges for me - but still not difference in the respons
e
> from VS2005.
> I am a local admin on this machine and my user is authorized in the above
> statement, along with me running a specific grant connect on this endpoint
. I
> am also a db_owner of this database, not to mention being part of the
> sy

min group.
> Can anyone assist in getting this going as I am not sure where to look fro
m
> here.
> Thanks in advance
> Troy
>|||Hi, Thanks Jimmy for your response.
I had already confirmed the proxy userdetails etc, but same error.
After playing around a little more I have managed to get it working - and
realise now that the Endpoints within SQL only allow HTTP Get's rather than
Posts - and this is most likely where this error is coming from *maybe*
Anyhow, I can at least select my web service now!
Thanks
Troy
"Jimmy Wu [MSFT]" wrote:
> Hi Troy,
> Please make sure in your Visual Studio application that you are setting th
e
> user credentials to use for the connection.
> Example:
> proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
> For additional details or options, please refer to the following MSDN arti
cle:
> http://msdn2.microsoft.com/en-us/library/ms175929.aspx
> Jimmy
> "Troy" wrote:
>

endpoint already registered to another service

I am unable to create endpoint in the Windows 2003 server using SQL 2005. I also tried reserving the namespace before CREATE but i still end up with this error

Msg 7806, Level 16, State 1, Line 1
The URL specified by endpoint 'zzzzzz' is already registered to receive requests or is reserved for use by another service.

Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint zzzzzz.

My code looks loke this:

IF EXISTS ( SELECT name from sys.http_endpoints WHERE name = 'zzzzzz' )
DROP ENDPOINT zzzzzz
GO

CREATE ENDPOINT zzzzzz

STATE = STARTED AS HTTP (
site='servername',
path='/sql/zzzzzz',
AUTHENTICATION=(INTEGRATED),
PORTS = (CLEAR) )
FOR SOAP(
WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'), WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),
WSDL = DEFAULT,
BATCHES=ENABLED)
GO
-- End of Script --

The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005. If anyone has an idea about my problem, please help me !!!
Thank You
Zee.

This evening I received a very similar response to the following T-SQL execution while under MS Server 2003 and SQL Server 2005;

CREATE ENDPOINT [xxx] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

An endpoint already exists with the bindings specified. Only one endpoint supported for a specific binding. Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.

Msg 7807, Level 16, State 1, Line 9

An error ('0x800700b7') occurred while attempting to register the endpoint 'xxx'.

sql

endpoint already registered to another service

I am unable to create endpoint in the Windows 2003 server using SQL 2005. I also tried reserving the namespace before CREATE but i still end up with this error

Msg 7806, Level 16, State 1, Line 1
The URL specified by endpoint 'zzzzzz' is already registered to receive requests or is reserved for use by another service.

Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint zzzzzz.

My code looks loke this:

IF EXISTS ( SELECT name from sys.http_endpoints WHERE name = 'zzzzzz' )
DROP ENDPOINT zzzzzz
GO

CREATE ENDPOINT zzzzzz

This evening I received a very similar response to the following T-SQL execution while under MS Server 2003 and SQL Server 2005;

CREATE ENDPOINT [xxx] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

An endpoint already exists with the bindings specified. Only one endpoint supported for a specific binding. Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.

Msg 7807, Level 16, State 1, Line 9

An error ('0x800700b7') occurred while attempting to register the endpoint 'xxx'.

Endless subqueries

I have a table with two columns: OID and Cumulative (witch is the same type as OID)

Each OID can have one or more Cumulatives.

Example of data:

OID Cumulative

167 292

167 294

167 296

168 292

169 302

169 304

The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical).

Example: 167->292->590

So the table would have on more row:

OID Cumulative

295 505

I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure:

OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln

in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.

How can I do the query?

Is there a better way as my table with n columns?

Thanks for suggestions

Your sample data is confusing..Can you fix it. Let us know the sample output from the input (sample data) you provided.

Endless running package using a proxy

I have a package which runs fine, when I execute it with my account (e.g. double-click on the .dtsx file and run it).

Now I would like to establish a job, which starts the package. I created first a credential for my Account (which is a domain administrator account also for the box, where SQL Server is running on), then I defined a proxy to this credential.

In the job definition I changed the Run as... to this Proxy (it is a SSIS Proxy) and then I started the job.

Th job does NOT abend, it runs forever! So I have to stop it manually.

In the log I can see as last entry : "operation complete". It stops at a "Execute process task" where I call the bcp utility.

Does anyone has an idea, why a package can run forever?!?!

Regards

Norbert

Sorry, it is not the package running forever (it logged "operation complete) but the job does not stop.

In addition I would like to add another information. I tested the package also as CmdExec Call (dtexec ....) but the result is still the same.

Regards

Norbert Bender

Endless looping job!

i have created a job that i have scheduled to run every 10 min everything is configured well since i have tested preety everything their is to be tested and found that it was my last step wich as a fetch in it so i imagine that this fetch is making it loop over and over again. the job goes trought all the steps and starts back at the first step and keep going like that till i disable it here is my fetch statement and if you have any clue any help would be widely apreciated.

PS: i suspected it to be that fetch statement causing the havoc ;)

DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = 'C'

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = 'C'
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursorChange the loop terminator to WHILE @.@.FETCH_STATUS = 0
not <> -1

Endless loop while sync.

I am trying to replicate a database but it seems to go into endless
loop showing:
Processing article: 'Table1'
Processing article: 'Table2'
Processing article: 'Table3'
Processing article: 'Table1'
Processing article: 'Table2'
..
..
..
I stopped it after quite a lot of loop cycles, but it looks like it is
still running in background. In enterprise manager the last action is
"Synchronization in progress" and status is "Running". I already
restarted the sql-server service and also the computer - didn't help.
The replication is done using an external program and not with job.
Thanks
This can be normal. You should let this process finish. It is somewhat
transactional, ie it will pick up where it left off.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"or dromi" <or_dromi@.walla.co.il> wrote in message
news:539934ef.0411240754.37ab924a@.posting.google.c om...
>I am trying to replicate a database but it seems to go into endless
> loop showing:
> Processing article: 'Table1'
> Processing article: 'Table2'
> Processing article: 'Table3'
> Processing article: 'Table1'
> Processing article: 'Table2'
> .
> .
> .
> I stopped it after quite a lot of loop cycles, but it looks like it is
> still running in background. In enterprise manager the last action is
> "Synchronization in progress" and status is "Running". I already
> restarted the sql-server service and also the computer - didn't help.
> The replication is done using an external program and not with job.
>
> Thanks
sql

Endless loop

*** edited by: master4eva ***
Please enclose your code in

</ code> tags (without the space between the "</" and "code"). This will make your code easier to read online; therefore, encouraging a response to be faster. It is to your own benefit for your question to be answered in future.I have already done the editing to include the <code></ code> tags.
*********
I have a trigger that fires this stored procedure during an update event. But i think i am getting an endless loop. Any idea? Thanks in advance...
<code>
ALTER PROCEDURE TrigRetReqRecIDP1
@.REID int
AS
Declare @.RRID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare @.RetValint
Declare cr cursor
for
select RRID from RequestRecords where REID=@.REID and RRStatus = 'PE'
open cr
fetch next from cr
into
@.RRID
while @.@.fetch_status = 0
Begin
select @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.IDENTITY
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
end
close cr
deallocate cr
GO

and what is the code for the trigger ?|||CREATE TRIGGER trUpdateRequestNewP1
on dbo.Requests
For Update
AS
DECLARE @.REIDint

SELECT @.REID = (SELECT REID from inserted where REStatus ='AC' and REType = 'N')

-- EXEC TrigAssignImpTaskNewP1 @.REID
EXEC TrigRetReqRecIDP1 @.REID|||i dont see any update stmt for records within the proc TrigRetReqRecIDP1...do you have any updates in TrigAssignImpTaskNewP1 ?

hth|||Here's the new version but it is only updating the first record @.RRID...

What I am trying to do is...

I have multiple RRID from RequestRecords where REID = @.REID.
I want to assign each RRID a task from the implementationtasks table...
SO if there are 10 RRID's then ten tasks should be created in the implementationtable. Each @.retval returned from the implementationTasks table will be assigned and updated to each RRID in requestrecords table...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE TrigRetReqRecIDP1
@.REID int
AS
Declare @.RRID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare @.RetValint
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'PE'
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
set @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.IDENTITY
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||This one works perfectly but do you have any suggestion to improve it?... Thanks


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE TrigRetReqRecIDP1
@.REID int
AS
Declare @.RRID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare @.RetValint
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'PE'
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
set @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.IDENTITY
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||this part of your code :


set @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(IMID,ITStatus,ITStatusDate)
VALUES
(@.intIMID,'2',GetDate())
SET @.RetVal = @.@.IDENTITY

does not have to be in the cursor since it is not dependent on the cursor values..the insert stmt will run for each record in the cursor...unless that is what you want...
you can move this part outside the cursor..so it will run only once...also i'd suggest using SCOPE_IDENTITY() instead of @.@.IDENTITY.

hth

Ending a Conversation

I am attempting to learn Service Broker from Bob Beauchemin's book "A Developer's Guide to SQL Server" - Chapter 11. I'm finding it to be very good but I'm confused over the concept of closing a conversation. Could someone answer the following questions for me?

When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)

Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?

Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?

Thanks very much.

Amos

I find Bob's book a real treasure chest, covering so much of the new SQL features.

Amos wrote:
1. When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)

Yes and no. After ending a conversation the handle can no longer be used to SEND (or MOVE), but it can still be used to END again (a no-op) or to END ... WITH CLEANUP (e.g. in case messages are stuck in transmission_queue and you know the're never going to get trough). Both sites have to END the conversation and once the EndDialog message is received from the other side, the conversation endpoint handle is deleted so all verbs (SEND,END, MOVE) will fail because the conversation handle value is invalid. the target side might keep around a handle for up to 30 minutes after it was closed (ENDed by both sides) for security reasons (prevent a replay attack).

Amos wrote:
2. Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?

Yes, you'll not receive any reply, since you already declared that you're no longer interested in the result of this conversation (by ENDing it). Even worse, if the conversation is ended with an error, you'll never know that about error, including system errors like 'SEND permission denied'. See http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx for more on this.

Amos wrote:
3. Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?

That is correct. In theory, your conversation will expire after many, many years (68, I believe). In practice, I doubt any application written today will be around after 60 years...

HTH,
~ Remus

Endian problems with converting to varbinary

We are in the process of migrating from a DOS based binary file storage
format to a relational database structure. However, for migration reasons we
need to be able to export the relational data to the binary file structure
for processing. I have accomplished this with a not-so-lightweight procedure
that basically steps through data, pulls out a variable, converts it to
binary, and appends it to an Image column. At the end of the process I use
textcopy within the TSQL code to write the binary file out. However, when
processing the data in a VB6 utility, reading an integer from the file
becomes impossible. 1's become 256, 2's become 512, 4's become 1024, and
larger values are crazy. Stepping through the original files, we notice that
a one would be stored as 0x10, a 2 as 0x20. Stepping through the file that
SQL Server wrote, we noticed that a one was 0x01, a two was 0x02, etc.
I understand why the old files work, as that is in little endian format,
native to windows. However, I don't understand why SQL server is converting
to a binary structure that looks to be big endian. Below is an example of my
conversion, please let me know what I'm doing wrong and/or how I can fix it
to make it work correctly (tempBlob.MainBlob is the Image that I'm updating)
.
Thanks!
Code:
DECLARE @.bin2 binary(2)
DECLARE @.PixelsAcrossDetector SMALLINT
SELECT @.PixelsAcrossDetector = (SELECT PixelsAcross FROM Strips WHERE GUID =
@.StripGUID)
SELECT @.bin2 = (SELECT CONVERT(BINARY(2), @.PixelsAcrossDetector))
UPDATETEXT tempBlob.MainBlob @.Pointer NULL 0 @.bin2> Stepping through the original files, we notice that
> a one would be stored as 0x10, a 2 as 0x20. Stepping through the file that
> SQL Server wrote, we noticed that a one was 0x01, a two was 0x02, etc.
> I understand why the old files work, as that is in little endian format,
> native to windows. However, I don't understand why SQL server is
> converting
> to a binary structure that looks to be big endian.
Consider the following:
DECLARE
@.PixelsAcrossDetector smallint,
@.bin2 binary(2)
SET @.PixelsAcrossDetector = 1
SET @.bin2 = CONVERT(BINARY(2), @.PixelsAcrossDetector)
SELECT @.bin2
The result is a fixed-length *binary string* with a value of 0x0001 rather
than the little endian smallint value 0x0100 you want to serialize. One
method to address the problem is to swap the bytes like the example below.
It's ugly but Transact-SQL isn't really designed to create binary records.
DECLARE
@.PixelsAcrossDetector smallint,
@.bin2 binary(2),
@.Byte0 binary(1),
@.Byte1 binary(1)
SET @.PixelsAcrossDetector = 1
SET @.bin2 = CONVERT(BINARY(2), @.PixelsAcrossDetector)
SELECT @.Byte0 = SUBSTRING(@.bin2, 2, 1)
SELECT @.Byte1 = SUBSTRING(@.bin2, 1, 1)
SELECT @.Bin2 = @.Byte0 + @.Byte1
SELECT @.Bin2
Hope this helps.
Dan Guzman
SQL Server MVP
"David Harris" <DavidHarris@.discussions.microsoft.com> wrote in message
news:35FE8697-EE20-4641-9042-3F569D5BCDC9@.microsoft.com...
> We are in the process of migrating from a DOS based binary file storage
> format to a relational database structure. However, for migration reasons
> we
> need to be able to export the relational data to the binary file structure
> for processing. I have accomplished this with a not-so-lightweight
> procedure
> that basically steps through data, pulls out a variable, converts it to
> binary, and appends it to an Image column. At the end of the process I use
> textcopy within the TSQL code to write the binary file out. However, when
> processing the data in a VB6 utility, reading an integer from the file
> becomes impossible. 1's become 256, 2's become 512, 4's become 1024, and
> larger values are crazy. Stepping through the original files, we notice
> that
> a one would be stored as 0x10, a 2 as 0x20. Stepping through the file that
> SQL Server wrote, we noticed that a one was 0x01, a two was 0x02, etc.
> I understand why the old files work, as that is in little endian format,
> native to windows. However, I don't understand why SQL server is
> converting
> to a binary structure that looks to be big endian. Below is an example of
> my
> conversion, please let me know what I'm doing wrong and/or how I can fix
> it
> to make it work correctly (tempBlob.MainBlob is the Image that I'm
> updating).
> Thanks!
> Code:
> DECLARE @.bin2 binary(2)
> DECLARE @.PixelsAcrossDetector SMALLINT
> SELECT @.PixelsAcrossDetector = (SELECT PixelsAcross FROM Strips WHERE GUID
> =
> @.StripGUID)
> SELECT @.bin2 = (SELECT CONVERT(BINARY(2), @.PixelsAcrossDetector))
> UPDATETEXT tempBlob.MainBlob @.Pointer NULL 0 @.bin2|||>> We are in the process of migrating from a DOS based binary file storage f
ormat to a relational database structure. <<
Now you know why BIT is not part of the SQL Standards. SQL is meant to
be high-level, abstract language; use files and machine-specific
assembly language for that level of programming.
Right tool for the job; no kludges.|||"--CELKO--" Wrote:
> Now you know why BIT is not part of the SQL Standards.
> SQL is meant to be high-level, abstract language; use
> files and machine-specific assembly language for that
> level of programming.
One of my joys is that this is only interim, until we can rewrite our
processing software to make database calls insted of reading the binary
files. But unfortunately that will be a ways off for now, and we have to
kludge it up right now. :( But good design is right around the corner... :)
"Dan Guzman" wrote:
> The result is a fixed-length *binary string* with a value of 0x0001 rather
> than the little endian smallint value 0x0100 you want to serialize. One
> method to address the problem is to swap the bytes like the example below.
> It's ugly but Transact-SQL isn't really designed to create binary records.
Yeah, I was hoping it was a setting or a separate convert function. :) But
this seems like the best solution, will just have to abstract it to simplify
things. Appreciate the help. :)
Will the swap method also work with doubles/floats, just by scaling the
variables to 8 instead of 2, and completely reversing it? Or must the double
s
have a separate format?|||> Will the swap method also work with doubles/floats, just by scaling the
> variables to 8 instead of 2, and completely reversing it? Or must the
> doubles
> have a separate format?
It gets especially nasty when you deal with single and double data types.
Not only do you need to address the big/little endian issue, you'll need to
create the exponent correctly. It's likely to be easier and faster to write
a custom application program than to do this in Transact-SQL. I don't
always agree with Joe but, IMHO, this is beyond an acceptable temporary
kludge.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Harris" <DavidHarris@.discussions.microsoft.com> wrote in message
news:219FAFF8-546B-47B1-B744-02BFD0391989@.microsoft.com...
> "--CELKO--" Wrote:
> One of my joys is that this is only interim, until we can rewrite our
> processing software to make database calls insted of reading the binary
> files. But unfortunately that will be a ways off for now, and we have to
> kludge it up right now. :( But good design is right around the corner...
> :)
> "Dan Guzman" wrote:
> Yeah, I was hoping it was a setting or a separate convert function. :) But
> this seems like the best solution, will just have to abstract it to
> simplify
> things. Appreciate the help. :)
> Will the swap method also work with doubles/floats, just by scaling the
> variables to 8 instead of 2, and completely reversing it? Or must the
> doubles
> have a separate format?|||>> Now you know why BIT is not part of the SQL Standards.
It was a bit suprising that SQL:99 bought it up & is already deprecated in
SQL:2003
Anith|||"Dan Guzman" wrote:
> It gets especially nasty when you deal with single and double data types.
> Not only do you need to address the big/little endian issue, you'll need t
o
> create the exponent correctly. It's likely to be easier and faster to wri
te
> a custom application program than to do this in Transact-SQL. I don't
> always agree with Joe but, IMHO, this is beyond an acceptable temporary
> kludge.
Agreed. Too bad everything isn't an integer. :) I did manage to create a few
procedures to abstract a good part of the process into TSQL, which cuts the
time at least in half from doing everything from C#. Appreciate the help,
even though it's not exactly what I wanted to hear... But this is interim,
and we'll take just about anything right now. :)

EndDialog

I have a SB program that sends a message and there is an activation procedure on my queue. The activation procedure takes the message from the queue, does some processing with the data, and ends the conversation. (I may, in the future, consider reusing the dialogs.) What I am wondering about is that I never come across the

'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' message type name that indicates that the dialog has ended. Any ideas why?

From your description it sounds like the activated procedure issues the END CONVERSATION verb after processing the first message. After the END CONVERSATION verb was issued on a handle, you will not receive any other message on that conversation (including EndDialog sent by the peer). If we wouldn't ensure that application would have to keep around the state related to that conversation for an undetermined time.

HTH,

~ Remus

P.S. Thanks for writing those SSB articles

|||

Thanks Remus, that definetly makes sense. Because I won't see that EndDialog message type, what is the best way to go about cleaning the conversation up once I am finished? What I have ran into so far is that when I used END CONVERSATION WITH CLEANUP, the conversation remains in sys.conversation_endpoints with a status of 'Conversing', but if I omit 'WITH CLEANUP', the dialog will close as expected. I would like a way to cleanup that message so that it is removed from the catalog view. Does that make sense?

Thanks again,

Tim

|||

Never ever use END CONVERSATION ... WITH CLEANUP. It is realy a last resort statement itended for administrators. Using it in applications can result in very serious problems.

You must decide who ends the conversation first, depending on the business semantics of the conversation. The party that ends the conversation first is the first one that can say 'I am no longer interested in this conversation, even if this last (EndDialog) message never makes it to my peer'. Some common patterns are:

Notification

1) Initiator begins conversation

2) Initiator sends the notification message

3) Target receives the message

4) Target ends the conversations

5) Initiator receives EndDialog message

6) Initiator ends the conversation

Request-Response (when target does not care is response is lost)

1) Initiator begins conversation

2) Initiator sends the request message

3) Target receives the message

4) Target sends response

5) Target ends the conversations

6) Intiator receives the response

7) Initiator receives EndDialog message

8) Initiator ends the conversation

Request-Response (when target does care is response is lost)

1) Initiator begins conversation

2) Initiator sends the request message

3) Target receives the message

4) Target sends response

5) Intiator receives the response

6) Initiator ends the conversation

7) target receives EndDialog message

8) target ends the conversation

One way stream of messages

1) Initiator begins conversation

2) Initiator sends the one message

2') Initiator sends the one message ...

3) Target receives message(s)

... conversation continues for while with initiator sending messages

4) Initiator decides to intrerupt the stream and sends a special application message (EndOfStream)

5) Target receives the EndOfSream message

6) Target ends the conversations

7) Initiator receives EndDialog message

8) Initiator ends the conversation

One pattern that is actually incorrect is fire-and-forget: initiator begins a conversation, then sends one or more messages, then ends the conversation. This way the initiator never gets any feedback if the messages we actually sent or not. I have a blog entry on this subject: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx

It is important to mention the conversation lifetime role in these patterns. If a conversation is not ended by both sides before it's lifetime expires, the conversation is errored and an Error message is sent to any endpoint still open (not ended). This is why endpoints that still care about delivery of the last message sent cannot issue an END CONVERSATION. They are supposed to send the message and the if they get an EndDialog message is a confrmation of succesfull delivery, while an Error message is an indication of a problem.

As about the endpoint states, a 'Conversing' endpoint is an endpoint that did not receive nor sent an EndDialog message. All of the patterns I mention above clean up the endpoints after issuing the END CONVERSATION. The first endpoint that issues the END CONVERSATION will be kept in the system until the EndDialog message is acknowledged by the peer (in an DISCONNECTED_OUTBOUND state), then it will be closed. An endpoint that has received an EndDialog message will stay in DISCONNECTED_INBOUND state until is explictly ended with END CONVERSATION, then it will be closed.

When initiator endpoint is closed, the endpoint is immedeately deleted.

When target endpoint is closed, the endpoint might be kept around for up to 30 minutes to prevent a replay attack, then it will be deleted.

Another negative side effect of the fire-and-forget pattern is that the target endpoint is not kept for 30 minutes in that case, but until the original conversation lifetime expires. Since most application do not specify a lifetime, that mens the target is scheduled to be deleted 74 years from now, thus being leaked for all practical means.

HTH,

~ Remus

|||Thanks again Remus, this is perfect. I was confused about the dialogs getting cleaned up. I noticed that the WITH CLEANUP cleaned the dialog up, and that is what I wanted at the time. I have since taken this out of all of my code. I didn't realize that the dialogs got cleaned up after 30 minutes to prevent a replay attack. Thanks again for your help...I love this new feature of 2005.
Timsql

End User Utility

I heard some talk in the past about a tool called Data Views, or
something close to that name, that will be the end user tool for
Reporting Services. Currently, I can't seem to find any information on
it and my company is evaluating Reporting Services. We are looking for
some sort of information on end user tools that can be used to help
them write reports with RS instead of using visual studio. Any ideas
or suggestions?
ThanksMore information on Report Builder (aka Active Views) is available here:
http://www.microsoft.com/sql/reporting/productinfo/av_activeviews.asp
More detailed information will be available closer to RS 2005 Beta 3.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <cooperb@.gmail.com> wrote in message
news:6aba6d9.0411300723.7ea6957c@.posting.google.com...
> I heard some talk in the past about a tool called Data Views, or
> something close to that name, that will be the end user tool for
> Reporting Services. Currently, I can't seem to find any information on
> it and my company is evaluating Reporting Services. We are looking for
> some sort of information on end user tools that can be used to help
> them write reports with RS instead of using visual studio. Any ideas
> or suggestions?
> Thanks

end user report modification

Hi,
I need to allow users to modify end reports to their liking. Changing
grouping format etc. Currently I am using Crystal Reports and heard
that sql server reporting services offer end user report modifications.
I was wondering if any one using it can give me some feedback. Do you
know where i can get some literature on this particular feature of the
reporting service. If some one knows any place that uses it so i can
see what all report modifications the end users are allowed that would
be awesome :)
Thanks
:)look at 90 degree software
"B" wrote:
> Hi,
> I need to allow users to modify end reports to their liking. Changing
> grouping format etc. Currently I am using Crystal Reports and heard
> that sql server reporting services offer end user report modifications.
> I was wondering if any one using it can give me some feedback. Do you
> know where i can get some literature on this particular feature of the
> reporting service. If some one knows any place that uses it so i can
> see what all report modifications the end users are allowed that would
> be awesome :)
> Thanks
> :)
>

End User Report Designer

Hi,
I am looking into a suitable report tool I can use in an application.
I want to be able to give the end user the ability to create there own
reports, and then have the application access them just like any other
report I ship with the application.
Does SQL Reporting 2005 have a report design tool that can be used by end
users ? If not are there any third party ones ?
ThanksYou can use the Report Builder, which comes with SQL Reporting Services
2005.
You can create new reports from a set of templates. If you make the
templates general enough, your users should be able to report on everything
they need to report on.
More about the Report Builder at
http://msdn2.microsoft.com/en-us/library/ms155933.aspx
Kaisa M. Lindahl Lervik
"Aussie Rules" <AussieRules@.nospam.nospam> wrote in message
news:%23wGa6WP8GHA.608@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am looking into a suitable report tool I can use in an application.
> I want to be able to give the end user the ability to create there own
> reports, and then have the application access them just like any other
> report I ship with the application.
> Does SQL Reporting 2005 have a report design tool that can be used by end
> users ? If not are there any third party ones ?
> Thanks
>|||Hi,
Thanks for the reply.
Is the license on this report builder only available to be run on the
server.
Ideally it would be good if any number of end users could create a report at
there own workstations, and save them centrally...
THanks
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:e4jLJqP8GHA.4996@.TK2MSFTNGP03.phx.gbl...
> You can use the Report Builder, which comes with SQL Reporting Services
> 2005.
> You can create new reports from a set of templates. If you make the
> templates general enough, your users should be able to report on
> everything they need to report on.
> More about the Report Builder at
> http://msdn2.microsoft.com/en-us/library/ms155933.aspx
> Kaisa M. Lindahl Lervik
>
> "Aussie Rules" <AussieRules@.nospam.nospam> wrote in message
> news:%23wGa6WP8GHA.608@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am looking into a suitable report tool I can use in an application.
>> I want to be able to give the end user the ability to create there own
>> reports, and then have the application access them just like any other
>> report I ship with the application.
>> Does SQL Reporting 2005 have a report design tool that can be used by end
>> users ? If not are there any third party ones ?
>> Thanks
>|||Hello Aussie,
You do not need to purchase any additional license for the report builder.
For more information about license issue, You can call 1-800-426-9400,
Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific time) to speak
directly to a Microsoft licensing specialist, and you can get more detailed
information from there. Worldwide customers can use the Guide to Worldwide
Microsoft Licensing Sites to find contact information in their locations.
For detailed information on contacting Microsoft Customer Service, please
reference the following Microsoft Knowledge Base article.
Q295539 How and When to Contact Microsoft Customer Service
http://support.microsoft.com/?id=295539
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei,
Thanks for you reply.
Is there a standalone installation for the report builder tool, or must the
SQL tools be installed and this option selected?
Thanks
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:YbcGiPQ8GHA.1860@.TK2MSFTNGXA01.phx.gbl...
> Hello Aussie,
> You do not need to purchase any additional license for the report builder.
> For more information about license issue, You can call 1-800-426-9400,
> Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific time) to speak
> directly to a Microsoft licensing specialist, and you can get more
> detailed
> information from there. Worldwide customers can use the Guide to Worldwide
> Microsoft Licensing Sites to find contact information in their locations.
> For detailed information on contacting Microsoft Customer Service, please
> reference the following Microsoft Knowledge Base article.
> Q295539 How and When to Contact Microsoft Customer Service
> http://support.microsoft.com/?id=295539
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Aussie,
If you installed the SQL Reporting Services 2005, this tool will installed
in the server side and end user could access it from the Report Manager web
site.
The report builder is a ClickOnce application that end user could download
from the web site and then installed on the client.
Once you installed the SQL Reporting Services 2005, you could use the
Report Builder.
For more information about Report Builder, please refer the following
article:
Report Builder
http://msdn2.microsoft.com/en-us/library/ms155933.aspx
How to: Start Report Builder
http://msdn2.microsoft.com/en-us/library/ms159221.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
From what i have read this is a web based tool, is there a desktop tool. My
application is a VB.net desktop client accessing a sqlserver server
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:9UvHYro8GHA.1860@.TK2MSFTNGXA01.phx.gbl...
> Hello Aussie,
> If you installed the SQL Reporting Services 2005, this tool will installed
> in the server side and end user could access it from the Report Manager
> web
> site.
> The report builder is a ClickOnce application that end user could download
> from the web site and then installed on the client.
> Once you installed the SQL Reporting Services 2005, you could use the
> Report Builder.
> For more information about Report Builder, please refer the following
> article:
> Report Builder
> http://msdn2.microsoft.com/en-us/library/ms155933.aspx
> How to: Start Report Builder
> http://msdn2.microsoft.com/en-us/library/ms159221.aspx
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Aussie,
In the VB.NET winform application, you could use the Report Viewer control
and creat a Client Report Definition (.rdlc) File to retrieve the data.
You could convert the rdl file to rdlc file. The rdlc file will render in
your winform application and does not need the report server.
For more information, please refer the following article:
Creating Client Report Definition (.rdlc) Files
http://msdn2.microsoft.com/en-us/library/ms252067.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Note: Report Builder is a winforms application. It is accessible via URL but
is downloaded to the user's desktop and store in the Click Once cache for
use.
--
Regards,
Carolyn Chau
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wei Lu [MSFT]" wrote:
> Hello Aussie,
> In the VB.NET winform application, you could use the Report Viewer control
> and creat a Client Report Definition (.rdlc) File to retrieve the data.
> You could convert the rdl file to rdlc file. The rdlc file will render in
> your winform application and does not need the report server.
> For more information, please refer the following article:
> Creating Client Report Definition (.rdlc) Files
> http://msdn2.microsoft.com/en-us/library/ms252067.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||you need to look at 90 Degree Software - they do just that
"Aussie Rules" wrote:
> Hi,
> I am looking into a suitable report tool I can use in an application.
> I want to be able to give the end user the ability to create there own
> reports, and then have the application access them just like any other
> report I ship with the application.
> Does SQL Reporting 2005 have a report design tool that can be used by end
> users ? If not are there any third party ones ?
> Thanks
>
>

End user query analyzer access

My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
backend for peoplesoft. Our end users are requesting sql query analyzer
access in order to run select queries. Peoplesoft does have a built in query
tool. I am against giving them query analyzer access because of the following
- The end users are not technical and I am concerned about their ability to
write correct queries
- A long running select can take down the system
- Escalation of security privleges
I am curious on other thoughts or if anyone knows of what microsofts
recommendation is on this. Or how this is handled in other shops."mmalatek" <u40725@.uwe> wrote in message news:7e5a365f773b0@.uwe...
> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
> backend for peoplesoft. Our end users are requesting sql query analyzer
> access in order to run select queries. Peoplesoft does have a built in
> query
> tool. I am against giving them query analyzer access because of the
> following
> - The end users are not technical and I am concerned about their ability
> to
> write correct queries
> - A long running select can take down the system
> - Escalation of security privleges
> I am curious on other thoughts or if anyone knows of what microsofts
> recommendation is on this. Or how this is handled in other shops.
>
From a security perspective. If they can already run the queries through
the PeopleSoft tool, then you could set them up with QA utilizing the same
permission sets.
As for the long running queries, again I point at the PS tool. They can
write poor queries there as well.
At my company, we have taken the time to train our PS developers to take
advantage of things like NOLOCK and TOP xxx when they run their queries.
In general we don't have issues with them tying up the systems.
Last point: Why do they need to run queries against production? They
should be running them against a development and/or a test environment.
Rick Sawtell|||Rick Sawtell wrote:
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
>From a security perspective. If they can already run the queries through
>the PeopleSoft tool, then you could set them up with QA utilizing the same
>permission sets.
>As for the long running queries, again I point at the PS tool. They can
>write poor queries there as well.
>At my company, we have taken the time to train our PS developers to take
>advantage of things like NOLOCK and TOP xxx when they run their queries.
>In general we don't have issues with them tying up the systems.
>Last point: Why do they need to run queries against production? They
>should be running them against a development and/or a test environment.
>Rick Sawtell
This is not against production. And these are not developers these are
accountants and other users. Who have no sql training what-so-ever.|||> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
You mean you hand over you military fighter jet to somebody who never piloted an airplane? I
wouldn't do that. Analyze what information they need and use some reporting tool to write reports
for that. Or, at the least, analyze what information they need, create views for them and they can
use some "power-user" tool (like SQL Server's Report Builder) against those views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mmalatek" <u40725@.uwe> wrote in message news:7e5b507a82324@.uwe...
> Rick Sawtell wrote:
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
>>From a security perspective. If they can already run the queries through
>>the PeopleSoft tool, then you could set them up with QA utilizing the same
>>permission sets.
>>As for the long running queries, again I point at the PS tool. They can
>>write poor queries there as well.
>>At my company, we have taken the time to train our PS developers to take
>>advantage of things like NOLOCK and TOP xxx when they run their queries.
>>In general we don't have issues with them tying up the systems.
>>Last point: Why do they need to run queries against production? They
>>should be running them against a development and/or a test environment.
>>Rick Sawtell
> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
>|||Some people prefer to hand out Access or Excel over Query Analyzer for that
set of skill levels.
For what it is worth, I prefer to give them access to views rather than
directly to the tables. If either wise or necessary you can the do a couple
of things:
- Prepare views that do a basic set of common table joins, easing their work
and reducing the number of poorly designed joins.
- Make views that do not lock the tables so as to increase concurrency.
(Depending on the server level and the method used, this may raise other
problems. For both server versions read about 'isolation levels' and the
FROM statement's WITH option.
FWIW,
RLF
"mmalatek" <u40725@.uwe> wrote in message news:7e5b507a82324@.uwe...
> Rick Sawtell wrote:
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for
>> the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
>>From a security perspective. If they can already run the queries through
>>the PeopleSoft tool, then you could set them up with QA utilizing the same
>>permission sets.
>>As for the long running queries, again I point at the PS tool. They can
>>write poor queries there as well.
>>At my company, we have taken the time to train our PS developers to take
>>advantage of things like NOLOCK and TOP xxx when they run their queries.
>>In general we don't have issues with them tying up the systems.
>>Last point: Why do they need to run queries against production? They
>>should be running them against a development and/or a test environment.
>>Rick Sawtell
> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
>|||Consider, if budget and time permits, creating one or more Analysis Services
cubes and letting them play with those instead.
Possibly only appropriate if your end users are dealing with aggregated data
much of the time, but this is usually the case.
"mmalatek" <u40725@.uwe> wrote in message news:7e5a365f773b0@.uwe...
> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
> backend for peoplesoft. Our end users are requesting sql query analyzer
> access in order to run select queries. Peoplesoft does have a built in
> query
> tool. I am against giving them query analyzer access because of the
> following
> - The end users are not technical and I am concerned about their ability
> to
> write correct queries
> - A long running select can take down the system
> - Escalation of security privleges
> I am curious on other thoughts or if anyone knows of what microsofts
> recommendation is on this. Or how this is handled in other shops.
>|||Will Alber wrote:
>Consider, if budget and time permits, creating one or more Analysis Services
>cubes and letting them play with those instead.
>Possibly only appropriate if your end users are dealing with aggregated data
>much of the time, but this is usually the case.
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
I appreciate everyones input. What i am trying to get at is I want to make
sure my reasoning is on track. I have already offered to create views but I
need supporting reasons other then end users scare me.|||If you can create very simple views, and your users can cope with SQL, go
for it. Just ensure your database is fully locked down so no smart Alec
ends up DELETEing a whole bunch of records 'just because they could'!
I'd recommend keeping an eye on the performance. If this were a production
server, I'd never let users have access!
"mmalatek" <u40725@.uwe> wrote in message news:7e5c625b80010@.uwe...
> Will Alber wrote:
>>Consider, if budget and time permits, creating one or more Analysis
>>Services
>>cubes and letting them play with those instead.
>>Possibly only appropriate if your end users are dealing with aggregated
>>data
>>much of the time, but this is usually the case.
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for
>> the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
> I appreciate everyones input. What i am trying to get at is I want to make
> sure my reasoning is on track. I have already offered to create views but
> I
> need supporting reasons other then end users scare me.
>|||If there are performance problems you can set up a query server by
replicating the data. That should be no problem.
One of my customers has a query database at the production server.
This query database contains only nolock-views onto the production
database. They use Access as a query tool.
Often I would appreciate if they would use Query Analyzer because in
Access it is much too easy to busy sql server AND network! They write
poor queries and they cannot rely on their results - but they wouldn't
invest in a relyable and fast querying tool.
There is not one solution. In Query Analyzer you experience problems
in handing out the data to Excel. In Excel and Access you experience
problems in writing good sql ... and no enduser will write pass
through queries in Access. The best way is a querying tool. If they
need to build queries on their own, they have to know that they are
developers - and they have to behave as. Perhaps could some skillfull
power-users build queries for others - that would help.
Bye and good luck, Manfredsql

End User Filter Option

I am using RS 2005. Here is my questionâ?¦ I have two different date fields
(FileDate and TransDate) in my query and I want to give end user an option to
filter either one i.e on filedate or on Transdate and also a date range in
where clause. Is this possible? If so. How can I implement this
functionality?manipulate the information on the datasource side.
your datasource must be a stored proc created in such a way that if
filedate is null then use transdate.
eg.
create proc mydataset @.filedate datetime = null, @.transdate datetime =null
as
if @.filedate is null
begin
select fields from table where transdate = @.transdate
end
else
begin
select fields from table where filedate = @.filedate
end
Reddy wrote:
> I am using RS 2005. Here is my question... I have two different date fields
> (FileDate and TransDate) in my query and I want to give end user an option to
> filter either one i.e on filedate or on Transdate and also a date range in
> where clause. Is this possible? If so. How can I implement this
> functionality?

Thursday, March 29, 2012

ENDPOINTs in SQL Server 2005 April CTP

ENDPOINTs in SQL Server 2005 April CTP

ENDPOINTs in SQL Server 2005 April CTP

Endpoints and authentication

endpoints

Endpoint wont deploy

Endpoint Security ?

Endpoint Authentication

Endpoint Authentication

endpoint already registered to another service

endpoint already registered to another service

Endless subqueries

Endless running package using a proxy

Endless looping job!

Endless loop while sync.

Endless loop

Ending a Conversation

Endian problems with converting to varbinary

EndDialog

End User Utility

end user report modification

End User Report Designer

End user query analyzer access

End user query analyzer access

End User Filter Option

Empty Column

Blog Archive

About Me