Problems installing SQL Server 2005 Mobile on device

I have a Fujitsu-Siemens LOOX 520 running WM 5 version 5.1.70 on which I am trying to create a Server 2005 Mobile database.

I have installed the following files on the Pocket PC after a hard reset to make sure the unit was in its vanilla state:

NETCFv2.wm.armv4i.cab

sqlce30.dev.ENU.ppc.wce5.armv4i.CAB

sqlce30.ppc.wce5.armv4i.CAB

sqlce30.repl.ppc.wce5.armv4i.CAB

All the files appeared to succesfully install. Connecting to the device in VS2005 works with no problems. Creating a small test app in VB.NET and running it on the device works no problems, so the device is satisfactorily connected to VS2005.

My problem is this:

When I try to create an SQL Server 2005 Mobile database on the device using Add Connection under the Data Sources window in Visual Studio 2005 Professional (RTM version), I get the following error:

"Microsoft SQL Server 2005 Mobile Edition and/or .NET Compact Framework v2.0 is not properly installed on your mobile device or the device is not responding."

Trying the same thing in Microsoft SQL Server Management Studio gives the same error, but a little bit of extra information:

"Microsoft SQL Server 2005 Mobile Edition and/or .NET Compact Framework v2.0 is not properly installed on your mobile device or the device is not responding. (SQL Server Mobile Edition ADO.NET Data Provider)"

As far as I can see, everything IS installed OK, so why is VS2005 not working?

Thanks for any help.

JB

A few others have reported this as well - creating the SQL Mobile database on device, directly from VS2005 seems a little flaky right now. My advice would be to create the SQL Mobile database on your development (desktop) machine and then add it to your smart device project as "Content" for deployment along with your CF app. You *can* create the database directly on device from SQL Server 2005 Management Studio if you have SQL Server 2005 installed - this does work well.

- Darren Shaffer

|||

Darren
Thanks very much indeed for your reply.
I discovered that installing a different set of files on the device actually solved the problem - specifically I installed the CE equivalents of the files that I listed previously. This seemed to sort the problem out - certainly installing those versions allowed the creation of an SQL mobile database on the device and locally on the desktop machine without any problems.
Incidentally, being something of a newbie to all this, I share the frustrations of Dave_AC with regards to the loss of simplicity of creating a database on the Desktop and being able to easily transfer it to the device without having to use SQL Server 2005 etc etc which I am completely new to. ADO.NET and Active Sync seemed to be a nice straightforward, relatively simple way of managing a single user database from desktop to device. Things seems a LOT more complicated now! I appreciate there is a tremendous amount of functionality and power that was not there before, but losing that simplicity seems to be a somewhat retrograde step in some ways.

(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163919&SiteID=1)
In that post, I see that you mentioned a webcast which you presented - -"I'm not entirely clear on why you need this - I build a *LOT* of CF apps for clients that include SQL CE and SQL Mobile and have never had a need to work with the .sdf file from a desktop app. If you are trying to find an effective way to load the initial database with data, my recent webcast shows how to do that using nothing but SQL Server 2005 Management Studio."
Where can we access that webcast? And are there other webcasts available that will help new users to get their heads round all this?

Thanks again for your help.

JB

|||

Darren

I stress that I truly am a Newbie in the midst of some serious Code Gods, so if I sound like I don't have a clue what I'm talking about - it's because I don't!

Cheers!

JB

|||

Here is the link to the on-demand webcast:

http://www.msreadiness.com/ws_abstract.asp?eid=15003229

I guess I still struggle to understand the need to create a SQL Mobile database from desktop .NET code when it can be created:

1. using query analyzer on device

2. on the desktop from VS2005

3. on the desktop from SS2005

4. in code on device

5. as a byproduct of a merge replication with AddOption.CreateDatabase enabled

Maybe I've been doing this for so long that I'm not seeing what it looks like to someone coming into it fresh. In any case, all of the CF MVPs are encouraging Microsoft to expand the reach of SQL Mobile. It would make a fabulous ClickOnce database option for Smart Clients.

-Darren

|||

Thanks for the link, Darren.

I'm not actually suggesting that I want to create an SQL mobile database from desktop .NET code - I understand how to create mobile databases via VS2005 or SS2005 which either reside on the desktop or on the device. That works, and works very well.

The difficulty for me comes in creating a simple single user database on the desktop which can be accessed by desktop code - for example an Access database like I used when programming with VS2003 VB.NET - and then having a simple way of synchronising the database on the mobile device with that database, purely for single user capability. In fact, for simple applications, you don't even want to be able to sync the database - you just want to be able to easily move the database back and forward from the desktop to the device, and be able to access that database from both locations ie. do some work on database on PC, then copy over to device and use there with no requirement to take data back from the device to the PC - really really simple. I write apps in which I actually don't WANT the user to be able to modify the desktop database from the device database. All the editing is done on the PC version, and then just copied to the devices for use in the field. Its a simple one-way process.

Previously I would have used RAPI and Active sync to move the database to the device with conversion being done automatically.

So in the past, you could make an Access database, then copy it over to the PPC and use the IntheHand wrapper to access the converted file on the pocket PC - and I'm sure you are aware of all this! All nice and straightforward - but with the new WM5 stuff and with SQL Server 2005 mobile, and SQL Server 2005, it seems that you can't do that as easily. You have to use replication, configure IIS for publication of data, and get into a whole load of stuff to do something which in the past was reasonably simple. Or do you? That's what I am trying to discover...

It may be that I am completely missing the point.. Remember, I've only had the VS2005 stuff for about a week now, and Christmas has inconveniently imposed itself on my programming time - and there's a lot to learn!

