Program Connection: February 2012

Saturday, February 25, 2012

Programmatic inspection of a dump?

I need to set up a job to allow users to restore their databases, on SQL Server 2000 SP3. The idea is that a user inserts a record into a table, identifying the dump they want to load. (They can only restore their own account.) A job picks up this record, restores the database, and notifies the user as appropriate.

My part of this is writing the procedure that the job executes, including the dump restore. Part of that is getting each dump's file groups (data, index, and log) into the proper locations for this server and this user.

Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?
Google is your friend. http://www.karaszi.com/sqlserver/util_restore_all_in_file.asp|||Man .. its better if you do not call the backup a dump (You know what I mean !!!)... coz its what will save your A$$ when the database goes down ...

Programmatic insert into SQL database

I have a page with over 20 Textbox and DDL controls and an upload in various Divs and Panels (Ajax enabled) that are used for gathering user data. Some of the fields are mandatory and some optional.

In the Code behind (VB- I am a complete novice) On the submit button click event, I iterate through the controls in the page and build an array with information from the controls that have data in them, (filtering out the non-filled textboxes, and DDLs).

All this works well, and I get an array called 'myInfo' with the columns with the control ID, and control values 'rvalue' (as string), with the number of rows equal to the filled textboxes and DDLs.

I then step through the array and build a string with 'name=values' of all the rows in the myinfo array and email this as a message:

ThisMessage = ""
NoOfControls = myInfo.GetLength(0)
For i = 0 To NoOfControls - 1
ThisMessage = ThisMessage & myInfo(i).ID.ToString & "=" & myInfo(i).rvalue.ToString & "; "
Next

SendMail(email address, ThisMessage)

I also want to add this information to a database, appended by the IP address and datetime.now.

Dim evdoDataSource As New SqlDataSource()
evdoDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnectionStringCK").ToString
evdoDataSource.InsertCommandType = SqlDataSourceCommandType.Text

Dim InsertMessage As String = """INSERT INTO evdoData ("
NoOfControls = myInfo.GetLength(0)

Dim k As Integer
For k = 0 To NoOfControls - 1
InsertMessage = InsertMessage & myInfo(k).ID.ToString & ", "
Next

InsertMessage = InsertMessage & "IPNo, DateEntered) VALUES ("

For k = 0 To NoOfControls - 1
InsertMessage = InsertMessage & "@." & myInfo(k).ID.ToString & ", "
Next
InsertMessage = InsertMessage & ", @.IPNo, @.DateEntered" & ")"""

evdoDataSource.InsertCommand = InsertMessage

I then similarly iterate through and do the insertparameters.

Now here is the rub- (My all too often DUH moment!)

When I look at the insertmessage in debug (and to be sure- I also show the insert string on a temporary debug label on the page), The insertmessage looks fine:

"INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@.FirstName, @.Age, @.Email, @.Phone, @.Country, @.City, , @.IPNo, @.DateEntered)"

However when the above code(evdoDataSource.InsertCommand = InsertMessage) is run, I get an error - the message with the error is:

The identifier that starts with 'INSERT INTO ModelData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@.FirsteName, @.Age, @.Email, @.Phone,' is too long. Maximum length is 128

EH? When I actually copy the InsertMessage from the debug window and paste it manually after the command "evdoDataSource.InsertCommand = " It works, and I get the data inserted into the table..

-It would seem that I am probably missing something obvious in my complete "noviceness" . HELP! (oh and thanks a bunch in anticipation)

_____________________________________________________________________________________________________________________

Its Easy --------When you know How. Meanwhile Aaaaaaaaah .

Remove the double-quotes around the insert statement.

Dim InsertMessageAs String ="INSERT INTO evdoData (" NoOfControls = myInfo.GetLength(0)Dim kAs Integer For k = 0To NoOfControls - 1 InsertMessage = InsertMessage & myInfo(k).ID.ToString &", "Next InsertMessage = InsertMessage &"IPNo, DateEntered) VALUES ("For k = 0To NoOfControls - 1 InsertMessage = InsertMessage &"@." & myInfo(k).ID.ToString &", "Next InsertMessage = InsertMessage &", @.IPNo, @.DateEntered)"

|||

In the post the you have an extra comma in your insert statement after @.City and before @.IPNo

INSERT INTO EvdData (FirstName, Age, Email, Phone,Country, City, IPNo, DateEntered) VALUES (@.FirstName, @.Age, @.Email,@.Phone, @.Country, @.City, , @.IPNo, @.DateEntered)

You may want to change

InsertMessage = InsertMessage & ", @.IPNo, @.DateEntered" & ")"""

InsertMessage = InsertMessage & "@.IPNo, @.DateEntered" & ")"""

|||

First, don't use " (double quotes) in your query, instead use ' (single quote) if and when needed. If at all you have to use double quotes then prefer setting QUOTED_IDENTIFIER OFF for the connection object. This sounds bit tricky, right?

kj@.zqtech.com:
"INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@.FirstName, @.Age, @.Email, @.Phone, @.Country, @.City, , @.IPNo, @.DateEntered)"

Second, did you notice that there are 2 , (comma) between @.city and @.IPNO or this is just by mistake when you were pasting your code in the post ?

Hope this will help.

|||

Thanks Guys. It was the combination of both things. No quotations required and also the extra comma. (Silly look on my face at this point).

Moral of the story:

1. Coding problems are like magician's tricks: one problem is a slight of hand to take your attention away from another. 2. Beware of taking error messages too literally. -

Programmatic access to the code SQLCMD uses to parse sql into batches

Hi,

I am thinking of writing a C# Windows app to iterate a set of sql scripts and submit them to SQL Server. Many scripts have "go" as a batch delimiter. I could probably figure out a regular expression to parse for that delimiter, but it seems tricky (need to exclude 'go', -- go, /* go */, etc). Obviously SQLCMD and Management Studio have a way to do this. I was wondering if that's exposed in an API anywhere. I looked into the SMO object model, but I couldn't find it.

Thanks much,

Mike

Hi,

thats no mafic, just use the SET PARSEONLY statement before your statements to check.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

As far as I can tell "set parseonly on" just checks the syntax. I want to get back a collection of strings to submit, each one a proper batch. Eg:

select 'Inside quotes, go or " go " or '' go '' is not a delimiter ' as go -- not go here

select 'Second go '

select 'Another batch here /* go */ go '

In query analyzer or management studio this appropriately returns 3 result sets, presumably executed as 2 batches. I would like to identify the first 2 lines as one batch, and the second select as another batch.

Mike

Programmatic Access to SQL database

New at this and would like to get the code in VB or . Net which would
allow connection, reading and writing to a SQL database previously
created.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Try taking a look here, there are many examples to choose from...
http://gotdotnet.com/Community/User...?query=database
"John Brown" wrote:

>
> New at this and would like to get the code in VB or . Net which would
> allow connection, reading and writing to a SQL database previously
> created.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Programmatic access to Enabled Protocols in SQL2005?

My C++ program is able to read the "Enabled Protocols" for SQL2005 by
reading the registry at:
HKLM\Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer\SuperSocketLib\<protocol alias> and then reading the
"Enabled" value to see if it is enabled or not.
This works fine for named instances, but I can't find the registry values
that tell me which Enabled Protocols are defined for the default instance in
SQL2005.
Can anyone assist?
Many thanks!Hi,
Thanks for your post!
From your description, I understand that:
Your application need to search for the SQL Server 2005 enabled protocols
information in registry.
You managed to get the "Enable" value if the SQL Server was installed as a
named instance;
however the "Enable" value was not found at that location if the SQL Server
was installed as a default instance.
If I have misunderstood, please let me know.
From my research, I found the "Enable" value was under the directory
"HKLM\Software\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\<protocol alias>",
which was different from yours. My SQL Server 2005 was installed as a named
instance as well.
The directory as you mentioned is also existed in my registry, however I
couldn't find the "Enable" value under that.
In fact, if your SQL Server 2005 was installed as a default instance, you
may found the default value "MSSQLServer" under
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\
Both default instance and named instance have the related registry key
MSSQL.x. The registry directory "MSSQL.1" references the first instance of
your SQL Server 2005, so I recommend you check that registry directory
firstly.
Also, you may try to search for the key value "SuperSocketNetLib" under the
registry directory "HKLM\Software\Microsoft\".
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

