Export Database Structure (No Data)

Is there a transact SQL statement that can be used to only export the database structure? I'm looking to schedule a nightly job to export the structure.
DTS has a task that will do just that.
Cheers,
Greg Jackson
PDX, Oregon
|||And you can create and schedule a job that uses dtsrun.exe to run the =
DTS package whenever you want.
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:uVBZPOjUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> DTS has a task that will do just that.
>=20
>=20
> Cheers,
>=20
> Greg Jackson
> PDX, Oregon
>=20
>

Export Database Structure (No Data)

Is there a transact SQL statement that can be used to only export the databa
se structure? I'm looking to schedule a nightly job to export the structure
.DTS has a task that will do just that.
Cheers,
Greg Jackson
PDX, Oregon|||And you can create and schedule a job that uses dtsrun.exe to run the =
DTS package whenever you want.
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:uVBZPOjUEHA.3540@.TK2MSFTNGP11.phx.gbl...
> DTS has a task that will do just that.
>=20
>=20
> Cheers,
>=20
> Greg Jackson
> PDX, Oregon
>=20
>

Export data from SELECT statement

Hello,

I have a SELECT statement which returns a result, depending on the selection the users make (application written in Visual Basic 2005). I want to export the result of the SELECT statement to a Excel file (.csv, or .txt). How should I proceed?
Are you using SSIS? Can't you just write out the data from the VB application?

Export data as bulk insert statement

Hi all,
Is there a way to export data from a particular table as a bulk insert
statements? I can export to all sort of database or even Excel spreadsheet
but seems like there is no option to export it as SQL Insert statements.
Anyone has any idea to achieve this?
Victor Hadianto
Check this: http://www.codeproject.com/dotnet/ScriptDatabase.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Victor Hadianto" <synop@.nospam.nospam> wrote in message
news:D4DA5860-6345-45CE-839B-59507B8AC7FB@.microsoft.com...
> Hi all,
> Is there a way to export data from a particular table as a bulk insert
> statements? I can export to all sort of database or even Excel spreadsheet
> but seems like there is no option to export it as SQL Insert statements.
> Anyone has any idea to achieve this?
> --
> Victor Hadianto
>
|||This proc will script data as INSERT statements:
http://vyaskn.tripod.com/code.htm#inserts
A BULK INSERT statement just loads data from an external delimited file.
It's easy to create such a file from Query Analyzer for example.
David Portas
SQL Server MVP
|||Hi Victor,
I think the articles MVP Dejan Sarka and MVP David Portas provided was very
helpful, I just want to post a quick note to see if you would like
additional assistance or information regarding this particular issue. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Export data as bulk insert statement

Hi all,
Is there a way to export data from a particular table as a bulk insert
statements? I can export to all sort of database or even Excel spreadsheet
but seems like there is no option to export it as SQL Insert statements.
Anyone has any idea to achieve this?
Victor HadiantoCheck this: http://www.codeproject.com/dotnet/ScriptDatabase.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Victor Hadianto" <synop@.nospam.nospam> wrote in message
news:D4DA5860-6345-45CE-839B-59507B8AC7FB@.microsoft.com...
> Hi all,
> Is there a way to export data from a particular table as a bulk insert
> statements? I can export to all sort of database or even Excel spreadsheet
> but seems like there is no option to export it as SQL Insert statements.
> Anyone has any idea to achieve this?
> --
> Victor Hadianto
>|||This proc will script data as INSERT statements:
http://vyaskn.tripod.com/code.htm#inserts
A BULK INSERT statement just loads data from an external delimited file.
It's easy to create such a file from Query Analyzer for example.
David Portas
SQL Server MVP
--|||Hi Victor,
I think the articles MVP Dejan Sarka and MVP David Portas provided was very
helpful, I just want to post a quick note to see if you would like
additional assistance or information regarding this particular issue. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Export data as bulk insert statement

Hi all,
Is there a way to export data from a particular table as a bulk insert
statements? I can export to all sort of database or even Excel spreadsheet
but seems like there is no option to export it as SQL Insert statements.
Anyone has any idea to achieve this?
--
Victor HadiantoCheck this: http://www.codeproject.com/dotnet/ScriptDatabase.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Victor Hadianto" <synop@.nospam.nospam> wrote in message
news:D4DA5860-6345-45CE-839B-59507B8AC7FB@.microsoft.com...
> Hi all,
> Is there a way to export data from a particular table as a bulk insert
> statements? I can export to all sort of database or even Excel spreadsheet
> but seems like there is no option to export it as SQL Insert statements.
> Anyone has any idea to achieve this?
> --
> Victor Hadianto
>|||This proc will script data as INSERT statements:
http://vyaskn.tripod.com/code.htm#inserts
A BULK INSERT statement just loads data from an external delimited file.
It's easy to create such a file from Query Analyzer for example.
--
David Portas
SQL Server MVP
--|||Hi Victor,
I think the articles MVP Dejan Sarka and MVP David Portas provided was very
helpful, I just want to post a quick note to see if you would like
additional assistance or information regarding this particular issue. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Export a table data in INSERT statement