I'm not knocking all the SQL Mobile stuff - it looks superb once you have got your head round it - but for a newbie, it is pretty imposing! I guess I am just supremely thick.

Are there any tutorials or walkthroughs which explain this stuff at my dullard level?

Thanks again for your input.

JB

Problems getting all the data back with a XML query

I am having a nightmare trying to get to the bottom of this problem.
I have a stored procedure that has a user defined function to create a
select table from a csv string of IDs.
The function and the Stored procedure works fine and returns the XML,
Elements as required except that when i process this sp on the client most
of the data is missing.
If i run the query in the query analyser with the csv parameter and no FOR
XML output i get 72 records (10 unique resources, the rest are due to the
joins in the query)
If i run it with the FOR XML i get 6 lines of XML although it is oddly
truncated. If i cut and past it into xmlspy there are missing elements so i
can't verify exactly what i'm getting.
But when i run this in my web app and use am XMLTextReader to turn the
results into a string I get 2 records, when i was expecting 10. I can verify
this by pasting the xml into xmlspy.
I have tried SQLXML managed classes but can't get them to accept parameters,
i keep getting the message that the sp is expecting parameter despite using
createParameter against the command as per all the documentation, so i gave
up with this approach. I thought that this way i could populate a new
dataset and then bind a table to a datagrid control to see what was
returned. I can get it all to work bar the parameters.
Can anyone shed any light on why the results are significantly less than the
sp should return
and can someone give me an example of working with a parameterised sp and
SQLXML
Many thanks
John
VS2003, SQLXML sp2, XPpro sp2
I am confused about what you are trying to get.
FOR XML results a single XML stream if you use the supported APIs through
ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC and
thus shows the XML result junked into 2033 characters per row. You should
not use QA if you plan on further process the result.
So if you can provide us with some more information about what exactly you
do on the API level, we may be able to help...
Best regards
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>I am having a nightmare trying to get to the bottom of this problem.
> I have a stored procedure that has a user defined function to create a
> select table from a csv string of IDs.
> The function and the Stored procedure works fine and returns the XML,
> Elements as required except that when i process this sp on the client most
> of the data is missing.
> If i run the query in the query analyser with the csv parameter and no FOR
> XML output i get 72 records (10 unique resources, the rest are due to the
> joins in the query)
> If i run it with the FOR XML i get 6 lines of XML although it is oddly
> truncated. If i cut and past it into xmlspy there are missing elements so
> i can't verify exactly what i'm getting.
> But when i run this in my web app and use am XMLTextReader to turn the
> results into a string I get 2 records, when i was expecting 10. I can
> verify this by pasting the xml into xmlspy.
> I have tried SQLXML managed classes but can't get them to accept
> parameters, i keep getting the message that the sp is expecting parameter
> despite using createParameter against the command as per all the
> documentation, so i gave up with this approach. I thought that this way i
> could populate a new dataset and then bind a table to a datagrid control
> to see what was returned. I can get it all to work bar the parameters.
> Can anyone shed any light on why the results are significantly less than
> the sp should return
> and can someone give me an example of working with a parameterised sp and
> SQLXML
>
> Many thanks
> John
> VS2003, SQLXML sp2, XPpro sp2
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.xml:25072
Michael,
thanks I have sorted the problem eventually. Brain fade and not enough time
thinking it throgh. The problem was in the sql statement which took ages to
track down but there we go.
One question that is unanswered is how do i pass parameters to SQLXML with
stored procedures, not raw sql text?
i keep getting the message expecting parameter as per my post
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>I am confused about what you are trying to get.
> FOR XML results a single XML stream if you use the supported APIs through
> ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC
> and thus shows the XML result junked into 2033 characters per row. You
> should not use QA if you plan on further process the result.
> So if you can provide us with some more information about what exactly you
> do on the API level, we may be able to help...
> Best regards
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>
|||To what exactly do you want to pass parameters? SQLXML is a general term and
the name of the mid-tier component.
Do you mean how to pass parameters into the SQL statement that uses FOR XML
via stored procs?
Thanks
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
> Michael,
> thanks I have sorted the problem eventually. Brain fade and not enough
> time thinking it throgh. The problem was in the sql statement which took
> ages to track down but there we go.
> One question that is unanswered is how do i pass parameters to SQLXML with
> stored procedures, not raw sql text?
> i keep getting the message expecting parameter as per my post
> thanks
> john
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>
|||Michael
here is the code that i am using
Dim strConn = "provider=SQLOLEDB;data source='....';initial
catalog=......;user id=sa;password=......"
Dim sxcCmd As New SqlXmlCommand(strConn)
Dim sdaDA As New SqlXmlAdapter(sxcCmd)
Dim sxpParam As SqlXmlParameter
Dim xr As Xml.XmlReader
Dim ds As New DataSet
With sxcCmd
..RootTag = "root"
..CommandType = SqlXmlCommandType.Sql
..CommandText = "Test2"
sxpParam = .CreateParameter
End With
With sxpParam
..Name = "@.IDs"
..Value = 5
End With
sdaDA.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
and here is the sp
ALTER PROCEDURE dbo.test2
(
@.IDs int
)
AS
/* SET NOCOUNT ON */
SELECT '<root>'
SELECT * FROM tblResource WHERE ResourceID=@.Ids
FOR XML AUTO, Elements
SELECT '</root>'
as you can see the sp has this parameter @.IDs but when i run the code the
error meesage says ' expecting parameter @.IDs'' yet i am passing it. If i
change the command text to a sql statement with a ? for the parameter then
it works.
Obviously i am missing something here, I presume the command type might be
wrongly set.
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O45cbdZrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> To what exactly do you want to pass parameters? SQLXML is a general term
> and the name of the mid-tier component.
> Do you mean how to pass parameters into the SQL statement that uses FOR
> XML via stored procs?
> Thanks
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
>