Programmatic "Script Table as Create to..." in T-SQL?

Is there a way to write a T-SQL query against the database object model to create the same output as you would get when you right-click the object in Management Studio and select 'Script table as Create to.."?

Or better yet, is there a way to retrieve the DDL that was used to create a table using a T-SQL query?

Thanks in advance,
-Preston M. Price

Perhaps something like this:

SELECT text

FROM sys.syscomments

WHERE id = object_id( 'MyStoredProcedure' )

|||

There is no way to query DDL from the database (beside the Routine_definition your get from INFORMATION_SCHEMA.Routines table or the sysobejcts as Arnie pointed out), you will have to either write your own way to do this or you can use API of SMO to do the stuff with the scripter object.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Programmatic "Script Table as Create to..." in T-SQL?

Or better yet, is there a way to retrieve the DDL that was used to create a table using a T-SQL query?

Thanks in advance,
-Preston M. Price

Perhaps something like this:

SELECT text

FROM sys.syscomments

WHERE id = object_id( 'MyStoredProcedure' )

|||

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Programmaing with SSIS Object model

Hi there,

Can anyone point me to some sample source codes or any articles that describes how I can programmatically create a package which will import data from a flatfile (csv) to Sql server database.

I know there is some example that describes exporting data from sql server to flatfiles. Anyway I have failed to accomplish my goal by following those examples.

If anyone have a code snippet to do that please help me with that.

Thanks in advance

Moim Hossain

If you want, post the code you have already to build the SSIS package (or a link to it). This response is not the code snippet you're looking for, but it sounds like the package builder code you already have (from the other post), is not far from working.|||

jaegd ,

Sorry I did not understand. Should I post my code to you?

I have already posted my code here into another posting. you can take a look on my code here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073656&SiteID=1

Can you tell me where I am going wrong?

Thanks

Moim

Programmability of parameter bar(layout/items)?

Hi all,

does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?

How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?

Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?

Thanks a lot for any help

Best Regards
Marc

Unfortunately, you can't do either of these things. (#1, #2, or the "hand-over" of parameter selection via Report Designer).

If the parameter selection needs to be as dynamic as you describe, the best solution is to actually build your own custom UI in which you display the appropriate parameter UI in the appropriate places to the appropriate users...Then take the values that get selected and pass them to the report via URL Access, or plug them into the ReportViewer with code, andrender the report in the viewer. www.gotreportviewer.com has samples of the latter.

Hope this helps!

|||

Hey Russell,

that's not really good news...but thanks a lot for your help anyway..i'm going to try the solution with passing parameters by code into the ReportViewer control.

Cheers
Marc

|||

Hi Marc,

I have a similar problem. Could you please explain how you were able to solve this problem.

Thanks a lot for your help.

Regards,

Simranjeev

|||You need to query the web service to find out which parameters a report has, their data types and values etc. then programatically populate dropdowns etc. and arrange them how you like in your custom UI. You can use either a web or a windows UI for this.|||

Thanks ... Can i find some article / sample code somewhere on how to build custom UI and use it as the Parameter Area in Reporting Services

Programmability of parameter bar(layout/items)?

Hi all,

does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?

How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?

Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?

Thanks a lot for any help

Best Regards
Marc

Unfortunately, you can't do either of these things. (#1, #2, or the "hand-over" of parameter selection via Report Designer).

Hope this helps!

|||

Hey Russell,

that's not really good news...but thanks a lot for your help anyway..i'm going to try the solution with passing parameters by code into the ReportViewer control.

Cheers
Marc

|||

Hi Marc,

I have a similar problem. Could you please explain how you were able to solve this problem.

Thanks a lot for your help.

Regards,

Simranjeev

Thanks ... Can i find some article / sample code somewhere on how to build custom UI and use it as the Parameter Area in Reporting Services

Programmability of parameter bar(layout/items)?

Hi all,
does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?
How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?
Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?
Thanks a lot for any help
Best Regards
MarcI don't know if this is the right way but I turned off the parameter bar and
added the following code
Dim parm As New
Microsoft.Reporting.WebForms.ReportParameter("week",
DrpWeeks.SelectedValue.ToString)
Dim p() As Microsoft.Reporting.WebForms.ReportParameter = {parm}
ReportViewer1.ServerReport.SetParameters(p)
DrpWeeks is a drop down box populated just as I would have if I had
populated it in the report designer.
User specific is by grabbing user!userid
"MarcBey" wrote:
> Hi all,
> does anybody of you know how to code and customize the parameter bar of the
> SSRS reports? I know about putting code behind expressions but how is it with
> parameters?
> How is it possible to...
> 1. define the layout of the parameter bar and the positions of the parameter
> items?
> 2. make the parameter items user-specific?
> Is it possible to define a report with the report designer and hand over
> data (parameters) by code-behind or a referenced assembly?
> Thanks a lot for any help
> Best Regards
> Marc
>
>

programm using ado(sql server 2005) do not run in Windows Vista

Hi!

My C++ programm that use ADO to access SQL Server 2005 Express do not work in Windows Vista. Programm code works in Windows XP, but fails in Vista.

_bstr_t strCnn = L"Provider = 'SQLNCLI'; Data Source='\SERVERSQL';Uid=sa;Pwd=devel; ";
ADODB::_CommandPtr _command;
TESTHR(_command.CreateInstance(__uuidof(ADODB::Command)));
_command->PutActiveConnection(strCnn);

Error:

Unhandled exception in test.exe (secur32.dll) 0xC0000005. Access Violation.

Help Me please.

cmd-connection

C:\>OSQL.exe /S USER123-VISTA\\SERVERSQL /U sa /P devel /i c:\\test\1.sql
error

[SQL Native Client]Client unable to establish connection due to prelogin
failure

I'm not sure why you double the backslashes in the command line, and don't double in the connection string.|||

ok , thanks

in command line run

|||

Help

not work in Vista!

My C++ programm that use ADO to access SQL Server 2005 Express do not work in Windows Vista. Programm code works in Windows XP, but fails in Vista.

Error:

Unhandled exception in test.exe (secur32.dll) 0xC0000005. Access Violation.

Help Me please.

The similar problem

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1517814&SiteID=1

I has not understood the decision

programm using ado(sql server 2005) do not run in Windows Vista

Hi!

My C++ programm that use ADO to access SQL Server 2005 Express do not work in Windows Vista. Programm code works in Windows XP, but fails in Vista.

Error:

Unhandled exception in test.exe (secur32.dll) 0xC0000005. Access Violation.

Help Me please.

cmd-connection

C:\>OSQL.exe /S USER123-VISTA\\SERVERSQL /U sa /P devel /i c:\\test\1.sql
error

[SQL Native Client]Client unable to establish connection due to prelogin
failure

I'm not sure why you double the backslashes in the command line, and don't double in the connection string.|||

ok , thanks

in command line run

|||

Help

not work in Vista!

My C++ programm that use ADO to access SQL Server 2005 Express do not work in Windows Vista. Programm code works in Windows XP, but fails in Vista.

Error:

Unhandled exception in test.exe (secur32.dll) 0xC0000005. Access Violation.

Help Me please.

The similar problem

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1517814&SiteID=1

I has not understood the decision

Programing: RENDER METHOD and PAGE COUNTS for HTML Rendering

