Expensive Update - Database Discussion Boards

Colin Angus Mackay13-Feb-07 2:14

13-Feb-07 2:14

You can put in a PRINT statement before (or temporarily in place of) the EXEC to see what is actually being generated

PRINT(@Query)
PRINT(@Where)

I think that probably the " is what is causing confusion. When expanded out the " is being seen as defining a column name. You can use [ColumnName] and "ColumnName" to define explicit column names, especially ones that contain odd characters or spaces.

Upcoming events:
* Glasgow Geek Dinner (5th March)
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Re: Problem Code

Pete O'Hanlon13-Feb-07 2:15

Pete O'Hanlon

13-Feb-07 2:15

As you are using dynamic SQL (and I don't see any reason why you need to do that), you need to put apostrophes around your variables. So, the where clause becomes WHERE UserID = ''' + @UserID and so on.

Also, the BEGIN doesn't need to be there - in fact, it causes an error.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Problematic connection string to SQL Express 2005

Joe Smith IX13-Feb-07 1:20

Joe Smith IX

13-Feb-07 1:20

Hi all,
I have a problem that have haunted me for days and no suggestion I tried from CP worked... Please help!

I have installed SQL Express 2005 on Win2003 server and created a database. Authentication is in Mixed Mode. No firewall.
Here is what happened:

LOCALLY: (login as administrator)
1. I can open it using SQL Server Management Studio.
2. I SUCCESSFULLY connect to it programmatically (VC++) with any of the following connection strings:

Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Trusted_Connection=yes;<br />

REMOTELY on XP SP2: (login as administrator and with SQL Server Native Client installed)
1. I can open it using SQL Server Management Studio (in both modes, SQL and Windows).
a. This confirms that I have correctly set up and enabled remote connection.
b. I enabled remote connection using both TCP/IP and named pipes.
c. I enabled Shared memory, Named pipes, and TCP/IP protocols.
d. I enabled and activated SQL Server Browser

2. I FAILED to connect programmatically. Here are some connection strings I've tried:
(most error messages: "Login failed for user 'admin'." (which is a renamed 'sa').

<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Trusted_Connection=yes;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Trusted_Connection=yes;<br />
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Trusted_Connection=yes;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Trusted_Connection=yes;

I've read thru www.connectionstrings.com and I don't think I missed anything, did I?
Did anyone have similar problem before, or have any idea why?
Thanks a lot.

Re: Problematic connection string to SQL Express 2005

Joe Smith IX15-Feb-07 2:02

Joe Smith IX

15-Feb-07 2:02

I finally found and fixed the problem. It was my original hunch, that 1433 is not the right LISTENING port (although the setting of SQL Express still points to 1433). To check the port, run cmd.exe and look at the result of "netstat -ano".

Hopefully someone find this useful and don't need to spend days figuring this out.

unicode problem

.NetRams13-Feb-07 1:12

.NetRams

13-Feb-07 1:12

Hi,
Is there any way to store the unicode characters in sql DB.

I want to store the unicode characters in sql DB and show it back to the user again.

Thanks in advance.

Be simple and Be sample.

Re: unicode problem

Mike Dimmick13-Feb-07 1:34

Mike Dimmick

13-Feb-07 1:34

Look into the nchar, nvarchar, and ntext data types.

Stability. What an interesting concept. -- Chris Maunder

Re: unicode problem

.NetRams13-Feb-07 1:53

.NetRams

13-Feb-07 1:53

Yes I tried with nText and stored in database. While retrieve usenig select, it will not return the unicode characters it shows some '?' mark characters.

Be simple and Be sample.

Expensive Update

Brady Kelly12-Feb-07 22:15

Brady Kelly

12-Feb-07 22:15

I just ran the following update on my local database. It has one user, me, and the update query took 57 seconds to update 111k records. That seems pretty long too me, so I assume the cost of the update is in the newid() function call. Pretty expensive.

Re: Expensive Update

andyharman12-Feb-07 22:27

andyharman

12-Feb-07 22:27

Can you provide the update statement.

Re: Expensive Update

Colin Angus Mackay12-Feb-07 22:35

Colin Angus Mackay

12-Feb-07 22:35

You might want to provide the actual update statement. It might be something else that you are doing that is expensive. I've updated hugh quantities of rows with a NEWID() before and it wasn't this long.

Upcoming events:
* Glasgow Geek Dinner (5th March)
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Re: Expensive Update

Pete O'Hanlon12-Feb-07 22:43

Pete O'Hanlon

12-Feb-07 22:43

Expensive queries tend to be the result of not using key fields in the filter criteria. Have you viewed your execution plan to see what it is doing? If you are doing a sequential scan, this will be the cause of your problem.

I really don't see the problem being with the newid() function.

Re: Expensive Update

Brady Kelly12-Feb-07 22:49

Brady Kelly

12-Feb-07 22:49

It was a table scan, but redoing the column addition and running the update again gave a time of 2s. The first one is probably due to some housekeeping work my very underused server was doing. The query is:

alter table myDoc<br />
add<br />
	GlobalDocID uniqueidentifier default newid()<br />
go<br />
update myDoc<br />
set GlobalDocID = newid() where GlobalDocID is null

creating view for Month filter facility

rrrriiizz12-Feb-07 21:43

rrrriiizz

12-Feb-07 21:43

hello all,

am working with sqlserver 2000 am using view option for retrieve values in a table.am using vb.net(Vs 2005) as my Front End. my problem is whenever i retrieve values from the database it simply retrieves the first row of the database. but in my case i want to retrieve data's which wasmodified by Last month. am declring one field as month. in that am getting value like jan 07, feb 07, etc....

can i able to filter last month modified data's and then move it to a view and then retriving data's from that view, is it possible please give me suggestions as well as solutions......

Thanks

Re: creating view for Month filter facility [modified]

Krish - KP13-Feb-07 2:59

Krish - KP

13-Feb-07 2:59

while fetching from SQL Server it is possible, to filter for a specific month & year.

for example
if the modify_dt is the column with datetime datatype
where clause for fetching this can be
1. WHERE modify_dt BETWEEN <frmdt> AND <enddt>
2. WHERE month(modify_dt) = <mth> AND year(modify_dt) = <yr>
if is for previous month (current month is Feb then prev. is Jan)
WHERE month(modify_dt) = month(getdate) - 1
AND year(modify_dt) = year(getdate())

-- modified at 22:45 Tuesday 13th February, 2007

Re: creating view for Month filter facility

rrrriiizz13-Feb-07 19:36

rrrriiizz

13-Feb-07 19:36

Thanks For U r Reply. but i am declaring that month fields as nvarchar, am also uploaod all the databases from excel to Sql.
Now is it Possible to get Last month Details?

That table contain 42 fields and also No of records in it is nearly 850. Please help me to avoid olderdata's getting repeated. only i want last month Modified Records. Please tell me any other way to solve this Issue. Sniff | :^)

..

Thank You

Re: creating view for Month filter facility

Krish - KP15-Feb-07 3:14

Krish - KP

15-Feb-07 3:14

does the data belongs to same year ? if so ....
In SQL datepart(m, getdate()) returns current month number
where clause can be used
MonthField = datepart(m, getdate()) - 1

explicit way of triming string parameter

firestoper12-Feb-07 20:16

firestoper

12-Feb-07 20:16

Hi Guru's

I know how to trim varchar parameters ltrim(rtrim(@parameter)) but is there more explicit way of doing this? e.g. vb.net string.trim()

Thanks
Dom Wink | ;)

Re: explicit way of triming string parameter

Sylvester george13-Feb-07 0:36

Sylvester george

13-Feb-07 0:36

Good. Thinking beyond the SQL Server 2000 Professionals

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com

Re: explicit way of triming string parameter

firestoper13-Feb-07 14:40

firestoper

13-Feb-07 14:40

Thanks, I guess its how this thing works.

Wink | ;)