Problems creating second relationship to the same table

I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:

- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.

Hi. Maybe the SecondCategoryId field has data that not exists in the categories table|||

mariop77 is probably right.

Try doing a SELECT DISTINCT SecondCategoryID FROM ADS

Make sure all FKs really do exist.

|||It has Nulls someties. How can I allow nulls? The reason is sometimes there will be no second category...|||

Hi,

Actually, null is allowed here. But you need to make sure that Allow Null has been checked in the design view of the table.

|||Yes, it is allowed on the column level of course, yet can not create relationship...|||

Seems to me like this is a limitation not allowing more than one join to the same table... Do I need to create alias for the existing table?

1 Category (alias 1) many Ads on Ads.CategoryID

1 Category (Alias 2) many Ads on Ads.SecondCategoryID

I used to be able to do this in MS Access, but maybe SQL does not allow...

sql

Problems creating a stored procedure in Management Studio

Hi,

I am probably going to sound really stupid but I have recently installed SQL Server Management Studio and been trying to figure out how to create a stored procedure in it. I have created a database called 'BTL_Dictionary' and that works fine. In the Object Explorer window when I click on the database, I get the tree view of all the different options. In order to write the stored procedure , I clicked on Programmability --> Stored Procedures --> New Stored Procedure.

This opened up the template and i made the following changes to the template :

-- =============================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: blah

-- Create date:

-- Description: Adds the values into the DictionaryTable

-- =============================================

CREATE PROCEDURE [dbo].[AddDictionary]

-- Add the parameters for the stored procedure here

@.DictionaryID int ,

@.DictionaryName nvarchar(50),

@.DictionaryType nvarchar(50)

AS

BEGIN

-- Insert statements for procedure here

INSERT INTO DictionaryTable

(

DictionaryID,

DictionaryName,

DictionaryType

)

VALUES

(

@.DictionaryID ,

@.DictionaryName ,

@.DictionaryType

)

END

GO

-- =============================================

I saved the stored procedure, but when I refresh the database, I cannot see the stored procedure there. I trired re-opening the query and it indicates that the procedure belongs to the 'master' database instead of the 'BTL_Dictionary' database (its prefixed 'master.AddDictionary' instead of ''BTL_Dictionary.AddDictionary')

Can anyone tell me whats is is that I am doing wrong and how I can get rid of this .

Thanks

When you click "New Stored Procedure" MS will open a new query window with the PROC template in it. Just check that the database context is set to your database and not the master database. Or add a "USE" statement before the first statement in the template to ensure that the context is correct.

|||Thanks a lot for , that resolved the problem.

Problems Creating a Full-Text Catalog

When I attempt to create a full-text catalog on a database, I receive the following error:

Create failed for FullTextCatalog 'AccountNotes'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL server failed to communicate with Full-Text Service (msftesql). The system administrator must make sure that same service account is used for both services and the service account has the permission to auto start the full-text service. (Microsoft SQL Server, Error: 9955)

I have ensured that my SQL Server Service and the Full-Text service are running as the same account. I've had little luck finding an answer in the documentation.

Any help is greatly appreciated.

Thank you in advance.

MB
Just checking to see if anyone had any suggestions. I'm still stuck where I was before.

I'm running SQL Developer Edition 2005.

Thank you.

Problems Creating a Cube using Informix IDS 10.0 as Data Source

Greetings:

I am a n00b trying to create a new SQL Server 2000 Analysis Services Cube using Informix IDS 10.0 as the data source. The trick is that the raw data needs to stay in IDS -- no ETL to get the data into SQL Server.

I have installed the Informix client stuff on my SQL Server computer, but when I try to use the "normal" way to create a cube, I am having problems. Here is what I am doing:

1. Right-click "Data Sources" folder under the new AS database I just created -- select "New Data Source".
2. Select "Informix OLE DB Provider".
3. Type in connection info, and click "Test Connection". The test succeeds!!!!!
4. Save it.
5. Right-click "Cubes" folder under the new AS database -- select "New Cube", then "Wizard".
6. When I am supposed to see a list of tables from my newly created data source, I see just an error that says AS cannot connect to IDS.

I am confused by the fact that AS is apparently either using different info or a different method to connect to IDS than what it used when I clicked "Test Connection" when I created the data source.

Hopefully, someone has some past experience getting these 2 products to "play nice".

Any assistance would be greatly appreciated.

-Joe

Joe, did you have any luck resolving this? I'm attempting to do the same and just getting started. Thanks!

Problems Creating a Cube using Informix IDS 10.0 as Data Source

Greetings:

I am a n00b trying to create a new SQL Server 2000 Analysis Services Cube using Informix IDS 10.0 as the data source. The trick is that the raw data needs to stay in IDS -- no ETL to get the data into SQL Server.

I have installed the Informix client stuff on my SQL Server computer, but when I try to use the "normal" way to create a cube, I am having problems. Here is what I am doing:

1. Right-click "Data Sources" folder under the new AS database I just created -- select "New Data Source".
2. Select "Informix OLE DB Provider".
3. Type in connection info, and click "Test Connection". The test succeeds!!!!!
4. Save it.
5. Right-click "Cubes" folder under the new AS database -- select "New Cube", then "Wizard".
6. When I am supposed to see a list of tables from my newly created data source, I see just an error that says AS cannot connect to IDS.

I am confused by the fact that AS is apparently either using different info or a different method to connect to IDS than what it used when I clicked "Test Connection" when I created the data source.

Hopefully, someone has some past experience getting these 2 products to "play nice".

Any assistance would be greatly appreciated.

-Joe

Joe, did you have any luck resolving this? I'm attempting to do the same and just getting started. Thanks!sql

Problems connecting to local SQL server [HELP!]

Hi all,

I have the developer edition of Visual Studio 2005. After successfully installing it in my local machine, I wanted to create a small application which connects to a database. I accessed the Server Explorer tool, right clicked on Data Connections and brought up the Create New SQL Server Database option.

The issue now is that I don't see a list of servers in the "Server Name" drop down box although in the Server Explorer itself, I have a server listed (your-c6b5e4eabc - The default computer name of my local machine).

I am given to believe that SQL server is unable to make a connection because I have not configured something which is essential.

I hope someone can point me as to how to make databases using SQL server and connect them to the application the way developers usually do. I'm a complete novice to VS 2005, and the SQL server configuration options have got me confused.

I'm not sure if this will help, but I'll include this here anyway :

When I tried copying and pasting the server name (your-c6b5e4eabc) into the "Server Name" drop down box and creating a database by entering a name in the "Enter database name" textbox, I get the following error message -

An error occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL server does not allow remote connections. provider : Named Pipes Provider error : 40 Error : Could not open a connection to SQL Server.

I'm running Windows XP Media Center Edition v2002 SP 2.

I just want to be able to create and work with databases from within the applications I create in Visual Studio 2005. I played around with the configuration options (like enabling some protocols, the SQL server browser etc., but to no avail.) Is there a way to revert back to the default settings in an easy way? Thanks in advance for all your guidance...

Have a look in my screencasts section on my site showing you the way to enabled remote connections.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Hey there,

Thanks very much for your screencast link. I think we should have more such user-friendly guides for first timers to Visual Studio. Unfortunately, I'm still facing the same problem creating a database from the Server Explorer.

I also have a rider question here : Although I've made a typical installation of Visual Studio 2005, the service I seem to be running isn't MSSQLSERVER, but rather SQLEXPRESS. I really need someone to throw more light on this aspect of Visual Studio 2005...or is my case an aberration?

Also, I mentioned playing around with the settings of the SQL Server 2005 Configuration Manager...can that be affecting things in any way? What are the ideal settings to connect to the server from within the server explorer in VS 2005? Most notably, I've set the SQL Server 2005 Services (SQLExoress and SQLBrowser) to log on as "NT Authority/LocalService"...is this the right setting?

Thanks again.

|||

Hi, thanks for the feedback, if you have any upcoming ideas about screencasts, feel free to contact me :-).

