Creating an IDENTITY column in a view

Is it possible to create an IDENTITY column in a view? I would like to have a column which will always start at 1 and increment 1 for every record in the view.
Thanks!
CSThere might be another way to get what you want. It depends on your data. For example if you have a table that has unique rows you could write something like this:

SELECT
COUNT(*) AS ID,
A.Activity_Type_Ky
FROM
Activity_Type AS A
JOIN Activity_Type AS B
ON A.Activity_Type_Ky > B.Activity_Type_Ky
GROUP BY
A.Activity_Type_Ky

You could also use a function or stored procedure with a temporary table to get what you want if you are not limited to a view.|||I suggest you use a stored procedure to:

1. create a temporary table that includes the identity column
2. insert all the records of your view into the temporary table using single T-SQL statement
3. select * from the temprary table

in sql server 2000 you don't need to drop the temp table

creating a view

I'm trying to create a view to check another view for a record that matches to the value in a textbox of a form. It is giving a syntax error.
This is how my SQL looks like

SELECT aql_level, aql_level_info
FROM v_tblPart_cost_info
WHERE cust_part_num = forms!edit_shiping_sched!cust_part_num

Thanks!!What was the error?
Check books online for correct syntax=.

Creating a Trigger that emails when new record is added.

I'm hoping one of you will be able to help me because I haven't had very good luck finding any information on this.

I'm kind of new to SQL, but I'm learning as much as I can.

I created a web form that sends a new record to a SQL 2005 table I setup.

This all works exactly as it should, but I would like to have an email sent out every time a record is added to this table.

I have SQL Mail setup and I ran a test and it worked, but I can't seem to find any info on how to create a trigger that will send an email to me when a new record is added to the table.

My Database is called Engineering

The table is called ESSPartNumLog

And I have the following Columns that I would like to send in my email..

ESSSequence (PK,int, not null)

