Answer Questions

• Kevin Berry help pls: procedure 'LoginListInput' failed because 'LoginListInput' is a table object

  When I execute the script on my machine it works but when we send it to the client it gives the error: procedure 'LoginListInput' failed because 'LoginListInput' is a table object Here is my script: use mosaikDB737 exec sp_MSforeachDB ' use [ ] insert into mosaikDB737.dbo.SearchOutput1 select sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS as searchedTxt , object_name(id) AS ProcName ,Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex (''%'' + ltrim(rtrim(sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text COLLATE SQL_Latin1_General_CP1_CI_AS)),char(13),''''))+1 AS Line, PatIndex(''%'' + ltrim(rtrim( ...Show All

• Atanu Maity Memory leak when importing large flatfiles?

  Hi, my process of incrementaly loading data from a flat file into my data warehouse is as follows: Step 1 : connect to the import and database sources Use the Flat File Source Task as Data Flow Source to connect to the DataFeed source. Use DataReader Source as Data Flow Source to connect to the database fact table. Step 2 : Add derived key values from the DataFeed source by using Look up Order by corresponding primary key used by the database fact table Step 3 : merge date from the converted DataFeed source with data from the fact table. A left outer join is used to permit missing records in the fact table to get filtered. Step 4 : Conditional split defines those records that are missin ...Show All

• Cody21 Execute a package on a remote server

  I want to execute a package that's found on a remote server as MSDB. The execute package task locally is not allowing me because i don't have a login permission i guess because i'm not on the same domain and my user account doesn't have the neccessary privelages to access the server. However i can execute sql statements and data flow tasks on the same server. Isn't it possible to use the login and password of the sql server in away or another to execute the package Thanks Thanks for your input. Unfortunately we are need to retrieve data from the SSIS package variables after execution. Alternatively we thought of storing all the return values to table and reading the table values af ...Show All

• wyktor SQL temporary tables and the datasource wizard

  Hi, I am designing locally processed reports using teh Visual Studio IDE. It seems the data source wizard cannot retrieve column names from a stored procedure that is returning data from a temporary table. Is their a quick fix on the IDE side to resolve this I know I can deal with it on the back end with permanent temp tables and SPIDs but would prefer not to. Thanks for any help. ...Show All

• Lim Tee Loong How to enforce SQL Server 2005 to use Worktable?

  I have a problem in SQL Server 2005. In some cases SQL Server produces an execution plan of complex query (8 joins of views, some of views contains couple of joins) which does not contain a woktable creation in tempdb. As a result time of query execution increasion for about 5 seconds to about 4 minutes. All necessary indexes are created. It sims all data located in cache. Is there any way to enforce SQL Server to create worktable Query SELECT [a0].[id],[a0].[Priority],[a0].[Heading],[a0].[DocumentDate],[a0].[LastName],[a0].[FirstName],[a2].[Name],[a1].[Position],[a4].[ContactTime],[a4].[Subject],[a0].[WorkPhone],[a0].[MobilePhone],[a0].[FaxNumber],[a0].[PrimaryEmail],[a5].[ContactTime],[a6].[Value],[a7].[id],[a7].[id_class],[a8].[id ...Show All

• WinFXGuy Locking Due To Select Query

  Hello Friends, I am totally new to locking field so I need your help. Below are my environment details: OS = Windows 2003 + Service Pack 1 SQL SERVER= SQL Server Enterprise Edition + Service Pack 4 My Organization is using an application that is designed in PowerBuilder. The problem is so many times this application got hang due to blocking in sql server. I have diagnosis the problem in the below steps, Waiting spid = 192 Select * from sysprocesses Where spid = 192 Dbcc inputbuffer (192) Then Select blocked,* from sysprocesses Where spid = 192 Returns 201 I had repeated the steps till I got BLOCKED = 0 , STATUS = sleeping and ...Show All

• James Boman Impact of ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE in production db

  System Configuration : OS : Windows 2003 latest SP SQL Server : Standard Edition, SP2 Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) DownTime : This is not a 24x7 kind of machine. It can have downtime Reference : http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1198044&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1026884&SiteID=1 I have been discussing this Service Broker issues in this forum for quite sometimes and sorry to bother u all again… To make things more clearer before im ...Show All

• alan279 How to write Select Statement inside CASE statement ?

  Hello friends, I want to use select statement in a CASE inside procedure. can I do it of yes then how can i do it following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this please give me a sample query. Thanks and Regards, Kiran Suthar forgive me but i think there's some problem with the database design.  can the content of those several tables be merge into one. i think this is a many to many relationship a member can be a member of many section and a section can have many members.   ...Show All

• Amelia_Dev Issues with database.dbo.tablename usage

  Hello, Can you guys think of all the possible reason why you wouldn't want 2 databases to cross reference each other using - database.dbo.tablename I can see one reason...Developers are going to go gagas using database.dbo.tablename for cross database updates/inserts etc. This will mean that the databases cannot scale anymore. What else Security issues, additional keystrokes required to use four part names, etc. I can also think of many equally valid reasons to separate data between different databases. Security issues, Backup/Restore Issues, etc. Saving Developers 'keystrokes' is a trivial concern and should be ignored. Make the decision based upon 'real' organizational needs and requirements. Wi ...Show All

• JGreene Subquery in SELECT statement before FROM

  Hello! I can use querys like these in Access: SELECT Field1, (SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1 SELECT Field1, (SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1 But when I try execute it with SQL Server Everywhere it says "Token in error = SELECT". Is there some kind of limitations to do this with SQL Everywhere SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys. -Teemu With SQL Server Compact/Mobile Edition v3.1, you can use sub-query in WHERE clause ONLY. However, with future versions of it (v3.5), you can use sub-query in WHERE, and FROM clauses. And you are using sub-query in proj ...Show All

• Greenstrike error occurred during local report processing on windows forms reportviewer

  Running a local report in report viewer on windows forms and get this error: Report exception: An error occurred during local report processing. Report inner exception: Failed to load expression host assembly. Details: Parameter count mismatch. Get error on first time run report but when refresh everything is OK! Any ideas anyone This is my code: ReportViewer1.LocalReport.LoadReportDefinition(rptdef) Dim rptDataSource As New Microsoft.Reporting.WinForms.ReportDataSource( "DataSet1" , data) ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local ReportViewer1.LocalReport.DataSources.Clear() ReportViewer1.LocalReport.DataSources.Add(rptDataSource) ReportViewer1.LocalReport.Di ...Show All

• Jeff Putz Dropping an auto numbered primary key and add a new one

  The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table. I need to write SQL to drop the current primary key and add a new one Say "RecordId" as the new primary key and which should be a autonumber too. any idea. thanks in advance droping and adding a primary key on a column wont affest its 'identity' property, so simply drop the primary key and add again with the new name u want... alter table tablename drop constraint oldpk alter table tablename add constraint newpk primary key ( id ) What's the point It is easy enough, just drop the column an ...Show All

• Dhananjay Help with Join Compatibility

  I was hoping someone could help me or put me on the right path to re-writing the join portion of this sql query in ANSI form for compatibility level 90. Im just not sure how to handle the three join statements and if they should go at the top in the FROM statement (dont know if that would mess up the rows produced). The query exists inside a stored proc. SELECT S.TYPE, S.LOCATION_TYPE, S.LOCATION_ID, S.PLANNED_ARRIVAL, S.PROJECTED_ARRIVAL, S.ACTUAL_ARRIVAL, S.PLANNED_DEPARTURE, S.PROJECTED_DEPARTURE, S.ACTUAL_DEPARTURE FROM TAB1 S, TAB2 RL, TAB LS WHERE S.LOAD_ID = @V_CURRENTLOADID AND (RL.REGION_ID = @REGION_ID AND RL.ROUTE_DATE = @ROUTE_DATE AND RL.ROUTE_ID = @ROUTE_ID) AND (S.REGION_ID = RL.REGI ...Show All

• Strini How Can I Database Backup using T-SQL From other instance of SQL Server 2000/2005????

  Hi All, I want Database backup from other instance of SQL Server using T-SQL. The Following T-SQL Stored Proc I using to do Database Backup...But if any errors raise on command line i am not getting any information about it....So How can i know that Database Backup statement runs successfully on command prompt or is there any way for Backup Database. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --master..xp_cmdshell 'C:\PackConfig\Sqlcmd.exe -Usa -Psedna -Smercury -dhr -Q"BACKUP DATABASE hr TO DISK = ''\\vista\e$\backup\hrfull1807.bak'' with init"' --master..xp_cmdshell 'C:\PackConfig\Sqlcmd.exe -SZenith2000-3 -Usa -Q"BACKUP DATABASE ACSZenith TO DISK = ''\\vista\e$\backup\ACSZenith18_07_200 ...Show All

• JoeSmith The "fastest" way to load string data

  I need to load a lot of data into a sql table as fast as possible. (All data is string data, CSV file). I have read that the "fastest" way to load data is using the "Fast Load" option of the OLEDB Destination but I have also read that the "SQL Server" Destination is fast. 1. Is there a general consensus as to which way is the quicksest load 2. The file is a CSV file. Would a fixed-format file be faster to read TIA, barkingdog Also be sure to read our whitepapers: http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisperfstrat.mspx And perhaps this webcast will ...Show All