does anyone know of tools that allow free format screen design, easily programmable data validation and prgramming for use with SQL. I am used to using 4GL languages and want to avoid low level programming. For example to fifind a record and display the details i am used to a line of code that would allow the data field to do the find on to be entered and the next line of code would say find fieldname using index name and the results displayed
please say yes !!!
Table table_2 is an intersection table between table_1 and another lookup table (called task). Whenever I delete from table_1 I need to delete from table_2 to maintain integrity. The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).
In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete. In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:
select * from table_1 WITH (UPDLOCK), table_2 WITH (UPDLOCK) where table_1.id = table_2.table_1_id " and table_1.id = ? and table_2.task_id = ?
I then issue my deletes in the following order:
delete from table_1 where task_id = ? and table_1_id = ?
and then,
delete from table_1 where id = ?
The application sets the isolation level to READ COMMITTED. The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end. When the deadlock occurs these are the locks that are held by the running processes:
Blocked SPID (10): ============
Object Lock Type Mode Status Owner Index Resource app DB S GRANT Sess TABLE_2 app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2 app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c) app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3 app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443 app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157 app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1) app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea) app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2 app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252
Blocking SPID (12): ============= Object Lock Type Mode Status Owner Index Resource app DB S GRANT Sess TABLE_2 app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1) app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3 app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2 app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252 app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176
I could not figure out what the problem is. Please help me with this. Any help will be much appreciated.
I have a client running RMS, since moving to SQL express his database size has jumped 2 from 2G to 4G in 8 months. Previiuosly it took 2 years to reach the 2G size. has anyone else experienced this rapid growth of their database?
what the program will do if we caught that exception .. i need some suggestions ... i got this exception(String or binary data would be truncated. The statement has been terminated.).. will it affect the functionality of the program...
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  :  Nestle milk | Rainbow milk packages  : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos  | CTN=8 Nos
i have a program which should give the out like this XAPIALTMKT20010829105525.TXT
but its giving the output = 'X' which is just the first initial...how can i get this...
if i don't write the @filename=.... only then its giving the output..but i want to store it in the varable..how can i do that ================================ declare @v datetime , @filename varchar
It is very interesting to me that most people in this group can't do simpleSQL. Are you viewing MySQL as a simple file system?As long as you do that,you willnot understand SQL in any dialect. So from now on:When you have a question about SQL. post the table structures. Uh, "WithCreate".Post some sample data in the form of INSERTS.Regards,Rich--The journey is the reward.
I am trying to send an email from a cmd prompt from SQL Server 2005 server using mailsend program. I am getting the message "Could not send mail". I am really stumped, don't know how to fix it. Any suggesstions?
I have installed SQL Server 2005 and created a job. I made the job failed to test the mailsend program. The notification step that uses the mailsend succeeded but I did not get an email. I am using mailsend program on SQL Server 2000 and it works fine. I am really stomped and don't know what to do to fix it?
tblHIT - this table will reveive record every minutes Date | Time | Main_ID | Hit ------------------------------------ 3/1/2006 | 100 | 200 | 8700 ... ... ... 4/14/2008 | 100 | 200 | 4500 4/14/2008 | 100 | 201 | 8700 4/14/2008 | 200 | 200 | 3500 4/14/2008 | 300 | 201 | 7700 ... ...
I've also 1 job in SQL Server Agent will execute STORED PROCEDURE and will filter 7 days record before from current date and insert it into tblHIT_7days. tblHIT_7days shown as follow tblHIT_7days Date | Time | Main_ID | Hit ------------------------------------ 3/7/2008 | 100 | 200 | 8700 ... ... ... 4/13/2008 | 100 | 200 | 4500 4/13/2008 | 100 | 201 | 8700 4/13/2008 | 200 | 200 | 3500 4/13/2008 | 300 | 201 | 7700
I've plan to create TRIGGER. This TRIGGER will insert the appropriate record from tblHIT into tblHIT_7days and also delete a appropriate record in tblHIT_7days. This INSERT and DELETE transaction depend on current date.
I am about to build a program that will require the use of DB. In the pass I used Access as my back end, but this time I would like to use Sql Server. I just have a few questions before I begin.
1. After deploying and installing my program, do I need to have sql server installed on users system or can the DB file exist on its own and just use ADO.NET to access the file? (As I done in the pass to access my Access mdb file)
2. If I can deploy a sql file -- can it be done with Sql Server Express?
3. If it can't be done, what db program should I use?
On some occasions when trying to send data back to the server, the program will lock up for up to 30 minutes. The code below is the rda data transfer process and the function IsConnected() determines whether there is a connection.
If Not MainFrm.IsConnected Then rda.ConnectionManager = True End If If MainFrm.IsConnected Then Dim sCon As String = "Provider=SQLOLEDB;Data Source=175.21.21.121;Initial Catalog=OurDb;User ID=UserId;Password=pwd" rda.InternetUrl = "http://Ourwebsite.co.uk/tfr/sqlcesa30.dll" rda.LocalConnectionString = "Data Source=Program FilesSysdata.sdf" For i As Integer = 0 To RecCount - 1 Try rda.SubmitSql(UpdateRec(i, 0), sCon) Catch Ex As SqlCeException ErrTxt = Ex.ToString End Try Next
Public Shared Function IsConnected() As Boolean Dim request As HttpWebRequest Dim response As HttpWebResponse Try request = CType(WebRequest.Create("http://www.intel.com/"), HttpWebRequest) response = CType(request.GetResponse(), HttpWebResponse) If response.StatusCode = HttpStatusCode.OK Then response.Close() Return True Else response.Close() Return False End If Catch Ex As WebException Return False End Try End Function
There are rda properties which determine a timeout. How can I use these to stop my program from locking up if the signal is weak?
Hi, please have look into the code and let me know the solution plz. string ProID; try { using(SqlConnection conn=new SqlConnection(source)) { conn.Open(); DataSet ds=new DataSet(); DDLProject.Items.Clear(); SqlCommand cmd=new SqlCommand("SP_ProjectSelect",conn); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add("Name",SqlDbType.NChar,30,"@Name"); cmd.UpdatedRowSource=UpdateRowSource.None; cmd.Parameters["Name"].Value=DDLProductLine.SelectedItem;
SqlDataReader dr=cmd.ExecuteReader(); ------------->>>> Here iam getting Following Error.
I have sql server 2005 (full version) installed on my local machine, while the databases (on the server) I want to program against are in sql 2000. Could I program against sql 2000 databases using sql 2005? If yes, what do I need to know before I start doing that? Any suggestions are welcome.
I have written this Program For Updating the profile Information .At the Page_Load Event I read the profile information from the database and write it in the respective Textboxes .At the Button_Click event I have written a program to Update the data from the respective Textboxes.The program executes but doesnot updates .public partial class CreateProfile : System.Web.UI.Page{ SqlConnection con; SqlCommand com; string un = HttpContext.Current.User.Identity.Name; protected void Page_Load(object sender, EventArgs e) { con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Profiledata.mdf;Integrated Security=True;User Instance=True"); com = new SqlCommand("select * from profiletable where username='" + un + "' ", con); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(com); SqlCommandBuilder cb = new SqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(ds); dt = ds.Tables[0]; Textname.Text = dt.Rows[0][1].ToString(); com.Cancel(); } protected void Button2_Click(object sender, EventArgs e) { con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Profiledata.mdf;Integrated Security=True;User Instance=True"); con.Open(); com = new SqlCommand("update profiletable set name='" + Textname.Text + "' where username='" + un + "' ", con); com.ExecuteNonQuery(); com.Cancel(); con.Close(); }} Plz help me with this program........................
I need to create a program that will run Client Access to download data from the AS400 to a flat file, then run SQL DTS to import the data into a table for use by another software package.
I've created a bat file that does that using the CA RTOPCB command and the SQL DTSRUN utility. Problem is that it appears that I need to first check to see if there is a table in SQL and if there is to delete it. Otherwise, rather than overlay the existing data in the table it adds to it.
Is there a way to issue a SQL drop table in DOS? Or am I missing something that could be done in SQL?
The DTSRUN is using a local package and CA is using a transaction request.
I have this Visual Basic program that if you are already logged into the application, the program get the username and password automatically to run the program. If the user runs the program not logged into the application, the program prompts username and password.
I need to know the T-SQL statement that will get the username and password when the user is already logged into the application. Basically I have the username part but the password keeps coming back as NULL and will not allow program to run.
They have provided search string examples like 'Microsoft, DBA, CA' or 'CA' or 'Microsoft, developer' or 'training'
I have script ready to remove comma from the string and store words from the input string in a temporary table. But this is the easiest part.
The Confusing part now for me is to write the optimal code for retrieving the exact match from the key_search table as there is no limit on how many words can be in the string, it can be 1, 2, 3 or more.
Any suggestions on how should I handle this search?
Hi, My company has recently migrated our Informix database to SQL server 2K. The data is successfully migrated and the client are able to connect to the database. However, when trying to run a VB application to access the data, it keeps on prompting for SQL server login whenever an sql statement is executed. What could be the problem? We are using SQL server 2k on Windows 2000 and on the client Windows 95. Thanks!
I have a bcp which generates .txt file perfectly. I just wanted to know how can i generate a text file in distributed environment.
Assuming that my Sql Server is running in machine A. I wanted the bcp to generate in Machine B. What are the permission's i should give in order to generate it in Machine B.
Is there a stored proc or a function I can call from Query Analyzer to execute another .EXE and return when it's finished?
I'd like something like SET @RET = sp_CallEXE('c:mypathmyprogram.exe myparameters')
(Except for the name of the function, there should be something that does this right? I mean, DTS does it already so it must be referring to a function or a SP.)
I want an answer to so that I can validate that everything went well (hence the @RET variable)
Does anyone know where to purchase a good (or great) SQL hour scheduler program, and pulls up information from a SQL server and puts this information into a schedule (hourly schedule). I'm looking for something that will show the schedule that people work, the information off the SQL server needed is the "agent" name. If anyone could help me, that would be greatly appreciated.
I have the following problem. I need to insert 100.000 records (50Kb each) in one operation from a VB program into a SQL Server 2005 database. All of these records will be ready for inserting at the same time. How to make this insert as one big transaction instead of 100.000 small ones?
Hi. First 3 paragraphs are about problem in general - this is for those who consider them well experienced and may have better ideas about how to solve this whole thing. In forth paragraph is the tehnical question.
Hi. First 3 paragraphs are about problem in general - this is for those who consider them well experienced and may have better ideas about how to solve this whole thing. In forth paragraph is the tehnical question.
Situation: So me was trying to find out why files are saved in database incorrectly. First idea was to create a MD5 checksum of a file in my application and then create one with SQL script that saves data in database (data being the file, by the way).
On software side it worked fine 'cuz i'm developing in Visual Basic .NET 2005 which has a function that return a 32 byte array of hash from a data that you pass to it (like file) - GREAT!
SQL Server 2005 side - not so great. The only function that I have found so far that creates hash is HashBytes that is supposed to do it's job only there's a limintation to data size which means i cant make hash of say 4mb big file.
Problem: So I figure maybe i could create a vb.net program that return a hash from a data passed to it and call it with SQL script (because hashbytes function doesnt work for me in case you didnt read the first 3 paragraphs)... but I don't know how:( Any ideas?
Guys and GirlsI have a server that was running SQL Server 2000 (on Windows 2000server).In preperation for an upgrade from an old SQL Server 6.5 system. Iun-installed 2000. I edited the registry to remove all references toSQL Server and deleted all files. I have rebooted the server.I have run an install of 6.5. It seems to have worked all right exceptthere is no program groups on my start menu. Any ideas what might havehappened?RegardsMatt
Hello, Folks. I am not sure if this can be done (and a brief search ofthis group didn't yield any results that I thought would do the trick)but if it can be done it would make my life easier so here goes.First, what I am trying to accomplish is to make a program I've writtenautomatically update itself if there is a newer version on the network.Second, my application runs on one or more computers at manygeographically separated locations. I have remote control softwarethat enables me to remotely update and troubleshoot my application butI since I have to pay for this access per computer I don't have theability to control each and every computer that runs my software (forthose clients with more than one computer that uses my software).Third, my application uses as its data storage system MSDE 2000 (withcurrent service pack).Fourth, my users are not technical people and I want to rely on them aslittle as possible.Fifth, not all of the workstations are Internet enabled or connected,so having the application check a website for an update is notfeasible.Finally, to bring those different pieces together, what I want to beable to do is connect to and control the "server" (the computer withMSDE installed on it) and store in the database what the currentversion number is along with the current program files. Then, when theapplication launches, it will check that stored version number againstit's own version number to determine if there is a newer versionavailable. If there is a newer version available I want it to updateitself (that workstation) by retrieving that newer software from thedatabase. I want to avoid file sharing because it's problematic andsince I already have SQL Server (MSDE) running on the network I'd liketo use that.Is there some way that I can either a) store my program files (one EXEapproximately 5MB in size and one OCX approximately 250KB in size) in atable to be retrieved by my application so it can update itself or b) away to get SQL Server (MSDE) to read a file locally on the server andreturn it to my application just like it would any recordset so I couldjust tell it where the file was and let it read it from whicheverfolder it's stored in?I've looked at making a column of datatype Binary, and perhaps that'sthe way I'll go (re-creating the field to the correct size each time Ido an update or perhaps making it really big so I don't have to worryabout the size). But I thought I'd ask around first since this isn'tanything I know much about.Thank you for your time and help.--HC
Hi,I'm just looking for some general advice on how to approach something.I have two tables A and B, containg common fields of product, salesdate and qtyo.Some records contain the same data and I want to dump them in a newtable called C and leave Tables A and B containg only data thatdoesn'r match.How do i go about approaching this??Regards,Ciarán
