Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylThere are two options:
1=2E Do a Restore (Perhaps you have a point in time backup of the
database with transaction logs)
2=2E If you capsulated the query in a transaction (which I assume you
didn=B4t) Roll it back with ROLLBACK
HTH, Jens Suessmeyer.|||Daryl
yes, if you are lucky. you have to resort to back up. when did you take last
back up?
If you have taken any back up just prior to you can apply that. Wait I
understand that is not there. so you have to depend on 'Point in time '
Recovery.
BOL Has this: But remember. It will restore back to that particulat time. So
all the oprations after that time are be done manually again. Anyway take
full back up with no_truncate option before doing anything.
To restore to a point in time BOL has this.
Expand a server group, and then expand a server.
Expand Databases, right-click the database, point to All Tasks, and then
click Restore Database.
In Restore as database, type or select the name of the database to restore,
if different from the default.
Click Database.
In the First backup to restore list, click the backup set to restore.
In the Restore list, select the database backup and one or more transaction
logs to restore.
Click Point in time restore, and then type values for Date and Time.
Click the Options tab, and then click Leave database operational. No
additional transaction logs can be restored.
--
search and read for point-in-time;point of failure and related topics before
doing anything
--
Take Preventive measures like using
1) use delete trigger to not to rollback when
@.@.rowcount >1 or some records( if it is practical for you)
2) use trigger to store in history tables if data is so crucial.
Regards
R.D
"Daryl" wrote:
> Dear All,
> If I used a SQL command "Delete" to delete for example 1000 records, can I
> get it back after? It is because I executed the delete command and later
> found out I need some records back. Thank you for your help.
>
> Daryl
>
>
Showing posts with label command. Show all posts
Showing posts with label command. Show all posts
Tuesday, March 27, 2012
Can I get back the deleted records
Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
Daryl
Daryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
Daryl
Daryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Can I get back the deleted records
Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Can I get back the deleted records
Dear All,
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
If I used a SQL command "Delete" to delete for example 1000 records, can I
get it back after? It is because I executed the delete command and later
found out I need some records back. Thank you for your help.
DarylDaryl,
some 3rd party tools will have this functionality eg Lumigent Log Explorer,
but there's nothing out of the box in SQL Server on a per-table basis. If
nothing else has happened, or if it is important enough, you could do a
point-in-time restore, assuming you have the relevant backup plan, however
in this case you'll lose all data changes made since this time (to all
tables). Another possibility is to do a restore but to a database of another
name, then synchronize the data between the 2 tables.
As an aside, one thing I do is use an explicit transaction before an update
or delete, and issue a commit once I've seen the rowcount, otherwise a
rollback - it has saved me more than once :)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Sunday, March 25, 2012
Can I export a report as PDF files within a DOS batch file?
Hi,
Is there any command line tool that helps me to export a report into a PDF
file?
I need to do that within a batch file. I am trying to avoid C# coding to do
that.
Any help would be appreciated,
Maxyes, rs.exe should do what you are trying to accomplish.
--
Shaun Beane, MCT, MCDST, MCDBA
dbageek.blogspot.com
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eU8dFLcXGHA.3724@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there any command line tool that helps me to export a report into a PDF
> file?
> I need to do that within a batch file. I am trying to avoid C# coding to
> do that.
> Any help would be appreciated,
> Max
>|||Great! Do you know any link to a sample that shows me how to export and save
the report into a PDF file?
"Shaun Beane" <shaun.beane@.gmail.nojunk.com> wrote in message
news:OhIWIZcXGHA.4148@.TK2MSFTNGP03.phx.gbl...
> yes, rs.exe should do what you are trying to accomplish.
> --
> Shaun Beane, MCT, MCDST, MCDBA
> dbageek.blogspot.com
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:eU8dFLcXGHA.3724@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Is there any command line tool that helps me to export a report into a
>> PDF file?
>> I need to do that within a batch file. I am trying to avoid C# coding to
>> do that.
>> Any help would be appreciated,
>> Max
>>
>|||Hi Maxwell,
You can use the RS.exe Utility to export the report to the PDF file.
RS.exe will read a script file which should be written by VB.NET.
Here is a article posted some sample code, you may try to refer.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_frm/thread/e608c8fecc95d08/8a2dcc6ca82bf3fc?tvc=1&q=RS+utility+export+pdf
&hl=zh-CN#8a2dcc6ca82bf3fc
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Is there any command line tool that helps me to export a report into a PDF
file?
I need to do that within a batch file. I am trying to avoid C# coding to do
that.
Any help would be appreciated,
Maxyes, rs.exe should do what you are trying to accomplish.
--
Shaun Beane, MCT, MCDST, MCDBA
dbageek.blogspot.com
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eU8dFLcXGHA.3724@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there any command line tool that helps me to export a report into a PDF
> file?
> I need to do that within a batch file. I am trying to avoid C# coding to
> do that.
> Any help would be appreciated,
> Max
>|||Great! Do you know any link to a sample that shows me how to export and save
the report into a PDF file?
"Shaun Beane" <shaun.beane@.gmail.nojunk.com> wrote in message
news:OhIWIZcXGHA.4148@.TK2MSFTNGP03.phx.gbl...
> yes, rs.exe should do what you are trying to accomplish.
> --
> Shaun Beane, MCT, MCDST, MCDBA
> dbageek.blogspot.com
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:eU8dFLcXGHA.3724@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Is there any command line tool that helps me to export a report into a
>> PDF file?
>> I need to do that within a batch file. I am trying to avoid C# coding to
>> do that.
>> Any help would be appreciated,
>> Max
>>
>|||Hi Maxwell,
You can use the RS.exe Utility to export the report to the PDF file.
RS.exe will read a script file which should be written by VB.NET.
Here is a article posted some sample code, you may try to refer.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_frm/thread/e608c8fecc95d08/8a2dcc6ca82bf3fc?tvc=1&q=RS+utility+export+pdf
&hl=zh-CN#8a2dcc6ca82bf3fc
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
can I export a database from query analyzer?
Is there a command or script I can run from query analyzer to export a
database?
Its a long story but I can only get to the database through query analyzer
and there is one database I need to pull out to another machine.
Thanks,
You can do a couple of things:
1) Backup the database and then restore it on the other server. Refer to SQL Server Books Online for syntax and examples
2) If you have a copy of the database files (data + logs) then you can use sp_attach_db to attach the database files on another server.
3) If you don't have the database files but can take the database offline for sometime then you can take the database offline (one method will be to use sp_dboption) then
copy the files to the new server. Now you can take db online. connect to the new server using QA and use sp_attach_db
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Great I do have the database files and log. I will just attach those in
another instance.
Thanks for your help.
"Uttam Parui[MS]" wrote:
> You can do a couple of things:
> 1) Backup the database and then restore it on the other server. Refer to SQL Server Books Online for syntax and examples
> 2) If you have a copy of the database files (data + logs) then you can use sp_attach_db to attach the database files on another server.
> 3) If you don't have the database files but can take the database offline for sometime then you can take the database offline (one method will be to use sp_dboption) then
> copy the files to the new server. Now you can take db online. connect to the new server using QA and use sp_attach_db
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
> to do this is to visit the following websites: http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
sql
database?
Its a long story but I can only get to the database through query analyzer
and there is one database I need to pull out to another machine.
Thanks,
You can do a couple of things:
1) Backup the database and then restore it on the other server. Refer to SQL Server Books Online for syntax and examples
2) If you have a copy of the database files (data + logs) then you can use sp_attach_db to attach the database files on another server.
3) If you don't have the database files but can take the database offline for sometime then you can take the database offline (one method will be to use sp_dboption) then
copy the files to the new server. Now you can take db online. connect to the new server using QA and use sp_attach_db
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Great I do have the database files and log. I will just attach those in
another instance.
Thanks for your help.
"Uttam Parui[MS]" wrote:
> You can do a couple of things:
> 1) Backup the database and then restore it on the other server. Refer to SQL Server Books Online for syntax and examples
> 2) If you have a copy of the database files (data + logs) then you can use sp_attach_db to attach the database files on another server.
> 3) If you don't have the database files but can take the database offline for sometime then you can take the database offline (one method will be to use sp_dboption) then
> copy the files to the new server. Now you can take db online. connect to the new server using QA and use sp_attach_db
> HTH,
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
> to do this is to visit the following websites: http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
sql
Can I disable log for a table?
Hi,
I Oracle we can disable redo log for a table by using this command:
ALTER TABLE your_table_name NOLOGGING
Do we have such capability in either 2000 or 2005 versions of SQL server?
Thanks,
MaxNo. You can change the recovery model but only at the database level.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
Hi,
I Oracle we can disable redo log for a table by using this command:
ALTER TABLE your_table_name NOLOGGING
Do we have such capability in either 2000 or 2005 versions of SQL server?
Thanks,
Max|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> No. You can change the recovery model but only at the database level.
True, but to be complete, you can change it to a simple recovery model but
some logging still occurs.
So you can't elimiante it cmopletely.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I Oracle we can disable redo log for a table by using this command:
>
> ALTER TABLE your_table_name NOLOGGING
>
> Do we have such capability in either 2000 or 2005 versions of SQL server?
>
> Thanks,
> Max
>|||"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I Oracle we can disable redo log for a table by using this command:
>
> ALTER TABLE your_table_name NOLOGGING
>
> Do we have such capability in either 2000 or 2005 versions of SQL server?
>
> Thanks,
> Max
>
You could put the table in its own database and use the Simple Recovery
model in that database. You can then create the reference to that table as a
view or synonym in another database.
Think about the consequences for your data integrity if you ever have to
restore the transaction log.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can eliminate logging completely, but you have to place the database in
a read only state to do so.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:e7sg7xaYGHA.4688@.TK2MSFTNGP04.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> No. You can change the recovery model but only at the database level.
> True, but to be complete, you can change it to a simple recovery model but
> some logging still occurs.
> So you can't elimiante it cmopletely.
>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
>> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I Oracle we can disable redo log for a table by using this command:
>>
>> ALTER TABLE your_table_name NOLOGGING
>>
>> Do we have such capability in either 2000 or 2005 versions of SQL server?
>>
>> Thanks,
>> Max
>>
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u5zmgmcYGHA.4796@.TK2MSFTNGP04.phx.gbl...
> You can eliminate logging completely, but you have to place the database
in
> a read only state to do so.
Details, details. ;-)
But I have to admit that's true.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:e7sg7xaYGHA.4688@.TK2MSFTNGP04.phx.gbl...
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> >> No. You can change the recovery model but only at the database level.
> >
> > True, but to be complete, you can change it to a simple recovery model
but
> > some logging still occurs.
> >
> > So you can't elimiante it cmopletely.
> >
> >
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> .
> >> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> >> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> >> Hi,
> >>
> >> I Oracle we can disable redo log for a table by using this command:
> >>
> >>
> >>
> >> ALTER TABLE your_table_name NOLOGGING
> >>
> >>
> >>
> >> Do we have such capability in either 2000 or 2005 versions of SQL
server?
> >>
> >>
> >>
> >> Thanks,
> >>
> >> Max
> >>
> >>
> >
> >
>
I Oracle we can disable redo log for a table by using this command:
ALTER TABLE your_table_name NOLOGGING
Do we have such capability in either 2000 or 2005 versions of SQL server?
Thanks,
MaxNo. You can change the recovery model but only at the database level.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
Hi,
I Oracle we can disable redo log for a table by using this command:
ALTER TABLE your_table_name NOLOGGING
Do we have such capability in either 2000 or 2005 versions of SQL server?
Thanks,
Max|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> No. You can change the recovery model but only at the database level.
True, but to be complete, you can change it to a simple recovery model but
some logging still occurs.
So you can't elimiante it cmopletely.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I Oracle we can disable redo log for a table by using this command:
>
> ALTER TABLE your_table_name NOLOGGING
>
> Do we have such capability in either 2000 or 2005 versions of SQL server?
>
> Thanks,
> Max
>|||"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I Oracle we can disable redo log for a table by using this command:
>
> ALTER TABLE your_table_name NOLOGGING
>
> Do we have such capability in either 2000 or 2005 versions of SQL server?
>
> Thanks,
> Max
>
You could put the table in its own database and use the Simple Recovery
model in that database. You can then create the reference to that table as a
view or synonym in another database.
Think about the consequences for your data integrity if you ever have to
restore the transaction log.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can eliminate logging completely, but you have to place the database in
a read only state to do so.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:e7sg7xaYGHA.4688@.TK2MSFTNGP04.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
>> No. You can change the recovery model but only at the database level.
> True, but to be complete, you can change it to a simple recovery model but
> some logging still occurs.
> So you can't elimiante it cmopletely.
>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
>> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I Oracle we can disable redo log for a table by using this command:
>>
>> ALTER TABLE your_table_name NOLOGGING
>>
>> Do we have such capability in either 2000 or 2005 versions of SQL server?
>>
>> Thanks,
>> Max
>>
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u5zmgmcYGHA.4796@.TK2MSFTNGP04.phx.gbl...
> You can eliminate logging completely, but you have to place the database
in
> a read only state to do so.
Details, details. ;-)
But I have to admit that's true.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:e7sg7xaYGHA.4688@.TK2MSFTNGP04.phx.gbl...
> >
> > "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> > news:u3H$8laYGHA.1204@.TK2MSFTNGP04.phx.gbl...
> >> No. You can change the recovery model but only at the database level.
> >
> > True, but to be complete, you can change it to a simple recovery model
but
> > some logging still occurs.
> >
> > So you can't elimiante it cmopletely.
> >
> >
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> .
> >> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> >> news:O7XrOfaYGHA.3448@.TK2MSFTNGP04.phx.gbl...
> >> Hi,
> >>
> >> I Oracle we can disable redo log for a table by using this command:
> >>
> >>
> >>
> >> ALTER TABLE your_table_name NOLOGGING
> >>
> >>
> >>
> >> Do we have such capability in either 2000 or 2005 versions of SQL
server?
> >>
> >>
> >>
> >> Thanks,
> >>
> >> Max
> >>
> >>
> >
> >
>
Tuesday, March 20, 2012
Can I copy a local package
Hi
I'm looking for a way to copy a local package. I thought it would be fairly
straight forward with just a simple command, but that doesn't seems to be
the case. Does anyone of you know how it can be done - if it can?....
Regards
Steen
Hi,
In DTS window in enterprise manager, Save the DTS package as a Structured
Storage File.
After that you can copy package to the destination server.
- In DTS Designer, on the toolbar, click Save.
- In the Location list, click Structured Storage File.
- Complete the rest of the required fields.
You can later open them in another Server.
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Os3PalPWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm looking for a way to copy a local package. I thought it would be
fairly
> straight forward with just a simple command, but that doesn't seems to be
> the case. Does anyone of you know how it can be done - if it can?....
> Regards
> Steen
>
|||Steen,
manually the simplest way is to open the package and save as a different
name, but to do this programatically is not straightforward - have a look at
this page for the posibilities available:
http://www.sqldts.com/default.aspx?204.
HTH,
Paul Ibison
I'm looking for a way to copy a local package. I thought it would be fairly
straight forward with just a simple command, but that doesn't seems to be
the case. Does anyone of you know how it can be done - if it can?....
Regards
Steen
Hi,
In DTS window in enterprise manager, Save the DTS package as a Structured
Storage File.
After that you can copy package to the destination server.
- In DTS Designer, on the toolbar, click Save.
- In the Location list, click Structured Storage File.
- Complete the rest of the required fields.
You can later open them in another Server.
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Os3PalPWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm looking for a way to copy a local package. I thought it would be
fairly
> straight forward with just a simple command, but that doesn't seems to be
> the case. Does anyone of you know how it can be done - if it can?....
> Regards
> Steen
>
|||Steen,
manually the simplest way is to open the package and save as a different
name, but to do this programatically is not straightforward - have a look at
this page for the posibilities available:
http://www.sqldts.com/default.aspx?204.
HTH,
Paul Ibison
Can I copy a local package
Hi
I'm looking for a way to copy a local package. I thought it would be fairly
straight forward with just a simple command, but that doesn't seems to be
the case. Does anyone of you know how it can be done - if it can?....
Regards
SteenHi,
In DTS window in enterprise manager, Save the DTS package as a Structured
Storage File.
After that you can copy package to the destination server.
- In DTS Designer, on the toolbar, click Save.
- In the Location list, click Structured Storage File.
- Complete the rest of the required fields.
You can later open them in another Server.
--
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Os3PalPWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm looking for a way to copy a local package. I thought it would be
fairly
> straight forward with just a simple command, but that doesn't seems to be
> the case. Does anyone of you know how it can be done - if it can?....
> Regards
> Steen
>|||Steen,
manually the simplest way is to open the package and save as a different
name, but to do this programatically is not straightforward - have a look at
this page for the posibilities available:
http://www.sqldts.com/default.aspx?204.
HTH,
Paul Ibison
I'm looking for a way to copy a local package. I thought it would be fairly
straight forward with just a simple command, but that doesn't seems to be
the case. Does anyone of you know how it can be done - if it can?....
Regards
SteenHi,
In DTS window in enterprise manager, Save the DTS package as a Structured
Storage File.
After that you can copy package to the destination server.
- In DTS Designer, on the toolbar, click Save.
- In the Location list, click Structured Storage File.
- Complete the rest of the required fields.
You can later open them in another Server.
--
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Os3PalPWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm looking for a way to copy a local package. I thought it would be
fairly
> straight forward with just a simple command, but that doesn't seems to be
> the case. Does anyone of you know how it can be done - if it can?....
> Regards
> Steen
>|||Steen,
manually the simplest way is to open the package and save as a different
name, but to do this programatically is not straightforward - have a look at
this page for the posibilities available:
http://www.sqldts.com/default.aspx?204.
HTH,
Paul Ibison
Can I copy a local package
Hi
I'm looking for a way to copy a local package. I thought it would be fairly
straight forward with just a simple command, but that doesn't seems to be
the case. Does anyone of you know how it can be done - if it can?....
Regards
SteenHi,
In DTS window in enterprise manager, Save the DTS package as a Structured
Storage File.
After that you can copy package to the destination server.
- In DTS Designer, on the toolbar, click Save.
- In the Location list, click Structured Storage File.
- Complete the rest of the required fields.
You can later open them in another Server.
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Os3PalPWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm looking for a way to copy a local package. I thought it would be
fairly
> straight forward with just a simple command, but that doesn't seems to be
> the case. Does anyone of you know how it can be done - if it can?....
> Regards
> Steen
>|||Steen,
manually the simplest way is to open the package and save as a different
name, but to do this programatically is not straightforward - have a look at
this page for the posibilities available:
http://www.sqldts.com/default.aspx?204.
HTH,
Paul Ibisonsql
I'm looking for a way to copy a local package. I thought it would be fairly
straight forward with just a simple command, but that doesn't seems to be
the case. Does anyone of you know how it can be done - if it can?....
Regards
SteenHi,
In DTS window in enterprise manager, Save the DTS package as a Structured
Storage File.
After that you can copy package to the destination server.
- In DTS Designer, on the toolbar, click Save.
- In the Location list, click Structured Storage File.
- Complete the rest of the required fields.
You can later open them in another Server.
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Os3PalPWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm looking for a way to copy a local package. I thought it would be
fairly
> straight forward with just a simple command, but that doesn't seems to be
> the case. Does anyone of you know how it can be done - if it can?....
> Regards
> Steen
>|||Steen,
manually the simplest way is to open the package and save as a different
name, but to do this programatically is not straightforward - have a look at
this page for the posibilities available:
http://www.sqldts.com/default.aspx?204.
HTH,
Paul Ibisonsql
Monday, March 19, 2012
Can I achieve WITH(NOLOCK) on all joins in a stored procedure with a single command?
I have a number of reporting stored procedures that purely list
records and make no changes to the data. I have noticed that some of
these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
a simple example :-
Select * from table1 WITH(NOLOCK)
INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
There lots of these and many of them have lots of joins so I'm looking
for a way to apply WITH(NOLOCK) to the whole procedure and save myself
the time it takes to add the hint to each table/join. I know that it
is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
volunteer as the deadlock victim, but this isn't suitable as I need
the procedure to return it's records.
Does anybody have a suggestion or am I looking ata couple of days of
ctrl-v'ing WITH(NOLOCK) everywhere?
Thanks,
LiamHow about below?
SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> I have a number of reporting stored procedures that purely list
> records and make no changes to the data. I have noticed that some of
> these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> a simple example :-
> Select * from table1 WITH(NOLOCK)
> INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> There lots of these and many of them have lots of joins so I'm looking
> for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> the time it takes to add the hint to each table/join. I know that it
> is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> volunteer as the deadlock victim, but this isn't suitable as I need
> the procedure to return it's records.
> Does anybody have a suggestion or am I looking ata couple of days of
> ctrl-v'ing WITH(NOLOCK) everywhere?
> Thanks,
> Liam|||Thanks, that's just what I was looking for.
Liam
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uvCEm35nDHA.2312@.TK2MSFTNGP12.phx.gbl>...
> How about below?
> SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Liam Weston" <liam_weston@.hotmail.com> wrote in message
> news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> > I have a number of reporting stored procedures that purely list
> > records and make no changes to the data. I have noticed that some of
> > these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> > a simple example :-
> >
> > Select * from table1 WITH(NOLOCK)
> > INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> > INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> > LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> >
> > There lots of these and many of them have lots of joins so I'm looking
> > for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> > the time it takes to add the hint to each table/join. I know that it
> > is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> > volunteer as the deadlock victim, but this isn't suitable as I need
> > the procedure to return it's records.
> >
> > Does anybody have a suggestion or am I looking ata couple of days of
> > ctrl-v'ing WITH(NOLOCK) everywhere?
> >
> > Thanks,
> >
> > Liam
records and make no changes to the data. I have noticed that some of
these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
a simple example :-
Select * from table1 WITH(NOLOCK)
INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
There lots of these and many of them have lots of joins so I'm looking
for a way to apply WITH(NOLOCK) to the whole procedure and save myself
the time it takes to add the hint to each table/join. I know that it
is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
volunteer as the deadlock victim, but this isn't suitable as I need
the procedure to return it's records.
Does anybody have a suggestion or am I looking ata couple of days of
ctrl-v'ing WITH(NOLOCK) everywhere?
Thanks,
LiamHow about below?
SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> I have a number of reporting stored procedures that purely list
> records and make no changes to the data. I have noticed that some of
> these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> a simple example :-
> Select * from table1 WITH(NOLOCK)
> INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> There lots of these and many of them have lots of joins so I'm looking
> for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> the time it takes to add the hint to each table/join. I know that it
> is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> volunteer as the deadlock victim, but this isn't suitable as I need
> the procedure to return it's records.
> Does anybody have a suggestion or am I looking ata couple of days of
> ctrl-v'ing WITH(NOLOCK) everywhere?
> Thanks,
> Liam|||Thanks, that's just what I was looking for.
Liam
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uvCEm35nDHA.2312@.TK2MSFTNGP12.phx.gbl>...
> How about below?
> SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Liam Weston" <liam_weston@.hotmail.com> wrote in message
> news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> > I have a number of reporting stored procedures that purely list
> > records and make no changes to the data. I have noticed that some of
> > these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> > a simple example :-
> >
> > Select * from table1 WITH(NOLOCK)
> > INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> > INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> > LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> >
> > There lots of these and many of them have lots of joins so I'm looking
> > for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> > the time it takes to add the hint to each table/join. I know that it
> > is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> > volunteer as the deadlock victim, but this isn't suitable as I need
> > the procedure to return it's records.
> >
> > Does anybody have a suggestion or am I looking ata couple of days of
> > ctrl-v'ing WITH(NOLOCK) everywhere?
> >
> > Thanks,
> >
> > Liam
Thursday, March 8, 2012
can dbcc inputbuffer show parameterized queries?
Hi,
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lc
LC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc
|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>
>
|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...[vbcol=seagreen]
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lc
LC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc
|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>
>
|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...[vbcol=seagreen]
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
can dbcc inputbuffer show parameterized queries?
Hi,
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lcLC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>
>|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...[vbcol=seagreen]
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
>
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lcLC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>
>|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...[vbcol=seagreen]
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
>
can dbcc inputbuffer show parameterized queries?
Hi,
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lcLC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> > Hi,
> > I'm trying to capture blocking information on my SQL Server 2k box and
> > running dbcc inputbuffer only shows me the "sp_executesql" command, it
> > doesn't show me the parameters which is the actual sql. Profiler seems to
> > have no problem showing the parameters b/c it always shows me the sql
> > that's
> > running, not the sp_executesql command. Is there some other command that
> > will pull the sql from a parameterized query a la Profiler? Thanks for
> > any
> > help here.
> >
> > lc
>
>|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
>> LC,
>> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
>> get nothing unless a stored procedure is being executed.)
>> RLF
>> "LC" <LC@.discussions.microsoft.com> wrote in message
>> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>> > Hi,
>> > I'm trying to capture blocking information on my SQL Server 2k box and
>> > running dbcc inputbuffer only shows me the "sp_executesql" command, it
>> > doesn't show me the parameters which is the actual sql. Profiler seems
>> > to
>> > have no problem showing the parameters b/c it always shows me the sql
>> > that's
>> > running, not the sp_executesql command. Is there some other command
>> > that
>> > will pull the sql from a parameterized query a la Profiler? Thanks for
>> > any
>> > help here.
>> >
>> > lc
>>
>>
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lcLC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> > Hi,
> > I'm trying to capture blocking information on my SQL Server 2k box and
> > running dbcc inputbuffer only shows me the "sp_executesql" command, it
> > doesn't show me the parameters which is the actual sql. Profiler seems to
> > have no problem showing the parameters b/c it always shows me the sql
> > that's
> > running, not the sp_executesql command. Is there some other command that
> > will pull the sql from a parameterized query a la Profiler? Thanks for
> > any
> > help here.
> >
> > lc
>
>|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
>> LC,
>> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
>> get nothing unless a stored procedure is being executed.)
>> RLF
>> "LC" <LC@.discussions.microsoft.com> wrote in message
>> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>> > Hi,
>> > I'm trying to capture blocking information on my SQL Server 2k box and
>> > running dbcc inputbuffer only shows me the "sp_executesql" command, it
>> > doesn't show me the parameters which is the actual sql. Profiler seems
>> > to
>> > have no problem showing the parameters b/c it always shows me the sql
>> > that's
>> > running, not the sp_executesql command. Is there some other command
>> > that
>> > will pull the sql from a parameterized query a la Profiler? Thanks for
>> > any
>> > help here.
>> >
>> > lc
>>
>>
Wednesday, March 7, 2012
Can anyone tell me why this code wont update my SQL Server database?
I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.
I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.
Thanks
Be sure to indicate parameter lengths for character-type data.Sub btnUpdate_Click_1(sender As Object, e As EventArgs)
Dim UserCode as String
Dim PropertyCode as String
Dim UnitCode as String
Dim address1 as String
Dim address2 as String
Dim address3 as String
Dim city as String
Dim zip_Code as String
Dim description as String
Dim price_Range as String
Dim state as String
Dim type_Property as String
Dim property_Status as Stringif chkChangeState.Checked = True then
state = ddl_State.SelectedItem.Value
else
state = lblState.Text
end ifif chkPropertyType.Checked = True then
type_Property = ddlPropertyType.SelectedItem.Value
else
type_Property = lblPropertyType.Text
end ifif chkPropertyStatus.Checked = True then
property_Status = ddlPropertyStatus.SelectedItem.Value
else
property_Status = lblPropertyStatus.Text
end iftxtPropertyCode.ReadOnly = False
txtUnitCode.ReadOnly = False'address1 = txtAddress1.Text
'address2 = txtAddress2.Text
'address3 = txtAddress3.Text
'city = txtCity.Text
'zip_Code = txtZipCode.Text
'description = txtDescription.Text
'price_Range = txtPriceRange.TextUserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
PropertyCode = txtPropertyCode.Text
UnitCode = txtUnitCode.TextDim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@.Address1, [Address2]=@.Address2, [Address3]=@.Address3, [City]=@.City, [State]=@.State, [Zip_Code]=@.Zip_Code, [Type_Property]=@.Type_Property, [Description]=@.Description, [Property_Status]=@.Property_Status, [Price_Range]=@.Price_Range WHERE ([Property_db].[Code] = @.Code) AND ([Property_db].[Prop_Code] = @.Prop_Code) AND ([Property_db].[Unit_Code] = @.Unit_Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnectionDim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@.Code"
dbParam_code.Value = UserCode
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)
Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_prop_Code.ParameterName = "@.Prop_Code"
dbParam_prop_Code.Value = PropertyCode
dbParam_prop_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_prop_Code)
Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_unit_Code.ParameterName = "@.Unit_Code"
dbParam_unit_Code.Value = UnitCode
dbParam_unit_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_unit_Code)
Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address1.ParameterName = "@.Address1"
dbParam_address1.Value = txtAddress1.Text
dbParam_address1.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address1)
Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address2.ParameterName = "@.Address2"
dbParam_address2.Value = txtAddress2.Text
dbParam_address2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address2)
Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address3.ParameterName = "@.Address3"
dbParam_address3.Value = txtAddress3.Text
dbParam_address3.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address3)
Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_city.ParameterName = "@.City"
dbParam_city.Value = txtCity.Text
dbParam_city.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_city)
Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_state.ParameterName = "@.State"
dbParam_state.Value = state
dbParam_state.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_state)
Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_zip_Code.ParameterName = "@.Zip_Code"
dbParam_zip_Code.Value = txtZipCode.Text
dbParam_zip_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_zip_Code)
Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_type_Property.ParameterName = "@.Type_Property"
dbParam_type_Property.Value = type_Property
dbParam_type_Property.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_type_Property)
Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_description.ParameterName = "@.Description"
dbParam_description.Value = txtDescription.Text
dbParam_description.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_description)
Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_property_Status.ParameterName = "@.Property_Status"
dbParam_property_Status.Value = property_Status
dbParam_property_Status.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_property_Status)
Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_price_Range.ParameterName = "@.Price_Range"
dbParam_price_Range.Value = txtPriceRange.Text
dbParam_price_Range.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_price_Range)Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try'if Not rowsAffected then
Response.Redirect("./editproperty.aspx")
'end if
End Sub
Terri
Thursday, February 16, 2012
Can a Snapshot report be created programmatically?
Can I create a Snapshot report with parameters programmatically in RS 2000 or RS 2005? If this can be done what is the command for it? Thanks.
Yes. The report has to be set to Snapshot Execution and has to have default values. The SOAP method to call is UpdateSnapshot.
If you want to create a history snapshot then you can call CreateReportHistorySnapshot.
You can not set the parameters when calling these parameters. If you want the parameters different then you must first set the report parameters then call one of the above methods.
-Daniel
|||Hi Daniel,
Thanks for the tip... you put me in the right direction. Now, how do I change the parameters before getting a Snapshot? Can I save the result in my own table instead of the History table?
|||
You need to call SetReportParameters to set the parameters. The history snapshot will be saved to the History table. Can you explain why you would want to save it elsewhere?
-Daniel
|||I was planning to use my own database to save the snapshot for ease of maintenance and backup, besides, using the SnapshotDate and converting it to another format before using it is extar work! I guess I can use the RS History Table for now. One thing though, when I get a snapshot, the date in History table doesn't match with the snapshot date! Any idea why? Thanks.|||I assume you mean the snapshot datetime stamp used as URL access parameter to render history snapshot. The snapshot datetime value is always based on UTC time which is independent of your local timezone, while the date in the internal catalog tables depends on your server timezone settings.
-- Robert
Tuesday, February 14, 2012
Can a ControlParameter be used to supply a parameter for a stored procedure?
The code below is an attempt at using the value from a dropdownlist to feed into stored procedure outlined in the Select command. I have seen examples where the control parameter is used with a select command but nothing where the parameter has to be fed into a stored procedure.
<
asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString=".."SelectCommand="procCAGetCustomerKPIs"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameterName="iCustomerGroupId"ControlID="CustomerFilterList"PropertyName="SelectedValue"/>
</SelectParameters>
</asp:SqlDataSource>
cheers-jim.
Yes. Normally, I would use the designer, and hit "refresh parameters", it will then generate a list of all the parameters the stored procedure is expecting. From that point, change the parameters from type "None" to "Control", etc.|||Forgot to add autoPostBack=true to the dropdownlist control!!Friday, February 10, 2012
calling vbscript from SP
I am trying to call a VB script file with the following command,
xp_cmdshell 'cscript c:\file.vbs'
But its giving the following output -
Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:\file.vbs".
NULL
----
If i changed the command to
xp_cmdshell 'cscript c:file.vbs'
it gives the folowing error -
Input Error: Can not find script file "C:\WINNT\system32\file.vbs".
Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.
Thanks & regards,
-dkoushikHow about using XML - http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=9 and another possibility is to directly access the ODBC lowlevel APIs directly. I've no exp.using languages like Visual Basic. You may have some luck accessing the stored procedures
using SQL Namespace APIs & MSEQ.|||Hi satya,
Thanks for your reply. But I guess u have misunderstood my question.
My Actual intention is to call web url from the SqlServe DB.
I have done the same in oracle. I have used Java stored procedure
where I have called a web url and the java sp is get called from a
Oracle function. I am sure there is nothing called java stored proc in Sql server.
So How can I achive the same result in Sql Server.
I was just trying some thing using xp_cmdshell (a system SP)
which can call any VB script which in turn will call the web url.
Plz help if you have any idea regarding this.
Thanks & Regards,
-- dkoushik
Originally posted by Satya
How about using XML - http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=9 and another possibility is to directly access the ODBC lowlevel APIs directly. I've no exp.using languages like Visual Basic. You may have some luck accessing the stored procedures
using SQL Namespace APIs & MSEQ.|||If you are using xp_cmdshell, you've got 90% of the battle won. xp_cmdshell can launch any file that the NT Login used by MS-SQL can see, using whatever tools are available on the machine that is running SQL Server.
If you can log into the machine that runs your SQL Server, using the same login credentials that SQL Server uses, then you will have almost exactly the same conditions to test with that the xp_cmdshell will run under. Find a command string you can type to get something to happend, and you can then issue the exact same command using xp_cmdshell.
-PatP|||Hi Pat,
Thanks for the reply.
What I understood from your response is ,
I have to execute the xp_cmdshell in the database server
query analyzer. I have done the same thing but nothing is happening.
But this time its not giving any error. But the result its showing as NULL.
Do have any idea whats happening.
please help.
xp_cmdshell 'cscript c:\file.vbs'
But its giving the following output -
Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:\file.vbs".
NULL
----
If i changed the command to
xp_cmdshell 'cscript c:file.vbs'
it gives the folowing error -
Input Error: Can not find script file "C:\WINNT\system32\file.vbs".
Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.
Thanks & regards,
-dkoushikHow about using XML - http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=9 and another possibility is to directly access the ODBC lowlevel APIs directly. I've no exp.using languages like Visual Basic. You may have some luck accessing the stored procedures
using SQL Namespace APIs & MSEQ.|||Hi satya,
Thanks for your reply. But I guess u have misunderstood my question.
My Actual intention is to call web url from the SqlServe DB.
I have done the same in oracle. I have used Java stored procedure
where I have called a web url and the java sp is get called from a
Oracle function. I am sure there is nothing called java stored proc in Sql server.
So How can I achive the same result in Sql Server.
I was just trying some thing using xp_cmdshell (a system SP)
which can call any VB script which in turn will call the web url.
Plz help if you have any idea regarding this.
Thanks & Regards,
-- dkoushik
Originally posted by Satya
How about using XML - http://www.awprofessional.com/articles/article.asp?p=102307&seqNum=9 and another possibility is to directly access the ODBC lowlevel APIs directly. I've no exp.using languages like Visual Basic. You may have some luck accessing the stored procedures
using SQL Namespace APIs & MSEQ.|||If you are using xp_cmdshell, you've got 90% of the battle won. xp_cmdshell can launch any file that the NT Login used by MS-SQL can see, using whatever tools are available on the machine that is running SQL Server.
If you can log into the machine that runs your SQL Server, using the same login credentials that SQL Server uses, then you will have almost exactly the same conditions to test with that the xp_cmdshell will run under. Find a command string you can type to get something to happend, and you can then issue the exact same command using xp_cmdshell.
-PatP|||Hi Pat,
Thanks for the reply.
What I understood from your response is ,
I have to execute the xp_cmdshell in the database server
query analyzer. I have done the same thing but nothing is happening.
But this time its not giving any error. But the result its showing as NULL.
Do have any idea whats happening.
please help.
Calling user-defined functions in OLE DB Command transformation
Hi
We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.
The following was inserted for the SQLCommand property:
EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)
However, when the Refresh button is pressed we are presented with the error below:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".
If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Any assistance would be greatly appreciated.
Thanks
Neil
You cannot use SET or EXEC with a UDF. You need to useSELECT @.ReturnVar = [dbo].fnYourFunctionName ( @.InputVar )
Calling user-defined functions in OLE DB Command transformation
Hi
We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.
The following was inserted for the SQLCommand property:
EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)
However, when the Refresh button is pressed we are presented with the error below:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".
If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Any assistance would be greatly appreciated.
Thanks
Neil
I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||I beleive this post belongs in the SS Integration Services Forum.
Alle
|||EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)
Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.
HTH
Alle
Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.
The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.
Thanks
Neil
|||Moved to the SQL Server integration services forum :).|||Did you resolve this? I'm having the same prob. too!
Cheers,
Tamim.
|||
Tamin,
The origianl poster seems to be providing a worng sintax to call the function...
Can you provide the syntax your are using and the error generated?
Subscribe to:
Posts (Atom)