Can I export a table data into a script file contains INSERT statements in
EM ?
Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>

Export a table data in INSERT statement

Can I export a table data into a script file contains INSERT statements in
EM ?Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>sql

Export a table data in INSERT statement

Can I export a table data into a script file contains INSERT statements in
EM ?Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>

Export a table data in a script file

How can I export a table data as a script file as INSERT statement from
Enterprise Manager or Query Analyser ?Try this
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eQbNxGs2FHA.3092@.TK2MSFTNGP10.phx.gbl...
> How can I export a table data as a script file as INSERT statement from
> Enterprise Manager or Query Analyser ?
>
>

explanation of update statement

--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 int, c2 int)
insert into t1(c1) values(1)
insert into t1(c1) values(2)
insert into t1(c1) values(3)
insert into t1(c1) values(4)
go
declare @.a int
set @.a = 1
update t1 set @.a = c2 = 2*@.a
go
select * from t1
-- end script
--begin output
c1 c2
-- --
1 2
2 4
3 8
4 16
--end output
The above script works very well, I was not expecting it to work, specially
the statement
update t1 set @.a = c2 = 2*@.a
I thought we always need a cursor for row by row processing, but this update
statement seems to be looping through the rows just like a cusrsor.
I just wanted to know if this is recomended way of avoiding a cursor or if
there are any known pitfalls to this approach.
Thanks in advance
--
Vikram Vamshi
Database Engineer
Eclipsys CorporationAnother way and probably safer (won't break with a service pack for
example)
update t1 set c2 = power(2,c1)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Vikram Vamshi wrote:
> --begin script
> if exists (select * from information_schema.tables where table_name = 't1'
)
> drop table t1
> go
> create table t1(c1 int, c2 int)
> insert into t1(c1) values(1)
> insert into t1(c1) values(2)
> insert into t1(c1) values(3)
> insert into t1(c1) values(4)
> go
> declare @.a int
> set @.a = 1
> update t1 set @.a = c2 = 2*@.a
> go
> select * from t1
> -- end script
> --begin output
> c1 c2
> -- --
> 1 2
> 2 4
> 3 8
> 4 16
> --end output
> The above script works very well, I was not expecting it to work, speciall
y
> the statement
> update t1 set @.a = c2 = 2*@.a
> I thought we always need a cursor for row by row processing, but this upda
te
> statement seems to be looping through the rows just like a cusrsor.
> I just wanted to know if this is recomended way of avoiding a cursor or if
> there are any known pitfalls to this approach.
> Thanks in advance
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation|||Be sure to follow SQL Menace's point about removing the @.a reference.

>I thought we always need a cursor for row by row processing, but this updat
e
>statement seems to be looping through the rows just like a cusrsor.
What you are seeing is SET processing. SET based processing is
exactly what SQL is all about. The entire set of rows that meet the
WHERE clause criteria - which in this case is all rows, since there is
no WHERE clause - is updated.

>I just wanted to know if this is recomended way of avoiding a cursor
Yes, SET based processing is the preferred alternative to cursors.

>there are any known pitfalls to this approach.
Make sure you write your WHERE clause correctly! If you forget it, or
get it wrong, an UPDATE against the wrong set of rows can ruin your
whole day.
Roy Harvey
Beacon Falls, CT
On Tue, 27 Jun 2006 12:35:47 -0400, "Vikram Vamshi"
<vikram.vamshi@.online.eclipsys.com> wrote:

>--begin script
>if exists (select * from information_schema.tables where table_name = 't1')
> drop table t1
>go
>create table t1(c1 int, c2 int)
>insert into t1(c1) values(1)
>insert into t1(c1) values(2)
>insert into t1(c1) values(3)
>insert into t1(c1) values(4)
>go
>declare @.a int
>set @.a = 1
>update t1 set @.a = c2 = 2*@.a
>go
>select * from t1
>-- end script
>--begin output
>c1 c2
>-- --
>1 2
>2 4
>3 8
>4 16
>--end output
>The above script works very well, I was not expecting it to work, specially
>the statement
>update t1 set @.a = c2 = 2*@.a
>I thought we always need a cursor for row by row processing, but this updat
e
>statement seems to be looping through the rows just like a cusrsor.
>I just wanted to know if this is recomended way of avoiding a cursor or if
>there are any known pitfalls to this approach.
>Thanks in advance|||I think my last example left some ambiguity...
--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 char, c2 int)
insert into t1(c1) values('a')
insert into t1(c1) values('x')
insert into t1(c1) values('n')
insert into t1(c1) values('e')
go
declare @.a int
set @.a = 1
update t1 set @.a = c2 = 2*@.a
go
select * from t1
-- end script
There is no relation between c1 and c2 ( i modified the sample to reflect
that )
The update statement seems to be using the previous value it has inserted
for c2 to generate the next value.
I am still not sure if it will work in all the cases, but in all my tests it
has worked without any issues.
Is it always gauranteed to work this way? Or is it safer to use a cursor for
this?
Thanks for taking your time to respond
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:gsp2a2h7mbadq7c8eo260j5d359t9pj29a@.
4ax.com...
> Be sure to follow SQL Menace's point about removing the @.a reference.
>
> What you are seeing is SET processing. SET based processing is
> exactly what SQL is all about. The entire set of rows that meet the
> WHERE clause criteria - which in this case is all rows, since there is
> no WHERE clause - is updated.
>
> Yes, SET based processing is the preferred alternative to cursors.
>
> Make sure you write your WHERE clause correctly! If you forget it, or
> get it wrong, an UPDATE against the wrong set of rows can ruin your
> whole day.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 27 Jun 2006 12:35:47 -0400, "Vikram Vamshi"
> <vikram.vamshi@.online.eclipsys.com> wrote:
>|||What you're getting is the following:
@.a = 1
SET @.a = c2 = 2*1 -- so @.a becomes 2
SET @.a = c2 = 2*2 -- so @.a becomes 4
SET @.a = c2 = 2*4 -- so @.a becomes 8
etc.
It is documented in BOL:
"SET @.variable = column = expression sets the variable to the same value as
the column. This differs from SET @.variable = column, column = expression,
which sets the variable to the pre-update value of the column."
So one would assume it is safe for use.
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:%23EMjlChmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I think my last example left some ambiguity...
> --begin script
> if exists (select * from information_schema.tables where table_name =
> 't1')
> drop table t1
> go
> create table t1(c1 char, c2 int)
> insert into t1(c1) values('a')
> insert into t1(c1) values('x')
> insert into t1(c1) values('n')
> insert into t1(c1) values('e')
> go
> declare @.a int
> set @.a = 1
> update t1 set @.a = c2 = 2*@.a
> go
> select * from t1
> -- end script
> There is no relation between c1 and c2 ( i modified the sample to reflect
> that )
> The update statement seems to be using the previous value it has inserted
> for c2 to generate the next value.
> I am still not sure if it will work in all the cases, but in all my tests
> it has worked without any issues.
> Is it always gauranteed to work this way? Or is it safer to use a cursor
> for this?
> Thanks for taking your time to respond
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:gsp2a2h7mbadq7c8eo260j5d359t9pj29a@.
4ax.com...
>|||Thanks Mike!!! Not sure how I missed it in the books online :)
What about the implicit looping, is it reliable?
For now we will go ahead and use it, If we run into any issues, I will post
them here ...
Thanks again
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Mike C#" <xyz@.xyz.com> wrote in message
news:eyGE7zhmGHA.3352@.TK2MSFTNGP02.phx.gbl...
> What you're getting is the following:
> @.a = 1
> SET @.a = c2 = 2*1 -- so @.a becomes 2
> SET @.a = c2 = 2*2 -- so @.a becomes 4
> SET @.a = c2 = 2*4 -- so @.a becomes 8
> etc.
> It is documented in BOL:
> "SET @.variable = column = expression sets the variable to the same value
> as the column. This differs from SET @.variable = column, column =
> expression, which sets the variable to the pre-update value of the
> column."
> So one would assume it is safe for use.
> "Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
> news:%23EMjlChmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||This might cause you potential problems with some future release, SP, or
even just changes in your data. It is true that you can do
@.variable = column = expression, and that updates the variable to the new
value in the column. But Vikram is updating multiple rows, and the
optimizer is allowed to update these rows in any order. If you are
depending on the first row getting 2, the second row getting 4, etc, it
won't work if the rows are updated in some other order.
As BOL also says, "Variable names can be used in UPDATE statements to show
the old and new values affected. This should only be used when the UPDATE
statement affects a single record; if the UPDATE statement affects multiple
records, the variables only contain the values for one of the updated rows."
Tom
"Mike C#" <xyz@.xyz.com> wrote in message
news:eyGE7zhmGHA.3352@.TK2MSFTNGP02.phx.gbl...
> What you're getting is the following:
> @.a = 1
> SET @.a = c2 = 2*1 -- so @.a becomes 2
> SET @.a = c2 = 2*2 -- so @.a becomes 4
> SET @.a = c2 = 2*4 -- so @.a becomes 8
> etc.
> It is documented in BOL:
> "SET @.variable = column = expression sets the variable to the same value
> as the column. This differs from SET @.variable = column, column =
> expression, which sets the variable to the pre-update value of the
> column."
> So one would assume it is safe for use.
> "Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
> news:%23EMjlChmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||Don't worry, it was pretty well hidden. Not an "undocumented" feature, but
a pretty well "barely documented" one. They specifically state in BOL that
this is how it's supposed to perform, and that's as reliable as it's gonna
get :) I would advise one thing, however: change the order of the INSERT
statements in your table, and the order of the update - and hence the values
assigned to @.a could change. I don't believe the order of rows is
guaranteed in an UPDATE statement, even if you were to put a primary key and
clustered index on it, so beware...
--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 char, c2 int)
insert into t1(c1) values('n') -- No longer in alphabetical order
insert into t1(c1) values('x')
insert into t1(c1) values('a') -- Also out of order
insert into t1(c1) values('e')
go
declare @.a int
set @.a = 1
update t1 set @.a = c2 = 2*@.a
go
select * from t1
-- end script
"Vikram Vamshi" <vikram.vamshi@.online.eclipsys.com> wrote in message
news:uttRxnimGHA.3732@.TK2MSFTNGP05.phx.gbl...
> Thanks Mike!!! Not sure how I missed it in the books online :)
> What about the implicit looping, is it reliable?
> For now we will go ahead and use it, If we run into any issues, I will
> post them here ...
>|||That's a little misleading. The variable will contain the value for one of
the updated rows after the UPDATE finishes, but as demonstrated it contains
the value for each row during execution. I also agree that the order of
updates is not guaranteed, so if that's important another method of updating
is required. Maybe something more like this:
--begin script
if exists (select * from information_schema.tables where table_name = 't1')
drop table t1
go
create table t1(c1 char, c2 int)
insert into t1(c1) values('a')
insert into t1(c1) values('x')
insert into t1(c1) values('n')
insert into t1(c1) values('e')
go
update t1 set c2 = (
select power(2, count(*))
from t1 a, t1 b
where a.c1 >= b.c1
and t1.c1 = a.c1
group by a.c1, a.c2
)
go
select * from t1
-- end script
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:486dnbD6FsVvBzzZnZ2dnUVZ_oudnZ2d@.co
mcast.com...
> This might cause you potential problems with some future release, SP, or
> even just changes in your data. It is true that you can do
> @.variable = column = expression, and that updates the variable to the new
> value in the column. But Vikram is updating multiple rows, and the
> optimizer is allowed to update these rows in any order. If you are
> depending on the first row getting 2, the second row getting 4, etc, it
> won't work if the rows are updated in some other order.
> As BOL also says, "Variable names can be used in UPDATE statements to show
> the old and new values affected. This should only be used when the UPDATE
> statement affects a single record; if the UPDATE statement affects
> multiple records, the variables only contain the values for one of the
> updated rows."
> Tom
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eyGE7zhmGHA.3352@.TK2MSFTNGP02.phx.gbl...
>