We understand how to use the Render method of the RS web service. And can
successfully render reports in image and PDF formats. I am having a problem
with HTML rendering. we understand how to (and are able to) render HTML
reports as a single "section" (i.e. page) or to generate all sections in a
single call. I am not sure how to ask the RS web service to tell me the
total number of "sections" (pages) in an HTML rendered report. Can anyone
help with this? we know it must be straight forward but have not found how.
thanks.
dlrThere is no direct way of determining number of pages
returned by the render method.
But when you call the render method you can see that the
method returns streamIds.
Your first page comes as as the byte array and the
remaining pages have streamIds associated.
So the number of pages is basically : StreamIds.Length+1
>--Original Message--
>We understand how to use the Render method of the RS web
service. And can
>successfully render reports in image and PDF formats. I
am having a problem
>with HTML rendering. we understand how to (and are able
to) render HTML
>reports as a single "section" (i.e. page) or to generate
all sections in a
>single call. I am not sure how to ask the RS web service
to tell me the
>total number of "sections" (pages) in an HTML rendered
report. Can anyone
>help with this? we know it must be straight forward but
have not found how.
>thanks.
>dlr
>
>.
>|||beg to differ. When I render using and IMAGE format I get StreamIDs (and
thus a page count as StreamIDs + 1) but when I render using an HTML format
StreamIDs are always null!
(I have no image links in the report).
what saz's you now?
thanks.
dlr
"Ravi" <ravikantkv@.rediffmail.com> wrote in message
news:84c401c495d0$4572bca0$a301280a@.phx.gbl...
> There is no direct way of determining number of pages
> returned by the render method.
> But when you call the render method you can see that the
> method returns streamIds.
> Your first page comes as as the byte array and the
> remaining pages have streamIds associated.
> So the number of pages is basically : StreamIds.Length+1
> >--Original Message--
> >We understand how to use the Render method of the RS web
> service. And can
> >successfully render reports in image and PDF formats. I
> am having a problem
> >with HTML rendering. we understand how to (and are able
> to) render HTML
> >reports as a single "section" (i.e. page) or to generate
> all sections in a
> >single call. I am not sure how to ask the RS web service
> to tell me the
> >total number of "sections" (pages) in an HTML rendered
> report. Can anyone
> >help with this? we know it must be straight forward but
> have not found how.
> >
> >thanks.
> >
> >dlr
> >
> >
> >.
> >

programing problem

Hi i have a view structured something like this :
id catname productname
1 aa prod1
1 bb prod2
1 aa prod1
1 bb prod2
2 cc prod3
2 dd prod4
2 cc prod3
2 dd prod4
i need to insert to another table so the result will look like this :
id data
1 aa bb prod1 prod2
2 cc dd prod3 prod4
what is the best way to approach this?
currently i am using a complicated code with cursurs that does ont realy wo
rk
and i feel is not in the right direction i hope there is a knowen solution
for this problem
thanks
shay cohen> what is the best way to approach this?
Doing it in your client app / reporting tool or programming language.
AMB
"Shay" wrote:

> Hi i have a view structured something like this :
> id catname productname
> 1 aa prod1
> 1 bb prod2
> 1 aa prod1
> 1 bb prod2
> 2 cc prod3
> 2 dd prod4
> 2 cc prod3
> 2 dd prod4
> i need to insert to another table so the result will look like this :
> id data
> 1 aa bb prod1 prod2
> 2 cc dd prod3 prod4
>
> what is the best way to approach this?
> currently i am using a complicated code with cursurs that does ont realy
work
> and i feel is not in the right direction i hope there is a knowen solution
> for this problem
> thanks
> shay cohen
>|||What you want is a cross-tab or pivot table see the following url:
http://msdn.microsoft.com/library/d...r />
_04j7.asp|||"patrick_brisbine" <patrickbrisbine@.hotmail.com> wrote in message news:<1107464600.631695.3
6160@.c13g2000cwb.googlegroups.com>...
> What you want is a cross-tab or pivot table see the following url:
> http://msdn.microsoft.com/library/d...4j7.
asp
thanks,
this looks like a good start, now what if need to do string
manipulation
on each value so aa is XXaa and prod1 is FFprod1 etc,all values need
to go in to one target colume and the number of columns in the view
can change ?
thanks alot for you time and attention
shay

Programatticaly setting up history snapshot schedule

I am trying to do this with a web service but cant seem to figure out how,
as there is no direct method or function for it, it seems. But I have a
report I created programatically through the web services. Now I want to set
up a history snapshot schedule for it, I already have schedules set up on
the server as shared schedules, but now I need to tie them to the report to
have them execute according to the schedule. Any ideas on how to do this?
Google wasnt much help to me in finding info. thanks!Is this particular report is going to be fixed for running from a snapshot ?
In that case go to Report manager and select that particular
report->Properties ->execution and select "Render this report from a report
execution snapshot"
Amarnath, MCTS
"Smokey Grindel" wrote:
> I am trying to do this with a web service but cant seem to figure out how,
> as there is no direct method or function for it, it seems. But I have a
> report I created programatically through the web services. Now I want to set
> up a history snapshot schedule for it, I already have schedules set up on
> the server as shared schedules, but now I need to tie them to the report to
> have them execute according to the schedule. Any ideas on how to do this?
> Google wasnt much help to me in finding info. thanks!
>
>|||Unfortinuatly that doesnt solve my problem of how to do it programatically,
which is what I was trying to find out, but I eventually found the web
service method SetReportHistoryOptions
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:87D6EAAC-C394-4A82-9140-F3FE9B30F8F3@.microsoft.com...
> Is this particular report is going to be fixed for running from a snapshot
> ?
> In that case go to Report manager and select that particular
> report->Properties ->execution and select "Render this report from a
> report
> execution snapshot"
> Amarnath, MCTS
> "Smokey Grindel" wrote:
>> I am trying to do this with a web service but cant seem to figure out
>> how,
>> as there is no direct method or function for it, it seems. But I have a
>> report I created programatically through the web services. Now I want to
>> set
>> up a history snapshot schedule for it, I already have schedules set up
>> on
>> the server as shared schedules, but now I need to tie them to the report
>> to
>> have them execute according to the schedule. Any ideas on how to do this?
>> Google wasnt much help to me in finding info. thanks!
>>

Programatticaly finding constraint "check existing data on creatio

A relationship between 2 tables has a property "check existing data on
creation" , "Enforce relationship for replication" , "Enforce relationship
for INSERTs and UPDATEs"
How to determine these programmaticaly from sysconstraints, sysrelationship
or from any other table?.
It appears that this information is hiding in the "status" field in
sysconstraints.
Any help ?
Check properties (CnstIsNotTrusted, CnstIsNotRepl, CnstIsDisabled) using
function objectproperty, they are the contrary.
select
object_name([id]),
object_name(constid),
~ cast(objectproperty(constid, 'CnstIsNotTrusted') as bit) as 'Check
existing data on creation',
~ cast(objectproperty(constid, 'CnstIsNotRepl') as bit) as 'Enforce
relationship for replication',
~ cast(objectproperty(constid, 'CnstIsDisabled') as bit) as 'Enforce
relationship for INSERTs and UPDATEs'
from
sysconstraints
where
object_name([id]) = 'order details'
and object_name(constid) = 'FK_Order_Details_Orders'
AMB
"swami" wrote:

> A relationship between 2 tables has a property "check existing data on
> creation" , "Enforce relationship for replication" , "Enforce relationship
> for INSERTs and UPDATEs"
> How to determine these programmaticaly from sysconstraints, sysrelationship
> or from any other table?.
> It appears that this information is hiding in the "status" field in
> sysconstraints.
> Any help ?

Programatticaly finding constraint "check existing data on creatio

A relationship between 2 tables has a property "check existing data on
creation" , "Enforce relationship for replication" , "Enforce relationship
for INSERTs and UPDATEs"
How to determine these programmaticaly from sysconstraints, sysrelationship
or from any other table?.
It appears that this information is hiding in the "status" field in
sysconstraints.
Any help ?Check properties (CnstIsNotTrusted, CnstIsNotRepl, CnstIsDisabled) using
function objectproperty, they are the contrary.
select
object_name([id]),
object_name(constid),
~ cast(objectproperty(constid, 'CnstIsNotTrusted') as bit) as 'Check
existing data on creation',
~ cast(objectproperty(constid, 'CnstIsNotRepl') as bit) as 'Enforce
relationship for replication',
~ cast(objectproperty(constid, 'CnstIsDisabled') as bit) as 'Enforce
relationship for INSERTs and UPDATEs'
from
sysconstraints
where
object_name([id]) = 'order details'
and object_name(constid) = 'FK_Order_Details_Orders'
AMB
"swami" wrote:

