updating text datatype: mssql

Showing posts with label mssql. Show all posts

Wednesday, March 28, 2012

Problem with big transfer

Problem with big data transfer

Hi,
I have MSSQl 2000 , SP3 with WIn2000
when I transfer from one table to another, 115852 records, le server hangs.
I tried with 70000 records and it worked. even with 90000 records.
With more than 90000 records, the server hangs.
Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
Thanks for your help
Olivier
How are you transferring the data?
I have used BCP, DTS and T-SQL to "transfer" data from one location to
another. I have successfully transferred more than 10x the data you are
talking about. I used T-SQL for table-table transfer (within the same
server) and DTS for server-server transfer.
Keith
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:uNrXWVySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have MSSQl 2000 , SP3 with WIn2000
> when I transfer from one table to another, 115852 records, le server
> hangs.
> I tried with 70000 records and it worked. even with 90000 records.
> With more than 90000 records, the server hangs.
> Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
> Thanks for your help
> Olivier
>
>
|||I am transfering using UPDATE Table SET (SELECT ...)
from one table to another table within the same server
That s strange
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eC2qgcySFHA.2324@.TK2MSFTNGP10.phx.gbl...
> How are you transferring the data?
> I have used BCP, DTS and T-SQL to "transfer" data from one location to
> another. I have successfully transferred more than 10x the data you are
> talking about. I used T-SQL for table-table transfer (within the same
> server) and DTS for server-server transfer.
> --
> Keith
>
> "oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
> news:uNrXWVySFHA.3176@.TK2MSFTNGP09.phx.gbl...
>
|||When the "transfer" (UPDATE) statement fails what error do you receive? I
am guessing that the drive with your data or log file on it is full. Do you
receive an error along the lines of "cannot allocate space...full?"
Keith
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:uM2vJkySFHA.2128@.TK2MSFTNGP14.phx.gbl...
>I am transfering using UPDATE Table SET (SELECT ...)
> from one table to another table within the same server
> That s strange
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eC2qgcySFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
|||First check your source table .... can you retrieve more than 90000 row
from source table ?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eX91hsySFHA.584@.TK2MSFTNGP15.phx.gbl...
> When the "transfer" (UPDATE) statement fails what error do you receive? I
> am guessing that the drive with your data or log file on it is full. Do
you[vbcol=seagreen]
> receive an error along the lines of "cannot allocate space...full?"
> --
> Keith
>
> "oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
> news:uM2vJkySFHA.2128@.TK2MSFTNGP14.phx.gbl...
are
>
|||yes, i run the SQL query SELECT top 90000 and it worked.
i even put the data in two different tables and i managed to transfer them
but when i try to transfer all in one go, it hangs
one of my colleague find that
http://support.microsoft.com/?kbid=892205
can it be the cause ?
"John" <joh@.mailcity.com> wrote in message
news:%23crW6ozSFHA.3980@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> First check your source table .... can you retrieve more than 90000 row
> from source table ?
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eX91hsySFHA.584@.TK2MSFTNGP15.phx.gbl...
I[vbcol=seagreen]
> you
to[vbcol=seagreen]
> are
same[vbcol=seagreen]
server
>
|||SELECT top 90000 * from tablename order by 1 desc
is it working ? I am just woundering like your table is alright or not...
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:erAp#B0SFHA.3156@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> yes, i run the SQL query SELECT top 90000 and it worked.
> i even put the data in two different tables and i managed to transfer them
> but when i try to transfer all in one go, it hangs
> one of my colleague find that
> http://support.microsoft.com/?kbid=892205
>
> can it be the cause ?
>
>
>
>
> "John" <joh@.mailcity.com> wrote in message
> news:%23crW6ozSFHA.3980@.TK2MSFTNGP12.phx.gbl...
row[vbcol=seagreen]
receive?[vbcol=seagreen]
> I
Do[vbcol=seagreen]
> to
you[vbcol=seagreen]
> same
> server
records.
>
|||this command is working
My server is using Hyper threading, is this ring a bell ?
"John" <joh@.mailcity.com> wrote in message
news:etGP8E0SFHA.3184@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> SELECT top 90000 * from tablename order by 1 desc
> is it working ? I am just woundering like your table is alright or not...
>
> "oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
> news:erAp#B0SFHA.3156@.TK2MSFTNGP15.phx.gbl...
them[vbcol=seagreen]
> row
> receive?
> Do
location
> you
> records.
>
|||Hi
Probably not. Does sp_who2 show increasing CPU and IO whilst the process has
"hung"?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:%237TiTK0SFHA.612@.TK2MSFTNGP12.phx.gbl...
> this command is working
> My server is using Hyper threading, is this ring a bell ?
>
>
> "John" <joh@.mailcity.com> wrote in message
> news:etGP8E0SFHA.3184@.TK2MSFTNGP14.phx.gbl...
> them
> location
>

Monday, March 26, 2012

Problem with assebly

