Answer Questions

• MrZap SQL 2005 default installation path

  In sql 2000, during the installation an option was given to allow the installer to install to a different drive or path, however in sql 2005, I do not see an option. During the installation it does give you the option of installing via the command line interface, however this would be the last option. If this is the only method of installing sql 2005 for a different data drive path, then please provide the command line to do this. I've used the command line osql/isql to restore db's in sql 2000/msde and it's a pain. What about the 90 folder, can we select where to install that Every thing that you can do with the command line install can be accomplished using the UI install as well. During the inst ...Show All

• Paula M Distributor cannot connect to subscriber

  I am setting up my 2005 Replication system... publisher = 2005 sp1 Subscriber = 2005 sp1 I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away. Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections. I am not sure where to look at next... I never had this issue in 2000. Ok... I did as su ...Show All

• zhihao Could I Resolve a KPI's Data Value in a SSIS Script Task?

  Hi, thanks. I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll. Thanks. But How I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll. Anyone know this Thanks! ivanchain wrote: But How I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll in ...Show All

• bobslayer You are not authorized to view this page

  Our Reporting Services was working fine i think maybe the secuity update messed our server now we can't see out reporting default website we messed around with the IIS and we were able to get to show the home pages of the reporting services when we click on the report it gets "not authorized to view pages" we messed around more with IIS and get can't see the homepage of Reporting Services the secuity patch was framework 1.1 we did remove the patch but the same problem we are not sure if this was the cause or not anybody know what i can do to resolve not access the website and fix You are not authorized to view this page Need help asap James, Were you able to solve this problem We are basically having ...Show All

• nbrege the owner of the table

  Hello, I need to user full name for the table as seen below. SELECT @RowCount = COUNT(*) FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID T2 may be re-created by different users, how can I get this working for all users if the creator is not dbo Which version of sql server is this SQL Server 2000 In SQL Server 2000, you can create a view under dbo schema, gfrant SELECT on view to users and change the definition whenever the table gets recreated. You can do the same in SQL Server 2005 via a synonym. ...Show All

• Stephen_Sbh Reusing package configuration file across all packages in a solution?

  I have 5 packages in a solution. For 1st package, I add a package configuration file (xml) named common.dtsConfig containing only Database Connection configurations. For the same package, I add another package configuration file names first.dtsConfig containing configurations specific to 1st package. Now for 2nd package, when I reuse from existing package configuration (common.dtsConfg) with same name, it allows me to do that. I also create a package specific configuration file for 2nd package. And so on for all 5 packages. This works fine for development. If my database user/password changes, I edit onyl one file i.e. common.dtsConfig. But, when I want to create the deployment utility, it fails by throwing error that "cannot cop ...Show All

• ken9865432 Date Select Query - select between two dates

  have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003. i have tried with the following query, i need help putting my queries together select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004') when i run this query i get this message Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15 please help mustfa ...Show All