MaterialType (nvarchar(255, null)

ESSPrefix (nvarchar(255, null)

PartDescription (nvarchar(255, null)

Project (nvarchar(255, null)

PM (nvarchar(255, null)

Any ideas, or can you point me in the right direction?

Hello,

Try this:

create trigger tr_ESSPartNumLog_Insert on dbo.ESSPartNumLog

for insert

as

declare @.id as integer

declare @.q as varchar(255)

--Get ID of record inserted.

select @.id = ESSSequence from inserted

--Create query string to return the record

select @.q = 'select ESSSequence, MaterialType, ESSPrefix, PartDescription, Project, PM

from ESSPartNumLog

where ESSSequence = ' + convert(varchar(10), @.id)

exec msdb.dbo.sp_send_dbmail

@.recipients = 'your_email@.domain.com',

@.subject = 'New record added in ESSPartNumLog',

@.query = @.q,

@.execute_query_database = 'Engineering'

go

Hope this helps.

Jarret

creating a text file from the contents of the database?

please help!!

i'm working on a project right now using Oracle Forms 6.0 and Oracle9i.

after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and upload the data in the text file to another database (also Oracle).

i will also need to create the text file for multiple records.

can someone help me please?? i read something about SELECT INTO OUTFILE... how exactly does this work?Hello,

use UTL_FILE package to spool records into a file via PL/SQL.
In AlligatorSQL you can use a template "How to spool a ...".

See at http://www.alligatorsql.com/download/alligator116.zip

But if you wish I can post an example again (it has been already posted in this forum)

Hope that helps ?

Manfred Peter
(Alligator Compay Software GmbH)
http://www.alligatorsql.com|||oh i see! thanks, i found the thread on extracting. will post again if i have any problems!|||sir manfred,

would it be possible to use TEXT_IO instead of UTL_FILE? Oracle Forms does not have the UTL_FILE package. i had a bit of difficulty following your examples (sorry!) as i am just a beginner with pl/sql.

this is what i have to do:
- save the information that was entered in Oracle Forms (this is finished)
- when a button is pressed, update the REQUEST_SENT flag and create the text file (of that same form which was just saved)

This is what i have done so far:

/*WHEN-BUTTON-PRESSED trigger*/

DECLARE

CURSOR cuProcess IS
SELECT *
FROM SIR
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO and SIR_COMPANY = :SIR.SIR_COMPANY;

rProcess cuProcess%ROWTYPE;
cOut VARCHAR2(2000);

N_FILE VARCHAR2(2000);

BEGIN

UPDATE SIR
SET SIR_REQUEST_SENT = 'Y'
WHERE SIR_TRANS_NO = :SIR.SIR_TRANS_NO AND SIR_COMPANY = :SIR.SIR_COMPANY;
COMMIT;

OPEN cuProcess;
FETCH cuProcess INTO rProcess;

WHILE cuProcess%FOUND LOOP
FETCH cuProcess INTO rProcess;

cOut := rProcess.SIR_TRANS_NO || ';'
|| rProcess.SIR_COMPANY || ';'
|| rProcess.SIR_PROJECT || ';'
|| rProcess.SIR_APPL || ';'
|| rProcess.SIR_BUS_FUN || ';'
|| rProcess.SIR_REPORTED_BY || ';'
|| rProcess.SIR_HANDLED_BY || ';'
|| rProcess.SIR_PHASE || ';'
|| rProcess.SIR_TYPE || ';'
|| rProcess.SIR_CAUSE || ';'
|| rProcess.SIR_CLASSIFICATION || ';'
|| rProcess.SIR_DESCRIPTION || ';'
|| rProcess.SIR_REASON || ';'
|| rProcess.SIR_REMARKS || ';'
|| rProcess.SIR_STATUS || ';'
|| rProcess.SIR_REQUEST_DATE || ';'
|| rProcess.SIR_RECEIVED_DATE || ';'
|| rProcess.SIR_START_DATE || ';'
|| rProcess.SIR_CLOSE_DATE || ';'
|| rProcess.SIR_TARGET_DATE || ';'
|| rProcess.SIR_ESTIMATED_MANHRS || ';'
|| rProcess.SIR_ACTUAL_MANHRS || ';'
|| rProcess.SIR_BILLABLE_MANHRS || ';'
||rProcess.SIR_ATTACHMENT || ';'
|| rProcess.SIR_REQUEST_SENT;
END LOOP BeginLoop;

CLOSE cuProcess;

CREATE_TEXT('filename', cOut);

EXCEPTION
WHEN OTHERS THEN
IF cuProcess%ISOPEN THEN
CLOSE cuProcess;
END IF;

END;

then i have a simple procedure that creates the text file:

PROCEDURE CREATE_TEXT (pfilename IN VARCHAR2, selected IN VARCHAR2) IS

N_FILE text_io.file_type;

BEGIN
N_FILE := TEXT_IO.FOPEN(pfilename||'.TXT', 'W');
TEXT_IO.PUT_LINE(N_FILE, selected);
TEXT_IO.FCLOSE(N_FILE);

END;

my problem is that i have to press the button twice for the update to happen. is there another way that i can first update SIR_REQUEST_SENT and then use a cursor to SELECT * ?
also, after the text file is created, how can i load it using sqlloader?

also, how can i specify the path where the text file will be saved? the TEXT_IO.FOPEN accepts only 2 parameters, the filename and the mode unlike UTL_FILE.FOPEN

i appreciate the help! thanks again!|||Hello,

sorry, but I am not so familiar with Oracle forms. But I know, that you can call PL/SQL routines from Forms.

Sorry again.

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Creating a Select statement with subqueries to 3 other tables...

I have four total tables.

Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields
related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group,
the second field (Stage_ID) is the ID of the record it is associated to
in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields
that is related to this issue. The first field (Stage_ID) is the ID of
the stage of an event, the second record is the ID number associated to
the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it
to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE
(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID
= A.Activity_ID))))))= '20000024')Wenin wrote:
> I have four total tables.
>
> Table One (Documents)- List of Documents. Each record has two fields
> related to this issue. First field (Document_ID) is the ID of the
> document, second field is the ID of the record (Task_ID) it is
> associated to in Table Two.
> Table Two (Activities)- List of activities. Each record has two
fields
> related to this issue. First field (Activity_ID) is the ID of the
> activity, the second field (Group_ID) is the ID of the record it is
> associated to in Table Three.
> Table Three (Groups) - List of groups. Each record has two fields
> related to this issue. First field (Group_ID) is the ID of the
group,
> the second field (Stage_ID) is the ID of the record it is associated
to
> in Table four.
> Table Four (Stages)- List of Event Stages. Each record has two
fields
> that is related to this issue. The first field (Stage_ID) is the ID
of
> the stage of an event, the second record is the ID number associated
to
> the event. This last ID is a known value.
> 20000024 = the Event ID
> I'm trying to come up with a list of Documents from the first table
> that is associated to an Event in the Fourth table.
> Query Analyzer shows no errors within the script. It just doesn't
> return any data. I know that it should, if it does what I'm wanting
it
> to do.
>
> SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
> Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C
WHERE
> (C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE
(B.Activity_ID
> = A.Activity_ID))))))= '20000024')