I have problems with referensing assebly to my report. I copy my.dll to
\MSSQL\Reporting Services\ReportManager\bin
and
\MSSQL\Reporting Services\ReportServer\bin.
I have added the assembly to the report using the Referecnce tab poniting to \MSSQL\Reporting Services\ReportServer\bin but i still get error File or assembly name AssTest, or one of its dependencies, was not found.
In assebly i have simple function and it's static, i call method like this =Namespace.Class.Method.
Please, help.
AleÅ¡I did mistake in path to dll
AleÅ¡
"AG, NLB d.d." wrote:
> I have problems with referensing assebly to my report. I copy my.dll to
> \MSSQL\Reporting Services\ReportManager\bin
> and
> \MSSQL\Reporting Services\ReportServer\bin.
> I have added the assembly to the report using the Referecnce tab poniting to \MSSQL\Reporting Services\ReportServer\bin but i still get error File or assembly name AssTest, or one of its dependencies, was not found.
> In assebly i have simple function and it's static, i call method like this =Namespace.Class.Method.
> Please, help.
> AleÅ¡
>
>sql

Problem with ASP and SQL ODBC connection

Hi everyone, I need your expert advice. I have a w2k AD service with mssql on
it. and I setup another w2k server with IIS on it as webserver. my website
works on NT servers, but when I moved the website to the w2k server, it
doesn't work. It saids:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
/media/IIS_Gen_3.0_Recordset.js, line 366
My problem not stops here, as my website created by a program, therefore, I
don't know how to change the open connection statement. so therefore, I can
only find a way to overcome this user account login problem.
=?Utf-8?B?UGF0cmljayBUYW5n?= <PatrickTang@.discussions.microsoft.com>
wrote in news:E04EF7D8-542E-455B-A5C3-D134A92653F2@.microsoft.com:
I'm up against a similar problem, but having checked out KB 247931 I'm
still baffled.
AFAICS, the browser takes the authentication happily, but sql 2k gives me
either a '\' as the failed login or the same as below.
Yet the users are all members of a domain group that has access set
up....

> Hi everyone, I need your expert advice. I have a w2k AD service with
> mssql on it. and I setup another w2k server with IIS on it as
> webserver. my website works on NT servers, but when I moved the
> website to the w2k server, it doesn't work. It saids:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'NT AUTHORITY\ANONYMOUS LOGON'.
> /media/IIS_Gen_3.0_Recordset.js, line 366
> My problem not stops here, as my website created by a program,
> therefore, I don't know how to change the open connection statement.
> so therefore, I can only find a way to overcome this user account
> login problem.
>
sql

Problem with ASP & SQL ODBC connection

Hi everyone, I need your expert advice. I have a w2k AD service with mssql on
it. and I setup another w2k server with IIS on it as webserver. my website
works on NT servers, but when I moved the website to the w2k server, it
doesn't work. It saids:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
/media/IIS_Gen_3.0_Recordset.js, line 366
My problem not stops here, as my website created by a program, therefore, I
don't know how to change the open connection statement. so therefore, I can
only find a way to overcome this user account login problem.
Hi
Since it is and IIS site, the local NT IIS account, IUSR_<machinename>, will
be the user coming through on the connection. You need to give it rights in
you DB.
The best way to do this is to create a domain account for IIS, give it
rights in your DB and then re-configure IIS to use that for anonymous access.
There are security implications, so make sure that you give the user the
least rights at domain and DB level.
Regards
Mike
"Patrick Tang" wrote:

> Hi everyone, I need your expert advice. I have a w2k AD service with mssql on
> it. and I setup another w2k server with IIS on it as webserver. my website
> works on NT servers, but when I moved the website to the w2k server, it
> doesn't work. It saids:
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'.
> /media/IIS_Gen_3.0_Recordset.js, line 366
> My problem not stops here, as my website created by a program, therefore, I
> don't know how to change the open connection statement. so therefore, I can
> only find a way to overcome this user account login problem.
>

Friday, March 23, 2012

Problem with ADO and MSSQL

We have just changed all of our DB functionality from the BDE with the standard component query to ADO with the ADOquery. The follow doesnt work anymore now :

qRooms->Close();
qRooms->SQL->Clear();
qRooms->SQL->Add("Select * from Billeting where (SDATE >=:startdate and");
qRooms->SQL->Add("SDATE <=:enddate) or (EDATE >=:startdate and");
qRooms->SQL->Add("EDATE <=:enddate) or (SDATE <:startdate and");
qRooms->SQL->Add(" :startdate < EDATE) order by BLDG");
qRooms->Parameters->ParamByName("startdate")->Value = StrToDate(VOQDate);
qRooms->Parameters->ParamByName("enddate")->Value = (StrToDate(VOQDate) + 14);
qRooms->Open();

