.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Minimum Permissions Needed To See Cdc tables In System Tables Folder

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
What are the minimum permissions needed for a user to see the cdc tables in the System Tables folder in SQL Management Studio?DJ Baby Anne's Biggest Fan................

View Complete Post

More Related Resource Links

Help needed in selecting coulmns from 2 tables using entity framework


Hi all,

I have two tabels as mentioned below. I am using entity framework and vs2010.I am not able to write linq query to get data from both the tables. there is one to many relationship(for one category ther can be multiple articles). Please help me out as this is very urgent. Thanks in advance.

1) Article

ArticleID bigint Unchecked
CategoryID int Unchecked
ArticleName varchar(500) Unchecked
ArticleDesc nchar(1024) Unchecked

merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

clean up system tables like sysmergepublications , sysmergesubscriptions...

Hi ALL,            How to clean up system tables like sysmergepublications , sysmergesubscriptions... Bcoz  in my subscriber database i still see the previosous publihser details in these system tables Same thing in publisher database....how to remove all these data .....when i reinitialize database at subscriber ..and if i see the local subscriptions in ssms there are subscriptions from publishers to which it is subscribed before..and i cant delete this....

SQL Management Studio 2008, does not display tables with specific permissions


Prev Reference Question: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/a6166b82-1e1a-4771-952d-a7b54e0b0f23?prof=required

I see the same problem with SQL 2008 Management Studio. I have different schema defined in my database, all the schema permissions work just fine in SQL 2005 but when viewed from SQL 2008. I cannot see tables for which i have given inidividual permissions. I have given SELECT permissions for those tables.

for example.

schema: dbo, schema1, schema2 ....

user: user1 (schema1 owner), user2 (schema2 owner), user3 ...

now i have given SELECT on schema1.table1 to user2

this works fine and i m able to see the schema1.table1 when login as user2 in SQL2005. I have tried your solution for giving "View Definition" permission as well. But this doesnt work in my case.

Seems to be some default permission settings with SQL 2008 ??

Any help/Suggestion will be appreciated

Multiple Tables needed for Report Builder Problem


I have 3 tables I need to join: Voucher, Voucher_Detail, and Place
Voucher is related to Voucher_Detail by column voucher_run_id
Voucher has no relationship to Place
Voucher_Detail is related to Place by column place_id

I have solved the one to many relationship problem I was having between Voucher and Voucher_Detail by adding a New Named
Query called LeftOuterJoin.

In my DSV, the Voucher table links to the LeftOuterJoin which links to the Voucher_Detail which links to the Place

When I go into Report Builder, as soon as I select Voucher_Detail ( which is the table that has the most columns I need in the report),
the Voucher table disappears from view.

Not sure what I have missed. Obviously I need access to all 3 tables' data.

I need help with adding membership tables to app_data folder database


The following code is used to add the asp membership tables to an already existing database instead of creating a seperate database for membership and having two databases in the application.

aspnet_regsql.exe -S .SQLEXPRESS -U username -P password -d databsename -A all

It works fine by adding the membership tables to the existing database that is located in the c drive, program files  sqlserver folder.

But my database however is not in the c drive but located in my asp website app_data folder, so the above code does not give any error and the tables are not added after I run it.

Please how can I now add the membership tables using the above code when my database is located in the website app_data folder, thanks

Getting server non system databases, getting database tables, getting table columns


I use next code to get server databases

string connectionString = "Data Source=DENIS\\SQLEXPRESS;Integrated Security=SSPI;";
DataTable tables = new DataTable("Tables");
using (SqlConnection connection =
    new SqlConnection(connectionString))
  SqlCommand command = connection.CreateCommand();
  command.CommandText = "sp_databases"; 
  command.CommandType = CommandType.StoredProcedure;


this.dataGridView1.DataSource = tables;

I've got many databases :

- model ( i guess it is system )

- master ( i guess it is system )

- tempdb ( i guess it is system )

- db1 ( my table )

and etc.

How to determinate what table is system.



FROM information_schema.Tables

So how to determinate what table is system or not.

Can I get tables of database

SSIS : Pulling large number of tables from Source system



we are migrating a datawarehouse application from asp - sql 2000 to dotnet2.0 - sql 2005
currently we are in the process of coming up with an optimum solution for backend design using sql server 2005.
This application pulls data from 3 different source systems (oracle,sql server and mainframe db2).

we have 3 different staging databases corresponding to each of the source application database.
For eg : RetailGarments application is an oltp application with backend oracle database .
we have a staging SQL server database called Retailgarments_stg for our datawarehouse application.
similarly OnlineTravelBooking is an OLTP application with DB2 database .we have corresponding
OnlineTravelBooking_Staging database for staging this data.

Each of these source systems have more than 100 tables and we are currently pulling most of the
tables to our side from the source .

These staging data from different datasources are accumulated in to a cleansed Schema database
which is used for Reporting and other OLAP requirements.

Our question is related to data pull from the source system.
Current SQL Server database pulls these data from sorce system using Stored proc dynamic queries containing
link server openquery.

We would like to improve the performance as part of sql server 2000 to

user access to system tables



I've some developers who want to be able to select from some system tables. I'm reluctant to give this in case we get any locking issues. As an alternative I'm thinking of creating views with nolock hints, and granting them access to this. e.g.

CREATE VIEW vw_sysjobs
select * FROM sysjobs WITH(NOLOCK)		

Is there a better way of doing this ? Am I right to asume that locking would be a potential problem on these tables ?

(SQL Server 2005)






SQL Server 2005 SP4 Install Fails - error log references replication system tables


The OS is Windows Server 2003 R2 SP2 (32 bit).  Trying to install SQL Server 2005 SP4 (current version is 9.00.4035).  All SQL products update without error, except for the Database engine.  The error listed in the summary.txt file in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix is:

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 5000
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log
Error Number              : 29537
Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Update or insert of view or function 'dbo.syspublications' failed because it contains a derived or constant field.. To continue, correct the problem, and then run SQL Serv

On rebuilding indexes of MSmerge_Contents and other replication system tables



In my previous question leads me to the index rebuild of MSmerge_Contents system table. However, I am still not sure what the explicit and correct way to rebuild index is. 

Refering to examples in BOL, the syntax should be something like the following:

ALTER INDEX ALL ON msmerge_contents

After checking system.indexes, I found that the fill_factor of 4 indexes of msmerge_contents are all 0. Does this mean I do not need to specify the value of FILLFACTOR? How about another 2 parameters, i.e. SORT_IN_TEMPDB and STATISTICS_NORECOMPUTE?

Do I need to specify ot

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Temporary Tables - MS SQL Server

Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Using a trigger or anything else to populate two tables


Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.

The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?

Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!


Regards, Monsterbadboll

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

SqlDataSource UpdateCommand using 2 tables


I have two tables

Trans  with fields TransID, Date, CustomerID and some other stuff

Customer with fields CustomerID, Name, TaxId

On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.


I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.

Let's say the user changes the date, then I need to do an UPDATE.

UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.

I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".


I tried taking out the set for CustomerID and I still get the error on page.


Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.


Do I need to do something with Control Parameters?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend