Showing posts with label created. Show all posts
Showing posts with label created. Show all posts

Monday, March 26, 2012

Load assembly from Stored Procedure

Hi,
Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I have created a stored procedure that needs to load different assemblies depending on a query and then invokes a method (called execute() passing a string query as a parameter).
From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It doesnt matter if I attempt to load the assembly from it's byte array or from a file location, it always gives the following error:

Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)
It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?
Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels
|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N
sql

Load assembly from Stored Procedure

Hi,

Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I

have created a stored procedure that needs to load different assemblies

depending on a query and then invokes a method (called execute() passing a string query as a parameter).

From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It

doesnt matter if I attempt to load the assembly from it's byte array or

from a file location, it always gives the following error:


Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]

rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark,

Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)

It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?

Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N

Friday, March 23, 2012

Little help with a ReturnValue from SQL

I use a SP to write resident information to the SQL table. Then, I want to get the newly created RESIDENT_ID and send it back to my page.

Can someone give me a hand with it? I get the error:
"Cast from type 'DBNull' to type 'Integer' is not valid. "

Here is my SP:


CREATE PROCEDURE LS_resident_add
@.RESIDENT_ID int output,
@.HOUSE_ID as int,
@.Type as int = '0',
@.FName as varchar(50)=NULL,
@.LName as varchar(50)=NULL,
@.NickName as varchar(50)=NULL,
@.Email as varchar(50)=NULL,
@.Notes as varchar(1000)=NULL,
@.Access_Level as int = '0',
@.Password as varchar(8)=NULL,
@.Status as int = '0'

AS

INSERT INTO Street_Resident
(House_ID,
Type,
FName,
LName,
NickName,
Email,
Notes,
Access_Level,
Password,
Status)

VALUES
(@.House_ID,
@.Type,
@.FName,
@.LName,
@.NickName,
@.Email,
@.Notes,
@.Access_Level,
@.Password,
@.Status)
GO

Here is my script:


Sub resident_add(Source as Object, E as EventArgs)
Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_LindenStreet"))
Dim myCommand as New SqlCommand("LS_resident_add", myConnection)
myCommand.CommandType=CommandType.StoredProcedure

myCommand.Parameters.Add(New SQLParameter("@.HOUSE_ID", frm_HOUSE_ID.SelectedItem.Value))
myCommand.Parameters.Add(New SQLParameter("@.Type", frm_Type.SelectedItem.Value))
IF frm_FName.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.FName", frm_FName.Text))
END IF

IF frm_LName.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.LName", frm_LName.Text))
END IF

IF frm_NickName.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.NickName", frm_NickName.Text))
END IF

IF frm_Email.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.Email", frm_Email.Text))
END IF

IF frm_Notes.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.Notes", frm_Notes.Text))
END IF

IF frm_Access_Level.SelectedIndex > -1 THEN
myCommand.Parameters.Add(New SQLParameter("@.Access_Level", frm_Access_Level.SelectedItem.Value))
END IF

IF frm_Password.text > "" THEN
myCommand.Parameters.Add(New SQLParameter("@.Password", frm_Password.Text))
END IF

myCommand.Parameters.Add(New SQLParameter("@.Status", 1))

'Returns the new Resident ID so we can add any activities they signed up for
MyCommand.Parameters.Add("@.RESIDENT_ID", SqlDbType.Int)
MyCommand.Parameters("@.RESIDENT_ID").Direction = ParameterDirection.Output

myCommand.Connection.Open()
myCommand.ExecuteNonQuery

'Returns the new Resident ID so we can add any activities they signed up for
RESIDENT_ID = myCommand.Parameters("@.RESIDENT_ID").Value

myCommand.Connection.Close()
'Resident_Activity_delete()
Resident_Activity_Update()
Notify_User()
Dim strClose As String
strClose = "<script>"
strClose &= "window.close();"
strClose &= "</"
strClose &= "script>"
Response.Write(strClose)
End Sub

You're not returning anything from the sproc, thus the null reference error. Add a line something like this to the stored procedure:

RETURN SCOPE_IDENTITY()

Don

little help on delete stored procedure

I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table

Please help!

************************************************** ***
My contrat DDL is follows

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

************************************************** ***
My insert stored procedure is follows

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char],
@.contactId [int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @.contactId = contactId
and @.studentId = studentId)


begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
goSyntactically, it should be:

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char] (4),
@.contactId [int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @.contactId = contactId
and @.studentId = s.studentId)
...

But logically, you're also missing a validity of JUST a student id:

declare @.student_check char(4)
select @.student_check = max(studentid) from student where studentid = @.studentid
if @.student_check is null begin
print 'Invalid studentid specified'
return (1)
end

if exists (select 1 from contact
where @.contactId = contactId
and @.studentId = @.studentId) begin
print 'Specified studentid/contractid already exist in the table!'
return (1)
end
...
After then you can go on with the rest of your procedure.

Listing procdure/trigger SQL code

I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.

What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.

Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?

Does anybody have any alternative approach to this problem?

Thanks in advance,

MarkI don't understand you problem. EM allows you to script out all the objects of any particular type(s) to separate files. There is nothing that limits you to one object at a time.

Monday, March 19, 2012

list sql server data types

hello,

im trying to make an we application, that from it i can create a database and table to the server

i created the database.

but i want to create the tables too, but the problem is how can i list the data types of the sql server in the aspx page so i can create the tables with columns of requuired types.

Kind Regards

Mahmoud ManasrahI believe you could use the collection System.Data.SQLTypes. I don't know that you could bind directly to the collection, but you could iterate through it adding it to a bindable object (like a dataset for instance). HTH|||thank you

its a cool idea, this is what i was looking for

but im trying to do the code to iterate through this collection, but still didnt reach it

can u tell me how can i write the code

Kind Regards|||How about
foreach(Object myType in System.Data.SqlTypes)
if (myType.GetType().Name.Substring(0,3) == "Sql")

I haven't tested this, just maybe a starting point.

Monday, March 12, 2012

List of stored procedures