SQL and Images

Illegal Operation12-Feb-07 19:11

Illegal Operation

12-Feb-07 19:11

I would like to find out how to insert images into a SQL table? I have an ID (int), Description(text) and Image(image).

Please help, I want to use a stored procedure to insert the data.

Thanks!

Illegal Operation

Making Computer Software Talk

Re: SQL and Images

Pete O'Hanlon12-Feb-07 22:42

Pete O'Hanlon

12-Feb-07 22:42

You just use the Image type as a parameter in your stored procedure. Then you can insert the information to your hearts content.

Trouble using DTSWizard.exe to Import data into SQL Database

9llla12-Feb-07 13:47

9llla

12-Feb-07 13:47

Hello,

I am using the DTSWizard.exe to import data into a SQL Server database from text files.

I have performed this operation several times before without incident. The database is running and by all accounts seems to be functioning fine. However, for some unknown reason, now when I try to select the database as the destination for my imported data, I get the error message pasted at the bottom of the page. Does anyone have advice for how I should proceed?

Your help would be greatly appreciated.

ERROR MESSAGE:

TITLE: SQL Server Import and Export Wizard
------------------------------

Could not retrieve table list.

------------------------------
ADDITIONAL INFORMATION:

One or more errors occurred during processing of command.
File activation failure. The physical file name "C:\PotentialBenchmarks\Benchmark Builder\benchmarkBuilder_v1d\benchmarkBuilder_v1d\RPU_log.ldf" may be incorrect.
Unable to open the physical file "C:\PotentialBenchmarks\Benchmark Builder\benchmarkBuilder_v1d\benchmarkBuilder_v1d\RPU_log.ldf". Operating system error 5: "5(Access is denied.)".
Unable to open the physical file "C:\PotentialBenchmarks\Benchmark Builder\benchmarkBuilder_v1d\benchmarkBuilder_v1d\RPU.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------

Re: Trouble using DTSWizard.exe to Import data into SQL Database

andyharman12-Feb-07 23:03

andyharman

12-Feb-07 23:03

I've not seen that before, but it sounds like permissions on the physical log and data files (or the folder they live in).
Check that the files can be accessed by the account that is used by SQL Server service to log on (see appropriate SQL Server service properties in "SQL Server Configuration Manager -> SQL Server Services".
Hope that helps.
Andy

rename the existed excel worksheet name

JaneQuestion12-Feb-07 13:33

JaneQuestion

12-Feb-07 13:33

Hello everyone,

In our project, we created a SSIS package to load data from Excel into SQL2005 database.

Right now, I want to write code (whatever is vb.net, Vbscrip, or storedprocedure code) to change the existed excel worksheet name. If anyone can give me any suggestion with it, I will really appreciate it.

Jane

Re: rename the existed excel worksheet name

Krish - KP12-Feb-07 16:55

Krish - KP

12-Feb-07 16:55

hi,

if is just for renaming the sheet name below code will work
objWorkSheet.Name = "mysheet1"
which i've tried in VB6 & VBA.

KP

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.