• PricklyPear SQL Express 2005 going idle and not allowing connections.

  Just upgraded to SQL Express 2005 from MSDE for Firehouse. Upgrade went great and the db is accessable and running fine. Except when not used for a while. Then I need to restart the db to allow connections of any kind. If I don't I get an error that the db is not accessable and I can't connect. Everything runs fine after until it goes to "sleep" on me again. We upgraded to make use of the 4GB limit as we were at 1.9GB and MSDE was getting a little unhappy. Any suggestions Is this a "feature" and if so, is there a script I can run to kick it into working mode again Thanks Chris hi Chris, perhaps you are getting troubles with the Autoclose property of the database (set as default for MSDE and S ...Show All

• Fly By Update Trigger

  Hello i have two table parent & child, if i'll update the foreign key in child table & updated value is not exits in parent then i have to update it as a null. for that i have to write a trigger can any one help me for that. You can pretty easily use an instead of trigger to do this. (Note that it makes using an identity key a bit more tricky. You will have to get the last inserted row via an alternate key instead of scope_identity()) drop table child drop table parent go create table parent ( parentId int primary key ) create table child ( childId int primary key, parentId int references parent(parentId) ) go create trigger child$insteadOfInsert on child instead of insert as begin set nocount on ...Show All

• Lehmberg sql query to return multiple records

  Hi, I'm trying to retrieve some records from an SQL database. I've a table named CustomerOrder with three fields - custID , productname and quantity No keys in the table. it's row based approach. every custID can have multiple entries for different products. Example: CustID ProductName Quantity 1 Product1 2 1 Product2 3 2 XXX 1 2 Product1 2 1 Product3 4 I would like to write a query that gives the result as follows : CustID ProductName Quantity 1 Product3 4 2 Product1 2 Meaning that, query has to retrieve only one record per custID based on highest quantity. sel ...Show All

• Julia Semenova Local cubes connection

  Hello, I have created 3 local cubes which resided in the same .cub file. The cubes created from AS 2005 cubes. How can I make ADOMD connection to all 3 cubes at the same time in my client application (C#) The problem is that Initial Catalog (database name) in connection string in case of local cubes is the same as the cube name. Thanks, GB Still have the problem. My local .cub file created by XMLA script contains 3 databases with 7 cubes each. When I open this .cub file in MDX Sample application I can make a connection and MDX query to any of 3 databases and accordingly their cubes. But in my C# application when I try open connection to all local databases in a loop from the same .cub file I got the error just aft ...Show All

• E. Rijsdijk SqlDMO connect error from Microsoft Operations manager (MOM)

  MOM connects to 19 instances without issue and fails on one (1). The failing instance resides on a server with two(2) other instances that do not fail. Security is setup the same on windows and sqlserver. Receiving error in MOM: "Could not connect using SQLDMO to SQL Instance WEB. However, the instance is currently running." Presents in Event Viewer as: "18452: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." Troubleshooting so far: Created simple SqlDMO VBScript that uses a trusted connection. This works fine from my desktop to all twenty (20) instances yet fails on the one server that MOM is running on. Any ideas The process which is trying the c ...Show All

• Ranal sp_helptext in select statement

  Hi, I need to get one trigger script and save it into a table or files....anyway this works well exec sp_helptext 'triggername' But, how can I save this rows into a table because I cannot execute this Insert into mytables select sp_helptext 'triggername' Can I work around this cheers, You can do: Insert myTable exec sp_helptext 'triggername' Assuming that myTable is defined properly. If your goal is to get the trigger definition for saving, you can reverse engineer in query analyzer or the management tool, depending on your version. If you are putting the values into a table, for some reason, you need to be aware that sp_helptext returns a number of rows and only a single column. If yo ...Show All

• furjaw Update Table from Flat File source

  Hi all, I'm trying to work out how to update a table from a flat file source. I could use the simple way of using OLE DB Command with UPDATE <tblName> SET Column1 = , Column2 = , <etc> WHERE ID = then using the mapping to join the parameter fields. However, this (maybe!) a slow process when you have potential 500k+ records in the source file (and approx 150 files to process!) What I would like to do is the following statement but I'm not sure if this is possible UPDATE <tblName> SET Column1 = sourceColumn1, Column2 = sourceColumn1, <etc> FROM <sourcefile> WHERE ID = sourceID I've also thought about importing the sourcefile into a Recordset Destination and trying to use the var ...Show All

• HenryM679 Precision and Scale in a calulated column

  How do I set Precision and Scale in a calulated column I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please Thanks, Scott You need to first DROP the computed column, then ADD it back, and you MUST include the computation formula. Do something like this: USE SIR; ALTER TABLE dbo.Table_1 DROP COLUMN Results; ALTER TABLE dbo.Table_1 ADD COLUMN Results cast( ( [put formula here] ) AS decimal(6,2))); Thanks for helping. I tried the following: *********************************** Use SIR ALTER TABLE dbo.Table_1 ALTER COLUMN Results nPRECISION(6,2) Go ************************** ...Show All