Why doesnt this work? What am I doing wrong?What programming environment are you using ?|||Borland C++ Builder 6|||I am not familiar with bc++ 6 builder - but does it matter that no spaces exist between the 1st and 3rd add methods (I put # where a space was lacking) ?

qRooms->SQL->Add("Select * from Billeting where (SDATE >=:startdate and #");
qRooms->SQL->Add("# SDATE <=:enddate) or (EDATE >=:startdate and #");
qRooms->SQL->Add("# EDATE <=:enddate) or (SDATE <:startdate and");|||What error are you getting?

Is it a runtime error, compile error - do you get an error at all..

More details please.

Stefan|||No errors, I just don't get all the data back. I run that in SQL analyzer and I get all the correct data back, but in Builder I only get some of the data back.sql

Wednesday, March 21, 2012

problem with @@IDENTITY

In MSSQL I have auto incrementing PK for the row I'm inserting into. The the
code I use below is to insert into the table. What I need is as soon as I
insert the record, I also need to return back what the PK was of my newly
inserted row. I will need to use this value for another section of the FORM
for use as a FK into another table in which I going insert some other data
based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help is
greatly appreciated. I pretty new at this stuff and just learning. Thank
You.
You forgot to include your code. Are you using a stored procedure or an
insert statement to insert the data?
Whatever option you are using you need to SELECT @.@.identity immediately
after the statement that performs the insert. If you are running SQL Server
2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
create proc foo
@.param type.....
as
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
go
Keith
"news.microsoftnews" <sapk81@.yahoo.com> wrote in message
news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> In MSSQL I have auto incrementing PK for the row I'm inserting into. The
the
> code I use below is to insert into the table. What I need is as soon as I
> insert the record, I also need to return back what the PK was of my newly
> inserted row. I will need to use this value for another section of the
FORM
> for use as a FK into another table in which I going insert some other data
> based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help
is
> greatly appreciated. I pretty new at this stuff and just learning. Thank
> You.
>
|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OcORPnHsEHA.3876@.TK2MSFTNGP15.phx.gbl...
All relevant stuff.
In addition, if the table you are inserting into has an INSERT trigger which
in turn performs a 'cascaded' INSERT into another table that also has an
identity the value of @.@.IDENTITY or scope_identity() will be that of the
the other table. Beware. THere is a workaround though:
You MUST contrive to cache @.@.IDENTITY coming into your trigger
(i.e. set @.myid = @.@.IDENTITY)and reset it before leaving. If you don't do
this, Access will not be able to correctly track the row inserted and you
will get error
messages (like, the row does not satisfy the underlying criteria, or some
such).
Here is an SQL 2000 idiom to reset @.@.IDENTITY to @.myid (should be done as
the last thing before the trigger exits):
EXECUTE (N'SELECT Identity (Int, ' + Cast(@.myid As Varchar(10)) + ',1) AS id
INTO #Tmp'
G'luck
Malcolm Cook - mec@.stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

> You forgot to include your code. Are you using a stored procedure or an
> insert statement to insert the data?
> Whatever option you are using you need to SELECT @.@.identity immediately
> after the statement that performs the insert. If you are running SQL
Server[vbcol=seagreen]
> 2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
>
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> create proc foo
> @.param type.....
> as
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> go
> --
> Keith
>
> "news.microsoftnews" <sapk81@.yahoo.com> wrote in message
> news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> the
I[vbcol=seagreen]
newly[vbcol=seagreen]
> FORM
data
> is
>
sql

Tuesday, March 20, 2012

Problem with " , " and " "

I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?whether you use stored procedures or straight sql instructions from a VB client, you still need to pass parameters and if you need to pass a string parameter that contains a single quote (') insert another quote just next to it and it should be fine. As for commas, a string parameter containing a comma and delimited by two single quotes should work fine.

try in QA:

create table #temp(field1 varchar(500))
insert into #temp(field1) values ('test1''')
insert into #temp(field1) values ('test2 ,')
select * from #temp
drop table #temp|||...but use stored procedures anyway...|||is it nececessary to user storedprocedures for data inserting?
cant we use

sSql = "insert into tablename values (" & var1 & "," & var2 & ")"
dbConn.Execute sSql

cud u pl tell, if thers ne advantage in using sp for data insertion|||Using an sp for data insertion can have the advantage of shielding the db layout from applications, so applications may not have to be modified, recompiled and distributed if changes are made. Some database administrators like to know all the update/insert statements that could be executed so they can tune the database. It might also help seperating business logic from your applications.

I'm sure there are more, but these are the ones I can come up with.

One thing I haven't mentioned is that the company you work for may have chosen for one type of approach (having all in vb or all in sp), which sort of overrules advantage/disadvantage.|||apart from separating the business logic from applications, will there be an improved performance for large insert/update statements while using an sp

i.e, for an insert statement like

sSql = "insert into table1(field1,field2,....fieldn) values ("
& val1 & "," & val2 & "," .... & "," & valn & ")"
dbConn.Execute sSql

pl post ur comments|||I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?

you can use this code to sole ur Problem

Pvar_DataBase.Execute "insert into " & TablName _
& "(Filed01,Filed02)" _
& " Values('" & value01 & "','" & Single_Qute(Value02) & "')"

Public Static Function Single_Qute(String_Value As String) As String
Single_Qute= Replace(String_Value, "'", "''")
Single_Qute= Replace(String_Value, ",", "''")
End Function

If u have any problem

send me to
tgamil@.egysoft-it.com

Best Regards

Tarek Gamil