I'm trying to import a spreadsheet to our database using SSIS. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. If I tr...
I need to group some records based on a date but it is a date and time field and I need to ignore the time part of is and just group by the date part - here is my SQL as it stands: SELECT AutoShipItems.CustomerID,AutoShipItems.NextOrderDate, Customer.FirstName,Customer.LastName, Customer.EmailAddress FROM AutoShip...
I’ve got a table: CA ca_id placement_id blah blah This table gets updated with new unique placements every hour from data warehouse. Now the end-user wants to track statuses for those placements. I was thinking of adding a new table which would get updated hourly as well: CA_Status placement_id record_status last_updated_on O...
I have a problem writing a SQL statement for both Oracle and MS SQL server. I'd like to write the SQL so that it works in both. I don't want to have to mess around with setting a database type variable and switching I have a column of course names that have values like this: 9RA923.2008W 1223.200710 P0033330.200901 I w...
The following (sanitized) code sometimes produces these errors: Cannot drop the table 'database.dbo.Table', because it does not exist or you do not have permission. There is already an object named 'Table' in the database. begin transaction if exists (select 1 from database.Sys.Tables where name ='Table') begin d...
I have a database table that looks like: ForeignId: int Key: varchar Value: varchar Where ForeignId and Key constitute a unique primary key I can easily determine the total set of keys defined for a given set of document with SELECT DISTINCT [Key] FROM [Table] WHERE [ForeignId] IN (...) What I would like to do however is to further...
[This is a bit of an unusual problem, I know...] What I need is a script that will change every unique id value to new one in our database. The problem is that we have configuration tables that can be exported between instances of our software which is id-sensitive (clobbering existing ids). Years ago, we set up a "wide-enough" id gap ...
For example, for heavily used tables with volumes in the order of 10 million rows that grow by a million rows a month, if the stats are 6-8 months old how detrimental to the performance of the database is this going to be? How often should you be refreshing the stats? ...
Hi All, I've inherited the code below but I'm puzzled as the code works just fine but I'm not convinced it's correct. I've not used LIKE before so I can't decide if I should bother changing this. How does this look to you? The code is for a filter in a db it checks if X value contains Y value, find X* in X (e.g. Match would be F* in Fu...
Is there command inside a Query Window that will open a stored procedure in another Query Window? i.e. MODIFY dbo.pCreateGarnishmentForEmployee I am using SQL Server management Studio 2005 and Red Gate's SQL Prompt. Currently I have to do the follwowing multiple steps: Open Object Explorer Navigate Programmability | Stored Procedure ...
How can you search all the Stored Procedures for a Pattern and then open the stored procedures to be edited? Is there anything built inside of SQL Server 2005? Or are there any 3rd party addins that will do this searching? I am also using Red Gate's SQL Prompt but I have not noticed that option. Currently I am using the following co...
I applied SQL Server 2005 SP2 and marked all the components to be updated, including Client tools, but the version in the Help -> About box still has version 1399, while the server does indeed had 3042. I then re-applied SP2 to only Client tools, and everything ran successfully (again, first time did too) but still, no version update fo...
I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide.... What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegrou...
I have to estimate the data size of database when populated with forecast values for each table. It should at least include size to be used for data and indexes. There used to be tool like this in SQL Server 2000 that produced an estimated size when supplied with the amount of records per table (I think it shipped with the SQL 2000 res...
I need to do a Full Text Search with NO word stemming...I tried to wrap the term I'm searching in double quotes, but no joy... I still get results like "bologna" when I search for '"bolognalo"' Any help is appreciated.. ...
Is ther a TSQL command to connect to another server? Or when you are in a Query Window, what are the Keyboard shortcuts to connect to another server and have a Query Window show up? I have seen Ctrl+N pop up the Connect to Server dialog in some screens but when I am in a Query Window already and hit Ctrl+N it just opens up another Quer...
Let's say I have a number in the german number format "1,00" which is equal to 1.0 in the en-US locale. Is there a built-in way to convert this text in T-SQL to the corresponding number? It seems like CONVERT and CAST accept only numbers with '.' as the decimal separator and I see no way to tell them otherwise. I was thinking about sim...
If you have a float in MSSQLServer, to what do you map this in .NET? Can you convert it to Double or will you loose numbers? thx, Lieven Cardoen ...
I've got a sql query (using MS-SQL 2005) that generates data with these columns: TimeStamp | SpeedMax | SpeedMin | HeightMax | HeightMin ------------------------------------------------------- 10 | 50 | 10 | 300 | 70 The form I need it in though is this: TimeStamp | Speed | Height --------------------------- ...
I have a situation where I need to add an arbitrary unique id to each of a group of records. It's easier to visualize this below. Edited 11:26 est: Currently the lineNum field has garbage. This is running on sql server 2000. The sample that follows is what the results should look like but the actual values aren't important, the numbers...