I'm thinking this is going to require the use of Joins, but I still
can't seem to wrap my head around how joins work exactly.|||First, to save Joe Celko from having to post...

Rows are not records, columns are not fields. You are thinking in the
old sequential file processing mentality. It will take you a year to
unlearn this and get your mind accustomed to thinking in a set-oriented
fashion.

Ok, now that that's out of the way...

You are correct, it will require the use of joins. Please don't take
this as an insult, as I'm sure that you are still learning SQL and
there was a time for all of us when we didn't know it, but this is a
pretty simple set of joins. You really do need to "get your head around
how joins work exactly" if you are going to be doing any SQL coding.
There are plenty of good books for SQL beginners out there. Even the
SQL for Dummies book should get you past this hurdle.

A join takes each table being joined, creates a cartesian product of
the two (i.e., every possible combination of records) then filters that
result based on the ON part of the join. As an example, if I have two
tables:

Customers
cust_id
1
2

Orders
ord_id cust_id
1 1
2 2
3 1

The cartesian product would be:
cust_id ord_id cust_id
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2
2 3 1

If I joined these tables on cust_id = cust_id then it would give me:
cust_id ord_id cust_id
1 1 1
1 3 1
2 2 2

Without the join criteria the cartesian product is useless, but it is
the starting point.

Another way to think of it is this... I know my two tables are related
and I know the column that relates them. Therefore I can join on that
column. That is a VERY simplistic approach to joins, but it will
hopefully point you in the right direction.

Good luck,
-Tom.

creating a insert trigger

i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.

The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.

My Two tables DDL are like down below

create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);

create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);

thanks for your help in advance!
gazawaymyYou don't need a trigger for this. In the proc that populates your student, if they have the insCheck = 'y' then execute the proc to populate the instructor with the appropriate parameters.

Also, why are you populating the studentId and instructorId manually? Can you explain this process? It sounds like a design issue. Is there a formula to determine this so you can automate it?|||I didn't know I can do it that way with procedure. I still have to figure out the actual procedure code. Do you have the code in mine?

I made student, instructor table's primary key to be manual because of relationshion between contract and these tables. Since i already populate a lot of records and these are the primary keys, I am not sure I should change the table structure. What do you think?

If I should change to automatic, how I do that?

thanks
michelle|||Why are you storing Students and Instructors in separate tables? Store them in a single table ("People", for instance).|||because

student and instructor table are connected with class table.|||That's not a reason to separate the two:

Table PEOPLE (PersonID, PersonName, InstructorFlag)

Table CLASSES(ClassID, InstructorPersonID, ClassName)

Table CLASSMEMBERS(ClassID, PersonID)

Strictly speaking, the instructor flag is not necessary but makes interface coding easier.|||You're doing this crap on a Saturday?

I thought I had an addiction...ok I do...

And yes the flag is bs...the relationship identifies if the person is or isn't an instructor...

what they can't be both?

Creating a fork in the road

Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?

Question the second: After I aggregate my records (down one of the paths), I need to store some columns as xml. Is there a tool for this?

Thanks for all your help!

Jim Work

Jim Work wrote:

Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?

Use the multicast transformation.|||For "forking" the data like that, you can use the Multicast component in the data flow tab.|||Oh, wow! Thanks a bunch!

Jim Work

Creating a copy of a record