1.
SQL Server Express comes with VS 2005, you can use SQL Server Express at no charge. The SQL Server instances are reflected in the Service names, which are for a default instance MSSQLSERVER and for named Instances MSSQL$InstanceName. Named instances can be accessed by using the MachineName\InstanceName (and in addition if you don′t use the default port the ",portnumber" addition to the) name.

2.
Sure, changing the configuration will affect the current SQL Server instance (some changes might be reflected after the next reboot, like the remote connections setting). Local Services is fine if you don′t need any network ressources.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problems configuring Execution Log Reports

I receive the following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'RSExecutionLog'.
(rsErrorOpeningConnection) Get Online Help
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
My ReportServer DB is separate from my ReportServer IIS boxes, I'm trying to
use Windows NT Integrated Security to access the Execution Log. I'm a Local
Admin of both servers but still receive the error.
Am I dealing with a permissions hop issue? Any suggestions would help.
DavidYes. Use stored credentials in the shared data source for the reports
(another slightly more complex option would be to use Kerberos delegation)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David Dubay" <DavidDubay@.discussions.microsoft.com> wrote in message
news:30C3BBF3-5DE7-4F4E-9008-349CA172D6EC@.microsoft.com...
>I receive the following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'RSExecutionLog'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> My ReportServer DB is separate from my ReportServer IIS boxes, I'm trying
> to
> use Windows NT Integrated Security to access the Execution Log. I'm a
> Local
> Admin of both servers but still receive the error.
> Am I dealing with a permissions hop issue? Any suggestions would help.
> David

Problems communicating across services.

Hello,

I am writting two applications that talk to each other. I create a service and then a dialog that points to Client2Service which means it is talking to it self. When I do that it works because I get pack the string "client1" which I am forcing it to send if it is calling ServiceProc in client1. I cahnge the Client2Service to Client1Service and it does not work any more. I do not understand why but am guessing it has something to do with the way I have the database setup. The installation script is bellow. Please share your thoughts because I am running out of ideas.

USE master;

GO

CREATE DATABASE ssb_FloorSystem;

GO

USE ssb_FloorSystem;

GO

CREATE MESSAGE TYPE Request VALIDATION = None;

CREATE MESSAGE TYPE Response VALIDATION = None;

GO

CREATE CONTRACT MessageTalk(

Request SENT BY INITIATOR,

Response SENT BY TARGET

);

GO

-- Install assemblies

BEGIN TRY

CREATE ASSEMBLY BrokerLibraryAssembly

FROM 'c:\ServiceBroker\Client1\Client1\bin\Debug\ServiceBrokerInterface.dll'

END TRY

BEGIN CATCH

-- Catch and handle exception

END CATCH;

GO

BEGIN TRY

