|
|
Yes it is.
I found, somewhere in MSDN library, that if there is a recordset opened, the BeginTrans doesn't work. In my case, I have a recordset opened. I tryed to do the operation while the recordset was closed and that worked. The problem is that I need the recordset to be opened.
Vinicius
|
|
|
|
|
I am needing to copy data from an MSSQL table to a dbaseIV table and would ideally like to do this with an SQL trigger on the source table.
Can someone please advise if it is possible to do this and if so provide some clues on how to go about this? I have established an ODBC connection to the dbase table folder but I cannot resolve how to address this from Query Analyser.
Many thanks,
Kerry
Kerry Johnson
Business Systems Consultant
|
|
|
|
|
Hi.
I'm trying to connect to a remote SQL serer express through TCP/IP.
I entered the <IP address\instance name> in the server name field. I tried also without the instance name.
I tried with integrated security as well as authentication. Also, I tried with the port address, and I get the error message "TCP Provider: Connection closed forcibly by the remote computer".
P.S. the protocol is enabled on noth the local and remote sides.
Can anyone explain me please how to establish a connection?
Thanks,
Yaakov
|
|
|
|
|
|
I'm trying to connect from the VS IDE.
I own both the server and the client, and I see no problem on both.
Can you reffer me to a relevant artical?
Thanks,
Yaakov
|
|
|
|
|
Hi.
I want to change a column in a table from BIGINT to DECIMAL or NUMERIC.
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations (like multiply and adds) on these data types (dec and numerics).
and, btw, what is the diffrence between 'NUMERIC' and 'DECIMAL'?
thanks
|
|
|
|
|
Menny Even Danan wrote:
what is the diffrence between 'NUMERIC' and 'DECIMAL'?
Reading the documentation indicates that they are synonyms for each other.
Menny Even Danan wrote:
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations
Normally the main killer in performance is to do with volume of data being processed(*) so I'd say that on a large dataset any performance hit is negligable. So, I'd suggest that you are careful about the size of the data. This table from the books on-line will help:
+-----------+---------------+
| Precision | Storage bytes |
+-----------+---------------+
| 1 - 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
+-----------+---------------+
[EDIT] Missed a bit: By comparison a BIGINT is 8 bytes in length but is constrained to a scale of 0 (nothing after the decimal point) and has a maximum value of (2^63)-1 while a NUMERIC/DECIMAL has a potentially much greater range [/EDIT]
* not necessarlily the same as volume of data returned
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi All,
I am wondering if anybody know sql statement of how to import all the text from the text file into Access database field?
I have a text file, a.txt and afield called TextBody in the table.
TextBody is a memo size.
I was inserting like following
<br />
aTextfile.open("c:\\a.txt",ios::in);<br />
if(aTextfile)<br />
{ <br />
while(!aTextfile.eof())<br />
{<br />
aTextfile.getline(data,1024,'\n');<br />
temp.Format("%s",data);<br />
<br />
qstr.Format("UPDATE %s Set TextBody = TextBody & '%s' ",tablename,temp);<br />
rs.m_pDatabase->ExecuteSQL(qstr);<br />
}<br />
<br />
aTextfile.close();<br />
}<br />
But it takes so long to import all the text from the a.txt. Is there any better way?
Thanks
|
|
|
|
|
Can you read the whole text file into a string and then write the string to the database?
|
|
|
|
|
I'm trying to create a query that counts records based on time periods. I'm querying two tables, one containing SubmittedDate, the other CompletedDate. I'm using DateDiff to calculate the time difference between the two. What I want to do is end up w/ a count for mulitple periods of time like....
< 2 Hours 3
2 - 4 Hours 6
4-8 Hours 10
1 Day 12
1 - 3 Days 8
5 Days 4
> 5 Days 2
Any ideas?
Jeff Martin
My Blog
|
|
|
|
|
|
Please tell me! how can i get all tables name in access file (.mdb) ?
Thanks!
xyz
|
|
|
|
|
I will assume that you do not have Access and/or can not use it.
When you use the Data Adapter Configuration Wizard, it will show the tables available and after you have choosen the one(s) you want, it will show you the fields in the table.
|
|
|
|
|
Try this, this is working on SQL Server.
Create Sample That Lists Tables in a Database
The following sample lists tables in the SQL Server Northwind database.
OleDbSchemaGuid.Tables returns those tables (including views) that are accessible to a given log on. If you specify an Object array of {Nothing, Nothing, Nothing, "TABLE"}, you filter to include only a TABLE_TYPE of TABLE. You then list the table name (TABLE_NAME) of each row in the returned schema table. 1. Start Visual Studio .NET.
2. Create a new Visual Basic Console Application project. Module1.vb is added to the project by default.
3. Open the Code window for Module1. Paste the following code into the top of the Code window, above the Module declaration: Imports System.Data
Imports System.Data.OleDb
4. In the Code window, paste the following code into the Sub Main procedure.
Note You must change User ID <username> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
Dim cn As New OleDbConnection()
Dim schemaTable As DataTable
Dim i As Integer
'Connect to the Northwind database in SQL Server.
'Be sure to use an account that has permission to list tables.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;Password=;Initial Catalog=Northwind"
cn.Open()
'Retrieve schema information about tables.
'Because tables include tables, views, and other objects,
'restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
'List the table name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Console.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
Next i
'Explicitly close - don't wait on garbage collection.
cn.Close()
'Pause
Console.ReadLine()
|
|
|
|
|
Hi all.
I have a program running from Start > Programs > Startup.
When I start Winows, the program starts automatically, and I get SqlException (Sql Server Express 2005).
If I start the program manually, It starts normally.
I think that the program is loaded before the SQL service.
How can I check or change this?
Thanks,
Yaakov
|
|
|
|
|
You can wait until the service is started. Check This[^]. Remember to start the service automatically when you start your OS.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
Wow, thanks a lot.
I didn't know that.
|
|
|
|
|
Hi, I'm choosing between transaction on stored proc level and transaction on application level. You see:
<br />
DECLARE @ValidationCode INTEGER<br />
BEGIN TRAN<br />
SET @ValidationCode = 0<br />
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT<br />
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))<br />
<br />
... do other stuff...<br />
<br />
COMMIT TRAN<br />
<br />
CREATE PROCEDURE sp_CreateSupplier(<br />
@supplier_no INTEGER,<br />
@type VARCHAR(8),<br />
@group_no INTEGER = NULL,<br />
@short_name VARCHAR(50) = NULL,<br />
@full_name VARCHAR(100) = NULL,<br />
@address VARCHAR(100) = NULL,<br />
@country_code VARCHAR(10) = NULL,<br />
@status VARCHAR(8) = NULL,<br />
@createBy INTEGER,<br />
@ownerGroup INTEGER,<br />
@ValidationCode INTEGER OUTPUT<br />
)<br />
AS<br />
BEGIN<br />
<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
BEGIN TRANSACTION<br />
<br />
-- STEP 1: Validation<br />
SET @ValidationCode = 0<br />
IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)<br />
BEGIN<br />
SET @ValidationCode = -10<br />
ROLLBACK TRANSACTION<br />
RETURN<br />
END<br />
...<br />
COMMIT TRAN<br />
END<br />
The above snippet will result in:
Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26<br />
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):
Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier<br />
<br />
Dim sql As String = "sp_CreateSupplier"<br />
Dim cmd As IDbCommand<br />
Dim rdr As IDataReader<br />
Dim param As IDataParameter<br />
Dim factory As IDbProviderFactory<br />
Dim validationCode As Int32 = 0<br />
<br />
Try<br />
If _conn.State <> ConnectionState.Open Then<br />
_conn.Open()<br />
End If<br />
<br />
factory = apputil.GlobalVariables.ProviderFactory<br />
cmd = factory.CreateCommand(sql, Conn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Transaction = tran<br />
<br />
...<br />
rdr = cmd.ExecuteReader()<br />
...<br />
Catch<br />
...<br />
Finally<br />
...<br />
End Try<br />
End Function
What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:
<br />
vconn = vFactory.CreateConnection(...)<br />
tran = vconn.BeginTransaction(...serializable...)<br />
SupplierBean.Create(..., tran)<br />
AnotherBean.Update(..., tran)<br />
tran.Commit()<br />
So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?
Thanks in advance!
Norman Fung
|
|
|
|
|
declare @intnewmoviecode int
declare @vcmovieplot varchar(400)
declare @intreturn int
set @vcmovieplot=
'although the fellowship is shattered,war continues'+
'to fill the lands of middle earth.frodo heads toward'+
'mordor iwth sam,while in the west,' +
'saruman continues his plot against rohan.'
exec @intreturn=
spinsertdata'the two towers',2002,
@vcmovieplot,200,@intnewmoviecode output
select * from movies where moviecode=@intnewmoviecode
select @intreturn
declare @intreturn int
declare @intnewmoviecode int
exec @intreturn=
spinsertdata 'An old movie',1940,
'this movie won''t be added',200,
@intnewmoviecode output
select @intreturn
Error converting data type varchar to int.
aaffi
|
|
|
|
|
You are probably trying to insert varchar (string) data where int is required.
Check your spinsertdata and the input variables declared.
BTW, do remember not to name your stored procedure as sp_*** as this will incur a performance penalty.
Hope it helps,
Edbert
|
|
|
|
|
 declare @intnewmoviecode int
declare @vcmovieplot varchar(400)
declare @intreturn int
set @vcmovieplot='although the fellowship is shattered,war continues'+
'to fill the lands of middle earth.frodo heads toward'+
'mordor iwth sam,while in the west,' +
'saruman continues his plot against rohan.'
exec @intreturn=
spinsertdata'the two towers',2002,
@vcmovieplot,200,@intnewmoviecode output
select * from movies where moviecode=@intnewmoviecode
select @intreturn
aaffi
|
|
|
|
|
|
Like the previous post suggests, it would be very helpful if you would say what kind of trouble you're having with the code provided.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Hello,
I make a Database in SQL SERVER which has 9 tables. I want to copy that database to another computer, how can i do this.
kindly tell me the procedures, so that i can transfer database to another computer, along with its inserted data
EsHbAn BaHaDuR
EsHbAn BaHaDuR
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