EXPLAIN Verb

I have a DB2 SQL background.
Is there something similar to the "EXPLAIN" verb in Microsoft SQL Server
that will allow me to analyze a SQL statement and determine its access path,
etc., and determine how efficient the SQL is?
Let me know.
Thanks in advance.Run the query in Query Analyzer and turn on options like Show Execution
Plan, Show Server Trace, Show Client Statistics. You can also save the
query as a .sql file and give it to the database engine tuning advisor.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:BD207A57-4F6D-4543-9691-32466B165BA1@.microsoft.com...
>I have a DB2 SQL background.
> Is there something similar to the "EXPLAIN" verb in Microsoft SQL Server
> that will allow me to analyze a SQL statement and determine its access
> path,
> etc., and determine how efficient the SQL is?
> Let me know.
> Thanks in advance.
>|||In addition to what Aaron suggests, look up SET SHOWPLAN_ALL in Books Online
.
ML
http://milambda.blogspot.com/|||Just like on DB2 too, there are sql tuning tools like SQL Optimizer for
Visual Studio. You can check it out at
http://www.extensibles.com/modules...=Products&op=NS
examnotes <wnfisba@.discussions.microsoft.com> wrote in
news:BD207A57-4F6D-4543-9691-32466B165BA1@.microsoft.com:

> I have a DB2 SQL background.
> Is there something similar to the "EXPLAIN" verb in Microsoft SQL
> Server that will allow me to analyze a SQL statement and determine its
> access path, etc., and determine how efficient the SQL is?
> Let me know.
> Thanks in advance.
>
The Relentless One
Debugging is a state of mind
http://www.extensibles.com/|||Jeez. quit spamming the boards...
Stu|||Yep, he's relentless.
Nomen est omen.
ML
http://milambda.blogspot.com/

Experiencing substantial delay in reflecting updates to a table

I am experiencing an unusual problem in which a stored procedure that updates a table with but a single update statement executes in but 13ms per SQL Profiler but a following stored procedure doesn't display this change for upwards of five seconds.

The first stored procedure executes normally without error. Again, per the profiler it completes in 13ms.

A series of other stored procedures execute followed by a final SP which displays data that should reflect the change from the first SP executed at the beginning of the process. However, I find the value is not updated immediately but rather takes a few seconds to be reflected. The value changed is not a column on which a clustered index is based.

Some of the stored procedures that execute between these two SPs of interest do involved INSERT INTO statements. But, in all cases they are inserting into a table variable. I had read somewhere the INSERT INTO could introduce some locking but wasn't sure if it applied in my case.

I could really use some fresh ideas on what to look at - such as locking performance monitors or the like. Any ideas/suggestions?

Thanks!

What isolation level are you using?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Neither stored procedure explicitly declares an isolation level. So, I'm assuming it is using the default isolation level. Is it possible to change the default isolation level for a database? If so - how? If it isn't possible to change the default isolation level then the stored procedures should be using Read Committed.

Is it possible for SQL Profiler to display the isolation level used on a stored procedure?

Thanks!

|||

Hi,

You cannot set a default isolation level for a database (only read committed or read committed snapshot can be changed).

The default is read committed for SQL Server. If you are accessing your database through COM+ however the default is SERIALIZABLE.

I see now reason however why your updates would have a delay. Can you give some more background about the application?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The application is a ASP.Net v1.1 application. I've searched for usage of a transaction initiated from the .Net code and have not found that transactions are not used in this situation.