Hello,
What table can you use to get a list of all of the stored procedures in a
database? I need to be able to delete the ones created by users. I am
assuming that I will need to use a cursor to step through and delete them.
I created one for tables as an example (see below).
--
Thanks in advance,
sck10
USE MyDatabase
DECLARE @.strCursor varchar(255), @.strExe varchar(255)
-- Declare Cursor Batch
DECLARE csrPKey CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'tbl%') OR (TABLE_NAME LIKE 'tlkp%')
ORDER BY TABLE_NAME
OPEN csrPKey
FETCH NEXT FROM csrPKey INTO @.strCursor
WHILE @.@.FETCH_STATUS = 0 BEGIN -- 0 = success, -1 = outside recordset, -2
= row no longer exist
SELECT @.strCursor
SET @.strExe = 'DROP TABLE ' + @.strCursor
EXECUTE (@.strExe)
FETCH NEXT FROM csrPKey INTO @.strCursor
END
CLOSE csrPKey
DEALLOCATE csrPKeyThis is a multi-part message in MIME format.
--000808040309040806010702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You can do a similar thing with the INFORMATION_SCHEMA.ROUTINES view
(this would be the Microsoft approved, kosher way) or the dbo.sysobjects
table in the database (which is how I'd probably do it - old habits die
hard, but I'm trying to be good and start using the new catalog views &
DMVs).
--
*mike hodgson*
http://sqlnerd.blogspot.com
sck10 wrote:
>Hello,
>What table can you use to get a list of all of the stored procedures in a
>database? I need to be able to delete the ones created by users. I am
>assuming that I will need to use a cursor to step through and delete them.
>I created one for tables as an example (see below).
>
--000808040309040806010702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can do a similar thing with the INFORMATION_SCHEMA.ROUTINES
view (this would be the Microsoft approved, kosher way) or the
dbo.sysobjects table in the database (which is how I'd probably do it -
old habits die hard, but I'm trying to be good and start using the new
catalog views & DMVs).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
sck10 wrote:
<blockquote cite="miduwetxFQhGHA.4080@.TK2MSFTNGP03.phx.gbl" type="cite">
<pre wrap="">Hello,
What table can you use to get a list of all of the stored procedures in a
database? I need to be able to delete the ones created by users. I am
assuming that I will need to use a cursor to step through and delete them.
I created one for tables as an example (see below).
</pre>
</blockquote>
</body>
</html>
--000808040309040806010702--|||Hello Sck10,
As for the database server, is it a SQL Server 2000 instance or SQL Server
2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
catalog view to query all the user defined store procedures in a certain
database.
#sys.procedures (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188737.aspx
e.g. select * from sys.procedures
For sqlserver 2000, there is no such system catalog view, however, we can
query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
select * from sysobjects where type='P' order by name asc
This will return all the storeprocedures available in the current database
context(included system sp), you can filtering through the "category" field
in this system table to query user defined sp only.
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||This is a multi-part message in MIME format.
--090903010301050008030801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I'm guessing if the OP is using the INFORMATION_SCHEMA views rather than
the sys.* views then he's probably working with SQL 2000. As for SQL
2000, what's wrong with the INFORMATION_SCHEMA.ROUTINES view? I thought
Microsoft advised people to use the INFORMATION_SCHEMA views rather than
the system tables such as dbo.sysobjects.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Steven Cheng[MSFT] wrote:
>Hello Sck10,
>As for the database server, is it a SQL Server 2000 instance or SQL Server
>2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
>catalog view to query all the user defined store procedures in a certain
>database.
>#sys.procedures (Transact-SQL)
>http://msdn2.microsoft.com/en-us/library/ms188737.aspx
>e.g. select * from sys.procedures
>For sqlserver 2000, there is no such system catalog view, however, we can
>query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
>select * from sysobjects where type='P' order by name asc
>This will return all the storeprocedures available in the current database
>context(included system sp), you can filtering through the "category" field
>in this system table to query user defined sp only.
>Hope this helps.
>Regards,
>Steven Cheng
>Microsoft Online Community Support
>
>==================================================>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>==================================================>
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
--090903010301050008030801
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm guessing if the OP is using the INFORMATION_SCHEMA views rather
than the sys.* views then he's probably working with SQL 2000. As for
SQL 2000, what's wrong with the INFORMATION_SCHEMA.ROUTINES view? I
thought Microsoft advised people to use the INFORMATION_SCHEMA views
rather than the system tables such as dbo.sysobjects.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Steven Cheng[MSFT] wrote:
<blockquote cite="midQEGhq6ThGHA.4948@.TK2MSFTNGXA01.phx.gbl" type="cite">
<pre wrap="">Hello Sck10,
As for the database server, is it a SQL Server 2000 instance or SQL Server
2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
catalog view to query all the user defined store procedures in a certain
database.
#sys.procedures (Transact-SQL)
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn2.microsoft.com/en-us/library/ms188737.aspx</a>">http://msdn2.microsoft.com/en-us/library/ms188737.aspx">http://msdn2.microsoft.com/en-us/library/ms188737.aspx</a>
e.g. select * from sys.procedures
For sqlserver 2000, there is no such system catalog view, however, we can
query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
select * from sysobjects where type='P' order by name asc
This will return all the storeprocedures available in the current database
context(included system sp), you can filtering through the "category" field
in this system table to query user defined sp only.
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.microsoft.com/security</a>">http://www.microsoft.com/security">www.microsoft.com/security</a>
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
</pre>
</blockquote>
</body>
</html>
--090903010301050008030801--|||Thanks for your suggestion Mike,
I agree that the INFORMATION_SCHEMA.ROUTINES is also a good approach.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

List of stored procedures

