Answer Questions

• Sen_p_kumar I may just have to kill myself....Oracle

  I have a stored procedure in Oracle. I build the sql beautifully to call it with Openquery "select * from OPENQUERY(MYLink,'DECLARE P_DATE VARCHAR2(20); BEGIN P_DATE := ''15-Sep-2006 17:26:09''; EXEC poPT (P_DATE); COMMIT; End;') "Server: Msg 7357, Level 16, State 2, Line 1" "Could not process object 'DECLARE P_DATE VARCHAR2(20); BEGIN P_DATE := '15-Sep-2006 17:26:09'; EXEC poPT(P_DATE); COMMIT; End;'. The OLE DB provider 'MSDAORA' indicates that the object has no columns." I cannot seem to find away to make this work. I wanted to return just the record count to make it happy but that did not work either. The linked server is fine, the grants are fine. I even tried this as a function in orac ...Show All

• silentk62 UNION statement that works in SQL server but not access

  Hi, Is there a way I can get this select Union statement to work in Access. SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge' AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router I get this message in Access: Query input must contain at least input of query Thanks for any help Chris   SELECT '' AS Router from IPVPNRouterUpgradeCharges UNION SELECT   Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND Router <> 'IPVPN Lite' AND Router <> 'VPN Bridg ...Show All

• noob_vber Flat File into multiple SQL 2005 Tables.

  Probably a stupid and regularly asked question but I can't seem to find an answer, so here goes, we have 16 .txt files, some with over 350 columns. That info from each individual file needs importing to multiple sql tables. need to look at sql table1 does record exist if not create new then add in data once its been transformed eg datetime from yyyymmdd into datetime values [managed to get this using derived column] for first 20 columns, otherwise do update for the 20 columns... then look at sql table2 and repeat for next n columns.... So I was wondering is it going to be better to write this as a dtsx package if so can you point me to an example or should I just write the code as part of a code behind page that scrapes t ...Show All

• 2162 User accounts based start-up

  Hi, I would like to know whether it is possible to start-up SQL Server for selective user accounts in Windows XP. Say I have 2 user accounts - A and B. I want A to use SQL Server 2005, but dont want B to use the engine. Is it possible my any means Thanx in advance. Best Wishes. SQL Server runs as a Service on Windows and is typically configured to start when Windows starts, even if no user logs in. (It wouldn't be much of a Server if someone had to log in to start the Service.) I wouldn't suggest doing what you're proposing, there are other ways to handle it. You can easily manage users access to SQL Server using Logins. When you install SQL Server, you create a Login for User A that has the appropriate permiss ...Show All

• Fran&amp;#231;ois PIETTE Decimal Places error on opening SQLServer table from Access Project

  One single table when openen from a MSAccess project file gives error: The setting for DecimalPlaces property must be from 0 through 15, 255 for Auto (default). After which the table is openend normally. I don't recall setting Decimal Places to the table. The only numeric fields are two INTs that are also ID's (PK and FK). This error occurs also when recreating the db project. These fields can not be edited eitther from MSAccess nor Enterprise Manager. What can be done on the table, resp. MSAccess to change this wrong forum please tell me. You can see and edit or remove this property using MS' SQL Query Analyzer. Open the 'object browser', navigate to each col ...Show All

• clint 2 execution package task-SOS

  hello to every body.. well i have a problem.. well i created a package the task of which is to update thedimensions and the fact tables from the tables of source. i created a configuration file with all the parameters that are nedded (connections managers; parameters of packages).this config file will include all the paremeters need for the execution of other packages In that package i call an other package (execution package task). this package normally will take the parameters of the parent package isn't it (i put the parameters of this package to the config file of the parent package). i must also note that for the second package called there is also a package... i must also note that i have 2 DWHs (a and b) for testing. ...Show All

• Meat Help with writing sql statement

  I need some help writing a query. I have a text file that will be imported on a weekly basis, with 1000+ rows and 5 columns. This i need to import into table tblECR which i have added 2 of my own columns. The problem is the text file will arrive with current data and new data. The current data may or may not have changed (dates, status etc). How do i go about importing the new data and updating the existing data with the new fields, without deleting the data in the 2 columns i've added. I'm using vs 2005 with a sql 2005 express database. This is the code i'm using to import the data currently. Clicking the button more than once will obviously just import all the data into the database again. Private Sub CustomerDataToolStripMen ...Show All

• anee sql beginner - saving views to a file

  is there anyway to automatically save a view to a file at a scheduled time Thank you for any help or pointing me in the right direction. thanks for all the help so far. I was wondering if there is anyway to do this inside of query analyzer Yes, I am. Yes - just change the results in QA for query results to "Send to File" instead of "Grid" or "Text" Then when you run the query you'll be asked for a file name. I thought you wanted to automate the process. By the way, if this answers you're question, make sure you mark it as "answered" so that others can quickly locate the topic ...Show All

• tranders Is there support for xml path in SQL Server 2005 Express Ed?

  Hi. I tried a for xml query in SQL Server 2005 Express Ed. but it says that there is an error near path. I tried the rest of the statements in the query and they were fine. So this led me to ask if it there is for xml path support in SQL Server 20005 Express Edition Just in case this has anything to do with it, I have an installed version of SQL Server in my PC too. But I didnt find that in the requirements in installing the Express Edition. I really need to know this...It's kind of hard having to do the queries in for xml explicit, which works by the way. yes, thank you.. I was more careful this time.I still have to check it with the VPS though. Thanks a lot, its reassuring to know that at least that's ...Show All

• Harish.A Using FreeTextTable Conditionally

  Hello all! I have a stored procedure that accepts a parameter @SearchTerm that may be null. In my WHERE clause, I check for all records where @SearchTerm is null, or are LIKE '%' + @SearchTerm + '%' when not null: SELECT (some records) FROM (some tables) WHERE @SearchTerm IS NULL OR (some fields are LIKE '%' + @SearchTerm + '%'); I am trying to use FTS with a FreeTextTable to make our search ability more robust: SELECT (some records) FROM (some tables) JOIN FreeTextTable(tableName, columns, @SearchTerm) ft ON ft.[KEY] = tableName.[KEY] However, because @SearchTerm can be null, meaning the user is not filtering our records by a search, doing a JOIN to a FreeTextTable throws an error (cannot have null as search string). Ple ...Show All

• Michel Racicot Sending SQL 2005 dbmail when using Application role

  I am attempting to use dbmail from an application that logs in to my database using an application role.  Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it:  I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights. Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail.  But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or y ...Show All

• Andrew Buyan Active Directory Sql Servers

  Hi everyone, how to retrieve programatically the list of the AD's Sql Servers You've been looking at SMO classes but it seems that it depends of your own workstation. Thanks in advance Your best bet is to do something like an LDAP query specifying the OU (Organizational Unit) the servers were defined with. The EnumAvailableServers method will scout the SQL Server browsers on your network and bring back everything that responds to a ping, whether the server was defined in AD or not. (Joe Developer may have SQL Server Dev Ed running on his laptop, and you're probably not interested in that server.) Here's a starting point for you: Dim mySearcher As DirectorySearcher Dim resEnt As SearchResult Dim colRes ...Show All

• Pavel M. Attribute Hierachies

  Hi there. I have a problem that I have been struggling to understand. Here is the situation. I will use a time dimension just as an example. If I build a time dimension containing 2 Years of dates from 2005 to 2006 with 3 attributes: Year, Month, Date and set my attribute relationships as Date ->Month and Month -> Year. In management studio I can drag over Month and then Year (in that particular order ) and it will give me the correct results. For example if I expand the Month of January 2006 the year column will show 2006. Now If I filter the year column for the year of 2006, you would expect that the month column would only contain the months of 2006, but it doesn't. It still has months for 2005. Now if I rebuild that dimensi ...Show All

• KennyAC Sending and checking Null value on IIF

  I Want to check if i have null value (not empty string) on param if it is null i would like to send back null value (not "") I Just wrote small code probably not correct How should i correct it =iif(Parameters!SelectedSite.Value.equals(NULL) ,NULL ,Parameters!UserSites.Value) nop, I tried, nothing on returning in RS equals to "" (string empty) Do you have another id Does your parameter accept Null Values of course, The checking goes good the sending is problematic... :( I think the correct way is: =iif(IsNothing(Parameters!SelectedSite.Value),Nothing,Parameters!UserSites.Value) ...Show All

• Rhubarb Slowly Changing Dimensions

  I am new to SSIS and I am investigating using the Slowly Changing Dimension transform. The data source that I receive is a daily snapshot of the external source system table. I need to store the history of the entity attributes (Type 2 SCD) and I am using the Start / End Date mechanism. When an entity (identified by the business key) is no longer received in the source snapshot, I would like the data flow to update the End Date of the current row to show that the entity has now expired. Does anyone have any suggestions for a good way to achieve this NB: Changing the source system extract to include and flag expired entities is not an option for me. Many thanks Graham Hi Graham, ...Show All