> A relationship between 2 tables has a property "check existing data on
> creation" , "Enforce relationship for replication" , "Enforce relationship
> for INSERTs and UPDATEs"
> How to determine these programmaticaly from sysconstraints, sysrelationshi
p
> or from any other table?.
> It appears that this information is hiding in the "status" field in
> sysconstraints.
> Any help ?

Programatticaly finding constraint "check existing data on creatio

A relationship between 2 tables has a property "check existing data on
creation" , "Enforce relationship for replication" , "Enforce relationship
for INSERTs and UPDATEs"
How to determine these programmaticaly from sysconstraints, sysrelationship
or from any other table?.
It appears that this information is hiding in the "status" field in
sysconstraints.
Any help ?Check properties (CnstIsNotTrusted, CnstIsNotRepl, CnstIsDisabled) using
function objectproperty, they are the contrary.
select
object_name([id]),
object_name(constid),
~ cast(objectproperty(constid, 'CnstIsNotTrusted') as bit) as 'Check
existing data on creation',
~ cast(objectproperty(constid, 'CnstIsNotRepl') as bit) as 'Enforce
relationship for replication',
~ cast(objectproperty(constid, 'CnstIsDisabled') as bit) as 'Enforce
relationship for INSERTs and UPDATEs'
from
sysconstraints
where
object_name([id]) = 'order details'
and object_name(constid) = 'FK_Order_Details_Orders'
AMB
"swami" wrote:
> A relationship between 2 tables has a property "check existing data on
> creation" , "Enforce relationship for replication" , "Enforce relationship
> for INSERTs and UPDATEs"
> How to determine these programmaticaly from sysconstraints, sysrelationship
> or from any other table?.
> It appears that this information is hiding in the "status" field in
> sysconstraints.
> Any help ?

Programaticly setting the value of a Select Query Property.

I have a query in which I'd like to use the username of the user currently logged in. The expression im using to retrieve the username is: Membership.GetUser().UserName.
Currently I have the following:
<asp:SqlDataSource ID="ProjectSource" runat="server" ConnectionString="<%$ ConnectionStrings:Code %>"
ProviderName="<%$ ConnectionStrings:Code.ProviderName %>" SelectCommand="Select Name, Namespace from Project where User = $Username">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="$Username" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ProjectSource" />

I'd somehow like to set up $Username to be equivlent to Membership.GetUser().UserName.I'd be interested to hear of a better way, however, currently, I cheat. I stuff things like that when they authenticate to session variables (I only have 3 in my current project). Then I pull them in the sqldatasources from there.|||Yeah, my solution doesnt look to be much better. Currently Im Setting the default value every time I need to execute a query.

programaticaly create subscriptions

i have one report which is common to many clients say [a..n] and many more could be added. the report for each client needs to be saved to a unc path. how do i programatically create a subscription for each client to run the same report and save to its unique unc path i.e one path for each client.

You can create subscriptions programatically using the Report Server SOAP API. You can find more information here: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

Concerning delivery, you can create a data-driven subscription to deliver reports customized to each user. These can be sent via email, or you could use a fileshare delivery and expose them via a virtual directory.

Thanks, Jon

programaticaly create subscriptions

Have you looked into Data Driven Subscriptions?|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_subscribing_v1_11sy.asp

Programatically viewing the data in a particular column

This is my first attempt to create a Vb.net class as well as my first attempt to programmatically view SQL retrieved data as opposed to using the built-in data controls...so be gentle.

My question is looking at the code below which I have already put together...how do I actually see what the data is in a particular column... One of my columns for example is the "Title" which contains the Page Title for Search Engine Optomization. I need to be able to read this value so I can assign it to the Page.

'First Do a Database Connection

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsString = _

ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString

'Create a SqlConnection instance

Using myConnectionAsNew SqlConnection(connString)

'Specify the SQL query

Const sqlAsString ="SELECT * FROM SEO where ParentType='Department' AND ParentLevel='0' "

'Create a SqlCommand instance

Dim myCommandAsNew SqlCommand(sql, myConnection)

'Get back a DataSet

Dim myDataSetAsNew DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet,"SEO")

'Create a DataView

Dim myDataViewAsNew DataView

myDataView.Table = myDataSet.Tables("SEO")

'Ok...this is where I'm lost. Now how do I look up the value for the column "Title" in this Dataset

'Close the connection

myConnection.Close()

'End database connections

EndUsing

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

To access other columns, simply replace the column name ("Title") with a respective column name

Alternatively you can use the following line to access the value inside the1st row of the coloumn
Dim strAsString =CStr(myDataSet.Tables("SEO").Rows(0).Item("Title"))

To access other rows, simply replace the row number (0) with a respective row number

Hope this helps you.

|||

I'm relatively new too.

Instead of writing the entire class as 'codeaholic' did (looks daunting), can I use the drag'n'drop methodology to place a SqlDataSource1 control onto an ASPX page and configure the datasource with an appropriate Select statement, (easy part) and then use less VB code to find the value of a column for a given record?

In other words, if I want to extract a single column of a record do I have to 'throw away' all those luxury features of design time Tongue Tied

|||

i'm personally not familier with using data controls in my pages as im currently using either 2-tier or 3-tier application development, but it's possible to do what you want.
just create the data controls one-by-one till you get a datagrid or dataview object containing the values you wanted, then use code-behind to access the datagrid or dataview objects for your values.
or you could even directly bind your values to controls using the datasource property and other respective properties.
however it may get tedious when you need a large number of data objects to access different values in different tables.

i would also advise strongly against doing so as it would affect the performance of your pages due to unneeded controls needed to be complied and loaded.
and there's always the security issue.
there's always better ways to do it.
hope this helps you.

|||

AnjinG:
You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

Hmm, just getting around to trying this and it doesn't work. Using this code above. myColumnValue.toString is simply the word "Title". Its not actually extracting the value of the column Title but simply setting this to the name of the column...

Tongue Tied

|||

Hi,

you can try doing this to directly access the particular column value
you willl need to traverse the rows in the table

Dim i as integer

For i = 0To myDataSet.Tables("SEO").Rows.Count - 1

Dim vAsString = myDataSet.Tables("SEO").Rows(i).Item(columnIndex)

|||Yeh, I just used the row syntax you indicated as the "second" possibility. Since there is only one row, we don't have to loop it. Thanks.

Programatically View / Stored Procedure with N fields

We have a table that has 10 fields of which say 5 are either non textual. We
want to have a resultant view with 7 fields, namely 1 numeric field and the
5 non-numeric fields (textual) and one additional field for the non textual
field name. In the example below the last 5 field names are Prod1, Prod2,
Prod3, Prod4, Prod5.
E.g.
Original
SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
Result
SA, Sales, Man1, Actual, Prd1, Jan, 100
SA, Sales, Man1, Actual, Prd2, Jan, 200
SA, Sales, Man1, Actual, Prd3, Jan, 300
SA, Sales, Man1, Actual, Prd4, Jan, 400
SA, Sales, Man1, Actual, Prd5, Jan, 500
SA, Sales, Man1, Actual, Prd1, Jan, 1
SA, Sales, Man2, Actual, Prd2, Jan, 2
SA, Sales, Man3, Actual, Prd3, Jan, 3
SA, Sales, Man4, Actual, Prd4, Jan, 4
SA, Sales, Man5, Actual, Prd5, Jan, 5
In essence the size of the table will end up as
n records * 5 fields.
Thanks in advance.Avril
I'm not sure understood you.
Have you tride using UNION ALL to get what you wanted?
"Avril" <Avril@.ppp.com> wrote in message
news:effVz3oZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> We have a table that has 10 fields of which say 5 are either non textual.
> We want to have a resultant view with 7 fields, namely 1 numeric field and
> the 5 non-numeric fields (textual) and one additional field for the non
> textual field name. In the example below the last 5 field names are Prod1,
> Prod2, Prod3, Prod4, Prod5.
>
> E.g.
> Original
> SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
> SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
> Result
> SA, Sales, Man1, Actual, Prd1, Jan, 100
> SA, Sales, Man1, Actual, Prd2, Jan, 200
> SA, Sales, Man1, Actual, Prd3, Jan, 300
> SA, Sales, Man1, Actual, Prd4, Jan, 400
> SA, Sales, Man1, Actual, Prd5, Jan, 500
> SA, Sales, Man1, Actual, Prd1, Jan, 1
> SA, Sales, Man2, Actual, Prd2, Jan, 2
> SA, Sales, Man3, Actual, Prd3, Jan, 3
> SA, Sales, Man4, Actual, Prd4, Jan, 4
> SA, Sales, Man5, Actual, Prd5, Jan, 5
> In essence the size of the table will end up as
> n records * 5 fields.
> Thanks in advance.
>|||Avril,
In SQL Server 2005, you can use the UNPIVOT operator for this.
In SQL Server 2000, you can use a CROSS JOIN like this:
select
column1, column2, column3, column4,
'Prd' + Nch as Prd,
column5,
case Nch
when 1 then Prod1 when 2 then Prod2 when 3 then Prod3
when 4 then Prod4 when 5 then Prod5 end as LastColumn
from yourTable
cross join (
select '1' as Nch union all select '2' union all select '3'
union all select '4' union all select '5'
) as F
There may be some typos, since you didn't give the create table
statements and sample data as INSERTs to test with, and you
will have to fix column names for the columns whose names
you didn't provide.
Steve Kass
Drew University
Avril wrote:

