difference between set @flag =1 and set @@flag = 1
Hi What is the difference between set @flag=1 and set @@flag = 1 in SQL Server? Thank you ...
sql-server-2005
How to restore sql server history-files with vb.net?
How can I restore history-files of a SQL Server 2005? ...
whether sitescope can monitor MS SQL 2005 Analysis Services which uses olap/mdx data manipulation?
1. whether sitescope is able to monitor for database failover? 2. whether sitescope can monitor MS SQL 2005 Analysis Services which uses olap/mdx data manipulation? ...
Continue flow on certain specific Error in SSIS
Hi, In SSIS 2005, I am using the FTP Task. I have a flow where when the package runs, it retrieves any files in a specific folder from FTP to a local folder. Remote folder path is set by variable such as /root/abc/*abc.txt The task works fine if there are files in that folder matching this criteria. If there are no files, the task fail...
Can I: loop through sql select and then fire off for each loop?
Hi All, I'm probably missing something (looking at it too long), but in a stored procedure can I select all distinct values from one table and then do a for each loop based on each of those returned rows, which themselves build up sql statements based on the distinct values? Cheers. ...
Using Derived Column to create a date value in the format YYYY-MM-01 00:00:00.000 for each row
Im having a problem with derived columns in SSIS. When in SSMS i can set a column to have a default value using the following code: (dateadd(month,datediff(month,(0),getdate())-(1),(0))) and when data is entered into the database it will give it the timestamp of the previous month in the following format for example: 2010-09-01 00:...
How to alter a column from char(2) to varchar(5) on 1000 Tables in 100 Databases on 5 servers?
I need to come up with a script to alter a column from char(2) to varchar(5) on 1000 Tables in 100 Databases on 5 servers. The column will most probably have 'Office' in the name. However: The column may have different name, not necessarily containing 'Office'. Sometimes a column is indexed, sometimes not. Sometimes a column is a Prima...
Recursive SQL to Find Critical Path?
Given these two tables: [dbo].[Task] [Id] [Duration] [ScheduledStart] int int Nullable DateTime [dbo].[TaskDependencies] [Id] [PredecessorTaskId] [TaskId] int FK_Task_Id FK_Task_Id I'm trying to find the latest end date of the Tasks immediate predecessors. For the Task table, ScheduledStart is nullab...
Find Non-Ascii Characters in One Varchar Column or Mulitiple Columns using SQL Server
How can rows with Non-Ascii Characters be returned using SQL Server? If you can show how to do it for one column would be great. I am doing something like this now but it is not working select * from Staging.APARMRE1 AS ar where ar.Line like '%[^!-~ ]%' For extra credit, if it can span ALL VARCHAR columns in a Table would be outst...
How to update one table from another one without specifying column names?
I have two tables with identical structure and VERY LARGE number of fields (about 1000). I need to perform 2 operations 1) Insert from the second table all rows into the fist. Example: INSERT INTO [1607348182] SELECT * FROM _tmp_1607348182; 2) Update the first table from the second table but for update i can't found proper sql synt...
What is the best way to setup hierarchy in sql server 2005?
Hi all, I am trying to setup an hierarchial structure for company. I was using SQL Server 2008 and used hierarchy id to set it up. But now I need to move back to SQL Server 2005...and "just do it"... Anyways I thought of setting it up this simple way - Id | ParentId | CompanyName | Desc where ParentId is a int field which will stor...
select data from excel spreadsheet that's read only
I've got a dtsx package that selects data from an excel spreadsheet on the network and inserts it into a sql server table twice a day. However, the process fails if someone is in the spreadsheet modifying data. Is there a way to select data from an excel spreadsheet so that it doesn't fail if someone is in the spreadsheet? ...
Simple INNER JOIN Query Returns No Value - Where Did i Go Wrong?
The query below fetches the wageTypeID based on the current active hiring info record and salary record. SELECT wt.wageTypeID FROM TimeSheet t INNER JOIN Employee e ON e.employeeID = t.employeeID INNER JOIN Salary s ON s.salaryID = t.salaryID INNER JOIN Wage w ON w.wageID = s.wageID INNER JOIN EmpHiringInfo ehf ON ehf.Emp...
How do you find the ID of the parent stored procedure within a nested procedure?
If I am within a nested stored procedure, I would like to be able to get the procedure ID of the procedure that is one level up. Example: create proc procedure1 as print 'Current proc id: ' + @@PROCID print 'Parent proc id: ' + @@PROCID_PARENT --or something? go create proc procedure2 as exec procedure1 go exec procedure2...
SQL "All" Functionality?
This is probably a really easy question, it's just very hard to google a word like "All". SELECT a.Id, Max(b.Number) FROM Table1 a JOIN Table2 b ON a.FK = b.Id GROUP BY a.Id But I want to add a where clause that specifies that all b.Id's linked to an a.FK must have values. So basically I don't want to select the a.Id grouping of b...
Is it faster to UPDATE a row, or to DELETE it and INSERT a new one?
Given two scenarios on SQL Server 2008/2005 - 1 Table has 5 rows 2 Table has 1 million rows If we need to update a few rows, what is is efficient and why? 1) UPDATE the required columns 2) DELETE the row and INSERT new row with the updated information ...
SQL Server Management Studio - Adding/Moving Columns require drop and re-create?
Why do I get message that the table needs to dropped and re-created when I add/move columns? I believe this happens after adding foreign key constraints. What can I do to add new columns without dropping table? ...
SQL Server DB Designer/Editor - which one do you use?
I am using SQL Server Management Studio. I find it good in a few ways and frustrating sometimes. Which one to you guys use? Preferably something I can try for sometime before I buy... ...
Question Regarding Fetching Records based on Date Range
SELECT COUNT(td.timeSheetID) FROM TimeSheet t INNER JOIN TimeSheetDetail td ON t.timeSheetID = td.timeSheetID WHERE (CONVERT(DateTime, t.setDate, 23) BETWEEN '2012-08-10' AND '2012-08-12') AND t.employeeID = 300 The above code returns the value i'm looking for. However, when i do the following, i get nothing: SELECT COUNT(td.ti...
Changing data types in SQL Server 2005
Hello everyone, I have several tables in SQL Server 2005 that have columns with datatype money. I would like to change them into datatype numeric(20, 2). I am a lazy guy so would like to update each and every money data types to numeric data types with as little hassle as possible. Can anyone help please. Thank you so very much. ...