What is the simplest way to create a copy of a row?
The table in question has a primary key that is an identity field, which
will obviously need to have a different value, but otherwise I want to be
able to create a row that is identical to another one.
AFAIK Select Into only copies rows into a new table.
I could obviously retrieve each field from the original row (bar the primary
key) and INSERT a new record with this information, but since there are a
fair few columns, I wondered if there might be a simpler one-line SQL
instruction that will do the job...
Thanks in advance
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Think again. Why would you ever want to duplicate a row in a table?
(even with a different IDENTITY key).
In principle:
INSERT INTO YourTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE /* some row */ ;
However, this ought to fail on a key violation because IDENTITY should
never be the only key of a table. Rethink your requirement and your
table design.
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
pk INT NOT NULL PRIMARY KEY,
col CHAR(1),
col1 INT
)
INSERT INTO #Test VALUES (1,'a',20)
--Copy Row
INSERT INTO #Test
SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
col,col1 FROM #Test WHERE pk=1 --change to variable
SELECT * FROM #test
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23cT1aMdvFHA.3688@.tk2msftngp13.phx.gbl...
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the
> primary key) and INSERT a new record with this information, but since
> there are a fair few columns, I wondered if there might be a simpler
> one-line SQL instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127214710.180280.324430@.g43g2000cwa.googlegroups.com...
> Think again. Why would you ever want to duplicate a row in a table?
> (even with a different IDENTITY key).
> In principle:
> INSERT INTO YourTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE /* some row */ ;
> However, this ought to fail on a key violation because IDENTITY should
> never be the only key of a table. Rethink your requirement and your
> table design.
>
I can see where you are coming from, but I'm afraid my requirement is
genuine & valid.
The table in question hold orderlines. In this example an orderline is being
closed off, but a new copy of the line is to be added to the order.
Therefore, I hoped to copy original orderline (with a different PK
obviously) and then Update the original to close it off. [Obviously this is
a simplistic explanation).
As you can see, in this case the table design is fine. And the requirement,
IS the requirement. But how we satisfy the requirement is the question...
and hence my post.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> CREATE TABLE #Test
> (
> pk INT NOT NULL PRIMARY KEY,
> col CHAR(1),
> col1 INT
> )
> INSERT INTO #Test VALUES (1,'a',20)
> --Copy Row
> INSERT INTO #Test
> SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
> col,col1 FROM #Test WHERE pk=1 --change to variable
> SELECT * FROM #test
Uri,
Thanks for this.
Q. Will this still work if my PK is an Identity field?
Chris|||> As you can see, in this case the table design is fine.
I don't see that. There are serious problems with tables that have only
an IDENTITY key. The consequences of storing duplicate data make it
very difficult (perhaps impossible in some conditions) to validate and
integrate data without logical keys. Also, there are practical
programming issues in TSQL because of the way IDENTITY values are
assigned in multiple row INSERTs.
Putting that aside, why store redundant data? If the row is going to be
modified anyway as you have said then why not insert the modified row
instead of duplicating an existing one and incurring the overhead of an
extra update? Seems like you are paying a high price in integrity and
performance for no discernable benefit.
David Portas
SQL Server MVP
--|||CJM
I think David has
already answered this question , however you are free to try it.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:u4WSrhdvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Uri,
> Thanks for this.
> Q. Will this still work if my PK is an Identity field?
> Chris
>|||> I can see where you are coming from, but I'm afraid my requirement is
> genuine & valid.
> The table in question hold orderlines. In this example an orderline is
> being closed off, but a new copy of the line is to be added to the order.
WHY? What is different about the row? Why does it need to be deleted and
re-inserted?
A|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127217457.000463.24290@.g44g2000cwa.googlegroups.com...
> I don't see that. There are serious problems with tables that have only
> an IDENTITY key. The consequences of storing duplicate data make it
> very difficult (perhaps impossible in some conditions) to validate and
> integrate data without logical keys. Also, there are practical
> programming issues in TSQL because of the way IDENTITY values are
> assigned in multiple row INSERTs.
>
Well I'll have to reserve judgement until I have digested all this... (which
I will).
What alternative would you offer instead of using an identity field?
I've not noticed any significant issues with Identity fields, but I'm always
open to improving my knowledge and my techniques...

> Putting that aside, why store redundant data? If the row is going to be
> modified anyway as you have said then why not insert the modified row
> instead of duplicating an existing one and incurring the overhead of an
> extra update? Seems like you are paying a high price in integrity and
> performance for no discernable benefit.
>
What data is redundant? The original record? No, not so. It remains an
essential part of the order. It would take too long to truly put this into
the right context, but suffice to say that the new row is NOT replacing the
old row. Both will co-exist and both are essential.
I've adapted the INSERT INTO statement that you suggested, and I now have
one command that achieves what I need. I don't see how it could get any more
efficient.|||-- if ur table has huge no of columns, u can try
select * into #t from tbl_name where ...
insert into tbl_name
select * from #t
drop table #t
Rakesh
"CJM" wrote:

> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the prima
ry
> key) and INSERT a new record with this information, but since there are a
> fair few columns, I wondered if there might be a simpler one-line SQL
> instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>

Creating a Auto Increment which contains Numbers & Text

Hi,
Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-
ABC000001
ABC000002
ABC000003
Etc, Etc
I am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?
Regards,
BrettI would just create a view that exposes the alpha you want plus the IDENTITY column, formatted as you wish.|||

Douglas' solution is a horrible hack. It is not scalable (what happens when you want "BCD" and "ABC") and requires having two keys -- the "real" key and the "identity" key.
You were on the right track with a stored procedure. I don't know what you're trying to model ... so I'm going with Accounts.
Your proc should look like:
PROCEDURE Create_New_Account ( @.Account_Number CHAR(8) OUT, @.Salesperson_Number CHAR(5), (... other required fields) )
I'd recommend pre-creating your identifiers, and doing a SELECT / DELETE out of the Account_Numbers table. If that won't fly, just SELECT MAX(Account_Number) from your Accounts table, parse out the string and increment the number part.

|||

I would disagree with the "Horrible hack" characterization. If there was a column [Prefix] and a column with the identity, the [Prefix] could be whatever you want. I saw a reference to Auto Increment and was thinking about Access rather than SQL Server (yes, of course I realize the post was on the SQL Server forum).

If you can ensure all access to inserting data is through the stored procedures, then of course use that. method.

What is being attempted (adding meaning of some sort to an identifier) is not often a wonderful thing.

|||You're right; it's ideal to have to have both parts of the key in the table instead of combining it into one.
But I won't concede to "Horrible Hack" being a mischaraterization. Everytime I've seen this done ... it's a horrible hack ... here's my favorite example ...
Order_Numbers were defined something as : (Order_Date + Order_Seq) + XOR Parity Shift. This was to ensure order_nums were not predictable (from end users) and were verifiable.
The programmer who implemented this requirement used this schema:
Orders_Base = TABLE (Order_ID, Order_Date, Order_Seq, ...)
Orders = VIEW( SELECT complex_generator_fn( Order_Date, Order_Seq) as Order_Num, Order_Date, ... )
Guess how fun that got when they actually used the system to place orders. And tried to query on the non-indexed Order_Num.
They first implemented decode logic in the Middle tier to get Seq + Date. Surprise surprise, that didn't quite work out so well either.
Final iteration (and still as it exists today):
Orders_Base = TABLE( Order_ID, Order_Date, Order_Seq )
Order_Numbers = TABLE( Order_ID, Order_Number )
Oh, and warehousing and analytics is a COMPLETE nightmare.

CreateSubscription Extension Parameters

