|
I know is not very good, but it was only a test. The type of the fields will be changed when i start codding seriously.
10x for the link.
|
|
|
|
|
Hi,
recently I needed a tool to run SQL scripts against a Microsoft Access database. I found OleDbCmd on Sourceforge.net (http://sourceforge.net/projects/oledbcmd/[^] which did the trick.
However it had a number of shortcomings, so I extended it somewhat and have released my updates through sourceforge.net as version 1.0. These usage notes should explain the extent of the updated application:
----------------------------------------------------------------------------------------------------------------
Purpose:
This program allows you to execute SQL commands against an OleDB Database
- eg a Microsoft Access database, an Excel spreadsheet, etc. This can be a
powerful adjunct to LGX Report (see www.freereporting.com) as it allows
database creation to be scripted rather than manually creating a database
with Microsoft Access. Another useful free tool to load data into your
database is Microsoft's LogParser 2.2 - search their site for the latest
download location for this tool.
Usage:
OleDBCmd [/help] [/ver] [/connect=<oledb connect="" string=""> | /file=filename] ...
[/create] [/script=<sql script="" filename=""> | /cmd="sql command" ...
[/quiet] [/CSV] /nohead] [/starComments]
Parameters:
/help Print the usage details and exit.
/ver Print the version and license details.
/connect Specifies the OleDB connection string for the database, eg
'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=test.mdb'
/file Specifies a file name, the provider will be set to
Microsoft.Jet.OLEDB.4.0. Both .mdb and .xls files are
handled.
/create The program will attempt to create the database.
/script Specifies a script file for the SQL commands to be
read from. Note, commands may span multiple lines and
must be terminated with a semi-colon. Remark lines must
start with -- following any leading whitespace.
/cmd Specifies a single SQL command to execute.
Note, if neither the /script nor /cmd arguments are given
commands are read from the console - comment processing
is not performed.
/quiet Suppresses command echo and status messages. Only SQL
output is written to the console.
/CSV SQL results are output in CSV format rather than the default
TSV format.
/nohead Excludes the header row from the results.
/starComments Allows embedded and multi-line comments using /* ... */ format.
----------------------------------------------------------------------------------------------------------------
As noted above, I found this useful in conjuction with the free web reporting tool - LGX Report (http://www.freereporting.com/[^]) as I can then design databases using Enterprise Architect from http://www.sparxsystems.com.au/[^] and generate the script to create the database and run it with OleDbCmd.
Hope this is useful to others out there looking to leverage free and low-cost tools to build solutions quickly.
Regards,
Ian.
Ian Hogan - Consultant
THINKronicity Pty Ltd
http://THINKronicity.com.au/[^]
|
|
|
|
|
i want to make an select statment like that
select * from Table_name Where ID=2 and ID=4 and ID=5 while the Id in not the primery key
amarni
|
|
|
|
|
|
sorry it is not primery key
but i need this
imagine that i have three table
x has X_ID(Primery) and Name,..... atrribute
y has Y_ID(Primery) and desc,..... atrribute
the relation between them is many to many
so that i creat a table between them named xy that conatain both IDs X_ID and y_ID so that i want to make select statment like that select * from x Where Y_ID=2 and Y_ID=4 and Y_ID=5
i hope you understatnd it now
Amarni
|
|
|
|
|
Could you please post exactly what you want to arrive ?
|
|
|
|
|
I think you just want a join like this:
SELECT DISTINCT x.*
FROM x
INNER JOIN xy
ON x.x_id = xy.x_id
WHERE xy.y_id in (2,4,5)
|
|
|
|
|
Hi there!
Anyone having any idea that how can i configure SQL MAIL on my machine as i am working on a LOCAL MACHINE on which i have installed SQL Server 2000 and VS .NET 2003 as i am working on ASP.NET. No server access i am having. My machine is acting as the localhost. I have tried configuring SQL MAIL on my machine but failed. And I have searched on net SQL Server uses two services to handle mail : MSSQLServer-provide SQL MAIL feature and SQLServerAgent(SQLAgentMail)...
I need to use Trigger to send an email notificaltion to reciepents for which i have written a trigger but on Updating the table it shows me the following error...
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.
(1 row(s) affected)
Any sort of help will be appriciated.
Thanks a Lot in advance...
Bhawna
bhawna
|
|
|
|
|
Have you read this: clickety[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
My issue is the update of a dataBase table with a dataSet table as a parameter using C# and SQL Server 2005.
Below is the function I used in my code ---> verry simple.
I noticed that in line :updates = da.Update(tmpDS, tableName) ,updates variable is 0 ,so no update possible.
Perhaps this is not the way to do the update.
IF you can help me with some hints ,
Thanks in advance!
public DataSet dsUpdateData(DataSet ds, string tableName, string ConnectionString)
{
DataSet tmpDS = new DataSet();
try
{
SqlDataAdapter da = new SqlDataAdapter(SQLQuery + tableName, ConnectionString);
int updates = da.Fill(tmpDS,tableName);
foreach (DataRow dataRow in ds.Tables[tableName].Rows)
{
tmpDS.Tables[tableName].ImportRow(dataRow);
tmpDS.Tables[tableName].AcceptChanges();
}
updates = da.Update(tmpDS, tableName);
return tmpDS;
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
}
-- modified at 14:43 Tuesday 18th July, 2006
|
|
|
|
|
Check your table names. It appears you are importing rows into tableName1 but trying to update tableName
|
|
|
|
|
Yes you where right on that,but it was just a typo error tableName1.
Even so, it is still not working.
I modified the original message.
Thank you anyway!
|
|
|
|
|
Hi
in some applications i have seen that DataGrids have a Control which is the number of the DataGrid's Row and it has next( > ), previous, last and ...
whats the name of that control?
thanks
|
|
|
|
|
hamidreza_buddy wrote: whats the name of that control?
BindingNavigator or something like that?
|
|
|
|
|
Hi,
There is a requirement to insert data into Bulk like 20,000 - 30,000 rows into database (sql server 2005).
Description:
I have to get one number from text file,bifercate that number and from 1st 5 digit i have to find out ID from sql server 2005 table and then go for second number of that number ,find out ID from sql server 2005 table,this process happens for 4 times for that one number and i have to do same process for 20000 rows.
Once i get all those IDs into variables into vb.net from sql server 2005,i have to insert those into one physical table and than one package will run that insert those records into another table.
Here main problem i am feeling is speed issue,to process 20000 rows for getting ids and inserting back into sql server takes somuch time.
So can some body help me out which way to go to insert records into database using sql server 2005?
thanks
montu3377
|
|
|
|
|
You can Bulk insert (using BCP or managed SqlBulkCopy) the text file contents to a table and use a stored proc to do read from that table and insert/update into the target table.
|
|
|
|
|
Hi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks, Jessica
|
|
|
|
|
Why don't you give it a try, post your code, and we will help you where you are struggling. What you have provided here sounds too much like a school assignment. People aren't likely to do your assignment for you - but they will help you figure out the solution, provided you put forth an effort.
That's just my suggestion.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Nasty requirements - here is a first attempt using a cursor. I think I would change the EndDate calculation to a function so that you could remove the cursor.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @cnt INTEGER
DECLARE cur CURSOR FOR
SELECT
AbsentDate
FROM
#tmpTable
OPEN cur
FETCH NEXT FROM cur INTO @StartDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = 0
SET @EndDate = @StartDate
WHILE @cnt < 6
BEGIN
SET @cnt = @cnt + 1
SET @EndDate = DATEADD(d,1,@EndDate)
WHILE (DATEPART(dw,@EndDate) IN (1,7)) OR
(EXISTS(SELECT * FROM Holidays
WHERE HolidayDate = @EndDate))
BEGIN
SET @EndDate = DATEADD(d,1,@EndDate)
END
END
IF ((SELECT COUNT(*)
FROM #tmpTable
WHERE AbsentDate BETWEEN @StartDate AND @EndDate) > 5)
BEGIN
-- Save Date
-- or do other desired processing
END
FETCH NEXT FROM cur INTO @StartDate
END
CLOSE cur
DEALLOCATE cur
|
|
|
|
|
Hi,
When I have the following:
db.AddOutParameter(dbCommand, "@ReturnCode", DbType.Int32, 4);
What does the 4 mean? Does it mean the highest number will 9999? Or what?
When adding the follwoing datatypes, what does the size indicate:
int - size = 4
ntext - size = 16
Please can someone help me with my confusion.
Regards,
ma se
|
|
|
|
|
The int size of 4 refers to the number of bytes required to store an integer (which has a precision (i.e. max number of digits) of 10). The 16 for an ntext size refers to the bytes to hold a pointer to the actual ntext data, which is stored outside of the sql server leaf.
|
|
|
|
|
Number of bytes.
In this case, as 32 bit integer takes up 4 bytes (32/8 = 4). Normally, I only add the size to chars, varchars and varbinarys. The defaults for the standard fields (like Int32) is well known by the system.
|
|
|
|
|
Hi
I have an SQL database under a MS2005 SQL Server.
This is a ~200MB databse that grows from time to time, only one person (Administrator) adds\removes data from this database.
On the other side I have a client application that uses this database. At the moment the the SQL database is locked and has stored procedures as an "API".
Since allot of users accessing the data is CPU consuming and takes allot of bandwidth I would like to have the database localy on the clinet side, *locked*, enabeling only the execution of the stored procedures with an option to sync' with the main database on the SQL server. The end user can't add\remove items from the main\local database nor does he have the login\password for his local database.
I started looking into this and found SQL express and SQL mobile (everywhere).
My quersion is, which of the above is more suited for my application ?
Thanks,
Gilad.
|
|
|
|
|
|
Thank you for your quick answer.
At the moment the database is ~200MB growing at a rate of 80k per day, thats about 30MB a year, not too much. 99.999% of the data never changes so I would like the history data to stay on the client side.
The client gets all the data it needs and just the data it needs, nothing extra. This is very optimized.
I have many users accessing the database at once and would like only to send them the 80k daily "update data" rather then all the data.
Gilad.
|
|
|
|