>We have a table that has 10 fields of which say 5 are either non textual. W
e
>want to have a resultant view with 7 fields, namely 1 numeric field and the
>5 non-numeric fields (textual) and one additional field for the non textual
>field name. In the example below the last 5 field names are Prod1, Prod2,
>Prod3, Prod4, Prod5.
>
>E.g.
>Original
>SA, Sales, Man1, Actual, Jan, 100, 200, 300, 400, 500
>SA, Sales, Man2, Actual, Jan, 1, 2, 3, 4, 5
>Result
>SA, Sales, Man1, Actual, Prd1, Jan, 100
>SA, Sales, Man1, Actual, Prd2, Jan, 200
>SA, Sales, Man1, Actual, Prd3, Jan, 300
>SA, Sales, Man1, Actual, Prd4, Jan, 400
>SA, Sales, Man1, Actual, Prd5, Jan, 500
>SA, Sales, Man1, Actual, Prd1, Jan, 1
>SA, Sales, Man2, Actual, Prd2, Jan, 2
>SA, Sales, Man3, Actual, Prd3, Jan, 3
>SA, Sales, Man4, Actual, Prd4, Jan, 4
>SA, Sales, Man5, Actual, Prd5, Jan, 5
>In essence the size of the table will end up as
>n records * 5 fields.
>Thanks in advance.
>
>|||Steve
Thanks that worked for me. Now if I wanted to extend that to say
If Column3 = 'Man2' then multiply the amounts by 0.15 or
If Column3 = 'Man3' then multiply the amounts by 0.3
How would this affect the statement below.
Column1 Column2 Column3 Column4 Column5
Prd1 Prd2 Prd3 Prd4 Prd5
SA Sales Man1 Actual
Jan 100 200 300 400 500
SA Sales Man1 Actual
Jan 1 2 3 4 5
SA Sales Man2 Actual
Jan 100 200 300 400 500
SA Sales Man2 Actual
Jan 1 2 3 4 5
SA Sales Man3 Actual
Jan 100 200 300 400 500
SA Sales Man3 Actual
Jan 1 2 3 4 5
select
column1, column2, column3, column4, column5,'Prd' + Nch as Prd,
case Nch
when 1 then Prd1
when 2 then Prd2
when 3 then Prd3
when 4 then Prd4
when 5 then Prd5
end as Amount
from dbo.Sheet1$
cross join (
select '1' as Nch union all select '2' union all select '3'
union all select '4' union all select '5'
) as F
Thanks in advance.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAu7oipZGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Avril,
> In SQL Server 2005, you can use the UNPIVOT operator for this.
> In SQL Server 2000, you can use a CROSS JOIN like this:
> select
> column1, column2, column3, column4,
> 'Prd' + Nch as Prd,
> column5,
> case Nch
> when 1 then Prod1 when 2 then Prod2 when 3 then Prod3
> when 4 then Prod4 when 5 then Prod5 end as LastColumn
> from yourTable
> cross join (
> select '1' as Nch union all select '2' union all select '3'
> union all select '4' union all select '5'
> ) as F
> There may be some typos, since you didn't give the create table
> statements and sample data as INSERTs to test with, and you
> will have to fix column names for the columns whose names
> you didn't provide.
> Steve Kass
> Drew University
> Avril wrote:
>|||On Mon, 24 Apr 2006 06:55:21 +0100, Avril wrote:

>Steve
>Thanks that worked for me. Now if I wanted to extend that to say
>If Column3 = 'Man2' then multiply the amounts by 0.15 or
>If Column3 = 'Man3' then multiply the amounts by 0.3
>How would this affect the statement below.
(snip)
>select
>column1, column2, column3, column4, column5,'Prd' + Nch as Prd,
>case Nch
>when 1 then Prd1
>when 2 then Prd2
>when 3 then Prd3
>when 4 then Prd4
>when 5 then Prd5
>
END * CASE Column3
WHEN 'Man2' THEN 0.15
WHEN 'Man3' THEN 0.3
ELSE 1

>end as Amount
>from dbo.Sheet1$
>cross join (
>select '1' as Nch union all select '2' union all select '3'
>union all select '4' union all select '5'
> ) as F
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Thanks
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:vmgq42tjek1ur7ru4velvtv3hiqnv7gekq@.
4ax.com...
> On Mon, 24 Apr 2006 06:55:21 +0100, Avril wrote:
>
> (snip)
> END * CASE Column3
> WHEN 'Man2' THEN 0.15
> WHEN 'Man3' THEN 0.3
> ELSE 1
>
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP

Programatically Verifying SQL Syntax

OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"

and transforms it into something that looks like this:

"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"

My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?

Any help would be appreciated! Cheers.No, you have to sit down and start programming.

Having a parser generator available helps, if you find a SQL syntax as LALR.|||I was afraid you would say that.

Programatically stop a query in C#?

How can I do this so that if the user hits a cancel button, I can issue a SQ
L
command to stop the query execution for that users' session ?. Ive
configured the connection by default for a 90 sec time out, but they can
still navigate other places leaving the query to run the full 90 secs when i
t
doesnt need to if they leave. Thats just a wate of SQL cpu time.
--
JP
.NET Software DevelperI should mention that this is a web app and not a client app in C#. If the
cancel button reside on a page that is already rendered, how will I tell the
running process which process to cancel. I wouldnt think I could use
SqlCommand.Cancel b/c this is user initiated either dorectly or indirectly
from a web page.
--
JP
.NET Software Develper
"JP" wrote:

> How can I do this so that if the user hits a cancel button, I can issue a
SQL
> command to stop the query execution for that users' session ?. Ive
> configured the connection by default for a 90 sec time out, but they can
> still navigate other places leaving the query to run the full 90 secs when
it
> doesnt need to if they leave. Thats just a wate of SQL cpu time.
> --
> JP
> .NET Software Develper

Programatically Show Hide Report Members?

Does anyone know how to programatically show hide members (specificallytextboxes) in a report based on whether another field is Null orEmpty? I have tried to do something to the effect of
<code>
Sub ShowBox()
If Len(Fields!itm.Formula.Value)>0 Then
Fields!textbox30.Visibility = True
End If
End Sub
</code>
I put this in the code section under report properties but I get an error to the effect of
References to a non-shared member require an object reference
and
Expression does not produce a value
I know i am doing this totally wrong, can someone help me out?

thanks

in the textbox expression you can do something like :

=IIF(Len(Fields!itm.Formula.Value)>0, Fields!itm.Formula.Value, "")

|||

ndinakar wrote:
in the textbox expression you can do something like :

=IIF(Len(Fields!itm.Formula.Value)>0, Fields!itm.Formula.Value, "")

Just wondering if any one got any more ideas:
I tried this way:

=IIF((Fields!group1.Value)=Group2,Fields!Isuue.Value,

Fields!Name.Value = False) does not work.(By the way, Issue is a varchar field)

The exception is thrown saying

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException

=IIF((Fields!group1.Value)=Group2,Fields!Issue.Value,

Fields!Name.Value = "") doesn't give any error but doesn't work properly.