The following Extension Parameters are not being accepted --
CreateSubscription works and a record is created, but three values are not
getting through to the database:
extensionParams(3) = New
rsSubscriptions.SVR03_ReportService.ParameterValue
extensionParams(3).Name = "RENDERFORMAT"
extensionParams(3).Value = pReportType
extensionParams(4) = New
rsSubscriptions.SVR03_ReportService.ParameterValue
extensionParams(4).Name = "WRITEMODE"
extensionParams(4).Value = "AutoIncrement"
extensionParams(5) = New
rsSubscriptions.SVR03_ReportService.ParameterValue
extensionParams(5).Name = "UserName"
extensionParams(5).Value = "myuserid"
extensionParams(6) = New
rsSubscriptions.SVR03_ReportService.ParameterValue
extensionParams(6).Name = "PassWord"
extensionParams(6).Value = "mypassword"
The "WRITEMODE" is the only of the above Extension Parameters that is
correct when I go into the detail screen. The format says "XML file with
report data" (the default) and the Userid and Password are blank.
Everything else is working great.
Any suggestions? I tried all-caps for the Param.Name (as they display coming
out of the ListSubscriptions method), but that made no difference.
Thanks!
AJThere isn't much documentation that I found that tells you exactly how
the name should be spelled, so I used this code:
rs.GetDataDrivenSubscriptionProperties(subscriptionID, out exSet,
out dataPlan, out desc, out aState, out status,
out eventType, out matchData, out parameterVal);
ParameterValue param =(ParameterValue)exSet.ParameterValues.GetValue(3);
lblName.Text = param.Name.ToString();
To get the names (you just change the number in GetValue). Here is
what I found from this:
FILENAME
FILEEXTN
PATH
RENDER_FORMAT
USERNAME
WRITEMODE
In addition, trial and error has shown me that:
PASSWORD
is what RS is expecting for the password.
Try using these EXACTLY as shown here and see if it helps.
Sheridan Saint-Michel|||Thanks, Sheridan,
I went back and checked my test matrix and realized that I hadn't tested
case-sensitivity on that parameter name. It appears that CreateSubscription
will load Extension Parameters into the database even if they are incorrect,
and this was throwing me off.
I've now loaded my (hundreds) of new report requests and the schedule is
running!
AJ.
"Sheridan" wrote:
> There isn't much documentation that I found that tells you exactly how
> the name should be spelled, so I used this code:
> rs.GetDataDrivenSubscriptionProperties(subscriptionID, out exSet,
> out dataPlan, out desc, out aState, out status,
> out eventType, out matchData, out parameterVal);
> ParameterValue param => (ParameterValue)exSet.ParameterValues.GetValue(3);
> lblName.Text = param.Name.ToString();
> To get the names (you just change the number in GetValue). Here is
> what I found from this:
> FILENAME
> FILEEXTN
> PATH
> RENDER_FORMAT
> USERNAME
> WRITEMODE
> In addition, trial and error has shown me that:
> PASSWORD
> is what RS is expecting for the password.
> Try using these EXACTLY as shown here and see if it helps.
> Sheridan Saint-Michel
>|||I am having the same issue but with only PASSWORD though. all of my other
info is being passed. here is my code. any suggestions?
ParameterValue[] extensionParams2 = new ParameterValue[7];
extensionParams2[0] = new ParameterValue();
extensionParams2[0].Name = "FILENAME";
extensionParams2[0].Value = ScheduleName;
extensionParams2[1] = new ParameterValue();
extensionParams2[1].Name = "FILEEXTN";
extensionParams2[1].Value = "true";
//check if folder exists for User. If not then create
string strPath =ConfigurationManager.AppSettings["ReportsSchedulePath"].ToString()+ UserName;
if (!System.IO.Directory.Exists(strPath))
{
System.IO.Directory.CreateDirectory(strPath);
}
extensionParams2[2] = new ParameterValue();
extensionParams2[2].Name = "PATH";
extensionParams2[2].Value = strPath;
extensionParams2[3] = new ParameterValue();
extensionParams2[3].Name = "RENDER_FORMAT";
extensionParams2[3].Value = ReportType;
extensionParams2[4] = new ParameterValue();
extensionParams2[4].Name = "WRITEMODE";
extensionParams2[4].Value = "AutoIncrement";
extensionParams2[5] = new ParameterValue();
extensionParams2[5].Name = "USERNAME";
extensionParams2[5].Value =ConfigurationManager.AppSettings["FileShareUser"].ToString();
//extensionParams2[6] = new ParameterValue();
//extensionParams2[6].Name = "PASSWORD";
//extensionParams2[6].Label = "PASSWORD";
ParameterValue PW = new ParameterValue();
PW.Name = "PASSWORD";
PW.Value = ConfigurationManager.AppSettings["FileSharePass"].ToString();
//extensionParams2[6].Value =ConfigurationManager.AppSettings["FileSharePass"].ToString();
extensionParams2.SetValue(PW, 6);
ExtensionSettings extensionSettings2 = new ExtensionSettings();
extensionSettings2.Extension = "Report Server FileShare";
extensionSettings2.ParameterValues = extensionParams2;
string matchData = XML;
string txtResults2;
txtResults2 = schReport.CreateSubscription(ReportPath(),
extensionSettings2, ScheduleName, "TimedSubscription", matchData, paramaters);
return txtResults2;
also this worked when i first implimented this but has recently stopped
working. any security issues i should look at? i have SP1 and SP2 put on
Reporting services.
Josh

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards