Tuesday, March 27, 2012
Getting the identity of some newly created row
i.e. auto-generated, and I insert a new row like so (sorry, but all-
caps keywords are so 70s):
insert account
values (20000.00, 42)
and then want to do something with the newly created account rows
accountno, like insert it in another table, like:
insert account
values (20000.00, 42)
insert transactions
values (accountno, 20.000)
except this of course does not work. Maybe something like:
insert account
values (20000.00, 42)
declare @.accountno int
select @.accountno = ?
insert transactions
values (accountno, 20.000)
Help!Check out Scope_Identity in BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<pinkfloydhomer@.gmail.com> wrote in message
news:1194105832.364153.128820@.o38g2000hse.googlegroups.com...
> If I have an account table where the column "accountno" is an identity
> i.e. auto-generated, and I insert a new row like so (sorry, but all-
> caps keywords are so 70s):
> insert account
> values (20000.00, 42)
> and then want to do something with the newly created account rows
> accountno, like insert it in another table, like:
> insert account
> values (20000.00, 42)
> insert transactions
> values (accountno, 20.000)
> except this of course does not work. Maybe something like:
> insert account
> values (20000.00, 42)
> declare @.accountno int
> select @.accountno = ?
> insert transactions
> values (accountno, 20.000)
> Help!
>|||SELECT @.@.IDENTITY
Returns the last-inserted identity value
From http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||No it doesn't in some cases. Use @.@.IDENTITY instead. Check out
Scope_Identity in BOL against @.@.IDENTITY and you will see why..
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Use SCOPE_IDENTITY unless you have a compelling reason to use @.@.IDENTITY.
See BOL for the differences.
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
Getting the identity of some newly created row
Returns the last-inserted identity value.
From http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
No it doesn't in some cases. Use @.@.IDENTITY instead. Check out
Scope_Identity in BOL against @.@.IDENTITY and you will see why..
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
|||Use SCOPE_IDENTITY unless you have a compelling reason to use @.@.IDENTITY.
See BOL for the differences.
"Joseph" <nospam@.developmentnow.com> wrote in message
news:aa380281-33fb-4635-af1e-a69558eddb52@.developmentnow.com...
> SELECT @.@.IDENTITY
> Returns the last-inserted identity value.
> From
> http://developmentnow.com/g/118_2007_11_0_0_1035068/Getting-the-identity-of-some-newly-created-row.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
Getting the id generated by SQL Server for a new record
database. The id field is automatically created when a record is added, and
I would like to be able to know what value was assigned to the id field for
use in my ASP.NET application. Is there a way to find out what value was
assigned to the record at the same time I create the record (in otherwords,
I do not want to use a SELECT statement)? Thanks.
Nathan Sokalski
njsokalski@.hotmail.com
http://www.nathansokalski.com/
On Nov 29, 10:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
Look in the Books Online for the SCOPE_IDENTITY function. Lots of
good examples there.
|||On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokal...@.hotmail.comhttp://www.nathansokalski.com/
You would want to return the SCOPE_IDENTITY() value as an output
parameter from your DbCommand after you perform the insert.
|||On 30 Nov., 07:18, Dan Gartner <dgart...@.gmail.com> wrote:
> On Nov 29, 9:42 pm, "Nathan Sokalski" <njsokal...@.hotmail.com> wrote:
> You would want to return the SCOPE_IDENTITY() value as an output
You could use the OUTPUT feature of sql server 2005. But in this case
SCOPE_IDENTITY() and @.@.IDENTITY give back odd results while
IDENT_CURRENT() and inserted.id seem to give correct values.
inserted.id is the way the output feature is meant.
Example:
begin tran
create table tmp (id int identity, xyz varchar)
insert into tmp (xyz) output inserted.id values ('w')
insert into tmp (xyz) output @.@.identity values ('x')
insert into tmp (xyz) output ident_current('tmp') values ('y')
insert into tmp (xyz) output scope_identity() values ('z')
select * from tmp
drop table tmp
rollback
Results:
1
1
3
3
id xyz
-- --
1 w
2 x
3 y
4 z
(4 Zeile(n) betroffen)
|||Hi
You can use the output parameter of the stored procedure and return the
value that is being inserted. If you are using identity column then you can
use @.@.IDENTITY to return the last inserted indentity value.
--
Thanks,
Ibrahim
Software Consultant - Web Development, GB
"Nathan Sokalski" wrote:
> I am using ASP.NET 2.0 to add records to a database table in an SQL Server
> database. The id field is automatically created when a record is added, and
> I would like to be able to know what value was assigned to the id field for
> use in my ASP.NET application. Is there a way to find out what value was
> assigned to the record at the same time I create the record (in otherwords,
> I do not want to use a SELECT statement)? Thanks.
> --
> Nathan Sokalski
> njsokalski@.hotmail.com
> http://www.nathansokalski.com/
>
>
|||This method obviously requires using stored procs (which is almost always a
good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
bit of a bind I think. IIRC you can't issue an insert statement and get a
select back out in a single Execute... type command.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...[vbcol=seagreen]
> Hi
> You can use the output parameter of the stored procedure and return the
> value that is being inserted. If you are using identity column then you
> can
> use @.@.IDENTITY to return the last inserted indentity value.
> --
> --
> Thanks,
> Ibrahim
> Software Consultant - Web Development, GB
>
> "Nathan Sokalski" wrote:
|||Kevin,
Sure you can, like this:
cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
(@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
Then:
ID = cmd.ExecuteScalar
Kerry Moorman
"TheSQLGuru" wrote:
> This method obviously requires using stored procs (which is almost always a
> good idea for a bunch of reasons). If you are using ADO/ADONET you are in a
> bit of a bind I think. IIRC you can't issue an insert statement and get a
> select back out in a single Execute... type command.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
> message news:88277D10-9F15-42C3-BA0B-D18E4377E160@.microsoft.com...
>
>
|||I seem to recall a client trying to do that recently (using ADO classic) and
it not working. Perhaps they missed the semicolon. I will recheck their
attempts and see if that does it.
One additional question since I am not an ADO guru. Does the Select
Scope_identity() not return a single-column single-row result set, which the
executescalar isn't expecting?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:D12AFECB-DC31-4CE3-A2A2-14F3AB3CD95D@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Sure you can, like this:
> cmd.CommandText = "Insert Into Students (StudentName, Test1, Test2) Values
> (@.StudentName, @.Test1, @.Test2); Select Scope_Identity()"
> Then:
> ID = cmd.ExecuteScalar
> Kerry Moorman
>
> "TheSQLGuru" wrote:
|||Kevin,
ExecuteScalar returns the first column of the first row in the result set
returned by the query.
Kerry Moorman
"TheSQLGuru" wrote:
> One additional question since I am not an ADO guru. Does the Select
> Scope_identity() not return a single-column single-row result set, which the
> executescalar isn't expecting?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
|||I just checked back with the developer that had the issue. He swears that
using VB6 and ADO classic your example fails. Were you using ADOc or
ADO.NET?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kerry Moorman" <KerryMoorman@.discussions.microsoft.com> wrote in message
news:6AD6FF28-692A-4DFA-9BCB-7D8E9A2302C1@.microsoft.com...
> Kevin,
> ExecuteScalar returns the first column of the first row in the result set
> returned by the query.
> Kerry Moorman
>
> "TheSQLGuru" wrote:
>
Getting the Base Type from User Defined Data Types
USER_NAME which was created using:
sp_addtype USER_NAME, 'Char (10)'
Is there a query that I can use to display what the base type is? For
instance, I'd like to query a table like systypes and have it tell me
that USER_TYPE is Char(10).
I can see that systypes has an xtype field, but it contains a number.
Is there somewhere that maps that number to a physical datatype?You could also do this as a join...
SELECT s1.name, type = (SELECT name
FROM systypes s2
WHERE s2.xtype = s1.xtype
AND s2.xusertype<=256
),
s1.length
FROM systypes s1
where s1.xusertype > 256
"Janel E." <jmeichler@.oasvas.com> wrote in message
news:b5dd350d.0308060839.7dd690da@.posting.google.com...
> Suppose I have a user defined data type in SQL Server 2000 called
> USER_NAME which was created using:
> sp_addtype USER_NAME, 'Char (10)'
> Is there a query that I can use to display what the base type is? For
> instance, I'd like to query a table like systypes and have it tell me
> that USER_TYPE is Char(10).
> I can see that systypes has an xtype field, but it contains a number.
> Is there somewhere that maps that number to a physical datatype?
Friday, March 23, 2012
Getting Started
I have created a basic report.
Now I am looking for options to send in some parameters to the report as
in a url to call from a web application, which can be applied on the query.
Also rendering in pdf format.
Any help would be appreciable.
Thanks!
SandyEverything you need to know:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_urlaccess_374y.asp?frame=true
Kulgan.
Wednesday, March 21, 2012
Getting rid of the "All" level in query results
I have two seemingly identical cubes, one imported from AS 2000 and one
created using AMO in AS 2000 (they are both named new1 and are located in
separate databases). Yet, when issuintg the following simple query:
select {[RecvPay].[RecvPay].members} on columns from new1
I get only columns 1 and 2 on the imported cube, while I get an additional
"All RecvPay" column on the created cube. I have scoured the XMLA script
generated from each RecvPay dimension and how it is used in the new1 cube
without seeing any meaningful differences. I want to get rid of this "All
RecvPay" in my results. Am I missing something?
Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance Solutions
You could just use select {[RecvPay].[RecvPay].Children} on columns from new1
|||I understand there are other ways to get it, but why the difference?|||
There is an IsAggregatable property on each attribute which sets if it generates this automatic All member. If you don't want an All member in the attribute at all you can change this setting from the default of "true" to false. If you had the all member turned off in AS2000, this setting was probably kept when you migrated, if you generated the dimension in AMO without overriding the defaults, then it would automatically generate an All member.
If you only want the All member excluded from the query then there is a slight change in naming between AS2000 and AS2005. AS 2000 uses a <dimension>.<level>.members syntax, and was not truely multi-hierarchy aware. AS2005 is truely multi hierarchy aware and you need to use <dimension>.<hierarchy>.<level>.members. When you create an attritube it generates an implied single level hierarchy.
|||Thank you. I'll try it first thing tomorrow|||
That does work in my example (and in the much more complicated originsl query this came from), but that is definitely not how the 2000 dimension was constructed since I can see "All RecvPay" when browsing that dimension. I am just wondering whether other queries might fail or produce unexpected results with this change.
|||In this case, the difference in you queries is most likely explained by the fact that in SSAS 2005 you need to specify the heirarchy in the member/level references.
In AS 2000 [RecvPay].[RecvPay] refers to the RecvPay level of the RecvPay dimension
In AS 2005 [RecvPay].[RecvPay] refers to the RecvPay hierarchy of the RecvPay dimension
When you get the members of an entire hierarchy it includes the All member. To refer to just the level of RecvPay members (which does not include the all member) you need to use a [RecvPay].[RecvPay].[RecvPay] reference.
Getting rid of the "All" level in query results
I have two seemingly identical cubes, one imported from AS 2000 and one
created using AMO in AS 2000 (they are both named new1 and are located in
separate databases). Yet, when issuintg the following simple query:
select {[RecvPay].[RecvPay].members} on columns from new1
I get only columns 1 and 2 on the imported cube, while I get an additional
"All RecvPay" column on the created cube. I have scoured the XMLA script
generated from each RecvPay dimension and how it is used in the new1 cube
without seeing any meaningful differences. I want to get rid of this "All
RecvPay" in my results. Am I missing something?
Thanks,
Boris Zakharin, MCAD
Metavante Risk and Compliance Solutions
You could just use select {[RecvPay].[RecvPay].Children} on columns from new1
|||I understand there are other ways to get it, but why the difference?|||
There is an IsAggregatable property on each attribute which sets if it generates this automatic All member. If you don't want an All member in the attribute at all you can change this setting from the default of "true" to false. If you had the all member turned off in AS2000, this setting was probably kept when you migrated, if you generated the dimension in AMO without overriding the defaults, then it would automatically generate an All member.
If you only want the All member excluded from the query then there is a slight change in naming between AS2000 and AS2005. AS 2000 uses a <dimension>.<level>.members syntax, and was not truely multi-hierarchy aware. AS2005 is truely multi hierarchy aware and you need to use <dimension>.<hierarchy>.<level>.members. When you create an attritube it generates an implied single level hierarchy.
|||Thank you. I'll try it first thing tomorrow|||
That does work in my example (and in the much more complicated originsl query this came from), but that is definitely not how the 2000 dimension was constructed since I can see "All RecvPay" when browsing that dimension. I am just wondering whether other queries might fail or produce unexpected results with this change.
|||In this case, the difference in you queries is most likely explained by the fact that in SSAS 2005 you need to specify the heirarchy in the member/level references.
In AS 2000 [RecvPay].[RecvPay] refers to the RecvPay level of the RecvPay dimension
In AS 2005 [RecvPay].[RecvPay] refers to the RecvPay hierarchy of the RecvPay dimension
When you get the members of an entire hierarchy it includes the All member. To refer to just the level of RecvPay members (which does not include the all member) you need to use a [RecvPay].[RecvPay].[RecvPay] reference.
Monday, March 19, 2012
Getting results from sqldatasource in codebehind
(New to ASP.net 2.0 and database connection)
I have created an sqldatasource on the aspx page, which works fine, but how do I get the results from it in the codebehind?
hmm..you should create the datasource in the code behind page. I mean that's what the page is for. Place all your asp.net code in it. If you use a code-behind page then aspx page is just for displaying the data.|||
I can't seem to get it working. The connection works fine (I believe), but I can't seem to give the parameters a value
I have this code: (I know it's Insert instead of select, but that's what I'm working on now)
SqlDataSourceSupplierList.InsertCommand ="INSERT INTO dbo.ProdSup(SupID, ProdID) VALUES (@.SupID, @.ProdID)";
SqlDataSourceSupplierList.InsertParameters.Add("@.SupID", SqlDbType.Int) = 4;
SqlDataSourceSupplierList.InsertParameters.Add("@.ProdID","8");
SqlDataSourceSupplierList.Insert();
As you can see I have tried two ways to give them values, but none of them works. The fields in the DB are both integer, which I guess is why the second try doesn't work (it sends '8' in as a string)... The error I get here is that it "cannot insert the value NULL into column"
The line with @.SupID gives me this error:
CS1502: The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(string, string)' has some invalid arguments
What's wrong here?
Monday, March 12, 2012
Getting problem in AcquireConnections
Hi All,
I just started looking at the SSIS programming, wanted to created a package having table to table data transfer. My source and destination databases are in Oracle.
I gone throught the code samples and started creating the source component. And the whatever samples i've seen the code i have written (copied) looks correct to me but still getting following error.
The basic questions i have is,
1. Do i need to setup something to start programming in SSIS. I am using 'Microsoft Visual C# Express Edition' for programming.
I have all the dll's in place.
And after compiling the code the exception i got is,
{"Exception from HRESULT: 0xC020801C"} System.Runtime.InteropServices.COMException was caught
Message="Exception from HRESULT: 0xC020801C"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071611876
StackTrace:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)
The Code is as Follows:
public static void CreateSource()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable e = package.Executables.Add("DTS.Pipeline.1");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = e as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlow = thMainPipe.InnerObject as MainPipe;
// Add an OLEDB connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = @."Data Source=pqdb9i;User ID=srcDbUserId;Provider=MSDAORA.1;Persist Security Info=False;Integrated Security=SSPI;Auto Translate=False;";
package.DelayValidation = true;
cm.DelayValidation = true;
component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource.1";
// Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate();
// Initialize the component
instance.ProvideComponentProperties();
// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
// Set the custom properties.
//instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "Select * from srcTable");
// Reinitialize the metadata.
try
{
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine(component.InputCollection.Count);
}
///End Code
Thanks in advance
-Yuwaraj
Does Oracle use Windows Authentication in your case? If not, where's the password for the database user?|||Hi
I have the same problem, did u find any solution?
Friday, March 9, 2012
getting newly created id number(asap)
hello,
i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.
it works like:
insert into table() values()
select @.id = (newly created id number) from table
insert into table1() values(@.id)
something like that.
Hi lenerd,
You should usescope_identity for this!
Here are some links, please give it a look and try!
http://msdn2.microsoft.com/en-us/library/ms190315.aspx|||
Hi lenerd3000
its too easy, i hope you know what command object is. execute scalar will return the primary key of newly generated record.
dim myID as integer =CType(myCommand.ExecuteScalar(),Integer)
hope it helps
Sunday, February 26, 2012
Getting Images to render in HTML using Web Service (SOAP)
pull reports from the SQL Server.
The web server is on one machine, SQL Server on another and I use the web
service to get the reports. I do not use URLs in any way in order to keep as
much information hidden as possible.
I have been able to list all reports, select a report, get back the
parameters required, set the parameter values and render reports using
ReportingService.Render
HOWEVER...
The reports that work do not include charts (graphics). When I render a
report using my app, if it has no chart I can view it in HTML, PDF, TIF,
Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is fine.
If I try to use HTML or MHTML I get a big empty space where my charts should
be.
In reading the BOL under ReportingService.Render Method
I found this: "StreamIds
[out] The stream identifiers. These IDs are passed to the RenderStream
method. You can use them to render the external resources (images, etc.)
that are associated with a given report. "
I have found this topic:
ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
ReportingService.RenderStream Method
I read the code, and I have tried to implement it as in the BOL. I am able
to write the images onto the web-server hard-drive, and the image place
holder properties no longer point to my SQL server (which they did at first)
but I still don't get an image.
For example, my image is actually at:
http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
It is there and it works. Now my report opens in a new window since I do the
final call to open it like this:
Response.AddHeader("Content-Disposition", "attachment; filename=" &
fileName)
Response.BinaryWrite(data)
When I right click on the image place holder I get this:
file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
The images don't seem to get from being written on the drive, to being
picked up for display.
My code for writing the images looks like this:
If format = "HTML4.0" Then
For Each streamid In streamIDs
image = rs.RenderStream(strpath, "HTML4.0", streamid,
Nothing, Nothing, parameters, Nothing, Nothing)
Dim stream As System.IO.FileStream = _
System.IO.File.OpenWrite(Application("TempReports") &
streamid)
stream.Write(image, 0, CInt(image.Length))
stream.Close()
Next
End If
Sandy Murdock MCPlook at my previuos post:
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=rome&lang=en&cr=US&guid=&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a830cc14-9b06-496b-b1f4-e55ab3611f0b
Antonio
"Roger Twomey" wrote:
> I have created a web app that uses the reporting services Web Service to
> pull reports from the SQL Server.
> The web server is on one machine, SQL Server on another and I use the web
> service to get the reports. I do not use URLs in any way in order to keep as
> much information hidden as possible.
> I have been able to list all reports, select a report, get back the
> parameters required, set the parameter values and render reports using
> ReportingService.Render
> HOWEVER...
> The reports that work do not include charts (graphics). When I render a
> report using my app, if it has no chart I can view it in HTML, PDF, TIF,
> Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is fine.
> If I try to use HTML or MHTML I get a big empty space where my charts should
> be.
> In reading the BOL under ReportingService.Render Method
> I found this: "StreamIds
> [out] The stream identifiers. These IDs are passed to the RenderStream
> method. You can use them to render the external resources (images, etc.)
> that are associated with a given report. "
> I have found this topic:
> ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
> ReportingService.RenderStream Method
> I read the code, and I have tried to implement it as in the BOL. I am able
> to write the images onto the web-server hard-drive, and the image place
> holder properties no longer point to my SQL server (which they did at first)
> but I still don't get an image.
> For example, my image is actually at:
> http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
> It is there and it works. Now my report opens in a new window since I do the
> final call to open it like this:
> Response.AddHeader("Content-Disposition", "attachment; filename=" &
> fileName)
> Response.BinaryWrite(data)
> When I right click on the image place holder I get this:
> file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
> The images don't seem to get from being written on the drive, to being
> picked up for display.
> My code for writing the images looks like this:
> If format = "HTML4.0" Then
> For Each streamid In streamIDs
> image = rs.RenderStream(strpath, "HTML4.0", streamid,
> Nothing, Nothing, parameters, Nothing, Nothing)
> Dim stream As System.IO.FileStream = _
> System.IO.File.OpenWrite(Application("TempReports") &
> streamid)
> stream.Write(image, 0, CInt(image.Length))
> stream.Close()
> Next
> End If
>
> Sandy Murdock MCP
>
>|||Thank you!
I was able to take your solution and from it build one that works for me. I
was stuck.
Thanks.
"Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
news:61BF34C8-98DB-4388-8E06-F5F8C1F22BBE@.microsoft.com...
> look at my previuos post:
>
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=rome&lang=en&cr=US&guid=&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a830cc14-9b06-496b-b1f4-e55ab3611f0b
> Antonio
>
> "Roger Twomey" wrote:
> > I have created a web app that uses the reporting services Web Service to
> > pull reports from the SQL Server.
> >
> > The web server is on one machine, SQL Server on another and I use the
web
> > service to get the reports. I do not use URLs in any way in order to
keep as
> > much information hidden as possible.
> >
> > I have been able to list all reports, select a report, get back the
> > parameters required, set the parameter values and render reports using
> > ReportingService.Render
> >
> > HOWEVER...
> >
> > The reports that work do not include charts (graphics). When I render a
> > report using my app, if it has no chart I can view it in HTML, PDF, TIF,
> > Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is
fine.
> > If I try to use HTML or MHTML I get a big empty space where my charts
should
> > be.
> >
> > In reading the BOL under ReportingService.Render Method
> > I found this: "StreamIds
> > [out] The stream identifiers. These IDs are passed to the RenderStream
> > method. You can use them to render the external resources (images, etc.)
> > that are associated with a given report. "
> >
> > I have found this topic:
> > ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
> > ReportingService.RenderStream Method
> >
> > I read the code, and I have tried to implement it as in the BOL. I am
able
> > to write the images onto the web-server hard-drive, and the image place
> > holder properties no longer point to my SQL server (which they did at
first)
> > but I still don't get an image.
> >
> > For example, my image is actually at:
> > http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
> >
> > It is there and it works. Now my report opens in a new window since I do
the
> > final call to open it like this:
> > Response.AddHeader("Content-Disposition", "attachment; filename=" &
> > fileName)
> > Response.BinaryWrite(data)
> >
> > When I right click on the image place holder I get this:
> >
file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
> >
> > The images don't seem to get from being written on the drive, to being
> > picked up for display.
> >
> > My code for writing the images looks like this:
> >
> > If format = "HTML4.0" Then
> > For Each streamid In streamIDs
> > image = rs.RenderStream(strpath, "HTML4.0", streamid,
> > Nothing, Nothing, parameters, Nothing, Nothing)
> >
> > Dim stream As System.IO.FileStream = _
> > System.IO.File.OpenWrite(Application("TempReports") &
> > streamid)
> >
> > stream.Write(image, 0, CInt(image.Length))
> > stream.Close()
> > Next
> > End If
> >
> >
> > Sandy Murdock MCP
> >
> >
> >|||I've been rendering chart images successfully for a report using the
ReportingService web services for a few weeks now. Today I had to add a
parameter to the report and now after I call my render method and loop
through the returned streamId's to get the images, I get the exception:
"rsStreamNotFound The stream cannot be found. The stream identifier that is
provided to an operation cannot be located in the report server database."
Why would this be happening?! I'm looping through the very streamId's that
the render method just returned to me!
Thanks in advance, Brian Parker
Getting Identity when inserting a record from Stored Procedure....
I need to get the Identity seed or Key that was just created by this
insert. Can I do this inside the stored procedure, returning the just
created key?
Here is the stored procedure...
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1
AS
INSERT INTO a_TixDetail
(SvcTixKey, Date, [Performed by], [Detail Summary],
External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
RETURN
TixKey is a field in the table that is the Primary key. It will be
genereated when the Insert is executed. How can I get this returned from
this procedure?
Thanks,
RogIn SQL Server 2000 use SCOPE_IDENTITY() to return the inserted IDENTITY
value. In SQL Server 7.0 use @.@.IDENTITY.
David Portas
SQL Server MVP
--|||Roger,
Create an output parameter and get the value from the function
SCOPE_IDENTITY().
Example:
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1,
@.new_id int output
AS
set nocount on
INSERT INTO a_TixDetail (SvcTixKey, Date, [Performed by], [Detail
Summary],External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
set @.new_id = scope_identity()
RETURN
go
declare @.i int
declare @.rv int
exec @.rv = R_InsertTicketDetail ..., @.i output
print @.rv
print @.i
go
AMB
"Roger" wrote:
> I have a stored procedure that inserts a record into a table.
> I need to get the Identity seed or Key that was just created by this
> insert. Can I do this inside the stored procedure, returning the just
> created key?
> Here is the stored procedure...
> ALTER PROCEDURE R_InsertTicketDetail
> @.SvcTixKey int,
> @.OpenDate datetime,
> @.PerformedBy nvarchar(50),
> @.Detail ntext,
> @.External bit = -1
> AS
> INSERT INTO a_TixDetail
> (SvcTixKey, Date, [Performed by], [Detail Summary],
> External)
> VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
> RETURN
>
> TixKey is a field in the table that is the Primary key. It will be
> genereated when the Insert is executed. How can I get this returned from
> this procedure?
> Thanks,
> Rog
>
>
Friday, February 24, 2012
Getting Exception while installing SQL Express 2005 in Windows Vista OS
Hi All,
I have developed an windows application using C#. I am using SQL Express as back-end.
I have created an installer class which first installs .NET Framework2.0, SQL Express2005 and then the
application. It is woking fine with WindowsNT and XP oprating systems.
If i try to install this on Windows Vista OS, I am getting expection while installing SQL Express(Creating SQL
Instance).
Can anybody help me in this scenario.
Regards,
Doppalapudi.
Can you supply the exact error message you are seeing? Are you seeing it on multiple Vista machines or just one? Can you also check your log directory for the text string "value 3" and supply the 10-15 lines above it?
Thanks,
Sam Lester (MSFT)
Hi
Thanks for giving quick reply. I got the problem and solved it. I was able to install the application.
Thanks
Doppalapudi.