Hi, I have a table called tblProducts with 3 columns: intID, dateMain, intStatus. I have a stored procedure like so: ALTER PROCEDURE [dbo].[spList_Report] @id INT, @startDate DATETIME = NULL, @endDate DATETIME = NULL, @includeStatus1 BIT, @includeStatus2 BIT, @includeStatus3 BIT, @includeStatus4 BIT AS SET NOCOUNT ON ...
I have a table in MS SQL Server 2008 with a varchar column called WONO. For most records, the value of this field is a padded-on-the-left string representation of an integer, such as: ' 384564' However in some cases there is a non-integer value in the field. I want to select the record with the greatest integer in the WONO column, i...
We have a situation where the collation of databases, tables, and some columns are different between our dev and production SQL Servers, and it's wreaking havoc on development. Things will work on dev and then break due to collation conflicts when promoted, data and structures will be copied from prod to dev which in turn breaks the que...
Hello SO, I'm inserting records from one sql server db into another. I need to get the newly inserted id and update a field on the source table. I can get the new ID with INSERTED option no problem. However, I cannot seem to get the ID from the source table. The destination table does not have a field for the source's ID. The source tab...
I'm trying to run a command script that executes a BCP utility. I want it to be able to find any files with a specific file name format, for example, fileyyyymmdd.dat. Is there any way of replacing the yyyymmdd with wildcards, like a ? or * in other programs? Thanks! ...
I have 3 values that I need to copy from one table to another table. Here is my amateur attempt at it, I know it is horribly ineffecent, what would the correct way of doing this query? update [IDAT_PATIENTS] set TargetRabiesSerial = (select top 1 SERIAL_NUMBER from [IDAT_RABIESHISTORY] as rab where TargetPetAccount...
I have column OFF_SAT_COMP.LINKACCT and OFF_SAT_COMP.COUNTRY. I am trying to add the values of these two columns in a given row together, but when I do so as follows: (OFF_SAT_COMP.LINKACCT + OFF_SAT_COMP.COUNTRY) It is concatenating rather than returning the sum of the two column values. e.g. It is returning 500300 where I want 800. ...
My company is working on a development project using SQL Server 2008 Express. The amount of data we plan to store in our main table will quickly exceed the 4GB size limit of Express. We can buy ourselves some time with SQL Server 2008 R2, but eventually we will surpass the 10GB limitation as well. The team lead wants to hear all availab...
I have a complex expression calculating a value from a date that I have to use on multiple date columns. Can I define a temporary local function in my query to avoid copy and pasting this expression. ? like: create MyLocalFunc(@ADate datetime) returns int as begin blablabla end select MyLocalFunc(col1), col2, MyLocalFunc(col3), c...
I am writing a SQL Stored Proc which takes in a single table valued parameter. Is it possible for me to create the table type in the parameter definition, for example: CREATE PROCEDURE example ( @param (CREATE TYPE tableparameter ( column1 int, colunn2 varchar.... )) READONLY ) ...
I have a database with a field that holds permit numbers associated with requests. The permit numbers are 13 digits, but a permit may not be issued. With that said, I currently have the field defined as a char(13) that allows NULLs. I have been asked to change it to varchar(13) because char's, if NULL, still use the full length. ...
In SQL Server 2005, there was a stored procedure available called sp_generateinserts. However in SQL Server 2008, it doesn't seem to exist. Does anyone know if it is still around? Thanks. ...
I am still learning SQL so this may seem a very odd question, but is this the best way to use CASE within a CASE to check for NULL? @FN_InputDt datetime) RETURNS varchar(3) as BEGIN DECLARE @Result varchar(3), @MonthNo int Set @MonthNo = datepart(m,@FN_InputDt) Set @Result = CASE WHEN @FN_InputDt IS NOT NUL...
I have a table User with a bunch of indexes. One of them is a unique index on the AccountIdentifier column. Since this is a unique index, why is a key lookup required in addition to the index seek? The index seek tooltip reports that only one record is returned. I've also tried converting the index to a "unique key" type. ...
I have a view which combines two tables using UNION ALL. Is it possible to create a unique index on the view? ...
Hello, I have a view which combines two tables via UNION ALL. The view is also schemabound, if that matters (WITH SCHEMABINDING). Is it possible to enable fulltext search on this view? I understand that Fulltext Search requires a unique index, but I can't create it because of UNION. Is there another way to make Fulltext Search work on...
I have a scenario where I combine two tables into one (using UNION) and also JOIN data from some other tables. I can do this either with a VIEW or a UDF/User Defined Function. Considering that I have no view so far at all in my database, but quite a few UDF's for fulltext search and the like, I'm tempted to use a UDF for this scenari...
I've been looking into a lot of database's recently, and am not sure if it's because I'm bored or what, but I want to create a few web applications using database's other than MS SQL Server. Any suggestions that tie into ASP.NET nicely? ...
The data is in rows and column format. I want to know the difference between SQL Server and SQL native client. While making the connection string, it asks for 'SERVER'...what should I put over there? Please help and please send the code if u have.. thanks a lot... ...
Hi, I'm trying to run a trace with Sql Server Profiler against an Asp.NET Website Application running in Visual Studio development server. However, whenever the trace is running, all db requests from the web application fails giving me the error message: "Timeout expired. The timeout period elapsed prior to completion of the operation...