Is it possible to get the properties retrievable with T-SQL
SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
object that can provide this kind of information (e.g. LicenseType or
NumLicenses)? Any Ideas?You could use SQL DMO object model. See SQL Server Books Online for more
info. See SQLServer2 object for a start.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Holger" <atlan@.tournedos.de> wrote in message
news:1124040877.686226.191970@.g43g2000cwa.googlegroups.com...
> Is it possible to get the properties retrievable with T-SQL
> SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
> object that can provide this kind of information (e.g. LicenseType or
> NumLicenses)? Any Ideas?
>|||Thank you for your reply but in COM programming it is always good to
read the manual first. The object SQLServer2 does not provide access to
the properties LicenseType or NumLicenses exposed by SERVERPROPERTY.
These properties are of special interest to me. Any further object you
can recommend?|||Another approach is simply the registry to get these values
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL
Server\80\MSSQLLicenseInfo\MSSQL8.00]
"ConcurrentLimit"=dword:00000006
"Mode"=dword:00000002
Mode = 2 means PerProcessor
Mode != 2 means PerSeat
ConcurrentLimit = Number of Seats OR Processors
Just to complete this topic,
Holger
Showing posts with label instead. Show all posts
Showing posts with label instead. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Columns inster into problem
Hi Every1,
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:
> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:
> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks
Columns in Crystal
hey
is it possible to create columns under crystal ... so instead of the values going vertically, for them go to horizontally ...
eg. when you put the value 'icecream'
it is going to show you all the possible icecream types
eg:
vanilla
chocolate
strawberry
etc
now would you be able to show them like this :
vanilla chocalate strawberry etc .....Even I would like to know if it is possible, right now I m using temp tables to achieve this.|||hey
well i have found a way that it can be done. under the details section in crystal, select 'Format with Multiple Columns'. tt should display the values going horizontally rather than vertically. but there are still some issues outstanding like formatting of the actual values. you do not seem to be able to move the field after you have selected the 'Format with Multiple Column'.
hope it helps.
cheers
milos|||Hey
yes its me again ... Well there is a better way than the one described previously.
By Inserting a Cross-Tab, you have the option of selecting values to show as Columns and Rows. Its a VERY useful feature for those who need to add columns in their reports.
Any questions, feel free to ask ...
Cheers
Milos
is it possible to create columns under crystal ... so instead of the values going vertically, for them go to horizontally ...
eg. when you put the value 'icecream'
it is going to show you all the possible icecream types
eg:
vanilla
chocolate
strawberry
etc
now would you be able to show them like this :
vanilla chocalate strawberry etc .....Even I would like to know if it is possible, right now I m using temp tables to achieve this.|||hey
well i have found a way that it can be done. under the details section in crystal, select 'Format with Multiple Columns'. tt should display the values going horizontally rather than vertically. but there are still some issues outstanding like formatting of the actual values. you do not seem to be able to move the field after you have selected the 'Format with Multiple Column'.
hope it helps.
cheers
milos|||Hey
yes its me again ... Well there is a better way than the one described previously.
By Inserting a Cross-Tab, you have the option of selecting values to show as Columns and Rows. Its a VERY useful feature for those who need to add columns in their reports.
Any questions, feel free to ask ...
Cheers
Milos
Tuesday, February 14, 2012
Column Alias
Hello everyone.
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.I don't know if you would be able to do it but I have 2 ideas that might work.
1. do some kind of sub query which would return the alias name
2. write a stored procedure in which you could write a query to get the alias name, assign that to a variable, and then use the variable as the alias in the next query.
I have no idea if either one of these will work, but they are just some ideas.
I hope this helped
Good luck|||You can do this with dynamic SQL.
As a matter of fact, you can do also sorts of stupid things with dynamic SQL. And many people take up the challenge.|||Thanks for the suggestions. I am doing a little research to see how i can accomplish this using dynamix sql statements.|||And the challenge has been accepted.|||Is there a problem with aliasing the column in a view?|||And the challenge has been accepted.
Oh great. Are you gonna pay for my new keyboard (after I just spit my coke all over it)?
Regards,
hmscott|||First of all, if you just sniffed the coke, how on earth did you end up spitting it onto your keyboard? Its not really any of my business I guess, but inquiring minds want to know.
More importantly, I saw this one coming a long way off, and I'm usually the optimist in this crowd... Why should Blindman buy you new coke when you ought to have expected this quite a while back?
-PatP|||Coke? Keyboard? hmscott?
OK, I'm lost now.
The poster is (against advice) taking the "dynamic sql challenge". You appear to be confusing this with the "Pepsi Challenge".|||Just to clarify, I do not support dynamic sql statements, but it seemed like the only way to achieve the goal. If anybody has a better idea I am all ears.|||To paraphrase Slim Pickens from BLAZING SADDLES ...Why in the wide, wide world of sports would you want to do that?|||In this case, it would be more appropriate to paraphrase Slim Pickens whooping and hollering as he rides the nuclear warhead down to destruction in "Dr. Strangelove".|||well i know you can also update the column name in the syscolumn table.
I would have to use:
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
This will allow me to use ad hoc commands to rename table. But i am not sure if allowing updates on the system tables would be an approach to take.|||Modifying system tables beats dynamic SQL every time when you are looking to create self-inflicted injuries!
-PatP|||But of course, modifying system tables WITH dynamic sql would be like the biathalon of bad design!|||trifecta if you do it in a stored proc with a cursor|||trifecta if you do it in a stored proc with a cursorOh ouch!
-PatP|||there is a much easier way of doing this if the alias names are again column names of some other table and the datatype matches. is that the case?|||Assume it is. You've piqued my curiosity. What are you proposing?|||Ummm...Is there a problem with aliasing the column in a view?|||Assume it is. You've piqued my curiosity. What are you proposing?
select top 0 * from TableHavingAliasColName
union
select * from TableWithDiffColName
OR
select top 0 * into Dummy from TableHavingAliasColName
insert into Dummy select * from TableWithDiffColName|||Ah. So the table schemas have to be identical.
But the poster wants to set the column name to the VALUE in another table. Not the column name from another table.
Very creative, though.
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.I don't know if you would be able to do it but I have 2 ideas that might work.
1. do some kind of sub query which would return the alias name
2. write a stored procedure in which you could write a query to get the alias name, assign that to a variable, and then use the variable as the alias in the next query.
I have no idea if either one of these will work, but they are just some ideas.
I hope this helped
Good luck|||You can do this with dynamic SQL.
As a matter of fact, you can do also sorts of stupid things with dynamic SQL. And many people take up the challenge.|||Thanks for the suggestions. I am doing a little research to see how i can accomplish this using dynamix sql statements.|||And the challenge has been accepted.|||Is there a problem with aliasing the column in a view?|||And the challenge has been accepted.
Oh great. Are you gonna pay for my new keyboard (after I just spit my coke all over it)?
Regards,
hmscott|||First of all, if you just sniffed the coke, how on earth did you end up spitting it onto your keyboard? Its not really any of my business I guess, but inquiring minds want to know.
More importantly, I saw this one coming a long way off, and I'm usually the optimist in this crowd... Why should Blindman buy you new coke when you ought to have expected this quite a while back?
-PatP|||Coke? Keyboard? hmscott?
OK, I'm lost now.
The poster is (against advice) taking the "dynamic sql challenge". You appear to be confusing this with the "Pepsi Challenge".|||Just to clarify, I do not support dynamic sql statements, but it seemed like the only way to achieve the goal. If anybody has a better idea I am all ears.|||To paraphrase Slim Pickens from BLAZING SADDLES ...Why in the wide, wide world of sports would you want to do that?|||In this case, it would be more appropriate to paraphrase Slim Pickens whooping and hollering as he rides the nuclear warhead down to destruction in "Dr. Strangelove".|||well i know you can also update the column name in the syscolumn table.
I would have to use:
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
This will allow me to use ad hoc commands to rename table. But i am not sure if allowing updates on the system tables would be an approach to take.|||Modifying system tables beats dynamic SQL every time when you are looking to create self-inflicted injuries!
-PatP|||But of course, modifying system tables WITH dynamic sql would be like the biathalon of bad design!|||trifecta if you do it in a stored proc with a cursor|||trifecta if you do it in a stored proc with a cursorOh ouch!
-PatP|||there is a much easier way of doing this if the alias names are again column names of some other table and the datatype matches. is that the case?|||Assume it is. You've piqued my curiosity. What are you proposing?|||Ummm...Is there a problem with aliasing the column in a view?|||Assume it is. You've piqued my curiosity. What are you proposing?
select top 0 * from TableHavingAliasColName
union
select * from TableWithDiffColName
OR
select top 0 * into Dummy from TableHavingAliasColName
insert into Dummy select * from TableWithDiffColName|||Ah. So the table schemas have to be identical.
But the poster wants to set the column name to the VALUE in another table. Not the column name from another table.
Very creative, though.
Subscribe to:
Posts (Atom)