Hello,
What table can you use to get a list of all of the stored procedures in a
database? I need to be able to delete the ones created by users. I am
assuming that I will need to use a cursor to step through and delete them.
I created one for tables as an example (see below).
--
Thanks in advance,
sck10
USE MyDatabase
DECLARE @.strCursor varchar(255), @.strExe varchar(255)
-- Declare Cursor Batch
DECLARE csrPKey CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'tbl%') OR (TABLE_NAME LIKE 'tlkp%')
ORDER BY TABLE_NAME
OPEN csrPKey
FETCH NEXT FROM csrPKey INTO @.strCursor
WHILE @.@.FETCH_STATUS = 0 BEGIN -- 0 = success, -1 = outside recordset, -2
= row no longer exist
SELECT @.strCursor
SET @.strExe = 'DROP TABLE ' + @.strCursor
EXECUTE (@.strExe)
FETCH NEXT FROM csrPKey INTO @.strCursor
END
CLOSE csrPKey
DEALLOCATE csrPKeyYou can do a similar thing with the INFORMATION_SCHEMA.ROUTINES view
(this would be the Microsoft approved, kosher way) or the dbo.sysobjects
table in the database (which is how I'd probably do it - old habits die
hard, but I'm trying to be good and start using the new catalog views &
DMVs).
*mike hodgson*
http://sqlnerd.blogspot.com
sck10 wrote:

>Hello,
>What table can you use to get a list of all of the stored procedures in a
>database? I need to be able to delete the ones created by users. I am
>assuming that I will need to use a cursor to step through and delete them.
>I created one for tables as an example (see below).
>|||Hello Sck10,
As for the database server, is it a SQL Server 2000 instance or SQL Server
2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
catalog view to query all the user defined store procedures in a certain
database.
#sys.procedures (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188737.aspx
e.g. select * from sys.procedures
For sqlserver 2000, there is no such system catalog view, however, we can
query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
select * from sysobjects where type='P' order by name asc
This will return all the storeprocedures available in the current database
context(included system sp), you can filtering through the "category" field
in this system table to query user defined sp only.
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I'm guessing if the OP is using the INFORMATION_SCHEMA views rather than
the sys.* views then he's probably working with SQL 2000. As for SQL
2000, what's wrong with the INFORMATION_SCHEMA.ROUTINES view? I thought
Microsoft advised people to use the INFORMATION_SCHEMA views rather than
the system tables such as dbo.sysobjects.
*mike hodgson*
http://sqlnerd.blogspot.com
Steven Cheng[MSFT] wrote:

>Hello Sck10,
>As for the database server, is it a SQL Server 2000 instance or SQL Server
>2005 instance? As for SQL Server 2005, you can use the "sys.procedures"
>catalog view to query all the user defined store procedures in a certain
>database.
>#sys.procedures (Transact-SQL)
>http://msdn2.microsoft.com/en-us/library/ms188737.aspx
>e.g. select * from sys.procedures
>For sqlserver 2000, there is no such system catalog view, however, we can
>query the "sysobjects" system table.(Also works for SQL Server 2005) e.g:
>select * from sysobjects where type='P' order by name asc
>This will return all the storeprocedures available in the current database
>context(included system sp), you can filtering through the "category" field
>in this system table to query user defined sp only.
>Hope this helps.
>Regards,
>Steven Cheng
>Microsoft Online Community Support
>
> ========================================
==========
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
> ========================================
==========
>
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>|||Thanks for your suggestion Mike,
I agree that the INFORMATION_SCHEMA.ROUTINES is also a good approach.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Friday, March 9, 2012

List Of Reports

I have created an application that uses reports published via SQL reporting services. I have a form with a report viewer control on it and above that I have a drop down list box. I want to fill the list box with available reports from the server and allow the user to select the report they wish to see. The report list could change so I don't want to hard code any report names in the list The problem I am having is that I can't figure out how to get a list of available reports from the server.

Thanks for any help offered.

Check out this article:

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.listchildren.aspx

BobP

|||

You may have to alter this for your system but you can try the query below.

selectdistinctnamefrom dbo.Catalog

where policyroot = 0

andpathnotlike'/Data Source%'

andpathnotlike'/Model%'

orderbyname

|||

Thanks to both of you, now I have options because I used both and they work (with slight modifications).

I modified the query as follows and it gives me just the reports

selectdistinctnamefrom dbo.Catalog

where policyroot = 0

andtype= 2

orderbyname

I just didn't realize it was in the Catalog table, I swore I looked but I guess not.

Thanks again

Wednesday, March 7, 2012

List of data mining techniques not populated - BI Studio hangs

From within the Business Intelligence Studio, I've created a data source and a data source view. Next, when I try to build a mining structure from a relational database, the studio hangs when it goes to the screen which lists the data mining techniques.