It shows the column, doesn't hide it.

Even if it hides the coulmn it doesn't delete the space for that column because

there are already some items in that column as shown below

Group1

Name Issue Marks

ss sds 90

gss pds 90

Group2

Name Marks

ss 90

gss 90

Marks Column should be printed in the place of Issue column under group2. Is that possible

Programatically setting the sub-report to load

My system uses a variety of invoice templates for each client, so 'Bank A' would have one invoice type sent to it, and 'Bank B' would have another etc. Each invoice template is a report, and they can vary quite substantially.

Each month the accounts team need to print out all the invoices for a month. I would like to create a report which has the invoices as a sub-report, so they can all be exported together and printed together.

However each sub-report could obviously be a different invoice-template. I notice that the sub-report selector field is one of the only fields that doesn't take an expression. Is there a way to programatically set which sub-report is used? The parameter sent to each report type is always the same (the primary key of the invoice to report on).

Any ideas? Or any other methods which would achieve the same goals?

Thanks,

Paul

One way is to add all subreport-types you could ever need to your report and hide all but the one you need dynamically via the hidden-expression..|||

Yes I could see that working, but it is pretty messy. As I would like the facility to just add extra invoice-template reports at any time. But with your method I'd have to also modify the main list report.

Thanks for your help anyway :)

programatically setting reportViewer datasource

I'm trying to set the DataSource of a ReportViewer programmatically. I'm doing the following but I'm getting the error

"A data source instance has not been supplied for the data source 'DataSet1_ORG'". DataSet1_Org is the xml datasource that the .rdlc is bound to.With ReportViewer1 Dim rdsAsNew ReportDataSource("rdsReport","sqlReport") 'sqlReport is a SQLDataSource on the webform

.LocalReport.DataSources.Clear()

.LocalReport.DataSources.Add(rds)

.LocalReport.ReportPath = Server.MapPath("MyReport.rdlc")

End With

I know I'm close...it's just a bit elusive.

Thanks, Alex

What you can try is go to the Report menu, then to the Data Sources option, remove what is there and add the appropriate data source again.

|||

Hello

you need to click on the reportviewer and select the choose data source and select the datasource that u want

Programatically running the SQL Server Agent

I have a scenerio where I am using a Push Merge Subscription set with "Run
On Demand". How can I programatically (Stored procedure) run the SQL Agent
Job that synchronizes the Subscriptions?
Thanks,
RSHHave a look at sp_start_Job.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O%23tZgcf9FHA.3660@.TK2MSFTNGP09.phx.gbl...
>I have a scenerio where I am using a Push Merge Subscription set with "Run
>On Demand". How can I programatically (Stored procedure) run the SQL Agent
>Job that synchronizes the Subscriptions?
> Thanks,
> RSH
>

Programatically re-ordering a "DisplayOrder" column

Anyone know of any resources or information on writing a query that would accept a new "DisplayOrder" for a particular row and re-order the column for all other rows?

Thanks in advance!If you are asking the question I think you are asking, I had posted an answer to thishere.

The idea is to bump up the sequence number for each record that has a sequence number greater than or equal to the new sequence number...BUT only if the sequence number already is on file.


IF EXISTS(SELECT NULL FROM myTable WHERE sequence = @.NewSequence)
 BEGIN
 UPDATE myTable SET sequence = sequence + 1 WHERE sequence >= @.NewSequence
 END
UPDATE myTable SET sequence = @.NewSequence WHERE id = @.idToChange

Terri|||Thank You! A few questions...

IF EXISTS(SELECT NULL FROM myTable WHERE sequence = @.NewSequence)

This tests for the existence of the "@.NewSequence" value?
And what exactly is achieved by "SELECT NULL", I haven't seen that before?

I think this will work well for me...

Also, the sequence number must exist as it is selected from a DropDownList populated from the existing sequence.

Again, Thank You!|||The "SELECT NULL" works the same as "SELECT *" or "SELECT columnname" -- the column(s) selected have no bearing on the success of the EXISTS test. I tend to use NULL because I believe that requires the least amount of resources to process. Others choose to use the primary key column. I am honestly not sure how much of a difference there really is.

All that code is doing is checking to see if the new sequence number is already on file. If it is, then it pushes down all of the sequence numbers from that point forward to create a "space".

I'm glad you could make use of the method :-)

Terri

Programatically query database and save report in an excel table

Hello All,

I am working on constructing a software layer around some features of the RDL language that would allow me to programatically generate reports.

I am reading the RDL specification language, and I do not understand three things:

1) How the DataSet element is populated by the query or more precisely how do the <Field> elements capture all the rows inside the table that is being queries?

To my understanding I wilkl have to define several fields that correspond to all columns of interest in the query.

But that is only for one row (?!) How do the rest of the rows get populated? Does the server recursively create new rows based on my definitions until it matches row for row all the data in the table?

2) Once the elements are inside a DataSet how do make use of that data to render it in a table.

I understand how the DataSource, DataSet, and Table work individually, yet I do not understand how to establish a flow of data between DataSet and Table.

3) Do I even need to use a <Table> as an RDL element in order to organize the data in an excel table?

I would appreciate any help. Thank you!

Anyone?

programatically obtain primary keys

Given a table, is there a way to look into the systables and obtain the primary keys of that table via a select statement?

Thanks,

Phil

I got it nevermind.

select s.name as TABLE_SCHEMA, t.name as TABLE_NAME

, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;

programatically move to the next column

