Empty Column: object

Showing posts with label object. Show all posts

Thursday, March 29, 2012

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:
>

Monday, March 26, 2012

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Idea

Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same serversql

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Monday, March 19, 2012

Encrypted object is not transferable

Hi,
i m getting error "Encrypted object is not transferable, and script cannot be generated", while trying to open few stored procedure. It is extemly important for me to see the script of the sp as I need to study it.
help?
regards,
sim sim
Hi
The SP you are trying to look at is Encrypted.
There are a few tools on the NET to decrypt the SP. Look for "sql decrypt
sp" on Google.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:930C6B12-8E72-4A27-BB16-12E744937B82@.microsoft.com...
> Hi,
> i m getting error "Encrypted object is not transferable, and script
cannot be generated", while trying to open few stored procedure. It is
extemly important for me to see the script of the sp as I need to study it.
> help?
> regards,
> sim sim
>
>

Encrypted object is not transferable

Hi,
i m getting error "Encrypted object is not transferable, and script cannot b
e generated", while trying to open few stored procedure. It is extemly impor
tant for me to see the script of the sp as I need to study it.
help'
regards,
sim simHi
The SP you are trying to look at is Encrypted.
There are a few tools on the NET to decrypt the SP. Look for "sql decrypt
sp" on Google.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:930C6B12-8E72-4A27-BB16-12E744937B82@.microsoft.com...
> Hi,
> i m getting error "Encrypted object is not transferable, and script
cannot be generated", while trying to open few stored procedure. It is
extemly important for me to see the script of the sp as I need to study it.
> help'
> regards,
> sim sim
>
>

Wednesday, March 7, 2012

Enabling the Service Broker

I'm trying to enable the Service Broker for Sql Server 2005 because I want to be able to use a SqlDependency object.

I ran the following query to see if my local sql server service broker was enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname';

It came back with a value of 0 (which means it is not enabled).

I tried executing the following sql command to enable it:
ALTER DATABASE dbname SET ENABLE_BROKER;

The query has been running for over 5 mins and just keeps spinning (should it take this long to enable the Service brfoker), so I cancel it.

I even try to issue the command to see if the service broker is enabled after I cancel the query and it is not enabled.

How can I properly enable the Service Broker?

You need exclusive access to the database. See http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||

Thanks for the tip. It resolved my issue of enabling the service broker.

Now that I have it enabled, I am testing an application, and have code set up which is supposed to test whether a table got updated or not and am not haveing any luck with the Haschanges method changing to TRUE.

I have the following sql set up in my stored procedure:

<code>

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set NUMERIC_ROUNDABORT OFF
set ARITHABORT ON
GO
ALTER PROCEDURE [dbo].[SelectAllQCCSwitch]
AS
SET NOCOUNT ON;
SELECT GateWay, Description, CDRTable, Priority
FROM QCCSwitch

</code>

Here is my vb.net code that I have inside a class:

<code>

Public blnQCCSwitch As Boolean = False

Dim depQCCSwitch As New SqlDependency()

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try

If blnQCCSwitch = False Then

depQCCSwitch.AddCommandDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)

SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)

blnQCCSwitch = True

End If

If (depQCCSwitch.HasChanges) OrElse (blnQCCSwitch = False) Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

Else

If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

End If

Return DstFindBatch2

Catch ex As Exception

Throw ex

Finally

clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()

clsSelectAllQCCSwitch.Dispose()

End Try

End Function

.</code>

Notice the BOLDFACED text above which is what specifically concerns this post.

During debugging, I simply manually added a record to the table which is included in the "clsSelectAllQCCSwitch.cmdSelectQCCSwitch" Select command object.

I was expecting the HasChanges method of the Dependency object to change to TRUE and read the DB again to refresh the data.

What am I missing that I need to do?

|||

After you started the subscription, you are going to receive the notifications on the OnChangeEventHandler callback. You shouldn't explictily check for changes, you should simply continue and rely on the callback to notify you when a change occured.

HTH,
~ Remus

|||

Remus, thanks for the info...

I took that example logic and applied it to my app. I'm still expecting the Onchange event to occur (which only happens the first time the table is hit in my code) after I manually change a record in the table. However, the OnChange event is not firing when I do this. Is this because of the ConnectionString (meaning the table would have to be changed via the same connectionstring other than me doing it manually in order for it to fire)?

Here is my middle tier logic code. If you can verify that is seems okay, I would appreciate it very much. Or if I need to change something else.....

<code>

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
SqlDependency.Stop(clsSelectAllQCCSwitch.Connection.ConnectionString)
SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)
Dim depQCCSwitch As New SqlDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)
AddHandler depQCCSwitch.OnChange, AddressOf QCCSwitchDependency_OnChange
If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
End If
Return DstFindBatch2
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
End Try

End Function

Private Sub QCCSwitchDependency_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
Dim dependency As SqlDependency = CType(sender, SqlDependency)
RemoveHandler dependency.OnChange, AddressOf QCCSwitchDependency_OnChange
End Try

End Sub
</code>

|||

Have a look at the MSDN SqlDependency sample at http://msdn2.microsoft.com/en-us/a52dhwx7.aspx

The updates that trigger the notification can happen on any session and under any settings, it is not related whatsoever to the connection string of the SqlDependency.

Each time the OnChange is fired, the underlying Query Notification gets torn down and you have to subscribe again to get a new notification next time a change occurs. In the MSDN sample, the client calls again GetData at the end of it's dependency_OnChange method. Inside the GetData method, it sets up again a notification.

If you believe you've set up the notification correctly and applied an update yet the notification does not fire here are some steps to investigate:

- make sure the notification is created, see it in select * from sys.dm_qn_subscriptions
- make sutre the notification is fired. The Profiler will show this as an Broker:Broker Conversation event with the subclass SEND.
- make sure the notification message is delivered. Look in the database sys.transmission_queue, if the notification is still pending there it should have a transmission_status explaining why it cannot be delivered. My blog has a troubleshooting guide, at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx. While is generic for Service Broker, it applies just as well to Query Notification messages.

If the notification is successfuly delivered, then the OnChange method should fire.

HTH,
~ Remus

Friday, February 17, 2012

Empty table check!

Hi,

Is there any way i can check at report level or from a container object if a given table is empty?

Thank you all..

Please see this thread:

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

If you want to check whether or not a table is empty, use the RowNumber function.

For example, you can set Hidden = Iif(RowNumber("DataSetName") = 0, True, False)

Thursday, March 29, 2012

Endpoint Authentication

Endpoint Authentication

Monday, March 26, 2012

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Monday, March 19, 2012

Encrypted object is not transferable

Encrypted object is not transferable

Wednesday, March 7, 2012

Enabling the Service Broker

Friday, February 17, 2012

Empty table check!

Empty Column

Blog Archive

About Me