I have a DB named zCIFRecord with a column named CIFUpdateDate which is of datatype varchar. The data is a date MM/DD/YYYY 01/30/2008, this is al that is in this column. I can search this colum for individual dates and for a range of dates. My problem is with a range of dates that is not within the same year, such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '12/01/2007' and '01/30/2008'
will return nothing because it seems to only search on the 12 then the 01 then the 2008. this search can be performed properly on dates within the same year such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '01/01/2008' and '01/30/2008'
will return the proper values because now all the numbers are in correct order. How can i create a search that lets me perform the first query as well as the second query. I tried to convert to a float but you cant convert a varchar to a float.
I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
,[CIFExpDate]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'
I get error:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?
I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.
I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error: [OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block CREATE PROCEDURE [dbo].[sqlOutlookSearch] -- Add the parameters for the stored procedure here @OLIssueID int = NULL, @searchString varchar(1000) = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @OLIssueID <> 11111 SELECT * FROM [OLissue], [Outlook] WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' ELSE SELECT * FROM [Outlook] WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%'' END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
I'm pretty new to SQL and I've got a bit of a sticky problem. I've looked around on the net for a solution but possibly I just don't know what I should be searching on. I'm trying to join tables together where there is a one to many releationship - but I'm trying to put the results for each relationship on one row (which kind of results in dynamic columns).
Is this possible? There is no limit on how many rows in table 2 can be related to table 1. Can anyone point me in the right direction for what I should be looking at?
The reason I'm trying this is for SQL reporting services if that makes a difference? Thanks, Kelvin.
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
Hi all, I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000. The application is in production and everytime I want to do some changes, i do the changes on my development machine then I copy the application dll on the server. The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements. These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.
How can I do modifications without affecting the production server and the users ??? thanks.
I just want to get the sum of a table's column into a variable, in a stored procedure. The best I can do is SET @TotalBalance = SELECT SUM(Balance) FROM AccountDetails Not good enough, of course.
I'm trying to put a date into a SQL Server table. The database field type is "smalldatetime". The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#). When I run the following code the date in the database is always ends up being: 1/1/1900. Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _ "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery() Any other field types work fine, it's just dates that aren't working ???? Can someone please provide a code snippet showing me what I'm doing wrong??
set dateformat 'dmy' select count(c.id), e.name from call c left outer join employee e on c.req_id = e.id where c.posted between '01/01/2002' and '30/11/2002' group by e.name order by count(c.id) desc
set dateformat 'dmy' select count(ch.id), e.name from call_hist ch left outer join employee e on ch.req_id = e.id where ch.posted between '01/01/2002' and '30/11/2002' group by e.name order by count(ch.id) desc
the results:
42NULL 34Dirk Deloof 13Annick Leirman 11Ronny Loosen 9Geert Benoot 9Nicole Ferrari 8FLOCK 8Mosselmans Christoph 7Geert Pets 7Mireille Dutrieue 6johan 6Laurent De Schrijver 5Jeanette De SChrijve 5Marc De Vlieger 5minerva 5Pascal Saesen 5Rik Haghebaert 5Sonja Van Kerckhove 4Bcatron 4Luc Willems 3Brigit Brocken 3euroadmin 3Francine Kopp 3Luc Steyaert 3Marie-Rose Buysse 3Marnix Van Steirtege 3Mattias Denys 3Pieter Frooninckx 3Reserve 3Rik De Scheemaecker 3Thierry Linard 2Carlos Van Alboom 2Dorine Sierens 2Els Poelman 2Jean Claude Vermeir 2Katrien Colman 2Kim Impens 2Kris Lejeune 2MEDreserve01 2Roger De Wilde 1Agnes Lebon 1Carla Van Den Broeck 1Eric Vlaeminck
and
118NULL 58Marie-Rose Buysse 47Dirk Deloof 45Ronny Loosen 43Annick Leirman 41Geert Pets 38FLOCK 38Pascal Saesen 28Teamleiders afwerkin 24Kim Impens 22Ilse Soetens 22Rik Haghebaert 22Severine Balduck 21Teamleiders print 20Mosselmans Christoph 20Jeanette De SChrijve 19Geert Benoot 19Francine Kopp 18Geert Meuleman 17Rik De Scheemaecker 16johan 16Katrien Colman 15Gaby Eloot 14Kris Lejeune 14Gilbert Callebaut 14Laurent De Schrijver 13Els Poelman 13Luc Steyaert 11Marnix Van Steirtege 10Frans Hoogewijs 10Sonja Van Kerckhove 10Dorine Sierens 9Eric Vlaeminck 9Thierry Linard 7Frederic Denis 7Michel Poppe 6Carla Van Den Broeck 6Pieter Frooninckx 5Katlijn Poleyn 5MEDreserve01 5Mireille Dutrieue 5Agnes Lebon 4Guido Antoin 4Onderhoud 4minerva 4Jeanette Van Brussel 3Roger De Wilde 3Sofie Gabriels 3Verf2 3euroadmin 3Marc De Vlieger 2Luc Willems 2MEDRESERVE07 2Regina Decoster 2Monique Kohl 2MEDRESERVE04 2Portier 2Bcatron 2Pierre Hanet 2Tgabriels 2Isabelle Torrelle 2Nicole Ferrari 1Robert Zwaak 1Carlos Van Alboom 1testuser 1Brigit Brocken 1Reserve 1Opleiding 1Verf
How do I put these results in one? I need not two but one Query. Please help me. Thanks
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block ALTER PROC csp_ICASTransaction_AskDoc @ClientID INT AS DECLARE @ClientID INT SET @ClientID = 1 DECLARE @cClientID INT DECLARE @cMonth VARCHAR(20) DECLARE @cOccurance INT DECLARE @Counter INT DECLARE @AskDoc CURSOR SET @AskDoc = Cursor FOR select @ClientID, Month_Year, SUM(AskDocs) FROM zzenrolled$ WHERE ICASClientID = @ClientID GROUP BY Month_Year OPEN @AskDoc FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance /* SELECT @@Cursor_Rows PRINT @cClientID PRINT @cMonth PRINT @cOccurance */ DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT) WHILE (@@FETCH_Status = 0) BEGIN SET @Counter = 0 INSERT INTO @MyTable VALUES (@cClientID, CASE @cMonth WHEN 'Jan-03' THEN '20030101' WHEN 'Feb-03' THEN '20030201' WHEN 'Mar-03' THEN '20030301' WHEN 'Apr-03' THEN '20030401' WHEN 'May-03' THEN '20030501' WHEN 'Jun-03' THEN '20030601' WHEN 'Jul-03' THEN '20030701' WHEN 'Aug-03' THEN '20030801' WHEN 'Sep-03' THEN '20030901' WHEN 'Oct-03' THEN '20031001' WHEN 'Nov-03' THEN '20031101' WHEN 'Dec-03' THEN '20031201' WHEN 'Jan-04' THEN '20040101' WHEN 'Feb-04' THEN '20040201' WHEN 'Mar-04' THEN '20040301' WHEN 'Apr-04' THEN '20040401' WHEN 'May-04' THEN '20040501' WHEN 'Jun-04' THEN '20040601' WHEN 'Jul-04' THEN '20040701' WHEN 'Aug-04' THEN '20040801' WHEN 'Sep-04' THEN '20040901' WHEN 'Oct-04' THEN '20041001' WHEN 'Nov-04' THEN '20041101' WHEN 'Dec-04' THEN '20041201' WHEN 'Jan-05' THEN '20050101' WHEN 'Feb-05' THEN '20050201' WHEN 'Mar-05' THEN '20050301' WHEN 'Apr-05' THEN '20050401' WHEN 'May-05' THEN '20050501' WHEN 'Jun-05' THEN '20050601' WHEN 'Jul-05' THEN '20050701' WHEN 'Aug-05' THEN '20050801' WHEN 'Sep-05' THEN '20050901' WHEN 'Oct-05' THEN '20051001' WHEN 'Nov-05' THEN '20051101' WHEN 'Dec-05' THEN '20051201' WHEN 'Jan-06' THEN '20060101' WHEN 'Feb-06' THEN '20060201' WHEN 'Mar-06' THEN '20060301' WHEN 'Apr-06' THEN '20060401' WHEN 'May-06' THEN '20060501' WHEN 'Jun-06' THEN '20060601' WHEN 'Jul-06' THEN '20060701' WHEN 'Aug-06' THEN '20060801' WHEN 'Sep-06' THEN '20060901' WHEN 'Oct-06' THEN '20061001' WHEN 'Nov-06' THEN '20061101' WHEN 'Dec-06' THEN '20061201' WHEN 'Jan-07' THEN '20070101' WHEN 'Feb-07' THEN '20070201' WHEN 'Mar-07' THEN '20070301' WHEN 'Apr-07' THEN '20070401' WHEN 'May-07' THEN '20070501' WHEN 'Jun-07' THEN '20070601' WHEN 'Jul-07' THEN '20070701' WHEN 'Aug-07' THEN '20070801' WHEN 'Sep-07' THEN '20070901' WHEN 'Oct-07' THEN '20071001' WHEN 'Nov-07' THEN '20071101' WHEN 'Dec-07' THEN '20071201' END ,'3' )
SET @Counter = @Counter + 1 IF @cOccurance > @Counter FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance END CLOSE @AskDoc --INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance]) SELECT * FROM @MyTable DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT 'Test' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTESTarchived est.pdf', SINGLE_BLOB) AS BLOB"
When I try to put in a variable as follws
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT '" + @[User::MyFileValue] + "' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTestarchived est.pdf', SINGLE_BLOB) AS BLOB"
I keep getting errors Saying it contains an illegal escape sequence of w any ideas ?
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please take a look at the statement and see if its well formed, String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";
I am creating a stored Procedure and I am getting an error which relates to DATENAME. SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls' FROM CALL_LOG_MASTER WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?
hi guys, Could any one tell me whats the best way to put encypted data into a table.i have a textfield that a single integer is entered into it. It is then encrypted using DES encrption algorithim.It converts the value to byte(Convert2ByteArray method) I pass the data to a stored procedure which converts it to char before inserting it into the table. when i checked the database all it entered was System.Byte[]. Does anyone know where im goin wrong? Mairtin
We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.
Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?
I had an idea to put all my web design settings, css text and web content in the database..This way it would be easy for others to edit remotely. Do you guys think this would have an impact on performance if I do this?
I have a pivot transform that it believe is configured correctly but is not distributing the values accross the columns on the same row. for example.
input:
id seqno codevalue
1 A red
1 B red
2 C blue
2 A green
2 B violet
3 A green
desired output:
id Seq_A Seq_B Seq_C
1 red red null
2 green violet blue
3 green null null
what I am getting:
id Seq_A Seq_B Seq_C
1 red null null
1 null red null
2 green null null
2 null violet null
2 null null blue
3 green null null
I do have the pivot usage for the id column set to 1. I have the pivot usage for seqno column set to 2 and codevalue column set to 3. I have the source column for each of the output columns set to the lineageID of the apprpriate input columns. I have the pivotKey values set for each of the destination columns. A for column Seq_A, B for column Seq_B, C for column Seq_C. All four columns have sortkey positions set; 1 for id, 2 for Seq_A, 3 for Seq_B and 4 for column SEQ_C.
It seems like the id column's pivot usage is not set to 1 like it should but when I check it is 1.
I also have several other pivot transforms in the same data flow and they are working as expected.
I have a suspicion that there is some hidden meta data that is messed up that is over-ridding my settings (just my guess) I have deleted this transform and re-done it several times, checking each configuration value, but still getting the same result.
I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.
Here's what I want: I have a DTSX package run as a SQL Server agent job, that each time it runs I want it to create a new log file with the date & time in the filename. e.g. MyJobLog.20080311_103613.txt
I have implemented this using the SSIS log provider for Text Files, and defining the Connection String as an expression as follows:-
Here's the problem:- When the job runs, each time it tries to write to the log file it re-evaluates the filename, and as the seconds pass by the name changes! So instead of one file I end up with several:- e.g. MyJobLog.20080311_103613.txt MyJobLog.20080311_103614.txt MyJobLog.20080311_103615.txt
So the one execution of the job is now divided across three files! This is not what want!
Here's one way I fixed it:- One of my Jobs is run from an MS-DOS batch file, so I was able to pass the constructed date/time sting into the DTSX file as a parameter. This works perfectly.
Here's my problem:- Three more of my jobs are run as scheduled job from SQL Server Agent, so I can't pass in the date/time as a parameter each time (unless you know otherwise). And the clients' security policy won't allow the job to call an MS-DOS batch file which runs the DTSX package. Or is there really some other way to stop the Log filename from re-evaluating on every write?
The dataset2 control doesn't seem to want to place the text in the right spot. I put it right after the 1st Dataset and the user comments wound up all the way down in the footer of the site. I want them to appear in the white area just underneath the article text. Here's a link to one of my articles so you can see this. http://www.link-exchangers.com/view_full_article.aspx?aid=50 Down at the bottom left corner you'll see white space below the article. I'd like to have all the comments lined up going down the page. Is the dataset the best control for this task?