Hello All...
I am writing to an .xls via an ActiveX script in a DTS.
I need to programatically move to the next column when
the data dictates.
After checking params, I'd like to simply say...
If I am currently in column B, I need to make the
CurCol (Current Column) = C
Is there anything that works like
CurCol = Select 'b' + 1
Thanks in advance,
bob mcclellanNot sure if I follow your requirements, but using column names directly is
the most reliable approach. If you want the "column number" for some reason,
you'd have to query the metadata, for instance like:
DECLARE @.index INT
SET @.index =1
SELECT COL_NAME( OBJECT_ID( QUOTENAME( '<tbl>' ) ), @.index )
Anith|||Hello Anith.
I simply want to increment alphabetically.
I know that I can create a table with all the columns of
a spreadsheet and then increment through the table
but I was wondering if there was a way to simply say
what comes after B and then what comes after C...
the same way that you can say
declare @.i int
Select @.i = 1
Select @.i = @.i +1
I would like to say
declare @.c char(2)
Select @.c = 'A'
Select @.c = @.c + 1
I just want to gain control over what column in a spreadsheet I
am writing to.
like... .range(@.c + "1) = myValue
Thanks in advance,
bob mcclellan
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eYudD1KCFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Not sure if I follow your requirements, but using column names directly is
> the most reliable approach. If you want the "column number" for some
> reason, you'd have to query the metadata, for instance like:
> DECLARE @.index INT
> SET @.index =1
> SELECT COL_NAME( OBJECT_ID( QUOTENAME( '<tbl>' ) ), @.index )
> --
> Anith
>|||One common approach is to use a staging table that corresponds to your
source data and then manipulate the data into your final table
structure using an INSERT...SELECT.
ActiveX does support referencing a column by position number in a
fields collection but I'm not sure that will help you very much. Maybe
if you explain the actual problem someone can help you with an
alternative. Why can't you reference columns by name?
--
David Portas
SQL Server MVP
--|||I misread your post. I though you were imporiting an Excel spreadsheet
rather than exporting it.
Is SQL Server the data source? If so, I expect it's possible to write a
SELECT statement that prepares the data in the form your spreadsheet
requires. If you want help, please post DDL for your tables, sample
data INSERTs and show your required end result.
--
David Portas
SQL Server MVP
--|||Hello David...
> ActiveX does support referencing a column by position number in a
> fields collection but I'm not sure that will help you very much.
--this is part of the code that I use to write the labels into Column A.
----
--
xlBook.Worksheets(1).Range("A1..iv500") = Null
xlBook.Save
'This puts the classes in Column A
with xlBook.Worksheets(1)
do while not rs.eof
.Range("A" &rs.fields("arc_id")) = rs.fields("Class")
rs.movenext
loop
end with
----
--
I've created a proc that builds the cross ref table I need...
To identify each Column increment so that I can dynamically
write the cell range to the column I need ...
I'm sorry David if this explanation is not Clear....
I can make it work, using the table created from the following proc...
Thanks in advance,
bob mcclellan
----
--
alter Proc AlphaIncrement_MakeTable
as
Set NoCount On
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'AlphaIncrement')
DROP TABLE AlphaIncrement
CREATE TABLE AlphaIncrement
(
Alpha_ID int
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Letter char(2) Not Null
)
declare @.i int
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'a'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'b'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'c'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'd'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'e'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'f'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'g'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'h'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 119
begin
Insert into AlphaIncrement (Letter)
SELECT 'i'+CHAR(@.i)
Select @.i = @.i + 1
end
select * from AlphaIncrement order by alpha_ID
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107354249.455002.35430@.l41g2000cwc.googlegroups.com...
> One common approach is to use a staging table that corresponds to your
> source data and then manipulate the data into your final table
> structure using an INSERT...SELECT.
> ActiveX does support referencing a column by position number in a
> fields collection but I'm not sure that will help you very much. Maybe
> if you explain the actual problem someone can help you with an
> alternative. Why can't you reference columns by name?
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David...
I really appreciate it.
I actually worked through this prior to reading your post.
Thanks again...
bob mcclellan
Following is the script I put together ....
'***************************************
*********************************
'D:\Common\Mailers\Batches\AvgRatesByCla
ss\Avg.Rates.By.Class.xls
'Populate Spreadsheet with Monthly Rates
'***************************************
*********************************
Function Main()
Dim xlApp
Dim xlBook
Dim i
Set xlApp = CreateObject("Excel.Application")
Set xlBook =
xlApp.Workbooks.Open(" D:\Common\Mailers\Batches\AvgRatesByClas
s\Avg.Rates.By
.Class.xls")
Dim sqlText
sqlText = "select arc_ID, Class from avgRatesClasses"
sqlColsText = "Select * from alphaIncrement"
dim con
dim rs
dim rsCols
set con = createobject("ADODB.Connection")
set rs = createobject("ADODB.recordset")
set rsCols = createobject("ADODB.recordset")
con.open = "Provider = SQLOLEDB.1; data source = (local); initial catalog
= MyWorkDB; user id = 'me'; password ='mypassword';"
rs.open sqltext, con
rsCols.open sqlColstext, con
xlBook.Worksheets(1).Range("A1..iv500") = Null
xlBook.Worksheets(1).Range("A1") = "CLASS"
xlBook.Save
rs.movenext
'This puts the classes in Column A
with xlBook.Worksheets(1)
do while not rs.eof
.Range("A" &rs.fields("arc_id")) = rs.fields("Class")
rs.movenext
loop
end with
sqlText = "Select c.arc_id, d.* from avgRatesClassesData d " _
& " inner join avgRatesClasses c on " _
& " d.class = c.class " _
& "order by yr desc, Mnth desc, d.class " _
rs.close
rs.open sqltext, con
'Move to Column B
rsCols.movenext
dim CurMnthDate
CurMnthDate = rs.fields("yrMnth")
dim CurCol
CurCol = rtrim(rsCols.fields("letter"))
with xlBook.Worksheets(1)
.Range(CurCol & 1) = rs.fields("yrMnth")
do while not rs.eof
if CurMnthDate <> rs.fields("yrMnth") then
rsCols.movenext
CurCol = rtrim(rsCols.fields("letter"))
.Range(CurCol & "1") = rs.fields("yrMnth")
CurMnthDate = rs.fields("yrMnth")
end if
.Range(CurCol &rs.fields("arc_id")) = rs.fields("avgRate")
rs.movenext
loop
end with
xlBook.Save
rs.close
set rs = nothing
rsCols.close
set rsCols = nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Main = DTSTaskExecResult_Success
End Function
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107354731.006660.43150@.g14g2000cwa.googlegroups.com...
>I misread your post. I though you were imporiting an Excel spreadsheet
> rather than exporting it.
> Is SQL Server the data source? If so, I expect it's possible to write a
> SELECT statement that prepares the data in the form your spreadsheet
> requires. If you want help, please post DDL for your tables, sample
> data INSERTs and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>

Programatically monitor SQL SErver 2000?

How can I monitor (get access to how much space in a given DB is currently allocated, how much room is left, etc.) an SQL Server 2000 database programatically WITHOUT buying some sort of third party tool?

I want to be able to monitor the database from the web, and if I can get a stored procdure to retrieve the information I need, or even better still use a JDBC connection directly (no stored procdure call needed) to retrieve this sort of DB metadata, then I'm in business.

MANY THANKS!look into sp_spaceused system stored procedure.|||Thanks! That's a good start.

Originally posted by ms_sql_dba
look into sp_spaceused system stored procedure.

Programatically Getting User!UserId

Hi,
Is there a way to programatically get the UserId at runtime? I need
this information for a custom data processing extension.
Thanks in advance, HerbWouldn't you just use a parameter that you pass to the data processing
extension? The parameter is then mapped to an expression references
User!UserID
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<herbpatterson@.hotmail.com> wrote in message
news:086d8074-60a8-461e-af41-c9b139228d24@.18g2000hsf.googlegroups.com...
> Hi,
> Is there a way to programatically get the UserId at runtime? I need
> this information for a custom data processing extension.
> Thanks in advance, Herb|||On Dec 10, 12:15 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Wouldn't you just use a parameter that you pass to the data processing
> extension? The parameter is then mapped to an expression references
> User!UserID
>
Hi Bruce,
I have a custom Security Extension -- I would like to implicitly get
the UserId of the logged in user. Is this possible? Is there a
runtime or session based class I can use to get access to this
information?
Thanks, Herb
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <herbpatter...@.hotmail.com> wrote in message
> news:086d8074-60a8-461e-af41-c9b139228d24@.18g2000hsf.googlegroups.com...
> > Hi,
> > Is there a way to programatically get the UserId at runtime? I need
> > this information for a custom data processing extension.
> > Thanks in advance, Herb|||Ah, you said data processing extension.
I am not aware of whether or how you can do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<herbpatterson@.hotmail.com> wrote in message
news:c3aaa503-9208-438d-9730-97a8a0938cca@.e23g2000prf.googlegroups.com...
> On Dec 10, 12:15 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Wouldn't you just use a parameter that you pass to the data processing
>> extension? The parameter is then mapped to an expression references
>> User!UserID
> Hi Bruce,
> I have a custom Security Extension -- I would like to implicitly get
> the UserId of the logged in user. Is this possible? Is there a
> runtime or session based class I can use to get access to this
> information?
> Thanks, Herb
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <herbpatter...@.hotmail.com> wrote in message
>> news:086d8074-60a8-461e-af41-c9b139228d24@.18g2000hsf.googlegroups.com...
>> > Hi,
>> > Is there a way to programatically get the UserId at runtime? I need
>> > this information for a custom data processing extension.
>> > Thanks in advance, Herb
>|||Hi Bruce,
I actually would like to use both -- a security extension for
authentication and then I would like to dynamically change the
datasource based on the username in the data processing extension.
Any ideas?
Thanks, Herb
On Dec 10, 1:11 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Ah, you said data processing extension.
> I am not aware of whether or how you can do this.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <herbpatter...@.hotmail.com> wrote in message
> news:c3aaa503-9208-438d-9730-97a8a0938cca@.e23g2000prf.googlegroups.com...
> > On Dec 10, 12:15 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> Wouldn't you just use a parameter that you pass to the data processing
> >> extension? The parameter is then mapped to an expression references
> >> User!UserID
> > Hi Bruce,
> > I have a custom Security Extension -- I would like to implicitly get
> > the UserId of the logged in user. Is this possible? Is there a
> > runtime or session based class I can use to get access to this
> > information?
> > Thanks, Herb
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> <herbpatter...@.hotmail.com> wrote in message
> >>news:086d8074-60a8-461e-af41-c9b139228d24@.18g2000hsf.googlegroups.com...
> >> > Hi,
> >> > Is there a way to programatically get the UserId at runtime? I need
> >> > this information for a custom data processing extension.
> >> > Thanks in advance, Herb|||When you implement custom authentication then you validate a user. Once
validated then that user would be the user returned with User!UserID. (At
least that is my understanding of how this works). So really how the user is
authenticated is totally separate from the data processing.
If the issue is dynamically changing the data datasource you might be able
to use the expression based datasource functionality in RS 2005. In RS 2005
you can use an expression for the datasource. That expression could use a
report parameter (hidden) and that could use a query that returns the value
you would be using for the datasource. One point about expression based data
sources, you cannot used shared datasources, it is done per report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<herbpatterson@.hotmail.com> wrote in message
news:ed6603d6-e58f-434a-ab46-8c9a393fa634@.b40g2000prf.googlegroups.com...
> Hi Bruce,
> I actually would like to use both -- a security extension for
> authentication and then I would like to dynamically change the
> datasource based on the username in the data processing extension.
> Any ideas?
> Thanks, Herb
>
> On Dec 10, 1:11 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Ah, you said data processing extension.
>> I am not aware of whether or how you can do this.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <herbpatter...@.hotmail.com> wrote in message
>> news:c3aaa503-9208-438d-9730-97a8a0938cca@.e23g2000prf.googlegroups.com...
>> > On Dec 10, 12:15 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
>> > wrote:
>> >> Wouldn't you just use a parameter that you pass to the data processing
>> >> extension? The parameter is then mapped to an expression references
>> >> User!UserID
>> > Hi Bruce,
>> > I have a custom Security Extension -- I would like to implicitly get
>> > the UserId of the logged in user. Is this possible? Is there a
>> > runtime or session based class I can use to get access to this
>> > information?
>> > Thanks, Herb
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >> <herbpatter...@.hotmail.com> wrote in message
>> >>news:086d8074-60a8-461e-af41-c9b139228d24@.18g2000hsf.googlegroups.com...
>> >> > Hi,
>> >> > Is there a way to programatically get the UserId at runtime? I need
>> >> > this information for a custom data processing extension.
>> >> > Thanks in advance, Herb
>

programatically get values from sqldatasource

Hi All,

I am having problems getting values out of an sqldatasource.

I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.

the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.

The insert is fine, i just don't know how to get the values out of the first 'select source'

Any pointers or suggestions most appreciated.

Cheers

I have figured this out.

I am doing the whole lot in the page load event handler and assigning the values to sessions then inserting the sesion variables in the iteminserting event handler for the formview

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dvAs DataView = _

CType(get_LocData.Select(DataSourceSelectArguments.Empty), DataView)

ForEach drAs DataRowIn dv.Table.Rows

Session("LocMan") = dr("LocManID").ToString()

Session("LocID") = dr("ID").ToString()

Session("Status") ="New"

EndSub

ProtectedSub FormView1_ItemInserting(ByVal SenderAsObject,ByVal eAs FormViewInsertEventArgs)Handles FormView1.ItemInserting

e.Values("LocMan") = (Session("LocMan"))

e.Values("Status") ="new"

e.Values("LocationID") = (Session("LocID"))

EndSub

|||

Don't use an SqlDataSource for that.

using (SqlConnection conn = new SqlConnection("your connectionstring"))
{
SqlCommand cmd = new SqlCommand("SELECT ... FROM ... WHERE someColumn = @.someParameter", conn);
cmd.Parameters.AddWithValue("@.someParameter", Request.QueryString["yaddayadda"]);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
col1=reader["col1"]; //Here you need to cast the value to the proper type, or use e.g reader.GetString(0) if it's a string
col2=reader["col2"];
}
}
}

