Losing double quotes?

Hi,

I have written a little bit of VB.NET code that basically takes three strings, transforms them, and returns a single string to be stored in my table.

I am running into a strange problem, however... for some reason, a number of my processed rows are missing a pair of double quotes (").

The vast majority of the records are formatted properly, and have the double quotes in the expected locations.

The most frustrating thing about it is that I have included the offensive input strings in my Test.sql test script, and when I step through the entire routine, the return value is perfect...

i apologize for being the worst ever at posting questions here, please let me know if i can add anything

Could you please post your code, its hard to uess what is goind wron without seeing your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

it is over 500 lines, but i could certainly post it once i get to work...

in the meantime, however, perhaps this might be useful:

I set a breakpoint at my function's final return statement and have observed the return value as follows:

Author of "Some random book." Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

This is how I'd like, and expect, the value to appear in my database, but after SELECTING the particular row, I get:

Author of Some random book. Thoughts and frustrations. Publisher: Me (San Diego). Published in 2007.

I doubt this is much more revealing, and will be happy to post my code later, if thought to be any help.

|||

Another thing to add (probably useless):

The problem only occurs during an UPDATE. If I do:

SELECT dbo.EventBuilder([Title], [Author], [Published]) FROM MyTable

I get the correct output.

...getting desperate here

|||

Check your database hold the same result.

I can't able to understand where & what is your problem occurs..

Looping through stored procedure inside another stored procedure and displaying the catego

I used to do this with classic asp but I'm not sure how to do it with .net.

Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category.

I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.

Just for an example..

Category Table:

ID Category
1 Saints
2 Falcons
3 Bucaneers
4 Chargers
5 Falcons

Player Table:

ID CategoryID Player News Player Last Updated
1 1 Reggie Bush Poetry in motion 9/21/2006
2 1 Drew Brees What shoulder injury? 9/18/2006
3 5 Michael Vick Break a leg, seriously. 9/20/2006

Basically I would need to display on a page:

Saints
Reggie Bush
Poetry in Motion

Falcons
Michael Vick
Break a leg, seriously.

So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.

I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.

try building a query with sub-queries based on a join within the store procedure. test it in the query manager first then before making it a SP

Loop within a stored procedure

Hi,

Can I have a FOR loop within a stored procedure. Basically I want 2 loop through each user and send them an automatic email using Server Agent.

thanksThere's no FOR loop, but there is a WHILE loop. Look in BOL for full documentation on its use (I don't think I've ever actually used it, except in one or two rare cases where I broke down and used a cursor. Ah, foolish youth!)|||If you want to loop though a dataset, use a cursor:

declare NextRow cursor local for
select EmailAddress
from Table
where ...

open NextRow

and then you can get the rows one by one using fetch:

fetch next from NextRow into @.EmailAddress

Remember to check @.@.fetch_status to see if you're done.

if @.@.fetch_status = -1 -- you're done!
if @.@.fetch_status = -2 -- This row has been deleted.

Does this help?

Lookups and their error flows

So I have three lookups in a row in my data flow. Basically they are doing data quality checks for me using a reference table.

I want to be able to take the error flows of the three lookups and merge them together (union all) so that I can insert the "errors" (or non matches) into a table.

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

What I would like to see is a lookup act more like a conditional statment where you have three outputs of a lookup table: match found, no match found, and error. Either that, or I'd like to be able to edit the names of the errorCode and errorColumn fields.

Am I missing something here, or do I need to just add an OLE destination for each lookup error flow when I only want one? 'Course the problem then is that I want to count the number of rows that are in "error" across all of the lookups.

Phil Brammer wrote:

Can't do it. Because SSIS deems non-matches as "errors" you automatically get the errorCode and errorColumn fields. When you try to union a lookup error output with another lookup's error output, you can't do it.

Phil,

What do you mean with 'Can't do it'. Are you actually receiving an error?

I was able to set that up; two error outputs from lookup transformations going to an Union All; it seems to work; but that was with a simple test scenario I made. Yes, ErrorColumn and ErrorCode were there as well but is up to you to use them or remove them from the pipeline. What is actually no there is the lookup value but I would not expect to be since the lookup fail.

Another way to accomplish something like this is to configure lookups to 'ignore errors' and then at the end to use a conditional split to send the rows having nulls on the lookup values column to the error branch of the dataflow. Just a thought

Rafael Salas

|||User error... You made me think about it again, and when I looked at what I was trying to do, I realized my mistake. Never-the-less, what I was doing was taking the error "redirect" from the lookup and adding a new column with a derived column transformation. This was repeated on the other lookup error flows. Then my mistake (an accident!) was taking the *error* output of the derived column transformations instead of the data flow output and pushing them to the union all transformation.|||

oh, I see!. BTW, in my previous post I meant ignore (instead redirect) error as an alternative way. I corrected it though.

Rafael salas

sql

lookup tables

Hello,
I was wondering what the best way is to use lookup tables. i don't want to
use a new table for every lookup.
Basically what i want is a generic lookup table for all kinds of data. In
queries, to translate the integer value of the lookup, it should know which
description it should use in the lookup table.
Anyone knows a solution?This is the wrong approach. There are no "lookups" in a relational
database. A table models a single Entity - (all things of a similar
kind). Foreign keys are the mechanism used to guarantee referential
integrity between tables but your "universal lookup table" (all things
of any kind) destorys referential integrity because it makes these
integrity constraints impossible, or at least very difficult, to
implement.
The "lookup table" is a common error that comes from an
application-centric rather than data-centric approach to database
design. A wiser approach is to model the data first, then focus on
application presentation. One day other applications may want to access
the same data so it pays to have a correct and consistent data model.
In a normalized relational design a "lookup table" just wouldn't exist.
David Portas
SQL Server MVP
--|||HI David,
Thanks for the explaination. But what if the data in the "lookup" tables has
no relation. For example i have relational table where insert records about
pharmaceutical products. One of the fields contains a category value which i
"lookup" in the table lookupvalues.
Is this wrong?
Do you have an example how it should be?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111392935.643692.162300@.z14g2000cwz.googlegroups.com...
> This is the wrong approach. There are no "lookups" in a relational
> database. A table models a single Entity - (all things of a similar
> kind). Foreign keys are the mechanism used to guarantee referential
> integrity between tables but your "universal lookup table" (all things
> of any kind) destorys referential integrity because it makes these
> integrity constraints impossible, or at least very difficult, to
> implement.
> The "lookup table" is a common error that comes from an
> application-centric rather than data-centric approach to database
> design. A wiser approach is to model the data first, then focus on
> application presentation. One day other applications may want to access
> the same data so it pays to have a correct and consistent data model.
> In a normalized relational design a "lookup table" just wouldn't exist.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not sure what you mean by "no relation". "Lookup" is normally used
by programmers to refer to the parent table in a relationship. I assume
product categories might look like the example below. Note the foreign
key constraint and other keys.
Of course there is no requirement for every table to contain a foreign
key or be referenced by a foreign key. There are other types of
semantic relationships that aren't represented by constraints. However,
a table that was totally unrelated to another in the database by ANY
means probably wouldn't be particularly useful in queries and I don't
think anyone would call such a table a "lookup".
CREATE TABLE Product_Categories (product_category_code INTEGER PRIMARY
KEY, product_category_desc VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_desc
VARCHAR(50) NOT NULL UNIQUE, product_category_code INTEGER NOT NULL
REFERENCES Product_Categories (product_category_code) /* ... */)
David Portas
SQL Server MVP
--|||Hi David,
I understand your example, but in that way in need to create such a table
for every different category i have for different products. Below you can
find my example what i'm doing, please tell me if this the right way in
terms of good datamodelling. It's purpose is to define categories for
different types of data.
CREATE TABLE [Listcategories] (
[lcat_Id] [int] IDENTITY (1, 1) NOT NULL ,
[lcat_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Listcategories] PRIMARY KEY CLUSTERED
(
[lcat_Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Lists] (
[list_Id] [int] IDENTITY (1, 1) NOT NULL ,
[lcat_Id] [int] NOT NULL ,
[list_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Lists] PRIMARY KEY CLUSTERED
(
[list_Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Lists_Listcategories] FOREIGN KEY
(
[lcat_Id]
) REFERENCES [Listcategories] (
[lcat_Id]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Listoptions] (
[lopt_Id] [int] IDENTITY (1, 1) NOT NULL ,
[list_Id] [int] NOT NULL ,
[lopt_Value] [int] NOT NULL ,
[lopt_Label] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Listoptions] PRIMARY KEY CLUSTERED
(
[lopt_Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Listoptions] UNIQUE NONCLUSTERED
(
[list_Id],
[lopt_Value]
) ON [PRIMARY] ,
CONSTRAINT [FK_Listoptions_Lists] FOREIGN KEY
(
[list_Id]
) REFERENCES [Lists] (
[list_Id]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
Thnx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111400843.222937.306360@.g14g2000cwa.googlegroups.com...
> I'm not sure what you mean by "no relation". "Lookup" is normally used
> by programmers to refer to the parent table in a relationship. I assume
> product categories might look like the example below. Note the foreign
> key constraint and other keys.
> Of course there is no requirement for every table to contain a foreign
> key or be referenced by a foreign key. There are other types of
> semantic relationships that aren't represented by constraints. However,
> a table that was totally unrelated to another in the database by ANY
> means probably wouldn't be particularly useful in queries and I don't
> think anyone would call such a table a "lookup".
> CREATE TABLE Product_Categories (product_category_code INTEGER PRIMARY
> KEY, product_category_desc VARCHAR(50) NOT NULL UNIQUE)
> CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_desc
> VARCHAR(50) NOT NULL UNIQUE, product_category_code INTEGER NOT NULL
> REFERENCES Product_Categories (product_category_code) /* ... */)
> --
> David Portas
> SQL Server MVP
> --
>|||Going by the names alone, this doesn't look like a business data model.
It looks like a content management system implemented in SQL. Are you
trying to model pharmaceutical products or data entry screens? Is a
"List" really a businesss entity or just a menu on a combo box? If you
modeled the real meaningful data instead of the application metadata
then I don't think you would need stuff like "Options" and "Labels" in
the database at all - that data would appear as attributes in other
tables.
These questions are relevant because to build a Normalized data model
requires an understanding of the real-world scenario you are trying to
model and the relationships within it. Without that understanding I can
only give general advice. The most obvious problem is that all of your
tables have an IDENTITY key but no alternate keys. IDENTITY should
never be the only key of a table.
David Portas
SQL Server MVP
--|||Hi david,
It's for data entry screens. Is it done like what i did or should it be
approach differently.
All other tables are normalized.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111405645.646381.264350@.f14g2000cwb.googlegroups.com...
> Going by the names alone, this doesn't look like a business data model.
> It looks like a content management system implemented in SQL. Are you
> trying to model pharmaceutical products or data entry screens? Is a
> "List" really a businesss entity or just a menu on a combo box? If you
> modeled the real meaningful data instead of the application metadata
> then I don't think you would need stuff like "Options" and "Labels" in
> the database at all - that data would appear as attributes in other
> tables.
> These questions are relevant because to build a Normalized data model
> requires an understanding of the real-world scenario you are trying to
> model and the relationships within it. Without that understanding I can
> only give general advice. The most obvious problem is that all of your
> tables have an IDENTITY key but no alternate keys. IDENTITY should
> never be the only key of a table.
> --
> David Portas
> SQL Server MVP
> --
>|||On Mon, 21 Mar 2005 13:43:07 +0100, Jason wrote:

>It's for data entry screens. Is it done like what i did or should it be
>approach differently.
Hi Jaso,
No, it's not done like that.
You don't create tables for entry screens. In fact, you should reverse
the entire process: design tables first (starting from the business
requirements and normalizing to at least third normal form). Then, you
design data entry screens for these tables.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
Another question, when do i save the id or the value as text in a table?
Example, in a table of orders you can specify the employer which created the
order. In that case do you store the employeeid or the name of the employee.
Is there any rule when you need to store text instead of a int value?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:c1qu31l8aoab86upce73e95dajo0jg63oa@.
4ax.com...
> On Mon, 21 Mar 2005 13:43:07 +0100, Jason wrote:
>
> Hi Jaso,
> No, it's not done like that.
> You don't create tables for entry screens. In fact, you should reverse
> the entire process: design tables first (starting from the business
> requirements and normalizing to at least third normal form). Then, you
> design data entry screens for these tables.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 22 Mar 2005 21:01:35 +0100, Jason wrote:

>Hi Hugo,
>Another question, when do i save the id or the value as text in a table?
>Example, in a table of orders you can specify the employer which created th
e
>order. In that case do you store the employeeid or the name of the employee
.
>Is there any rule when you need to store text instead of a int value?
Hi Jason,
That's another deciusion that should be made in the design phase, as
part of the data model.
You'll first have to identify the appropriate key for each entity. For
employees, SSN is often used (but it can only be used if you're sure
that the SSN will be known for each employee), or the company might have
it's own EmployeeNumber or EmployeeCode. The number or code might be
numerical, but it needs not - I've also seen schems that use things such
as initials and a sequence number. For employees, the name is not a good
choice for primary key, as you'd run into problems if a second person
with the same name is employed.
When you start to create the tables to support the logical data model,
you can consider using a surrogate key. You should only do that if the
natural key (as identified in the logical data model) spans too many
columnsor is too long. For the various possible keys for employees, this
doesn't apply, so you don't need a surrogate key and you can simply
declare a PRIMARY KEY constraint on the column that holds the natural
key.
But suppose that projects are only identified in the company by the
combination of project titel (max 250 characters) and starting year,
you'd have a good reason to introduce a surrogate key ProjectId, make
that the primary key for the table and create a UNIQUE constraint to
ensure uniqueness of the columns that make up the natural key. The
surrogate key is often (but not necessarily always) an integer.
Now back to your question: how to store the data. If you add a new
employee who's just been hired, you'd have to store all data in the
appropriate format: name as character data, DOB as datetime, salary as
numeric(8,2) and so on.
If you only need to create a link to an existing employee (e.g. because
an employee has been appointed to work on a project), you use the
datatype corresponding to the primary key of the Employees table. If the
primary key is on SSN and SSN is declared CHAR(9), you'd use CHAR(9). If
the primary key is on EmpNumber and EmpNumber is declared smallint,
you'd use smallint. Etc.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Looking for help with sqldependency

Hello all,

I've been digging through this forum for an hour or two, and not found anyone having the same problem we are having.

Basically, we have a Windows service that uses sqldependency to get a notification on the existence of records to be processed. When the service receives the notification, it spawns multiple threads which each work on one of the records in the table, moving the records to a "completed" table. When the table is empty, the service sets up the sqldependency again and waits to do the whole thing over.

Our problem is, occasionally it stops being triggered. We don't know if the service stops listening, or the notification stops being sent, or if there is some other situation where the network connection between the db and the service is severed and the pieces can't resume their conversation.

In order to "fix" the problem, we restart the service. It does it's normal run of cleaning out the table, then sets the notification up again and works for another few hours before it flakes out again.

At the moment, we can't figure out how to determine whether it is the app, or the database, or network (or some combination of any of these parts) that is failing. If I could get some guidance as to how to start narrowing it down, I would be very grateful!

Let me know if you need more specific info, and thanks for any help you guys can offer!

Mike

Rather that answer with technical details about SqlDependency inner workings (with questionable success since I myself don't know how the client CLR part works exactly), I'll tell you something else: you're using the wrong tool for the job. The pattern you describe fits perfectly a workflow based on Service Broker queues. Rather than insert into a table and have SqlDependency react to the changes, send messages to a Broker service and have the application waitfor messages in a queue.|||

Thanks for a quick response Remus!

Here are some specifics about our system. I'm trying to figure out if your solution will fix our problem.

The system is designed to have web services, or in the case of applications more closely connected to the database, stored procedures that populate a messagequeue. It is absolutely necessary that any message that gets into this queue is logged, and processed. These messages could define: an email that goes to a customer with an attached file, an emailed error log from a website, the description of an ftp to be sent to an outside server, or even the description of a fax that will ultimately be packaged and sent to an offsite fax delivery service. The source of these messages vary from internal systems, to website "contact us" pages, to external Windows apps consuming one of the queuing web services.

The Windows service does the heavy lifting, determining that this an email, this is a fax, this is an FTP, etc... then using the appropriate method to send the message. Of course, along the way it is popping messages off the queue, and logging success or failure to a completed message log. (This is where we are failing... the service stops being notified, or stops listening to the changes to the message queue.)

The final piece of this is a management app that allows a customer service rep to see in realtime the condition of the queue with statistics like: how many emails were sent? did any of them fail? how many items are sitting in the queue? It also allows messages to be paused or requeued for any reason.

I think I understand what you are saying... put the messages directly in a Broker Service queue instead of a table. But does this insure that every message will get sent, even in the case of failure of the hardware hosting the broker service, and can (and should) the Broke Service queue be queried by multiple paranoid customer service reps using a client application? (The transparency of the queue is one of the major design goals of the system, as the state of the last delivery system was completely closed off from the average business side user.)

Let me know what you think, and thanks again for your response!

Mike

|||

I'd say this fits Service Broker like a glove.

Instead of inserting into a table, send messages to a queue. Queues are queriable (with SELECT ... FROM [<queuename>]) so you can always monitor the state of messages. Queues are normal database objects, so they benefit from all the availability/reliability of a database: they are backuped/restored with the database, they failover with clustering, they failover with mirroring. All queue operations are transacted, so they follow the normal pattern of COMMIT/ROLLBACK. For the kind of jobs you describe Service Broker services/queues have many advantages:

- dedicated queues for each type of application (fax, email, ftp). You can send the message to the appropiate queue and have each queue serviced by a dedicated application (service). This way you will not block fast operations (email) behind slower ones (fax) and you can govern the resources allocated for each type of operation (e.g. more queue readers to service the fax vs. ftp)

- reliable retry logic. This is a huge freebie you get. You can use the conversation timers to post a message to yourself for a retry, like this:

BEGIN TRANSACTION

RECEIVE MESSAGE

BEGIN TIMER

COMMIT

DO lengthy processing with possible failure (i.e. fax)

IF SUCCESS

RESET TIMER

If the timer ever pops, you simply follow the same logic again (set a new timer, retry the operation). No need for fragile application based retry timers: conversation timers are durable, so they will pop and send you a message to retry even after a server restart or a clustering/mirroring failover. Rushi Desai has a sample showing how to do this for a processing that is a Web service call, you can easily follow the same pattern for any other kind of processing (fax, ftp etc), see http://blogs.msdn.com/rushidesai/archive/2006/04/19/746827.aspx

- easy scale out. Service Broker applications are agnostic to the location of the queue, so you can easily scale out specific services (e.g. fax) to a separate machine. In addition Service Broker comes with built in load-balancing logic so you can scale out to a farm of dedicated server w/o any change in the application

- messages are queriable, you can SELECT across application queues and as well acros the system outbound queue (sys.transmission_queue)

- you can easily add intermediate worflow steps, like for fan out: send one message to one service, it gets fanned out to manny services to copy the same message to your ftp/email/fax based on whatever business rules you have. The logic can be implemented as an activated procedure, no need for an external process to service this part.

- optional built-in XML schema validation for you messages, so the application is guaranteed to receive only conforming XML payloads

- message correlation lock-out. If you have correlated messages (e.g. you must first send this email, and only then that email) Service Broker dequeue semantics can lock-out other concurent readers from even seeing that message, see http://msdn2.microsoft.com/en-us/library/ms171615.aspx

HTH,

~ Remus