CREATE ASSEMBLY SqlClient1BrokerServiceAssembly

FROM 'c:\ServiceBroker\Client1\Client1\bin\Debug\SqlClient1BrokerService.dll'

END TRY

BEGIN CATCH

-- Catch and handle exception

END CATCH;

GO

BEGIN TRY

CREATE ASSEMBLY SqlClient2BrokerServiceAssembly

FROM 'c:\ServiceBroker\Client2\Client2\bin\Debug\SqlClient2BrokerService.dll'

END TRY

BEGIN CATCH

-- Catch and handle exception

END CATCH;

GO

CREATE PROCEDURE Client1ServiceProc

AS

EXTERNAL NAME SqlClient1BrokerServiceAssembly.[SqlBrokerServiceNS.SqlBrokerService].ServiceProc

GO

CREATE PROCEDURE Client2ServiceProc

AS

EXTERNAL NAME SqlClient2BrokerServiceAssembly.[SqlBrokerServiceNS.SqlBrokerService].ServiceProc

GO

CREATE QUEUE Client1ServiceQueue

WITH

STATUS = ON,

RETENTION = ON,

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = Client2ServiceProc,

MAX_QUEUE_READERS = 4,

EXECUTE AS SELf

)

ON [default];

GO

CREATE QUEUE Client2ServiceQueue

WITH

STATUS = ON,

RETENTION = ON,

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = Client1ServiceProc,

MAX_QUEUE_READERS = 4,

EXECUTE AS SELf

)

ON [default];

GO

CREATE QUEUE [dbo].[ClientQueue] WITH STATUS = ON;

GO

CREATE SERVICE Client1Service ON QUEUE Client1ServiceQueue (

MessageTalk,

[http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho]

);

GO

CREATE SERVICE Client2Service ON QUEUE Client2ServiceQueue (

MessageTalk,

[http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho]

);

GO

CREATE SERVICE HelloWorldClient ON QUEUE ClientQueue (

MessageTalk,

[http://schemas.microsoft.com/SQL/ServiceBroker/ServiceEcho]

);

GO

EXEC sp_configure 'clr enabled', 1

GO

RECONFIGURE WITH OVERRIDE

GO

USE master;

GO

Thanks,

Scott Allison...

How are you beginning the dialog and sending the message? Did you try to diagnose the problem by looking at (i.e. simply doing a select * from...):

1. the initiator queue

2. the target quueue

3. sys.transmission_queue

|||

Hello Rushi,

I tried

select * from sys.dm_broker_activated_tasks

select * from sys.transmission_queue

and they both returned nothing. The code to send it bellow.

try

{

// Create a connection

conn = new SqlConnection(

"Initial Catalog=ssb_FloorSystem; Data Source=localhost;Integrated Security=SSPI;");

// Open the connection

conn.Open();

// Begin a transaction

tran = conn.BeginTransaction();

// Create a service object

client = new Service("HelloWorldClient", conn, tran);

// Set the FetchSize to 1 since we will receive one message at a time

// i.e. use RECEIVE TOP(1)

client.FetchSize = 1;

// Begin a dialog with the HelloWorld service

dialog = client.BeginDialog(

"Client1Service",

null,

"MessageTalk",

TimeSpan.FromMinutes(1),

false,

conn,

tran);

// Create an empty request message

MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));

Message request = new Message("Request", body);

// Send the message to the service

dialog.Send(request, conn, tran);

tran.Commit(); // Message isn't sent until transaction has been committed

}

catch

{

tran.Rollback();

}

While the Code to recieve is bellow.

public string GetMessage(SqlConnection conn, Conversation dialog, Service client, SqlTransaction tran)

{

TextReader reader = null;

try

{

// Begin a transaction

tran = conn.BeginTransaction();

Console.WriteLine("\nTransaction 1 begun");

client.WaitforTimeout = TimeSpan.FromSeconds(5);

if (client.GetConversation(dialog, conn, tran) == null)

{

Console.WriteLine("No message received - Ending dialog with Error");

dialog.EndWithError(1, "no response within 5 seconds.", conn, tran);

tran.Commit();

Console.WriteLine("Transaction 2 committed");

conn.Close();

Console.WriteLine("\nConnection closed - exiting");

return "";

}

// Fetch the message from the conversation

Message response = dialog.Receive();

// Output the message to the Console

//Console.WriteLine("Message received of type '" + response.Type + "'");

if (response.Body != null)

{

reader = new StreamReader(response.Body);

}

// End the conversation

dialog.End(conn, tran);

Console.WriteLine("Ended Dialog");

//tran.Commit(); // Remember to commit again

Console.WriteLine("Transaction 2 committed");

// Close the database connection

conn.Close();

Console.WriteLine("\nConnection closed - exiting");

if (response.Body != null)

return reader.ReadToEnd();

return "";

}

catch (ServiceException e)

{

Console.WriteLine("An exception occurred - {0}\n", e.ToString());

if (tran != null)

{

tran.Rollback();

Console.WriteLine("\nTransaction rolled back");

}

if (conn != null)

{

conn.Close();

Console.WriteLine("\nConnection closed - exiting");

}

return "";

}

finally

{

if (reader != null)

reader.Close();

Console.WriteLine();

Console.WriteLine("Press Enter to Exit");

Console.ReadLine();

}

}

Thanks,

Scott Allison...

Problems Backing up database to a PC with 200GB drive

