How can I compare the first 3 or 4 letters of the postcode in the Locations
table to the corresponding entry in the Brick Codes table so I can add it to
my report?
To ask my question i will create an example. Table STAFF contains FIRSTNAME and LASTNAME and STAFF_ID. Table CLASS contains TIME and STAFF_ID
Q: How do i perform a search on CLASS for a specific TIME, and display contents from STAFF, contents being FIRSTNAME AND LASTNAME only. Is it possible since both tables have STAFF_ID in common.
Hi all. I have been going round and round for the past 2 days on this and would appreciate any help. In a view select statement, I need to concat 2 varchar fields with a text field. If I CONVERT the TEXT field to VARCHAR, only the first 30 characters of the field appear in the result set. If I convert the VARCHAR fields to TEXT, I get an error that I cannot CONCAT TEXT fields. I'm not sure what to do. Can someone please offer some assistance? Thanks in advance! Steve
Hi , I have a database that records the users entrance to a building.The dates are recorded also .I have written some codes that to detect the period of dates that the person has entered.Lets say that a person named as jhon has entered the building on the days 02/08/2006 and 05/08/2006 and 11/08/2006 .Those dates are formated as dd/mm/yyyy . So that john has entered building for 3 times and the periods for the dates are one after another entrance is 3 days + 6 days =9 days .If you divide 9 by 3 we get the arithmetic average number 3 .So that we can say as john visits this building every 3 days and we can guess the next day that he may come. I have coded this and works great .I will paste the codes to the end of my message.But a master programmer friend of mine has said that I should have get this result by without writing code , by just using sql database .But some kind of stored procedure I mustn't use.So that I thought it can be done by views in sqldb .By using sql server enterprise manager I tried to use views but I could not succees. Can anyone guess this ? Here are my vb codes ... database data types : entry_id : int , identity user_id : int movie : nvarchar (50) dateenter : datetime stored procedure that selects the data from db: CREATE PROCEDURE veri_al ( @user_id int ) AS SELECT entry_id ,user_id, movie, dateenter from uye_aktiviteleri where (user_id=@user_id) ORDER BY entry_id ASC GO Code:Dim conn As New SqlClient.SqlConnection conn.ConnectionString = "data source=localhost;initial catalog=AFM;uid=nusret;pwd=araz" Dim command As New SqlClient.SqlCommand command.CommandText = "[veri_al]" command.CommandType = CommandType.StoredProcedure command.Connection = conn command.Parameters.Add("@user_id", SqlDbType.Int, 4).Value = Val(TextBox1.Text) If Not IsNumeric(TextBox1.Text) Then Exit Sub End If Try Dim adapter As New SqlClient.SqlDataAdapter adapter.SelectCommand = command Dim ds As New DataSet adapter.Fill(ds, "uyeler") DataGrid1.DataSource = ds.Tables("uyeler") Dim recordcount As Integer 'Found the recordcount recordcount = ds.Tables("uyeler").Rows.Count Dim mydatarow_ilk As DataRow Dim mydatarow_son As DataRow 'in stored procedure I used ORDER BY entry_id ASC so that first datarow 'will always be the first visit date and the last record of the 'datarow will be the last visit date mydatarow_first= ds.Tables("uyeler").Rows(0) mydatarow_last = ds.Tables("uyeler").Rows(recordcount - 1) Dim first_date As String Dim last_date As String first_date = mydatarow_first("dateenter") last_date =mydatarow_last("dateenter") Dim average_ As Integer 'What is the aveare of the visits ortalama = DateDiff("d", first_date , last_date ) / recordcount Label2.Text = "Member had visited for " & recordcount & "times" Label3.Text = "by average he/she comes here " & ortalama & " day to another." Dim last_time_visit As Integer last_time_visit = DateDiff("d", last-date , DateTime.Today()) Label4.Text = "Until the last visit it had been" & last_time_visit & " days" Label5.Text = "Guess for the next visit is" & DateAdd("d", average_ , last_date) ' MsgBox("average visits" & ortalama & " days" ) Catch z As Exception MsgBox("error : " & z.Message()) Finally MsgBox("Successfully calculated") End Try End Sub
I have another question about ADO (using C++). I have searched the MSDN but I haven't found a answer to my question (maybe I'm just too stupid)... If I make a connection to my SQL Server there may occur some errors, but how to find out what went wrong ?
In terms of code I have e.g. something like that: try { connection->Open(ConnectionString,Username,Password,ADODB::adConnectUnspecified); ...} catch (_com_error& e) { long numErrors = connection->Errors->Count; for (long i=0; i<numErrors; i++) { ADODB::ErrorPtr pErr = connection->Errors->GetItem(i); ..... }}
Now I could get the error number by pErr->GetNumber(). But with which symbolic constant has this to be compared to find out which error occured ?? I didn't find any...Or is there another better way to do this ?
How can I test sql codes and don't want to connect to company database? My company is using sql server 2000. Below is the test codes which create a simple table, calculate and just want to check before writing more complicated codes.
create table EquipmentUptime ( equipmentId int not null ,transactionId int not null ,dateOccured datetime not null ) go -- insert into EquipmentUptime values (1,200,'01/01/2007') insert into EquipmentUptime values (2,200,'01/01/2007') insert into EquipmentUptime values (3,200,'01/01/2007') insert into EquipmentUptime values (2,100,'02/12/2007') insert into EquipmentUptime values (1,100,'02/25/2007') insert into EquipmentUptime values (3,100,'03/10/2007') insert into EquipmentUptime values (1,200,'03/14/2007') go -- select equipmentId ,100*(cast((cast(sum(UpDays)as decimal(6,3))/cast(365 as decimal(6,3))) as decimal(4,3))) 'PctUpTime' from ( select c.equipmentId ,datediff(dd,c.[UpDate],c.DownDate) 'UpDays' from ( select a.equipmentId ,a.dateOccured as 'UpDate' ,coalesce( (select top 1 b.dateOccured from EquipmentUptime b where transactionId=100 and b.equipmentId=a.equipmentId and b.dateOccured >= a.dateOccured order by b.dateOccured asc ),'01/01/2008') as 'DownDate' from EquipmentUptime a where a.transactionId=200 ) c ) d group by d.equipmentId
I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.
I apologize for the length of this message, but I think I need to include all this info so that the problem is understood. I am having what appears to be a problem capturing the return code from a failed BCP.
I create a stored proc to use BCP to load a table:
create procedure sp_bcp_load as declare @RC int execute @RC = master..xp_cmdshell "bcp JON..W4KPV in e:inetpubftprootfinreslaw4kpv.g4000.data /Sdbmtss1 /m 0 /f d:mssqluserdatafinresW4KPV.fmt /Usa /P /e d:mssqluserdatafinrescp1.err /t""|"" /r "
select 'Return code from bcp = ', @RC if @RC <> 0 BEGIN print 'BCP Error.' return (8) END GO
If I execute the SP, and encounter a transaction log full error, the return code is still zero:
1000 rows sent to SQL Server. 45000 total 1000 rows sent to SQL Server. 46000 total Msg 1105, Level 17, State 2: Server 'DBMTSS1', Line 1:
Can't allocate space for object 'Syslogs' in database 'Jon' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
(54 row(s) affected)
----------------------- ----------- Return code from bcp = 0
If I execute the SP again, it correctly returns a non-zero value:
Msg 1105, Level 17, State 2: Server 'DBMTSS1', Line 1: Can't allocate space for object 'Syslogs' in database 'Jon' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
(6 row(s) affected)
----------------------- ----------- Return code from bcp = 1
(1 row(s) affected)
BCP Error.
Does anybody have an idea why this behaves this way? Any suggestions on how to trap an error on the first call?
hey all i am stuck with this little problem I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'
is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?
I am posting this information simply as an FYI. This is in reference to the MS-SQL command "CONVERT"
I spent over 2 hours :rolleyes: screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed. I googled everything I could think of and found multiple references that said the info is available on MSDN.. but I could not find it. What I did find were thousands of relatively useless references to "format codes" for converting dates, but with no references to what the different format codes would ultimately yield, or what format codes were available to use.
What I ended up doing was writing a small script to generate a list of all of the variations I could find.
Below is the script, and the output that it yielded. Now, before I get bombarded with "there is a better way" I know there probably is. But this is the way that I needed to do it this particular time. If there are technical errors in my explanation, anyone is welcome to correct them. But after 2 hours of messing with this for what should have been a super simple single .0009 second command, I am just irritated beyond belief that it had to be this complicated to find any useful information on the subject. That is why I am creating this. Hopefully it helps someone else.
The format for the MS SQL CONVERT command is :
CONVERT( length_of_output, date, format_code )
length_of_output : is exactly that . the number of characters that you want returned as your result. If you use a length of 6 you will only see the first 6 characters that are returned. I found the longest valid length to be 28 characters, but I went as high as 128 just for giggles and to see if it revealed any secrets.
date : is a valid date, I used directly the getdate() function
format_code : well.. that's the tricky part. See below.
What I did was ran a script that originally went from 1 to 20,000. It crashed at 15. Apparently the format codes are not totally sequential. So I put in an on error resume next.
What I found is that : 1) the codes are not uninterrupted sequential numbers. 2) the code output repeats every 255 3) negative numbers can be used, but its pointless. 4) useful valid codes are in the ranges of : 0-14, 20-25, 100-114, 120, 121, 126, 130 and 131 5) 0-25 typically represent "short dates" with the year being only 2 digits, but there are exceptions 6) 100 and above always returned a 4 digit year. the exception was 130 and 131, I don't know what it was trying to do.
Here is the script i ran
<% on error resume next
for iintCounter = 0 to 256 SQL = "SELECT CONVERT(CHAR(128), getdate(), " & iintCounter & " ) as TheDate" Set rsTheDateFormat = TheDatabase.Execute(SQL)
I have files with area codes that are several years old. Everything I've seen about updating area codes deals with area codes that are current and are about to split in the near future. How would I go about bringing old area codes up to date?
I am working with a table that has zip codes listed in lengths of 9,8,5 and 4 digits. The table is created this way and I have no way of changing the data outside of SQL. I am trying to get the last four digits off of all the zip codes so that I only have to work with zip codes in lengths of 5 and 4
I hope you can help me out here. I don't know if I even have enough information to give you. I need to create a SQL that pulls the most commonly Billed CPT Codes. The Fields that I am going to use is the CPTCODES Field and the DOS FROM Field. How I was going to right my statement is below. Do I need to have a count or anything?
SELECT TOP 100 DOSFROM, DOSThru, CPTCODES FROM VW_1000_Commonly_Billed_Charges WHERE (DOSFROM >= @BottomDate) AND (DOSThru <= @TopDate)
I'm looking to find out how I'd go about setting up a database where avisitor to my site could punch in their postal code, and find out how farthey are from another postal code. For example, AutoTrader has this featureI believe to tell you how far the vehicle is from you. Dating sites havethem so you can do proximity searches.Anyone have any ideas where I could start? I'm thinking the post office,but if anyone else has suggestions, I'm open to hear them.Thanks!
Using the following syntax, sometimes non-zero error codes are returned: D:SQL Server x86Servers>start /wait setup D:SQL Server x86Servers>echo %errorlevel% 3010
Searching BOL for 3010 returns nothing.
Other error codes that were seen are 1603 and 9009.
What do these error codes mean? Is there a place where they can be looked up?
I need to create formatting that is a bit different from the standard formatting codes in SSRS. Specifically, I need to have the percent sign come right after the number (SSRS inserts a space), whereas with currency fields I need to inster a space between the $ and the number (SSRS has no space). Is there any way to do this with custom formatting codes, or will I need to go through and add the percent and dollar sign in manually to each field so that the space can either be removed or inserted?
I am designing a SSIS package where i get text file as input. This file contains transaction details.
I have to map the members in each transaction for data integrity. For this i am using Lookup object.
Lookup object works very well upto my expectations but i am facing problem in mapping the error code sent bythe this object whenever a record fails. I am storing all the invalid records into text file.
All the records get stored into text file along with some SQL Server error_code and error_column. I cant interpret these last two columns as it displays some numeric code and column name.
Is there any way to map these error codes and columns and get relevant information out of it? Example error code = -1071607778 error column = 0
As part of our application deployment we have wrapped and automated the SQL Server Express install by launching the setup.exe with our parameters and waiting around for the process to exit.
I had been checking for a Exit Code != 0 to detect errors returned by setup.exe but this does not appear to be foolproof. Several customers' deployments have returned non-zero exit codes, but SQL Express seems to be successfully installed.
I have not found a reference of possible codes returned and their meaning that I could use to fine tune our checking. Does one exist? Or am I going about this the wrong way and should just check for the existence of our instance after setup is complete?
I have a system using asp pages & ADO & SQL Server 2000, which processes files, builds a SQL insert statement from the file content and then executes it.
If the insert SQL fails, I need to know whether there was something wrong with the insert SQL, or something wrong with the database (e.g. SQL Server times out), and handle those differently,
Questions ======= 1. Is there an easy way to do this without checking against a list of error codes? 2. Can anyone point me to a list of errorcodes? Can't find this anywhere
In my webconfig file I have this. <add name="vau" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|vau.mdf" providerName="System.Data.SqlClient"/>
I understand that there are several different ways to connect and manage information to the database. I think I want to use the behind the codes (.aspx.vb) and on the top of the page I have this. Imports System.Data.
That is where I am stuck. Can you push me in the direction by either explaing to me or post a link that has more information about this.
I am trying to use the haversine function to find the distance betweentwo points on a sphere, specifically two zip codes in my database. I'mneither horribly familiar with SQL syntax nor math equations :), so Iwas hoping I could get some help. Below is what I'm using and it is,as best as I can figure, the correct formula. It is not however,giving me correct results. Some are close, others don't seem right atall. Any ideas?SET @lat1 = RADIANS(@lat1)SET @log1 = RADIANS(@log1)SET @lat2 = RADIANS(@lat2)SET @log2 = RADIANS(@log2)SET @Dlat = ABS(@lat2 - @lat1)SET @Dlog = ABS(@log2 - @log1)SET @R = 3956 /*Approximate radius of earth in miles*/SET @A = SQUARE(SIN(@Dlat/2)) + COS(@lat1) * COS(@lat2) *SQUARE(SIN(@Dlog/2))SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))/*SET @C = 2 * ASIN(min(SQRT(@A))) Alternative calculation*/SET @distance = @R * @Cthnx,cjrsumner
Does anyone know where to obtain a complete list of error codes which can occur in SQL Server ? For example : errror number 547 is a foreign key violation - where can I get a complete list of these numbers and there descritions ?
Send Mail works fine when I just want to send a message. However, when I try to send the results from a query as an attachment, I get a ODBC error. Here is example of the code and the error code that I recieve.
Use MyDatabase go exec master.dbo.xp_sendmail 'Doe,John', @query = 'Select * from tblMyTable', @subject = 'SQL Server Report', @message = 'The contents of the tblMyTable:', @attach_results = 'TRUE', @width = 250 go
I'm trying to construct a query that takes results from a table (zip codes, essentially) and strips out the results so that it's just the first 4 digits (rather than the first 4 digits + '-.....'). Which function would allow me to do this in management studio?
I have one application through which i m installing SQL Server 2005 Express Edition.
If some failure occures, all I get is one exitCode which is a return code of SQL Express installer. Is there any way by which I can find out the exact reason of failure.
One example is error code 28062.
This error code is for "sql is unable to install on compressed folder / drive".
(OK, I guess bad table design, here's the question: )I have a table Buildings and one column is consultants. Inside this column are codes of another table Consultants separated with comma i.e. 0001, 0002, 0003, .... I want to select data from Buildings and last_names of Consultants in the same query.SELECT code, *other Building columns*, consultants_last_namesFROM Buildings Please help.
Somebody should please render me some VB.NET codes for implementing an add button click event to add record to my SQL Table from my web form in asp.net application running on my local machine. I am not here with my connection string codes from which the error exists.
