ListChildren Method Does Not Return All Columns From Catalog Table
Dec 5, 2007
I am trying to use the ListChildren method to query the SSRS Catalog. I am using this as a dataset in a report. I'd like to see more columns, in particular the Description column, included in the dataset. Is there some way to tell ListChildren what columns should be included or is there some other method to obtain this information.
ListChildren returns ID, Name, Path, Type, CreationDate, ModifiedDate, CreatedBy, ModifiedBy, xmlns.
I'd prefer not to query the Catalog table directly.
Thanks
Tim
View 1 Replies
ADVERTISEMENT
Jan 26, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 6 Replies
View Related
Oct 1, 2015
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
View 4 Replies
View Related
Jan 13, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 4 Replies
View Related
May 12, 2015
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
View 13 Replies
View Related
Jul 25, 2012
Write a select Statement that selects all of the columns for the catalog view that returns information about foreign keys. How many foreign keys are defined in the AP database?
View 4 Replies
View Related
Apr 24, 2008
I have a written a dll in 2.0 that calls a webservice.
This webservice is used to authenticate users in Active Directory. I created a assembly to that calls this dll because of the diverse languages versions that will use it (from asp,vb6 on up) and all can get values from a stored procedure that calls that assembly. I works great. Until now, I have to add another function to my dll that calls the webservice and returns the Users Full name from Active directory for electronic signitures. Okay I added to the dll then tried to reconstruct my Assembly and stored procedures and recieved the following error. "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void"
I want to keep all these Active directory call all in one place so I can be consistant in all the different applications. I was reading about UDF but that could get messy as I have a config file for the dll that allows the user to dynamically change the url for the webservice. Any suggestions/help will be greatly appreciated
View 3 Replies
View Related
Apr 26, 2006
Hi guys,
How can i read in my C# client application values returned from sqlpipe
send(string)/send(reader method from CLR stored procedure.....following
is example of a stored procedure.I want to read datetime string in my
C# client
Thanks
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PrintToday()
{
// Put your code here
SqlPipe p;
p = SqlContext.Pipe;
p.Send(System.DateTime.Today.ToString());
}
View 3 Replies
View Related
Apr 18, 2006
I've been trying to create an async transform component and one of the things I'd like to do is add a new output and/or columns to existing outputs for each input column that is selected.
I thought that the SetUsageType method would be a good place to do this as it will allow me to delete output's and columns based on the user's selections but, despite the fact that my code doesn't actually throw or cause errors, when I return from the SetUsageType method I'm informed that error 0xC0047041 (DTS_E_OBJECTNOTINHASHTABLE) has occurred .
I pared the method down to an absolute minimum and found that a call to GetVirtualInput is enough to push it over the edge even if it does nothing! What is wrong here?
Thanks
Charlie
Sample:
public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput,
int lineageID, DTSUsageType usageType)
{
if (usageType == DTSUsageType.UT_READONLY || usageType == DTSUsageType.UT_READWRITE)
{
ComponentMetaData.InputCollection[0].GetVirtualInput();
}
return base.SetUsageType(inputID, virtualInput, lineageID, usageType);;
}
View 9 Replies
View Related
Jan 25, 2008
I have a web application the instantiates the reporting service service and calls the listChildren method with the root "/" folder and recurse set to "true". I capture the collection of items and then in subsequent code make the assumption that the collection has the folders and reports, data sources, resources in the order that the recursion from the root found them. This has been true for a year of multiple users on this web application every day. Suddenly earlier this week the collection result from listChildren started returning the items in an order it could not possibly have found them from recursion, reports deep down in the folder structure were returned ahead of the folders above them. Nothing had changed except the reporting server had 10 patches put on it overnight. We rolled back the patches and rolled them forward one at a time with reboot and testing, which seemed to restore the original behavior of listChildren but then the next morning it was back to being bad again.
SO..... I stayed up late rewriting the code to move the recursion into my program instead of trusting listChildren to do it.
BUT..... Shouldn't collection returned from listChildren("/",true) be in the order of the recursion? Arn't collections the immutable unless the program changes their order? So... What order is listChildren supposed to return? Is listChildren actually recursing through the folders and their contents, or is it just pulling the info out of a table in the reportserver database and returning the items in whatever order that table has them?
View 1 Replies
View Related
Jan 16, 2008
I'm developing an asp.net app that will list the reports available on the report server. The app works OK on my local machine but when I deploy it I run into a permissions problem:
Exception Details: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: The permissions granted to user 'NT AUTHORITYNETWORK SERVICE' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'NT AUTHORITYNETWORK SERVICE' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute() at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems) at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
My code uses the DefaultCredentials:
ReportingService2005 rService = new ReportingService2005();rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
CatalogItem[] catalogItems;
catalogItems = rService.ListChildren("/", true);
I can avoid the exception if I use the Administrator account:
System.Net.CredentialCache cache = new System.Net.CredentialCache();// Add a NetworkCredential instance to CredentialCache.// Negotiate for NTLM or Kerberos authentication.cache.Add(new Uri(ReportServer), "Negotiate", new System.Net.NetworkCredential("Administrator", "<password>", "<domain>")); //Assign CredentialCache to the Web service Client Proxy(myProxy) Credentials property.rService.Credentials = cache;
I'm pretty sure using the Administrator account here is not the best practice. Any suggestions?
Thanks,
Al
View 3 Replies
View Related
Jul 20, 2005
I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)
View 6 Replies
View Related
Jul 20, 2007
I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.
Any help is greatly appreciated!
Kind Regards,
Mat
View 7 Replies
View Related
Oct 23, 2014
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports" ... Any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher." URL....Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMONDPAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
View 1 Replies
View Related
Aug 11, 2004
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
View 2 Replies
View Related
Mar 11, 2008
Hi,
What catalog table has the number of rows of a table ?
View 11 Replies
View Related
Jul 23, 2005
We hear about catalog table in documentation but is this the same asdatabase schema?What is the definiation of catalog table?What does it pertain to?Thanks--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Nov 20, 2012
Have a table with multiple rows, some columns that are bit (0,1).
Code:
INSERT INTO UserAccessRights (
UserID, AllowPhone, AllowTelevision, AllowComputer, AllowVisitation)
SELECT 434, 1, 0, 1, 1 UNION ALL
SELECT 512, 0, 0, 1, 0 UNION ALL
SELECT 982, 0, 1, 1, 1
Now, I'd like to run the query one user at a time. I'm missing something here, as I can't quite figure out how to only reutrn the results the where the value in the column = 1.
My pseudocode query would be something like...
Code:
select uar.userID, sc.columnName
from UserAccessRights uar
JOIN sc.syscolumns sc ON ?? = ??
where uar.userID = 982
and [sc.columnname] = 1
And I would hope to return something like..
Code:
982, AllowTelevision, AllowComputer, AllowVisitation
View 2 Replies
View Related
Jun 29, 2012
I wanted to know that when I create a table using SQL Server, does the table metadata like schemaname and catalogname is stored in memory or in hard-disc. I am trying to extract schemaname and catalogname when the database is down or correctly when I have no connection string. I know about sysindexes, systables etc, but i am not able to understand whether it stores the data which is useful to me like catalogname.
sql query to extract the column names from sysindexes?
View 2 Replies
View Related
Aug 27, 2007
Hi everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
View 12 Replies
View Related
Feb 20, 2006
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
View 2 Replies
View Related
Jan 19, 2008
I have a MyNames table which contains over 100 columns. The Name column may have the same name in many rows. The data looks like this.
Name..........Factor1..........Factor2...
John..........X................Y
John..........X................X
Mary..........T................Y
Mary..........V................Y
I need a result table containing all 100 columns from MyNames but only the unique names in the name column. I don’t care which name row is used.
Name..........Factor1..........Factor2...
John..........X................Y
Mary..........V................Y
I have tried DISTINCT and GROUP BY.
DISTINCT will unique on every column listed so I will end up with the name repetred in the Name column.
SELECT DISIINCT Name, Factor1, Factor2 => I do not want the Factor columns involved in the distinct. This will cause the same name to appear in more than one row.
Group by will requires that I place the remaininf 99 columns in a function in order to have the columns listed.
SELECT Name, MAX(Address)... => adding 99 fields.
Is there a way get only the unique Names with the 99 other columns with out having to manually put the other 99 columns in functions?
View 13 Replies
View Related
Nov 3, 2006
I want in one query return columns from tables where exist in different sql bases.
In MS Access can link tables from many defferents sql bases or other MSAccess bases and finally can make a simple query where return columns from tables where exist in different bases.
How can i have the same functionality in Visual Basic .Net 2005 (or Visual Basic 6.0)
View 1 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Sep 13, 2006
Given in a record in from a Table called WorkSchedule:
idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8
I need to return for each record in the WorkSchedule Table
1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006
8 8 8 8..................8
Please help.
Thank you.
-Robert
View 5 Replies
View Related
Aug 12, 2013
I have a database that has a table that contains basic demographics
•First_name
•Last_name
•Address
•City
•State
•Zip phone
•email
•dob
•etc
•etc
Contained in this table are a lot of duplicate names, and even different addresses. I would like to return an entire row of UNIQUE information based upon “first_name + last name”. Now I know just enough of sql to pull out unique first + last names, but not the entire row based upon this unique information.
View 5 Replies
View Related
Mar 9, 2008
I have FirstName,LastName columns in the database.I need to return FirstName,LastName as Name to client(as a single column).
View 3 Replies
View Related
Jun 2, 2014
Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?
View 3 Replies
View Related
Feb 19, 2008
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0
) AS p
GROUP BY p.id
View 9 Replies
View Related
Mar 24, 2008
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
View 1 Replies
View Related
Oct 11, 2006
Hello,
I have a Sql Server 2005 database with many tables, each with millions of records within them.
They all have a Receive Date field, with records going back 10 years or so.
What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?
~Le
View 2 Replies
View Related
Oct 21, 2006
Hi,
Sorry if these is not the right forum for this:
Im using the data adapter method FillSchema works fine in MS Access but SQL Server Express Edition it doesnt get the primary key of the table. For example if I try to use the Find method of the dataset I get this error Table doesn't have a primary key? but in MS Access these do not happen.
This is the code that Im using:
Dim sql As String = "SELECT admin.rents.id, admin.rents.transaction_id, admin.rents.item_id, admin.rents.description, admin.rents.due_date, admin.rents.return_date, "
sql &= "admin.rents.amount_to_pay, admin.rents.paid_amount, admin.rents.tax, admin.rents.type, admin.rents.deposit_id "
sql &= "FROM admin.rents INNER JOIN "
sql &= "admin.transactions ON admin.rents.transaction_id = admin.transactions.id "
sql &= "WHERE admin.transactions.customer_id=?; "
RentsDA = New OdbcDataAdapter(sql, cn)
With RentsDA.SelectCommand
.Parameters.Add(New OdbcParameter("@customer_id", OdbcType.Int)).Value = TextBox1.Text.Trim
End With
RentsDA.FillSchema(ds, SchemaType.Source, "Rents")
RentsDA.Fill(ds, "Rents")
It's because of the joins?
I need to configure something in the sql server?
What is the problem?
View 1 Replies
View Related