SQL Server 2000 on Windows 2000.
Is this possible. I try to create a new backup device and the only thing I
can locate is the local partitioned drives. I try to put in a UNC and it
does not work.
Is this not possible to put in a UNC to a shared drive on a PC. The backup
is needed to get off of the server because of space. We don't have a server
big enough to handle the size of the database.
Is it possible this is a a rights issue and I can't get off the server due
to rights. I have shared the heck out of the share on the PC.
Thanks for any help.
Gordon
I assume you tried mapping a drive letter to the share? I have a network
share mapped as a local drive letter "L:". I just did a backup from SQL2K to
"L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backup
device window in the backup dialog. What happens when you do this?
"Gordon" wrote:

> SQL Server 2000 on Windows 2000.
> Is this possible. I try to create a new backup device and the only thing I
> can locate is the local partitioned drives. I try to put in a UNC and it
> does not work.
> Is this not possible to put in a UNC to a shared drive on a PC. The backup
> is needed to get off of the server because of space. We don't have a server
> big enough to handle the size of the database.
> Is it possible this is a a rights issue and I can't get off the server due
> to rights. I have shared the heck out of the share on the PC.
> Thanks for any help.
> Gordon
|||I cannot see the share when I mapped it. When I go into the backup device
dialog, I can only see the partitioned drives. When I use a UNC to get to
anything else I get a message saying, "The backup device cannot see the
drive. Do you want to continue using this backup device."
Funny thing is, I tried this on another SQL server and I can get to the
shared drive on the PC. It is only on this server. Some setting somewhere.
All the rights are the same for the logged in account on both servers.
UGGGGHHHHH.
Thanks for any help you can give.
"kaborka" wrote:
[vbcol=seagreen]
> I assume you tried mapping a drive letter to the share? I have a network
> share mapped as a local drive letter "L:". I just did a backup from SQL2K to
> "L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backup
> device window in the backup dialog. What happens when you do this?
> "Gordon" wrote:
|||When a backup occurs, regardless of who is doing it the account that gets
used is the one that SQL Server is running under. THat account must be a
domain account with permissions to read and write to that share. UNC's are
the preferred way to backup to a remote share but you need the rght account
to access it.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:42BB3F1A-DCDC-471D-A221-2221304FE4F0@.microsoft.com...[vbcol=seagreen]
>I cannot see the share when I mapped it. When I go into the backup device
> dialog, I can only see the partitioned drives. When I use a UNC to get to
> anything else I get a message saying, "The backup device cannot see the
> drive. Do you want to continue using this backup device."
> Funny thing is, I tried this on another SQL server and I can get to the
> shared drive on the PC. It is only on this server. Some setting
> somewhere.
> All the rights are the same for the logged in account on both servers.
> UGGGGHHHHH.
> Thanks for any help you can give.
> "kaborka" wrote:

Problems Backing up database to a PC with 200GB drive

SQL Server 2000 on Windows 2000.
Is this possible. I try to create a new backup device and the only thing I
can locate is the local partitioned drives. I try to put in a UNC and it
does not work.
Is this not possible to put in a UNC to a shared drive on a PC. The backup
is needed to get off of the server because of space. We don't have a server
big enough to handle the size of the database.
Is it possible this is a a rights issue and I can't get off the server due
to rights. I have shared the heck out of the share on the PC.
Thanks for any help.
GordonI assume you tried mapping a drive letter to the share? I have a network
share mapped as a local drive letter "L:". I just did a backup from SQL2K t
o
"L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backup
device window in the backup dialog. What happens when you do this?
"Gordon" wrote:

> SQL Server 2000 on Windows 2000.
> Is this possible. I try to create a new backup device and the only thing
I
> can locate is the local partitioned drives. I try to put in a UNC and it
> does not work.
> Is this not possible to put in a UNC to a shared drive on a PC. The backu
p
> is needed to get off of the server because of space. We don't have a serv
er
> big enough to handle the size of the database.
> Is it possible this is a a rights issue and I can't get off the server due
> to rights. I have shared the heck out of the share on the PC.
> Thanks for any help.
> Gordon|||I cannot see the share when I mapped it. When I go into the backup device
dialog, I can only see the partitioned drives. When I use a UNC to get to
anything else I get a message saying, "The backup device cannot see the
drive. Do you want to continue using this backup device."
Funny thing is, I tried this on another SQL server and I can get to the
shared drive on the PC. It is only on this server. Some setting somewhere.
All the rights are the same for the logged in account on both servers.
UGGGGHHHHH.
Thanks for any help you can give.
"kaborka" wrote:
[vbcol=seagreen]
> I assume you tried mapping a drive letter to the share? I have a network
> share mapped as a local drive letter "L:". I just did a backup from SQL2K
to
> "L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backu
p
> device window in the backup dialog. What happens when you do this?
> "Gordon" wrote:
>|||When a backup occurs, regardless of who is doing it the account that gets
used is the one that SQL Server is running under. THat account must be a
domain account with permissions to read and write to that share. UNC's are
the preferred way to backup to a remote share but you need the rght account
to access it.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:42BB3F1A-DCDC-471D-A221-2221304FE4F0@.microsoft.com...[vbcol=seagreen]
>I cannot see the share when I mapped it. When I go into the backup device
> dialog, I can only see the partitioned drives. When I use a UNC to get to
> anything else I get a message saying, "The backup device cannot see the
> drive. Do you want to continue using this backup device."
> Funny thing is, I tried this on another SQL server and I can get to the
> shared drive on the PC. It is only on this server. Some setting
> somewhere.
> All the rights are the same for the logged in account on both servers.
> UGGGGHHHHH.
> Thanks for any help you can give.
> "kaborka" wrote:
>

problems adding records to linked tables

Hi everyone,

