This routine works in most cases, but fails when a bad date is entered
such as:
19910631 -- there is no June 31st.
Instead of ignoring the bad date the entire DTS job fails. Obviously
this is something that should be validated at data entry, but
unfortunately the only control I have is when appending to the table
with these data quirks. Any suggestions appreciated!!!
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************
Function Main()
'DTSDestination("Col002") = DTSSource("Col002")
if DTSSource("Col002") = "99999999" or DTSSource("Col002") =
Null then
Main = DTSTransforStat_SkipRow
else
DTSDestination("Col002") = mid(DTSSource("Col002"),1,4) & "/"
&
mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)
End if
Main = DTSTransformStat_OK
End Function
Hi. This is my first attempt at a using stored procedures and I'm a bit confused. I'm trying to follow as many best practices as I can to improve speed, security and scalability. However, I can't find a solution to what I think should be a simple problem. I have a search page where users enter the criteria of properties they are interested in (bedrooms, price etc...). That takes them to a results page where the properties are displayed. The problem is that I want the amount of times each property has been shown on the results page to be tracked so the property owner gets statistics. The property details are all held in a single table along with the amount of times each property has be shown: Table Name: zk_Property_USA
ID INT
User_ID INT
Property_Type TINYINT
Market_Status TINYINT
Price INT
Bedrooms TINYINT
Address_State VARCHAR
Address_Location VARCHAR
Property_Description VARCHAR
Searched INT 0
Contacted INT 0 I'm trying to find a way to SELECT all the property details to be returned to my results page and UPDATE the "Searched" field by 1 without re-scanning the table for the UPDATE. Is there a way to update "Searched" at the time when the record is chosen to be a result? I am using SQL Server 2005 and Visual Basic ASP.NET 2.0. Many Thanks
In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?
Using MSSQL (pre 2005): I have a Link table: int id (primary key/identity) varchar(50) linkName varchar(255) linkHref //some other stuff and my Hits table: int id (primary key/identity) int linkId (foreign key to Link) datetime dateCreated //some other stuff Hits gets an insert whenever a link is clicked. (All this works just fine) I'm trying to create a report that shows each link by its name and href, a counter and the last date each link was visited. What I currently have is an accurate listing for those that have been clicked on, but it does not show anything for links that haven't been clicked on. Any suggestions as to how I can modify the following SQL to return "0" and "never" (or DBNULL) if no entries are found in Hits that have the same id? Or do I have to do this in a couple queries? SELECT COUNT(h.id) as counter, MAX(h.dateCreated) as lastVisited, l.id as id, l.linkName as linkName, l.linkHref as linkHref FROM Link as l INNER JOIN Hits as h ON l.id=h.moduleId GROUP BY l.linkName, l.id, l.linkHref
Hello, i am making a Fulltextsearch on MS SQL Server 2005 (indexed, with "Contains"). Because of performance reasons i am only showing the first 200 rows mssql finds ("select top 200...:"). Is there any possibility to get the estimated totalnumber of all rows? i have heard something that is possible to get this in mssql-server. The server then estimates how many rows with that searchword could be in the whole database. google i.e. makes the same thing.... is that true? what do i have to do to get this? greetings and thx cpt.oneeye
I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
I am serving ad-units. In each ad-unit I show somewhere between 3 and 10 article headline. I track the headline impressions to get an idea of the headline click through rate. I save the output from the stats process in another table. I am currently evaluating the stats every hour, and then truncating the table every night at midnight. The problem is that I get lots of impressions and the database gets bogged down evaluating the data such as... SELECT COUNT FROM articleimpressions WHERE articleid = x
But the issue isn't the reporting of the data...so much as it's the capture. I had to add caching on the ad-server because the database couldn't handle the number of inserts.
I thought about parsing the web server log files the next day, but the file sizes seem to be too large, and I can't process them all in one day. (At least not on the hardware that I am using.)
I've thought about splitting log files by hour, but was wondering if there may be a more "native" solution within SQL Server? Maybe a trigger, and/or multi-threaded SP that fires and forgets an insert statement to a linked server. But performance is the key here.
In MSSQL, is there a way to count the number of instances of asubstring within a string, so that I can sort by that?For example:table tst contains one column: tst_dataif tst_data = "the man with the plan"I'd want a function that counts the occurances of "the"count_substring(tst_data,'the') = 2Basically, I'm making a search engine and I'd like to put the mostrelavent hits at the top of the page.
hi . would greatly appreciate a clever way to make an internal "counter" on an sql table that contains 6000 articles.(one in each row). these are being accessed from a web page and i would like to know which are the most read ones. so i thought of adding a column that would in some way count each time that the row is being accessed. is there a way for this to be done?? please be detailed since i am quite new to all this. thanks beforehands.
I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more.
Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter.
And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master
how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN' CASE WHEN A.[Value] < M.[Threshold] THEN 'RED' ELSE '' END END END AS 'Condition'Is this at all possible?
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30 208203492013-09-032014-09-01 308203492012-09-042013-09-02 408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
Hi, Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor 1.1) Within File Connections Manager Editor; - Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC" and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; - Input Column as INTERCHANGE_NET_APP_DATE and Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
Hi, I really need this help urgently. I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email... Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load con1.Open() Dim cmd As New SqlCommand cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)" 'Set the connect the command object should use cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet da.Fill(ds) con1.Close() If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then Dim objEmail As MailMessage = New MailMessage objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg")) objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName") objEmail.Priority = MailPriority.Normal Dim SmtpMail As New SmtpClient("servername") SmtpMail.Send(objEmail) End If End If End Sub Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy) Realli veri urgent Thanks so much for ur'll help
INSERT INTO [GPO].dbo.tblMetric (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) SELECT
1 AS KPI_OWNER_ID , 23 AS METRIC_ID , .75 AS GOAL , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example: When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007. When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
I have a table here. I want find a way of getting the latest date, when the code is the same. If the Declined date is null. Then I still want the latest date. E.g. ID 3.
If the declined date is filled in. Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but i got an error message when trying this out. Against the code
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. What do I need to do to get both my outputs working?