Has anybody come across this issue? What needs to be done to get further?

I'm using the June CTP of SQL Server 2005.

Thanks,
-GB.

In http://www.sqlserverdatamining.com I found a post talking about the same problem; the suggested fix was that a previous installation of Analysis Services be removed.

In my case, there was no previous installation of Analysis Services. However, there was an installation of SQL Server 2005 Express Edition, which the install program for SQL Server 2005 CTP detected, and asked me to remove (including a Beta of .NET version 2.0).

-GB.|||

A few questions:

Can you connect to your AS instance from SQL Mgmt Studio or another client?

Are you running a named instance? If yes, make sure that the SQL Server Browser service is functioning correctly.

Do you have network connectivity? There is a known issue with client-server connectivity when you're off the network.

|||Thank you for your reply, Raman.

I can connect to the AS instance from within SQL Management Studio. I can see the data source and data source views that I've already saved before starting with the data mining structure creation. I do not use any other client to connect to this instance.

I am using a default non-named instance.

About network connectivity: I have the TCP / IP stack running, but it is not (always) connected to a network. I also have the ZoneAlaram personal software firewall running. I've turned this off, but it still does not work.

Thanks,
-GB.|||This might be the result of a configuration/install bug. I would suggest uninstalling your June CTP version and moving to the RTM build.

List of data mining techniques not populated - BI Studio hangs

From within the Business Intelligence Studio, I've created a data source and a data source view. Next, when I try to build a mining structure from a relational database, the studio hangs when it goes to the screen which lists the data mining techniques.

Has anybody come across this issue? What needs to be done to get further?

I'm using the June CTP of SQL Server 2005.

Thanks,
-GB.

In http://www.sqlserverdatamining.com I found a post talking about the same problem; the suggested fix was that a previous installation of Analysis Services be removed.

In my case, there was no previous installation of Analysis Services. However, there was an installation of SQL Server 2005 Express Edition, which the install program for SQL Server 2005 CTP detected, and asked me to remove (including a Beta of .NET version 2.0).

-GB.|||

A few questions:

Can you connect to your AS instance from SQL Mgmt Studio or another client?

Are you running a named instance? If yes, make sure that the SQL Server Browser service is functioning correctly.

Do you have network connectivity? There is a known issue with client-server connectivity when you're off the network.

|||Thank you for your reply, Raman.

I can connect to the AS instance from within SQL Management Studio. I can see the data source and data source views that I've already saved before starting with the data mining structure creation. I do not use any other client to connect to this instance.

I am using a default non-named instance.

About network connectivity: I have the TCP / IP stack running, but it is not (always) connected to a network. I also have the ZoneAlaram personal software firewall running. I've turned this off, but it still does not work.

Thanks,
-GB.|||This might be the result of a configuration/install bug. I would suggest uninstalling your June CTP version and moving to the RTM build.

Friday, February 24, 2012

List control and pdf export issues

I have created a report that contained a subreport that was databound inside a table but I was getting errors when I export to excel - "Subreports within table/matrix cells are ignored".

So I changed my report to bind the subreport to a list control instead of a table and it exports to excel fine but I now have new issues:

1) When I export to pdf it seems to ignore width and height properties, creating a blank page every other page (because it thinks the list control is wider than the page), and it also overlaps other elements below the list control.

2) I also seems to have lost the paging in the viewer control that is handled by setting the interactive height property.

Are these known limitations of the list control and pdf or do I have something wrong in the way I have set-up the report. I can provide .rdlc file samples if necessary.

Thank you

Lee Whitney

For your first issue, I guess the width of your subreport (in designer) is more than the width of the subreport control in your main report. For example, if you had used 8 inches in your subreport designer and if the width of the subreport control in your main report is only 7 inches, it will be spilled to the next page.

Hopefully this will solve your other issues as well.

Shyam

|||

I already made sure that my sub report width is less than the control width, in fact if I use the table control rather than the list control (and the widths for both controls are the same) the table control exports just fine to pdf where the list control does not. The list control seems to add a colspan=2 to the raw html though I don't know why but this seems to be my culprit though I have no idea how to get rs not to render the html in this fashion.Thanks