I just used the upsize wizard in Access to create a Access front end and SQL server 2000 backend with linked tables, but I am receiving an error that I can't figure out.

When I am trying to add a record into my 'Cases' linked table in the SQL server, I get a error saying: "Invalid object name" with -2147217865 as the error message.

Here is my code:

Dim testrecord As ADODB.Recordset
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb.1;" & _
"DSN=intelitrak;" & _
"UID=geir;PWD=test"
conn.Mode = adModeReadWrite
conn.Open

Set testrecord = New ADODB.Recordset
testrecord.Open "Cases", conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect -- the ERROR occurs here!!!

With testrecord
.AddNew
If Forms(stDocName)!NewReopen = "New" Then
!MasterID = NewID
IsReOpen = False
Else

....etc.

I looked all around for an answer, and I partially found a answer at Microsoft's website: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1

However, I do not know how to make this work. I tried to create a stored procedure, and test if that made any differance, but it still said it the it was a invalid object.

Can anyone help me?

GeirJust a shot in the dark...

What if you add dbo_ to the front of Cases|||That was not it.

I tried to put 'dbo_Cases' in my VBA code, but the same error happens.

Geir|||Sorry...mayb a post in the VB forum...

Do you have other code that connects?|||none of my code that involves adding new records to the database is working. The code that views the records, however, works perfectly.

Geir

Problems acessing the database after creating a website on localhost

I cannot access the data source when I create a website in express - when I try to connect to the database I get an error telling that I am unable to connect to the data source - I tried doing it through the Visual Studio and through the Web Configaration.

Can some please help - I need to conncet!!!

What the connction string and the exact error message ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

"Failed to generate a user instance of SQL Server due to failure in starting the process for the user instance. The connection will be closed"

This is the error messgae that I get when I try to connect to the data source.

Can you help?

|||Do you have SQL Server Express installed ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Reese C. M. Harris wrote:

"Failed to generate a user instance of SQL Server due to failure in starting the process for the user instance. The connection will be closed"

This is the error messgae that I get when I try to connect to the data source.

Can you help?

This is a known issue and the solution is the one posted by user Reen in the long thread below. Hope this helps.

http://forums.microsoft.com/msdn/showpost.aspx?postid=293667&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

Problems accessing CreateSubscription via API

Hi,

I'm trying to create a subscription via API and am getting an error. BTW, creating it through ReportManager works great, meaning the Report, the DataSource and its credientials are all working as planned.

I'm following the sample for CreateSubscription() on the books online, http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

I'm hosting the script on a web page and when running, am getting an error:

"One of the extension parameters is not valid for the following reason: The account you are using does not have administrator privileges. A subscription cannot be created fordank@.adventure-works.com"

Well, obvisouly it's not working. Moreover, I don't understand the error, what does the 'TO' email to do with it?

Please help,

Edmund

Oops, I fogot to mention that this occurs only when authoenticated under ASP.NET 2.0's membership forms authentication.

Edmund

Problem: Reporting using Report Model

I'm having a reporting problem when using the report model as my data source. I was able to duplicate the problem by trying to create a report dataset using the ModelTutorial (based on the AdventureWorks database) as my data source.