But if you're selecting values just to insert them, you should be able to do it without retrieving the values in the first place. Please give us more details.

Programatically finding if a row has a foreign key constraint

Hello,

Does anyone know a way from within a .net application to find out if a row has a foreign key constraint before updating it? Or possibly in a SQL Stored Procedure if necessary.

What I am trying to do is basicly simulate a delete of a row in a SQL 2005 database from a .net application. Instead of actually deleting the row I want to set a bool column as deleted. The problem is that I can't orphan records that have a relationship to the one deleted.

This is normaly taken care of automaticly with refrential integrity working properly, but not actually calling delete comands I can't seem to find that out without writing a lot of code to check manually in other tables for that key.

Any help would be appreciated greatly,

Thanks,

Patrick

The following statement will bring you the references tables:

Select TC2.Table_Name from INFORMATION_SCHEMA.Table_constraints TC

INNER JOIN INFORMATION_SCHEMA.Referential_constraints RC

ON TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.Table_constraints TC2

ON TC2.CONSTRAINT_NAME = RC.Constraint_NAME

WHERE TC.TABLE_NAME ='Category' AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks. That worked

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current installed SQL Server Service pack via either a registry key or a WMI class entry...
I've had a good look, but can't seem to find anything that matches or is useful. I know you can retrieve this via a SQL query (with Select @.@.version), but I'm after a way that uses our existing WMI/registry reading process as we are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL permissions issues & have to use a different tool in order to run a SQL query...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
Iain
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain
|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>
|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday for me to have missed it. The other method I found was to check the event log via WMI for the event that SQL records on startup, which give the version of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message text from a range of
other event log informational messages from SQLServer, and the LIKE operator for WQL is only suppported in XP/Server2003.....not w2k which the majority of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection tool they retrieve the version from SQL statement, but I assume that it is to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is al
so stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and what the productIDs are for the various editions, that would be great, otherwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain
|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
Regards
|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'll also work when SP4 i released!
Any ideas?
|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current installed SQL Server Service pack via either a registry key or a WMI class entry..
I've had a good look, but can't seem to find anything that matches or is useful. I know you can retrieve this via a SQL query (with Select @.@.version), but I'm after a way that uses our existing WMI/registry reading process as we are collecting info from servers all over the world, so I don't want to have to deal with all the SQL permissions issues & have to use a different tool in order to run a SQL query..
Any info or advice that people can offer would be greatfully accepte
Thanks in Advanc
Cheer
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex..
This is exactly what I nee
Can't believe I didn't spot this, must have been due to a long day on friday for me to have missed it. The other method I found was to check the event log via WMI for the event that SQL records on startup, which give the version of SQL, and also the version of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message text from a range of other event log informational messages from SQLServer, and the LIKE operator for WQL is only suppported in XP/Server2003.....not w2k which the majority of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection tool they retrieve the version from SQL statement, but I assume that it is to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is also stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identify the product type..
if you already know if productID correlates with the different editions, and what the productIDs are for the various editions, that would be great, otherwise I'll post the results of what I find out back here for your referenc
Thanks agai
cheer
Iain|||Rasmus,
This should help:
How do I know which version of SQL Server I'm running?
http://aspfaq.com/show.asp?id=2160
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Rasmus wrote:
> Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
>
> Regards|||Thanks Mark
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'll also work when SP4 i released
Any ideas?|||... and on a related node
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current in
stalled SQL Server Service pack via either a registry key or a WMI class ent
ry...
I've had a good look, but can't seem to find anything that matches or is use
ful. I know you can retrieve this via a SQL query (with Select @.@.version), b
ut I'm after a way that uses our existing WMI/registry reading process as we
are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL
permissions issues & have to use a different tool in order to run a SQL quer
y...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday
for me to have missed it. The other method I found was to check the event l
og via WMI for the event that SQL records on startup, which give the version
of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...th
e only limitation on this is that you need a relatively specific WQL (WMI Qu
ery Language) query - as its an information event that only differs in the m
essage text from a range of
other event log informational messages from SQLServer, and the LIKE operator
for WQL is only suppported in XP/Server2003.....not w2k which the majority
of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection
tool they retrieve the version from SQL statement, but I assume that it is t
o grab the SQL product type (enterprise edition etc) at the same time.....i
s this something that is al
so stored in the registry, as the only reference that might give this info i
s the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL Server\
80\Registration), which I'll check on some different version machines to see
if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and
what the productIDs are for the various editions, that would be great, othe
rwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Servic
e Pack installed?
Regards|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding th
e SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - tha
t'll also work when SP4 i released!
Any ideas?|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?

Saturday, February 25, 2012

Blog Archive

About Me