How to rename a column in SqlCE?

When I execute EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN', I am getting the following error:
Major Error 0x80004005, Minor Error 25621
> EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN'
The specified argument value for the procedure is not valid. [ Argument # = 3,Name of procedure(if known) = sp_rename ]

Could some one guide me where I am wrong?
I am executing this query against SqlCE in Sql Server 2005 Management Studio.

The sp_rename procedure is not avialable in SQL CE!

In Sql Server 2005 Management Studio you have to create a new column with the new name, then you have to update it with the values from your old column and then you have to delete your old column. The last action is difficult if the column is (part of) an index.

ALTER TABLE YOURTABLENAME ADD PVPROC INTEGER (or your datatype)
UPDATE YOURTABLENAME SET PVPROC = PATHSPECIMEN.PVVALUE
ALTER TABLE School DROP COLUMN PATHSPECIMEN.PVVALUE

I did it yesterday myself in this way!

Anne-Jan Tuinstra

|||Hi,

Thanks for your prompt reply and for the nice solution.

As you said that column is a part of index, how to go to resolve this case?.

Thanks
GSR|||

I think you/ I have to remove the index.

Up till now it didn't work with the manager.

My table now has one column to many!!!!!

How to reference a dataset passed to script

Hi, I have a dataset created via the Execute SQL Statement control and wish to pass it into a script control that within the vb.net script I wish to perform different functions to it.

The main goal is to take the dataset loop through it and record values into an array then take that array and loop through that so I can send an email containing the information within the array.

Can anyone help?

If inside a script component the common method is to use row

So you can do row.xxx where xxx is the field name... then using row.next row etc you can loop and such.

|||Ok, how do we reference the dataset? I appreciate your answer Dave but its very vague.

So I have the execute SQL statement create a dataset named "dataset1" using an ado.net object type.

Then within the script component how to we initialize, and work with "dataset1"? I wish to loop through "dataset1" and perform various functions on it. Sure the row access sounds great for the looping part but what of the other parts?|||Use a foreach loop to open that ADO recordset and loop through it. There are plenty of examples in this forum.|||I know that method and that won't work for what i'm trying to do, or at least i don't think so.

All I want to know is how to reference a dataset from an execute sql statement within a script component. I wish to reference this dataset within the script component and loop through and create an array with specific information. Then I want to use the array as loop through that to create a body of an email. So PLEASE answer the question of ->>>>>

How do I reference a dataset after it being populated via an Execute SQL Statement?

<<<<<--
I tried
dim results as dataset
dim reader as datatablereader
reader = results.tables(0).createdatareaders
do while reader.read
execute statements
loop

I get the error that reader is referenced to no table.|||

I misunderstood your initial question, I thought you needed basic access. I was thinking you used the execute sql command component to create the data set. So if you execute the sql, and then have the output of that component connect to a script component you can just use the Row call I mentioned, because it automatically ties to the "input" dataset from the sql execute component.

|||Here is my code. I have OBJ_NULL_SET as the result set object from the execute sql components output.

Public Sub Main()
'Assume success

Try
Dim results As DataSet

results = CType(ReadVariable("OBJ_NULL_SET"), DataSet)
For Each tbl As DataTable In results.Tables
System.Windows.Forms.MessageBox.Show(tbl.TableName)
Next

Catch ex As Exception
Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
End Try
Dts.TaskResult = Dts.Results.Success

End Sub

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try

Return result
End Function

When the msgbox pops up it says Table with nothing else. It does not display the name of the dataset so i'm sure that i've done something wrong where the dataset is not being referenced properly.|||

You are already referencing the dataset by iterating through the DataTables. The first table's name so happens to be "Table", and that's what the message box call shows.

So, for the only DataTable of the DataSet, iterate through the rows using standard ADO.NET. For example, call this function right after you're first MsgBox() call, and it will display the column names and stringify the data. You're refencing the data from the Execute SQL task at this point, and what is done beyond that is up to your imagination.

Private Sub DisplayTable(ByVal tbl As DataTable)

Dim dr As DataRow, dc As DataColumn

Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()

For colNum As Integer = 0 To tbl.Columns.Count - 1

dc = tbl.Columns(colNum)

sb.Append(dc.ColumnName).Append(",")

Next

Dts.Events.FireInformation(1, "Columns", sb.ToString(), "", 0, True)

For rowNum As Integer = 0 To tbl.Rows.Count - 1

sb.Remove(0, sb.Length)

dr = tbl.Rows(rowNum)

For itemOffset As Integer = 0 To dr.ItemArray.Length - 1

sb.Append(dr.ItemArray(itemOffset)).Append(",")

Next

Dts.Events.FireInformation(1, "Data", sb.ToString(), "", 0, True)

Next

End Sub

sql

How to redirect a DTS package and connections

Hi,

I have a DTS package (SQL2k, sp3) that I can to execute agains a new
database in the same server. I don't see how to easily redirect. If you
change the connection properties and clear the transformations you have to
redo them one at a time (too long).

If you don't data still goes to the old database.

What to do?

Sd"SD" <npspam@.nowhere.net> wrote in message
news:483161980e01c04754af03e36c264ae2@.news.teranew s.com...
> Hi,
> I have a DTS package (SQL2k, sp3) that I can to execute agains a new
> database in the same server. I don't see how to easily redirect. If you
> change the connection properties and clear the transformations you have to
> redo them one at a time (too long).
> If you don't data still goes to the old database.
> What to do?
> Sd

Check out the dynamic properties task - it allows you to set any properties
dynamically as the package is running. In this case, you could have a global
variable to store the database name, and use the task to assign it to the
connection. At runtime, you can pass the value for the global variable to
dtsrun.exe, or get it from an .INI file or SELECT query.

Simon

How to read the DBCC page through cleint application

Hi,
I am writing a application that require to read some DBCC Pages.
But when I execute dbcc page command through ADO, I get a page in terms of e
rror collection.
In that error collection each row of output is just a string.
I need to interpret that string value.
Is there some other way through which I can read DBCC Page.
I am interseted only in the memory dump portion of the page.
Is there some better way to read the data base page or I need to use this er
ror collection method only.
Any help is appreciated.
Thanks
PushkarHi
You may want to use DBCC BYTES but you will have to calculate the starting
address. Ken Henderson's "The Guru's Guide to Transact-SQL" ISBN
0-201-61576-2 gives some information on all these undocumented calls and
using them in production code carries the usual health warnings.
John
"Pushkar" wrote:

> Hi,
> I am writing a application that require to read some DBCC Pages.
> But when I execute dbcc page command through ADO, I get a page in terms of
error collection.
> In that error collection each row of output is just a string.
> I need to interpret that string value.
> Is there some other way through which I can read DBCC Page.
> I am interseted only in the memory dump portion of the page.
> Is there some better way to read the data base page or I need to use this
error collection method only.
> Any help is appreciated.
> Thanks
> Pushkar|||I believe that you can add WITH TABLERESULTS to get back a resultset instead
a string (messages).
Note: It is not supported or documented (but nor is DBCC PAGE, so you are on
your own anyhow :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:%23x6XXvsvFHA.1256
@.TK2MSFTNGP09.phx.gbl...
Hi,
I am writing a application that require to read some DBCC Pages.
But when I execute dbcc page command through ADO, I get a page in terms of e
rror collection.
In that error collection each row of output is just a string.
I need to interpret that string value.
Is there some other way through which I can read DBCC Page.
I am interseted only in the memory dump portion of the page.
Is there some better way to read the data base page or I need to use this er
ror collection method
only.
Any help is appreciated.
Thanks
Pushkar|||Thanks, I think this will solve my problem.
Pushkar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OG8y%23h4vFHA.464@.TK2MSFTNGP15.phx.gbl...
>I believe that you can add WITH TABLERESULTS to get back a resultset
>instead a string (messages). Note: It is not supported or documented (but
>nor is DBCC PAGE, so you are on your own anyhow :-) ).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23x6XXvsvFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am writing a application that require to read some DBCC Pages.
> But when I execute dbcc page command through ADO, I get a page in terms of
> error collection.
> In that error collection each row of output is just a string.
> I need to interpret that string value.
> Is there some other way through which I can read DBCC Page.
> I am interseted only in the memory dump portion of the page.
> Is there some better way to read the data base page or I need to use this
> error collection method only.
> Any help is appreciated.
> Thanks
> Pushkar
>

How to read database compatibility level from c#

I'm trying to access the db compatibility level of a database from c# code.
If you execute sp_dbcmptlevel with no parameters, the current level is
returned to the output stream. My problem is how to get at this from c#
since it is not a scalar or tabular result. Any help gratefully received
JamesLook like it's returned via raiserror(). You need to catch it via
InfoMessage.
http://msdn.microsoft.com/library/d...ctionEvents.asp
-oj
"James E. Clarke" <james@.r> wrote in message
news:OacPMeLOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I'm trying to access the db compatibility level of a database from c#
> code. If you execute sp_dbcmptlevel with no parameters, the current level
> is returned to the output stream. My problem is how to get at this from c#
> since it is not a scalar or tabular result. Any help gratefully received
> James
>