Again, just to clarify the delay - the stored procedure executing the INSERT completes in approximately 200ms (it calls a scalar UDF which I've not researched yet). However, sometimes, querying for that changed data via a stored procedure executed later in the process does not reflect those changes but rather the original data. If I query multiple times it eventually shows up.

I'm at a loss on this one - any ideas?

|||

Very odd, I can not think of a good reason why there would be a delay.

The microsecond that your transaction gets committed it should be reflected in your select queries.

When you talk about your 'select' statement are you executing them from Query Analyzer or are you using the ASP.NET page?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Expected end of statement

i got this kind of error type:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/wanhe/services/update_succeed.asp, line 80, column 129
sqlStrDB = "update applicant set Name='"&username&"', Gender= '"&gender&"', Age='"&age&"',Marriage='"&maritalstatus&"',HighEdu='"&hEducation&"',EngScore='"&engScore&"',ToeflScore='"&toeflScore&"',IeltsScore='"&ieltsScore&"',FysmScore='"&fysmScore&"',NcetScore='"&ncetScore&"',Tel='"&coTel&"',Mobile='"&coMobile&"',Email='"&coEmail&"',AppSchool='"&appSchool&"',AppMajor='"&appMajor&"',Comment='"&coComment&"',companyNameCN='"&coNameCN&"',companyNameEN='"&coNameEN&"',CoAddress='"&coAddress&"',CoPostal='"&coPostal&"' where ID = "&userID&""

this is my coding:
<%
dim cnStrDB
dim rcSetDB
dim sqlStrDB

'create connection and recordset objects
Set cnStrDB = Server.CreateObject("ADODB.Connection")
Set rcSetDB = Server.CreateObject("ADODB.Recordset")

' connection string
dim pathDB
dim providerDB

' change this path to your database path
pathDB = "C:\Inetpub\wwwroot\wanhe\services\oApplication.mdb "
providerDB = "Microsoft.Jet.OLEDB.4.0"

' defining database connection (connectionstring.asp)
cnStrDB.ConnectionString = pathDB
cnStrDB.Provider = providerDB
cnStrDB.Mode = 3

cnStrDB.open
dim username
Dim coNameCN
Dim coNameEN
Dim coAddress
Dim coPostal
dim gender
dim age
dim maritalstatus
dim hEducation
dim engScore
dim toeflScore
dim ieltsScore
dim fysmScore
dim ncetScore
dim appSchool
dim appMajor
Dim coTel
Dim userID
Dim coMobile
dim coEmail
dim coComment

username = request.form("userName")
coNameCN = request.form("txtCoNameCN")
coNameEN= request.form("txtCoNameEN")
coAddress= request.form("txtCoAddress")
coPostal= request.form("txtPostal")
coTel= request.form("Tel")
coMobile= request.form("Mobile")
coEmail= request.form("Email")
coComment= request.form("Comment")
gender= request.form("Gender")
age= request.form("Age")
maritalstatus= request.form("Age")
hEducation= request.form("highEdu")
'response.write hEducation
'response.end
engScore= request.form("EngScore")
toeflScore= request.form("ToeflScore")
ieltsScore= request.form("IeltsScore")
fysmScore= request.form("FysmScore")
ncetScore= request.form("NcetScore")
appSchool= request.form("AppSchool")
appMajor= request.form("AppMajor")

userID=Request.cookies("ID")

sqlStrDB = "update applicant set Name= '"&username&"', Gender= '"&gender&"', Age='"&age&"',Marriage='"&maritalstatus&"', HighEdu='"&hEducation&"', EngScore='"&engScore&"',ToeflScore='"&toeflScore&"',IeltsScore='"&ieltsScore&"',FysmScore='"&fysmScore&"',NcetScore='"&ncetScore&"',Tel='"&coTel&"',Mobile='"&coMobile&"',Email='"&coEmail&"',AppSchool='"&appSchool&"',AppMajor='"&appMajor&"',Comment='"&coComment&"',companyNameCN='"&coNameCN&"',companyNameEN='"&coNameEN&"',CoAddress='"&coAddress&"',CoPostal='"&coPostal&"' where ID = "&userID&""

rcSetDB.ActiveConnection = cnStrDB
rcSetDB.cursorType = 3 'for add record into database
rcSetDB.cursorLocation = 3
rcSetDB.open(sqlStrDB)

%>

i know it's somehting wrong with
HighEdu='"&hEducation&"
but i cant find where is wrong

anyone can helps me??
thanks a lot
:()Maybe that "" at the end should have been ";"?

You should really use prepared statements with bind variables. Not only do they improve performance and remove a security loophole, they also make it easier to write your SQL:

sqlStrDB = "update applicant set Name=?, Gender=?, Age=?,Marriage=?, HighEdu=?, ... , CoPostal=? where ID = ?;"|||same error message|||your first line:
sqlStrDB = "Your statement" _ (add the "_" at the end)

your second line:
& "Your statement" (add "&" at the first)

try to manipulate it if error happens again. I think it should work because you have to separate your string to another lines by these characters.

regards.

chris|||the problem is the variable name
i change to another name can liao

by the way thanks for ur help

Expanding sp_executesql Parameters?

Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark HolahanI think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||Mike,
Plain Jane:
SELECT OrderNo, PropStreet1
FROM tbl_Orders
WHERE CONTAINS(PropStreet1, ' "0-14115 Ironwood Dr" ')
I see that I left out the word "WHERE" below; this was an oversight.
Thanks.
"Michael C#" <xyz@.yomomma.com> wrote in message news:uOIjvKpHFHA.3484@.TK2MSF
TNGP12.phx.gbl...
I think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||I'm having a little trouble installing Full-Text Search on my beat-up little
lap-top over here. So with the caveat that I haven't actually tested it, y
ou might try the following:
DECLARE @.Params NVARCHAR(50)
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
-- changed the parameter in your query from @.PropStreet1 to @.AddressStreet1
and got rid of the + sign
SET @.sSQL = N'SELECT OrderNo, PropStreet1 FROM tbl_Orders WHERE CONTAINS(Pro
pStreet1, '' " @.AddressStreet1 " '')'
-- changed @.AddressStreet to @.AddressStreet1
SET @.Params = N' @.AddressStreet1 VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I'll see if I can clean this little box up enough to get Full-Text Search in
stalled (it really needs to be re-formatted... ah well...)
Thx
Mike C.
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:uruEiRpHF
HA.720@.TK2MSFTNGP10.phx.gbl...
Mike,
Plain Jane:
SELECT OrderNo, PropStreet1
FROM tbl_Orders
WHERE CONTAINS(PropStreet1, ' "0-14115 Ironwood Dr" ')
I see that I left out the word "WHERE" below; this was an oversight.
Thanks.
"Michael C#" <xyz@.yomomma.com> wrote in message news:uOIjvKpHFHA.3484@.TK2MSF
TNGP12.phx.gbl...
I think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||Mike,
Thanks for your time. I'm going to post the question a little differently.
Mark
"Michael C#" <xyz@.yomomma.com> wrote in message news:ufFBc8pHFHA.4004@.TK2MSF
TNGP10.phx.gbl...
I'm having a little trouble installing Full-Text Search on my beat-up little
lap-top over here. So with the caveat that I haven't actually tested it, y
ou might try the following:
DECLARE @.Params NVARCHAR(50)
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
-- changed the parameter in your query from @.PropStreet1 to @.AddressStreet1
and got rid of the + sign
SET @.sSQL = N'SELECT OrderNo, PropStreet1 FROM tbl_Orders WHERE CONTAINS(Pro
pStreet1, '' " @.AddressStreet1 " '')'
-- changed @.AddressStreet to @.AddressStreet1
SET @.Params = N' @.AddressStreet1 VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I'll see if I can clean this little box up enough to get Full-Text Search in
stalled (it really needs to be re-formatted... ah well...)
Thx
Mike C.
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:uruEiRpHF
HA.720@.TK2MSFTNGP10.phx.gbl...
Mike,
Plain Jane:
SELECT OrderNo, PropStreet1
FROM tbl_Orders
WHERE CONTAINS(PropStreet1, ' "0-14115 Ironwood Dr" ')
I see that I left out the word "WHERE" below; this was an oversight.
Thanks.
"Michael C#" <xyz@.yomomma.com> wrote in message news:uOIjvKpHFHA.3484@.TK2MSF
TNGP12.phx.gbl...
I think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||No prob. But if you don't mind my asking, what happened when you tried the
change? Did it generate an error or what? To answer your other question, I
don't think you can view the SQL with your parameter replaced by your value
, because SQL Server doesn't do a simple string-type replacement when you in
voke sp_executesql. You can add a PRINT @.sSQL right behind the SET @.sSQL st
atement, but it won't have your replacement value in it.
Thx,
Mike C.
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:%234xWobq
HFHA.2936@.TK2MSFTNGP15.phx.gbl...
Mike,
Thanks for your time. I'm going to post the question a little differently.
Mark
"Michael C#" <xyz@.yomomma.com> wrote in message news:ufFBc8pHFHA.4004@.TK2MSF
TNGP10.phx.gbl...
I'm having a little trouble installing Full-Text Search on my beat-up little
lap-top over here. So with the caveat that I haven't actually tested it, y
ou might try the following:
DECLARE @.Params NVARCHAR(50)
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
-- changed the parameter in your query from @.PropStreet1 to @.AddressStreet1
and got rid of the + sign
SET @.sSQL = N'SELECT OrderNo, PropStreet1 FROM tbl_Orders WHERE CONTAINS(Pro
pStreet1, '' " @.AddressStreet1 " '')'
-- changed @.AddressStreet to @.AddressStreet1
SET @.Params = N' @.AddressStreet1 VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I'll see if I can clean this little box up enough to get Full-Text Search in
stalled (it really needs to be re-formatted... ah well...)
Thx
Mike C.

EXISTS with EXEC

This works:
IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @.qci_pk)
But since I may need to build the Sql statement I tried something like this,
which did NOT work,
IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk = @.qci_pk'))
Is there a way to go around this?
Evan>> Is there a way to go around this?
The EXISTS clause in SQL can have only a SELECT statement. Can you elaborate
on what your requirements are? Why do you want to do something like this?
Alternatively you can use the entire IF clause in your EXEC like:
EXEC ( 'IF NOT EXISTS ( SELECT ... ) ... ELSE .. ' )
Anith|||Evan Camilleri (e70mt@.yahoo.co.uk.nospam) writes:
> This works:
> IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @.qci_pk)
> But since I may need to build the Sql statement I tried something like
> this, which did NOT work,
> IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk =
> @.qci_pk'))
> Is there a way to go around this?
One way is to use sp_executesql:
SELECT @.sql = 'SELECT @.x = CASE WHEN EXISTS (SELECT ...) THEN 1 ELSE 0 END'
EXEC sp_executesql @.sql, N'@.x bit OUTPUT', @.exists OUTPUT
IF @.exists = 0
..
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Exists T-SQL

I would like to change the t-sql statement listed below to execute quicker.
Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY.
If a Pum value of 806478 is found within the last 60 minutes. The output
COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 = 0.
Please help me complete this task.
Thank You,
DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
SET @.COUNT_CALL_MOVEMENTS_REC_0 =
(Select count(Pum)
from Call_Movements
where DATEDIFF(mi, Started_Time, GETDATE()) <=60
AND left(cast(Pum as varchar(20)),6) = ('806478'))Try,
DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
declare @.d datetime
set @.d = convert(varchar(16), getdate(), 126) + ':00'
SET @.COUNT_CALL_MOVEMENTS_REC_0 =
case when exists (
Select
*
from
dbo.Call_Movements
where
(Started_Time between dateadd(minutes, -60, @.d) and @.d)
AND cast(Pum as varchar(20)) like '806478%'
) 1 then 0 end
go
AMB
"Joe K." wrote:

> I would like to change the t-sql statement listed below to execute quicker
.
> Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY.
> If a Pum value of 806478 is found within the last 60 minutes. The output
> COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 = 0.
> Please help me complete this task.
> Thank You,
>
> DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
> SET @.COUNT_CALL_MOVEMENTS_REC_0 =
> (Select count(Pum)
> from Call_Movements
> where DATEDIFF(mi, Started_Time, GETDATE()) <=60
> AND left(cast(Pum as varchar(20)),6) = ('806478'))

Existing Statement with Calculated Members

Hi,

I have a Calculated Measure that makes use of the Existing statement to establish the members of a particular attribute hierarchy of my "Geography" dimension that are in context at any particular time. These are then summed over to produce a value to be used in the rest of the calculation.

This has been working fine until I came to create a calculated member (e.g. a group of members from a different attribute hierarchy) on the Geography dimension, at which point the Existing statement appears to not be able to correctly pick out the correct context. I realised I will need to work around this by using filter as an alternative but I have been thinking about this for hours and I continue to make no progress,

For what its worth I have already read Mosha's post on Multiselects but it didn't really help me a great deal...

Does anyone have any ideas?

regards

Colin

Could you please attach a concrete example (relevant calculation and MDX query)?

Thank you

Exist Return Values

Hi ,
I like to use a variable to store the return values (True / False) of the
exists statement. How can I do that ? I unable to do that from my query show
below
declare @.bln
set @.bnl = Select Distinct Cust_Id,Cust_Name From Temp_Customer
Where Not Exists
(Select Cust_Id,Cust_Name From MyDb.dbo.Customer
Where MyDb.dbo.Customer .Cust_Id = Temp_Customer.Cust_Id)
Please Help ..
Travis Tan
On Sun, 24 Jul 2005 22:00:01 -0700, Travis wrote:

>Hi ,
> I like to use a variable to store the return values (True / False) of the
>exists statement. How can I do that ? I unable to do that from my query show
>below
>declare @.bln
>set @.bnl = Select Distinct Cust_Id,Cust_Name From Temp_Customer
>Where Not Exists
>(Select Cust_Id,Cust_Name From MyDb.dbo.Customer
>Where MyDb.dbo.Customer .Cust_Id = Temp_Customer.Cust_Id)
>Please Help ..
Hi Travis,
SQL Server doesn't have a boolean datatype, so it's not possible to
store the result of a logical expression in a variable. Of course, you
can use any variable to denote true and false in any way that appears to
be logical to you. Popular encodings for true and false are:
- datatype CHAR(1); values 'T'/'F' (or 'Y'/'N' - or even localised
versions [in the Netherlands, we'd use 'J'/'N' for yes/no]).
- datatype tinyint (or bit); values 0 / 1 (where you have to define [AND
DOCUMENT!!!] whether 1 means true and 0 means false or the other way
around).
To return the result of an exists expression, you can either use an IF
statement with two SET statements, or use one SET statement with a CASE
expression.
Example 1, using IF:
DECLARE @.YesOrNo CHAR(1)
IF EXISTS (SELECT *
FROM ...
WHERE ...)
BEGIN
SET @.YesOrNo = 'Y'
END
ELSE
BEGIN
SET @.YesOrNo = 'N'
END
Example 2, using CASE:
DECLARE @.YesOrNo CHAR(1)
SET @.YesOrNo =
CASE
WHEN EXISTS (SELECT *
FROM ...
WHERE ...)
THEN 'Y'
ELSE 'N'
END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Execution plan of UDF

Hi,

I have a table-valued user defined function (UDF) my_fnc.

The execution of statement "select * from my_fnc" takes much longer
time than runnig the code inside my_fnc (with necessary changes).
What can be the reason?
How can I see an execution plan used for UDF?

Thanks a lot
Martin"Martin Kraus" <mk.for.groups@.seznam.cz> wrote in message
news:2a25b99c.0404052334.476ade11@.posting.google.c om...
> Hi,
> I have a table-valued user defined function (UDF) my_fnc.
> The execution of statement "select * from my_fnc" takes much longer
> time than runnig the code inside my_fnc (with necessary changes).
> What can be the reason?
> How can I see an execution plan used for UDF?
> Thanks a lot
> Martin

I don't know about the performance issue, but you can use Profiler to view
the execution plan.

Simon