For example, say you wanted to get all products which have illustrations. The query shown below would retrieve the 23 rows as expected (don't worry about duplicate products for this example).

SELECT P.ProductID, P.ProductNumber, P.Name, I.IllustrationID, I.ModifiedDate
FROM Production.Product P
INNER JOIN Production.ProductModel PM ON P.ProductModelID = PM.ProductModelID
INNER JOIN Production.ProductModelIllustration PMI ON PM.ProductModelID = PMI.ProductModelID
INNER JOIN Production.Illustration I ON PMI.IllustrationID = I.IllustrationID

Trying to create a dataset to get this same information using the report model as the data source is my problem. I am unable to get fields from all the following 4 tables: Product, ProductModel, ProductModelIllustration, and Illustration. I can get fields from the ProductModel, ProductModelIllustration, and Illustration tables, but not the Product table. The circle with the diagonal shows up when I try to drag a field from the Product table into the dataset area. What am I doing wrong?

Are you able to query Product table alone?|||

Yes, I can return records from the Product table alone. I created a report to display the product name and number for all products. It returned 504 rows as expected.

In addition, I can generate a dataset with fields from both the Product and ProductModel table. However, when I try to add a field from the ProductModelIllustration table, I am denied (circle with the diaganol line).

|||It is hard to guess without knowing your model.|||

As mentioned above, I duplicated the problem with the ModelTutorial Model (based on the AdventureWorks database). You can duplicate this model by following the "Creating a Report Model" tutorial in SQL Server Books Online (see below link).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/395a4f26-6401-4ee1-8658-be8b43cc70a8.htm

If the link above does not work for you, you can find the tutorial in books online by searching for tutorials [Reporting Services], then click on Reporting Services Tutorials link, then click on the Creating a Report Model link. Once you have the model built and deployed, you should have no problem duplicating my problem.

|||

My understaning is that relations are:

Product -(many to one)- ProductModel -(one to many)-Illustration

This case is not supported automatically.

|||

You are close, but the relations are as follows:

Product -(many to one)- ProductModel -(one to many)- ProductModelIllustration -(many to one)- Illustration

This scenario will come up frequently for report model designers. In fact, it will occur every time there is a junction (aka intersection) table in the model.

If this case is not supported automatically, can you explain in detail the best way to handle it manually (e.g. view in database, new named query in model, etc.)?

Thanks for your help.

|||

Transition many-one-many is not supported. Additional relation (in this case ProductModelllustration -Illustration) is not important.

Easiest way to do it is to add named query.

PROBLEM: Report parameters can not be set. Default values always take priority.

I am trying to create a report with parameters which are prompted by the user and are initially populated with default values. However, whenever I edit the parameters in Report Viewer and click 'View Report', the parameter values revert to their default value. The user-specified parameters never "stick".

Please help!

The xml looks like this:

<ReportParameters>

<ReportParameter Name="ConnectString">

<DataType>String</DataType>

<Prompt>ConnectString</Prompt>

<Hidden>true</Hidden>

</ReportParameter>

<ReportParameter Name="FromDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-1-1</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>From:</Prompt>

</ReportParameter>

<ReportParameter Name="ToDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-12-31</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>To:</Prompt>

</ReportParameter>

</ReportParameters>

One more observation: It works within Report Manager. The problem is when the report is hosted in my application. It seems there might be something in my code that is causing the user-specified parameters to be ignored.

I tried eliminating SetParameters call for the one hidden parameter, and I made sure I was setting ShowParameterPrompts attribute once (in case changing value would reset parameters). The problem still exists.

|||One possible cause is that you are setting the report path or report server url after the LoadViewState event on the postback. If you do this, the report viewer control will see this as a change in the report definition and reload the report from the server, ignoring any input from the postback because it applies to the "old report". Try setting the report path/server url during OnInit, or only when Page.IsPostBack is false.|||

Thank you Brian! That worked!

I verified that either setting ServerReport properties in OnInit(), or after checking Page.IsPostBack is false in Page_Load(), will fix this problem. I also had to call SetParameters within these same constraints.

On to new problems...

Problem with view

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.

Here is the t-sql code I used to create the view:

CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''

Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?When you have UNION the columns in the first SELECT statement define the
datatype of the columns. The columns in the following SELECT statements must
match the datatype or be implicitly convertible. In your case implicit
conversion from varchar to float is allowed. Then the problem is that a
column in your first SELECT statement is of type float and the same column
in the second SELECT is a varchar, but the varchar column has some
characters that cannot be converted to float.

Without seeing the table definitions and just guessing it seems that your
_tblLeads.Zip might be of type float and tblClients.Zip is of type varchar.
Most likely tblClients.Zip has some invalid characters that cannot be
converted to float. One solution is to replace the column
[dbo].[_tblLeads].[Zip] in your first SELECT (if that is the correct column)
with an explicit conversion to varchar, like CAST([dbo].[_tblLeads].[Zip] AS
varchar). To be more precise you can convert to varchar(n) where n is the
exact size of the column.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Hi
What are the field format at each table?
Harry

On Jan 23, 1:34 am, "Chris" <cjscu...@.gmail.comwrote:

Quote:

Originally Posted by

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.
>
Here is the t-sql code I used to create the view:
>
CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''
>
Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?

problem with varbinary(max) in SQL Server 2005

Hi, we have stucked with strange problem. Please read comments in the following script.

CREATE TABLE ItemsTable1(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (max) NULL
)

CREATE TABLE ItemsTable2(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (20) NULL
)

-- insert same values in both tables

INSERT INTO ItemsTable1(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))

INSERT INTO ItemsTable2(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))

--query 1 returns nothing. SQL Server thinks that values are different. Why?
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemNameCode = it2.ItemNameCode

--query 2 returns 'Equals!!!'.
SELECT CASE WHEN it1.ItemNameCode = it2.ItemNameCode THEN 'Equals!!!'
ELSE 'Not equals!!!' END
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemId = it2.ItemId

--query 3 after casting all works fine.
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE CAST(it1.ItemNameCode as varbinary(20))
= CAST(it2.ItemNameCode as varbinary(20))

DROP TABLE ItemsTable1
DROP TABLE ItemsTable2

Note: If we use nvarchar(20) and nvarchar(max) all works fine. So may be this is a bug?
Looks like a bug to me|||This is a bug. And it is not fixed in a recent build of SQL Server 2005 SP1 either. Could you please file a bug for this in the MSDN Product Feedback Center? Thanks.

Problem With Using Stored Procs As I/p To Another Stored Procs

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1

ST.....1,
ST.....2,

END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-

CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC

ST.....1,
ST.....2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.One procedure = One output set = Good Programming.|||Hi,

Seems To Be An Ideal Programming Environment ,but Not Practical
When You Are Dealing With A Huge Database,several Hundred Procs And Many Thousand Computations To Get The Result.

Not Possible To Write A Proc For Each Process That Is Been Done.

Thanks.|||Not Possible To Write A Proc For Each Process That Is Been Done.Yes, it is possible. As a matter of fact, it is the only way I know to survive as the database complexity increases.

I've worked in "spaghetti shops" like you describe, where there are a few thousand routines that do many thousand things. Nothing is ever quite "plug and play" because everything has a side-effect. Finally the people that work there get tired of working 60-80 hours per week, especially when they realize that other folks doing more real work only have to work 45-50 hours per week.

At some point you'll get tired of rigging things with string and bailing wire, and will try a project or two using a more disciplined methodology. One routine does exactly one well defined task. Once you see how much easier that makes your job, there is no going back to the familiar chaos that is your spaghetti code!

-PatP|||Hi,
Thanks Pat,i Really Think I Am Getting The Points Now .and I Think I Have To Bring In More Discipline In My Programming And Towards Its' Approach.
Ok !!! Think,it's Time To Break Down Most Of The Routines Into Simple Steps.
Thanks,to You I Am Realizing That There Is No Point In Making It Happen All Once, It Would Rather Be One At A Time.