Answer Questions

• NessDan How can I list out all the server names and their database list, running in my network (LAN).

  I'm using C# - VS2005. Best Regards Trinadh P. Can't see how this relates to SQLNS... But, SQL Server 2005 allows you to register instances in the Active Directory. You then query AD for the list. For more info, check out BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/75a4fbe7-0848-45dc-878a-068480a822e0.htm HTH... Joe ...Show All

• pappascd Poor performance with functions in selects

  Hi We have the following query that we were trying to execute and it performs very poorly... SELECT ITEM_ID , PART_NO , PART_TITLE , dbo . SFDB_NVL_VARCHAR ( DRAWING_NO_DISP , 'N/A' ) AS DRAWING_NO , dbo . SFDB_NVL_VARCHAR ( DRAWING_CHG_DISP , 'N/A' ) AS DRAWING_CHG , DRAWING_NO_DISP , DRAWING_CHG_DISP , REV_SOURCE , ITEM_ID AS ASSY_ITEM_ID , dbo . SFDB_NVL_VARCHAR ( DRAWING_NO_DISP , 'N/A' ) AS ASSY_DRAWING_NO , SFMFG . SFDB_NVL_VARCHAR ( DRAWING_CHG_DISP , 'N/A' ) AS ASSY_DRAWING_CHG FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL ORDER BY PART_NO The function SFDB_NVL_VARCHAR is nothing but a replication of nvl function of oracle. CREATE FUNCTION [dbo] . [SFDB_N ...Show All

• BortNE24 Can a non-admin user generate "Create database" script using SMO?

  I posted this thread in security also. But cross posting here with hope of getting a quick answer. I am trying to generate the create database script using SMO. The issue is the user account which connects to SQL server is not SA, SMO fails to generate the "Create Database" script with error User ZZZ\User1 does not have permission to run DBCC SHOWFILESTATS. Is it possible to generate a "Create database" script using SMO when you are connecting to the SQL server using NON-ADMIN account The user login is a part of db_Datareader role on the database. I tried following grant permission to the concerned account at server level Grant view any database, grant view any definition and grant view server state. Still i ...Show All

• Alexander Petukhov SSL with Multiple Instances

  Can SQL Server be setup such that one instance on a server is using SSL and another instance is not Or is SSL a server-wide all-or-nothing issue Dave A question I have in addition to this is whether or not VM can be used to resolve this issue. Dave I think it's a global setting that affects all instances: http://www.sqlmag.com/Article/ArticleID/26908/sql_server_26908.html This article is relevant and all I found That is what I've experienced, but I wanted an outside opinion; thank you for your response. Dave ...Show All

• boran_blok_edan Permit user to select columns for a report: why not use Dynamic SQL?

  I certainly have read lots of negative comments about using Dynamic SQL. And I have read some of the excellent web pages on the subject, such as http://www.sommarskog.se/dynamic_sql.html I have learned much from those discussions. But I still find myself faced with a problem for which Dynamic SQL seems like the only reasonable solution: letting a user select the columns to include in a report, and the sort order for the output data. In the application of concern, there are maybe seven columns that one might want to have in a report. If the user can choose any combination of these seven columns, and the order in which they are displayed (as well as the SORT order), it would be necessary to write something like 7! (7-factorial = ...Show All

• Jem21 get the list of catalog views in SQL Server 2005

  How to get the list of catalog views sys.* (sys.objects, sys.columns ....) in sql server 2005 select * from sys.objects where objectproperty(object_id, 'IsSystemTable') = 1 did not return the list as I've expected thanks Or :-) SELECT From Information_SCHEMA.Views Where Table_schema = 'sys' and Table_Type = 'View' HTH, Jens K. Suessmeyer --- http://www.sqlserver2005.de --- You'll want to use sys.all_objects instead, which includes the system views:   select * from sys.all_objects where     type = 'v'     and is_ms_shipped = 1     and schema_name(schema_id) = 'sys' -- Adam Machanic Pro SQL Ser ...Show All

• Joe-ept Get the ID to start from 1

  I have a sql-database with one table and in the table there are now around 70 rows... I wonder if its possible to "delete" or "remove" these rows so that the ID (the primary key) start to increase from 1 again....or am I forced to create a new table (and delete the one who exists now) to get the ID to increase from 1 again .... Tigers21 wrote: I have a sql-database with one table and in the table there are now around 70 rows... I wonder if its possible to "delete" or "remove" these rows so that the ID (the primary key) start to increase from 1 again....or am I forced to create a new table (and delete the one who exists now) to get the ID to increase f ...Show All

• IgorB Metadata Model in SSIS 2005

  Hi *, Can anyone tell me what Metadata Model Microsoft supports with SSIS 2005 DTS in 2000 was supporting OIM but I was not able to get some information about this topic on SSIS 2005. The reason I need to know is that I wanna import some metadata from Business Objects Data Integrator into SSIS 2005 and Business Objects is supporting CWM.... I would appreciate your help! Thank you, Steve Hi Jamie, interesting paper... I'll give it a closer look this afternoon. Thanks! Steve, Some thoughts here for you: http://blogs.conchango.com/jamiethomson/archive/2006/08/15/4353.aspx I'd be interested in reading your paper when it is complete. Do you think that would be possible -Jamie Well, you can read it but it's in Ge ...Show All

• Jeremy Lew Client Certificates with SQL Mobile replication

  I am currently trying to replicate a SQL Mobile 2005 database with a SQL Server 2005 database through web synchronization using SSL Server AND Client Certificates. On IIS, with "Require Client Certificates" unchecked, I can replicate fine. Once I turn it on, I get a message from replication saying "A Secure Socket Layer connection is required to access this site". I have installed a client certificate in IE, and can access the https:// servername /Ojt/sqlcesa30.dll site (I tried removing the client certificate, and I was denied access, then reinstalled it and it worked - so I think that part is working). Does anyone have any experience with this My production operating environment requires client-side certificates. ...Show All

• AndersBank Web Synchronisation Using RMO

  hi every body please help me out on this issue , since i am at final stage in finishing up the module. Previously i had done web synchronisation using RMO it is working fine at my office where i have an 2003 server which is an domain controller , publisher i have configured on the 2003 server , and i had subscribers on xp Machines where it was well working fine, clients were not on the domain , it is an independent Machine. we had an security error which was resolve by this same forum, ie the client windows login id should exists on the server too. only then synchronisation is happening at our office, Now the problem is i have an same setup , but the server 2003 is hosted at a remote place , which is not an domain controller, n ...Show All

• Tom Roberts Report export to Excel

  Hi, I need to export various reports created using SQL Server 2005 Reporting Services to ONE Excel workbook (with multiple work sheets - tabs - one for each report). Is there an option in Reporting services that can allow me that Thanks. If you need to "combine" other reports into a single report, create a new Report, add a rectangle with a subreport for each other report, and set PageBreakOnEnd for the rectangles. EDIT: Chris's response here is much better: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=793576&SiteID=1 Try putting a page break after each section. That should force data onto multiple tabs. Afraid the answer is no to bo ...Show All

• joeydj # of Full-Text Catalogs per SQL 2005 server.

  In SQL 2000 there was a limit of 256 Full-Text Catalogs per server. What is the limit if any in SQL 2005 I heard SQL 2005 lifted this limit of 256, but I can't find anywhere what the new limit is. I've also looked all over for an answer to this question. There must be some limit - it would be good to have a definitive answer from Microsoft that this has been increased and what it has been increased to. I do not believe there is an imposed limit, just a physical hardware limit ...Show All

• foocc running MDX as user

  When browsing a cube, you can browse using the credentials of a particular user or using any combination of roles. How can I do the same thing when executing an MDX query Is there some sort of "run as" type of clause in MDX OK, thanks for the answer. Unfortunately, we do not have AS2000 here. I guess I could probably build an MDX analyzer on my own. Thanks. Thanks for your answer, Mosha. Is there a way to set connection properties when executing MDX from Management Studio Or will I have to execute this using code in Visual Studio Unfortunatelly, in Management Studio it is not possible to set custom connection string propert ...Show All

• Helen999888 SQL sa adminstrator account

  This 'sa' account was used for the initial setup of the SQL 2000 database. However, over a long period of time this password to this 'sa' account was forgotten. How can I recover this 'sa' password Any impact for future upgrade to SQL 2005 without the current SQL 2000 'sa' account password Any advice thanks in advance. Lawrence If you're using another account with the sysadmin role assigned to it you can reset the sa password with the other account. sp_password NULL, 'NewP@ssw0rd' , 'sa' go I don't know of any ways to recover the password, but if you have another account that can backup the databases, you could backup/restore them to a fresh install of SQL 2005. Thanks, Sam Lest ...Show All

• JWedg Transaction Rollback Issues

  Hi We are loading data from one extract file in to 4 different database tables. When we are loading if the first table gets loaded without any issues and the second table loading gives any issues, we need to rollback the data loaded in to the first table also. In the package for achieving this, we are using Sequence container. In the sequency container we are having 4 different tasks for loading data to 4 different tables from the same extract file. We also tried setting the different transaction options given in the properties box of the packages, container and tasks. Also we tried the properties by setting as mentioned in the Test# 7 and 8 in the following URL http://www.sqlservercentral.com/c ...Show All