How to assign string value to TEXT output parameter of a stored procedure?

Hello,

I am currently trying to assign some string to a TEXT output parameter
of a stored procedure.

The basic structure of the stored procedure looks like this:

-- 8< --
CREATE PROCEDURE owner.StoredProc
(
@.blob_data image,
@.clob_data text OUTPUT
)
AS
INSERT INTO Table (blob_data, clob_data) VALUES \
(@.blob_data, @.clob_data);
GO
-- 8< --

My previous attempts include using the convert function to convert a
string into a TEXT data type:
SET @.clob_data = CONVERT(text, 'This is a test');

Unfortunately, this leads to the following error: "Error 409: The
assignment operator operation cannot take a text data type as an argument."

Is there any alternative available to make an assignment to a TEXT
output parameter?

Regards,
ThiloIs there a reason you can't just do a select on it?

How to Assign Identity Key values in Replication

This is a very basic question on replication.

I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.

Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.

Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-

1. I don't want to use GUID.

2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.

i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.

any help will be highly appreciable.

You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||

thanks for your reply.

i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..

is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.

How to add a Date selector in the report?

Hi All,
How could I add a date selector in the search criteria of datetime field in
the report? It should be a basic function of any report, right?
Thanks a lot.
BillWell, you'd think. It is a highly requested item. If you want to have a
calendar to pick dates you will have to create your own asp application and
then use either web services or URL integration to integrate your
application with RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bill" <huangxiaohua@.hotmail.com> wrote in message
news:edCDYBZuEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> How could I add a date selector in the search criteria of datetime field
> in
> the report? It should be a basic function of any report, right?
> Thanks a lot.
> Bill
>|||Thanks for your reply.
Where could I find any sample for this case?
Bill
"Bruce L-C [MVP]" wrote:
> Well, you'd think. It is a highly requested item. If you want to have a
> calendar to pick dates you will have to create your own asp application and
> then use either web services or URL integration to integrate your
> application with RS.
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bill" <huangxiaohua@.hotmail.com> wrote in message
> news:edCDYBZuEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > How could I add a date selector in the search criteria of datetime field
> > in
> > the report? It should be a basic function of any report, right?
> >
> > Thanks a lot.
> > Bill
> >
> >
>
>|||Read up on URL integration in the BOL and make sure you know how to do that.
Once you know how to do that then you can write your own page to pass the
parameters. But note that you can end up with dealing with security issues
if the page is on another server. I think that if it is on the same server
then integrated security should still work transparently for you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"How to add a Date selector in the reort" <How to add a Date selector in the
reort@.discussions.microsoft.com> wrote in message
news:B3B6F726-B18B-42D8-9E86-F30CAF4FF2E4@.microsoft.com...
> Thanks for your reply.
> Where could I find any sample for this case?
> Bill
>
> "Bruce L-C [MVP]" wrote:
> > Well, you'd think. It is a highly requested item. If you want to have a
> > calendar to pick dates you will have to create your own asp application
and
> > then use either web services or URL integration to integrate your
> > application with RS.
> >
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bill" <huangxiaohua@.hotmail.com> wrote in message
> > news:edCDYBZuEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > How could I add a date selector in the search criteria of datetime
field
> > > in
> > > the report? It should be a basic function of any report, right?
> > >
> > > Thanks a lot.
> > > Bill
> > >
> > >
> >
> >
> >

How to accomplish this?

I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.

I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:

Event A Created
Event A Modified
Event A Property A Created
Event B Created
Event C Created
Event B Property A Modified
Event A Property B Created
Event C Voided
Event A Property A Closed
Event A Closed

In the end I want to create two tables like so:

Table 1:
Event A / Closed
Event B / Modified

(Event C is not here because it was voided, so any prior records of C are gone).

Table 2:
Event A / Property A / Closed
Event A / Property B / Created

(Only events with properties would be in this table).

In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.

Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.

"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).

I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).

I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.

So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?

Thanks for your help.May be you need to move the 'modifies' logic to a separate dataflow after the 'creates' are done. I don't think there is a easy way of enforcing the order of parallel data pipelines in a single dataflow.|||

Hi StGeorge,

I think you need to create some mechanism by which to order properties. I trust you when you say your actual process is more complicated, so I may be headed down the path with this suggestion - but here goes:

It sounds as if you are adding a row to Table 2 on a state named Created. It also sounds as if you are removing this row from Table 2 and its parent row in Table 1 on a similar state named Void.

Would it work if you created an integer column in Table 2 called PropertyOrder? You could populate it with 1 on Created, 3 on Void, and 2 for any other property event. In SSIS, you could then load the records from the source ordered by this field. If your transactional environment is intense (lots of asynchronous transactions), you may be forced to stage the ordered data (in a table or raw file) and then act on this resultset in a subsequent data flow or Execute SQL Task to guarantee transactional integrity (which is how I interpret Table 1).

I may be way off here. If I am, please accept my apologies.

The method I propose above is roughly analogous to a method many deterministic network protocols use to guarantee packets are output in the order sent. The packets are generated synchronously, transmitted asynchronously, received in any order, cached, and reconstructed by ordered key before delivery. I think you have all the pieces except the key by which to order the records.

Hope this helps,
Andy

How to access!

Hi everybody,
I want to know how can I access to SQL Server 7.0 (installed on windows 2000) from Other platforms line Win9X in a visual basic program.

Please tell me complete story,
1) What I have to do on server (windows 2000 - MSSQL 7.0)
2) What I have to do on clients (Windows 9X)
3) Connection string to connect to server from client in VB 6.0 language

Thanks :)Ali,

This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.

Your VB documentation should tell you how to go about getting data from outside datasources.

My short answer: ODBC connections.

blindman|||Originally posted by blindman
Ali,

This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.

Your VB documentation should tell you how to go about getting data from outside datasources.

My short answer: ODBC connections.

blindman

Just a question:
Is there any configuration nedded on Server and Client system?|||Ali,

This is a pretty big topic, and you might be better off talking with VB programmers than Database Gurus.

Your VB documentation should tell you how to go about getting data from outside datasources.

My short answer: ODBC connections.

Unatratnag

1) What I have to do on server (windows 2000 - MSSQL 7.0)
you have to create the database, and if you're doing odbc, then you'll need to create the odbc on the pc where the application resides

2) What I have to do on clients (Windows 9X)
write the code to connect to the database =P

3) Connection string to connect to server from client in VB 6.0 language
well if you have access to the database files create a .udl file and then double click it and it will allow you to create a connection string through the gui, open it back up in notepad and the connection string is the third line.|||People talking about ODBC are running behind for two Microsoft Data Access generations. After ODBC (related to DAO = Data Access Objects) came ADO (= ActiveX Data Objects) with its collection of OLE DB providers, needing as you asked connectionstrings. Currently, we are living in the ADO.NET age, however, you are working with VB 6, which means you would have to choose ADO.

You don't need any extra installations on your server.

On your Win9x client, you need to start with DCOM98. Furthermore, you need MDAC, preferably the latest version 2.7 SP1a. You may browse the www.keper.com (ftp://dbxprof:dbxprof@.FTP server of my DB Explorer for the installations, and you find some hints in the installation manual.

For a discussion of the connection string in trusted environments, or with DB security see ABLE Consulting (http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer). However, you may also want to build a connection string. Herefore, Microsoft utilizes Data Link Property forms, which can be accessed within your application.|||The FTP server link doesn't work. It is DB Explorer (ftp://dbxprof:dbxprof@.213.84.56.3/)|||setup an ADODB connection, use SQLOLEDB as the connection provider.|||I don't think there are Gurus here, just a bunch of people with no life :)

amirnezhad,

The topic may be big, but not the task. What is it you're trying to do? If you want to learn and looking for the way to start, then follow DoktorBlue's links. But if you were given a task to do a specific thing, then post it and someone may give you the whole answer.

P.S.: Since you're from Tehran, how are things looking from there?|||rocheyc: can't figure out the added vaue of your contribution, please help me.

rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)|||Originally posted by DoktorBlue
rocheyc: can't figure out the added vaue of your contribution, please help me.

Funny, when I read your post I felt the same way about you!

Originally posted by DoktorBlue
rdjabarov: sounds like you are proposing a new exciting thread about guru properties. Is it really the number of contributions? Or the lack of social life? Or just the nick name? :confused: Maybe the real gurus know ?! ;)

Yes, DoktorBlue the real qurus know.|||Thanks, Paul, you let me see that real gurus don't contribute to the topic, right?

I'd prefer to move further discussions in a new thread, or to keep it private.|||I see the p***ing contest is starting again. Well, I'll be here in the front row watching. Is it time to place bets yet?