|
hai all,
In my database table i have a data in a table like below
X 102
Y 101
Z 123
X 142
Y 245
Z 625
X 234
Y 432
Z 111
How can i get the data from table kike below.
X Y Z
102 101 123
142 245 625
234 432 111
Please help me how to write select query for below.
Thanks in advance,
cheers,
sekhar
modified on Saturday, April 4, 2009 7:26 AM
|
|
|
|
|
Hi,
You need to use SQL 2005's PIVOT statement. Have a look at examples here[^].
Regards,
Syed Mehroz Alam.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Hi,
I have a cte query that returns a hierarcy of content and subcontent shown below:
Id PId Name
----------------------------
1 0 HomePage
2 1 AboutUs
5 2 WhatWeDo
7 5 Alternate
8 5 Another one
10 8 Second
11 8 third
4 1 ContactUs
6 4 TotalItems
3 0 Testing
I want to take this result set and generate a Xml like the one shown below:
<ContentItems>
<Content Id='1' PId='0'>
<Name>HomePage</Name>
<Content Id='2' PId='1'>
<Name>AboutUs</Name>
</Content>
</Content>
....
</ContentItems>
I have tried using the FOR XML but it it seems to just put all the nodes under <contentitems> node. Any suggestions is appreciated. Thanks.
|
|
|
|
|
How to get the time difference between two dates in SQl?
|
|
|
|
|
|
Hi!
I have a database (master.mdb), and I want link 3 table (first, second, third) from a password protected (abc1234) database (secondary.mdb).
How could I link with VBA?
Thank you
|
|
|
|
|
Have a look into the information this site can supply Connectionstrings[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all,
I am getting Error: Truncated incorrect time value.
while working with TIMEDIFF function in mysql.
pls help resolve this error.
Thanks inadvance.
No Defeat Is Final Until You Stop Trying!.......
|
|
|
|
|
hi,
i got the solution,
there is a max limitation for time value for TIMEDIFF function that's why its throwing error.
anyways thanks
No Defeat Is Final Until You Stop Trying!.......
|
|
|
|
|
SQL> create or replace procedure create_tablename(name varchar2) is
2
3 str varchar2(20) := name;
4 v_str varchar2(255);
5 Begin
6 v_str := 'Create table '||'AFR'||'_'||str||'(rollno NUMBER(2),name varchar2(10))';
7 Execute immediate v_str;
8 End;
9 /
Procedure created.
SQL> variable abc varchar2(20)
SQL> exec create_tablename(:abc);
BEGIN create_tablename(:abc); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.CREATE_TABLENAME", line 7
ORA-06512: at line 1
hey can any1 suggest what exactly the problem is?
The above code is for creating the table dynamically in Oracle 9i....
|
|
|
|
|
That's as close to a clear text message as you get from Oracle.
ORA-01031 means you're not having the privilege to do what you just tried to do.
ORA-06512 tell you where the error happened.
So it means that your user is not having Create Table rights in tablespace Scott.
|
|
|
|
|
Technically it's the SCOTT schema, not necessarily a SCOTT tablespace.
Scott (coincidentally)
|
|
|
|
|
|
Firstly thnx for your quick reply. And could you suggest a way in which the above error could be corrected. Even tried logging in as user:system. But it did not help. Same error.
|
|
|
|
|
Does Scott have 'create table' privileges?
|
|
|
|
|
Login with sys and whatever password you have, and set the correct correct privileges for user scott.
Or create a new user with the proper access rights for schema scott.
Or (this is from memory and might not be correct) add "SQLNET.AUTHENTICATION_SERVICES = (NTS)" into sqlnet.ora to be able to use NT security
|
|
|
|
|
I suppose it is unkind to laugh at people who are fumbling around with problems in technologies they do not understand, but this is a funny little exchange.
First: SQLNET.AUTHENTICATION_SERVICES has nothing to do with the person's problem.
Second: The person's error is quite self-explanatory.
Third: One wonders why he (or his helpers here) did not just Google the error message. The solution would have been quickly found.
The problem is that the person is either 1) trying to create a table in his own schema without having the CREATE TABLE privilege, or 2) trying to create a table in another schema without the CREATE ANY TABLE privilege. In addition, the user's account must have a quota on its default tablespace or have the UNLIMITED TABLESPACE privilege without one of which the ORA-01536 error will occur.
Kind regards,
Russ
|
|
|
|
|
'34523@#$@%^@^'
how can i find that above value contain special character in sqlserver 2005
|
|
|
|
|
in sql server 2000 you can use PATINDEX which will return the index of the given pattern, or you could use LIKE. If 2k5 has support for Regular Expressions you could use those, but i havent used 2k5 yet. We're going to upgrade to 2k8 shortly though.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
I have Windows Server 2003 Enterprise OS, SQL 2005 Developer installled, And I am getting error with Web Synchronization with merge replication, I am stating here what i have done so till this time
- Configure a Publication with merge replication and pull subscription , Distributor & Publisher are on same machine, after Create a user Under Sysadmin Role, Create user for Publishing Database with db_owner schema, assign all permission to it,Running snapshot agent under SQL SERVER agent account, And for Web Synchronization I create account with DYNDNS.com and Create MyServer.dyndns.biz hostname with my static IP, And At publisher I assign address MyServer MyServer.dyndns.biz, configure web Synchronization at publisher as per Microsoft Site.
- At IIS I create a virtual directory under Default Web Site,named TestPublication,Create new user,IIS_user for IIS_WPG Group, Create new Application pool and Configure its identity with New Creted IIs_user,and set new application pool at my virtual directory application pool setting.At Default web site properties, I assign my static IP at WebSite Identification, At directory security tab,I enable anonymous access with "PINAAK-3\IUSR_PINAAK-3", select Basic authantication, Dafult domain as dyndns.biz, realm as MyServer.dyndns.biz, I create SSL trial with Thawte,but my IIS detected that there is not sufficient information available, and if i use self assigned cerificate with selfssl, At subcriber my IP cant be resolved,
- I create subcriber with pull subcription,give https://MyServer.dyndns.biz/TestPublication/replisapi.dll address,
running its agent under SQL server agent account.
I try web synchronization two times, first with https://***.**.**.***(My static IP)/TestPublication/replisapi.dll address, and configure all its related items as this address, create self ssl for that with static IP as its common name, At subcriber i get diagnosis window successful, and at authantication I gave MyServername\Administrator. But at second time login it ask again my UserName, Password.Here the problem is, at subcriber when i click View Subscrption status,"Specified File not found",error occured.
At second time I try with address https://MyServer.dyndns.biz/TestPublication/replisapi.dll, and this time also dignosis window is successful, but it ask me UserName, Password if i enter dignosis mode again. Here the problem is error "The Proxy Auto-configuration URL was not found.", comes.
|
|
|
|
|
Here is event log
**********************************************
This log comes to Subcriber job history, when address is
https://MyServer.dyndns.biz/TestPublication/replisapi.dll
-----------------------------------------------------------------------------------------------------------
a> Date 4/1/2009 8:29:27 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 1
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name Run agent.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The replication agent has been successfully started. See the Replication Monitor
for more information.
b> Date 4/1/2009 8:29:27 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 1
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name Run agent.
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
-XCancelEventHandle 00000414
2009-04-01 14:59:27.671 Connecting to Subscriber 'PINAAK-1\PINAAK1'
2009-04-01 14:59:27.812 Connecting to Subscriber 'PINAAK-1\PINAAK1'
2009-04-01 14:59:27.843 The upload message to be sent to Publisher
'PINAAK-3\PINAAK3' is being generated
2009-04-01 14:59:27.843 The merge process is using Exchange ID
'3148BDDD-384E-4F63-9EC6-A596612FDCE5' for this web synchronization
session.
2009-04-01 14:59:30.421 The Proxy Auto-configuration URL was not found.
2009-04-01 14:59:30.421 Category:NULL
Source: Merge Process
Number: -2147209324
Message: The Proxy Auto-configuration URL was not found.
2009-04-01 14:59:30.421 Category:NULL
Source: Merge Process(Web Sync Client)
Number: -2147023888
Message: The Merge Agent could not connect to the URL
'https://pinaak3.dyndns.biz/TestPublication/replisapi.dll' during Web
synchronization. Please verify that the URL, Internet login credentials and proxy
server settings are correct and that the Web server is reachable.
c> Date 4/1/2009 8:29:27 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 1
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name Run agent.
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: Pinaak-1\Administrator. The replication agent encountered a
failure. See the previous job step history message or Replication Monitor for more
information. The step failed.
d >
Date 4/1/2009 8:29:27 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 0
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name (Job outcome)
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User sa. The last step to run was step 1
(Run agent.).
-------------------------------------------------------------------------------------------------------------
This log comes to Subcriber job history, when address is
https://MyServer.dyndns.biz/TestPublication/replisapi.dll
-------------------------------------------------------------------------------------------------------------
a>Date 4/1/2009 8:46:49 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 1
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name Run agent.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The replication agent has been successfully started. See the Replication Monitor
for more information.
b> Date 4/1/2009 8:46:49 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 1
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name Run agent.
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
-XCancelEventHandle 00000440
2009-04-01 15:16:49.562 Connecting to Subscriber 'PINAAK-1\PINAAK1'
2009-04-01 15:16:49.687 Connecting to Subscriber 'PINAAK-1\PINAAK1'
2009-04-01 15:16:49.734 The upload message to be sent to Publisher
'PINAAK-3\PINAAK3' is being generated
2009-04-01 15:16:49.765 The merge process is using Exchange ID
'525DD6A9-1A59-4D2B-9DD4-4DE48BA5FDBC' for this web synchronization
session.
2009-04-01 15:16:52.125 The Proxy Auto-configuration URL was not found.
2009-04-01 15:16:52.125 Category:NULL
Source: Merge Process
Number: -2147209324
Message: The Proxy Auto-configuration URL was not found.
2009-04-01 15:16:52.125 Category:NULL
Source: Merge Process(Web Sync Client)
Number: -2147023888
Message: The Merge Agent could not connect to the URL
'https://219.64.82.212/TestPublication/replisapi.dll' during Web synchronization.
Please verify that the URL, Internet login credentials and proxy server settings are
correct and that the Web server is reachable.
c > Date 4/1/2009 8:46:49 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 1
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name Run agent.
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: Pinaak-1\Administrator. The replication agent encountered a
failure. See the previous job step history message or Replication Monitor for more
information. The step failed.
d>
Date 4/1/2009 8:46:49 PM
Log Job History
(PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0)
Step ID 0
Server PINAAK-1\PINAAK1
Job Name
PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0
Step Name (Job outcome)
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User sa. The last step to run was step 1
(Run agent.).
+++++++++++++++++++++++++++++++++++++++++
Here is th log at System Event Log
--------------------------------------------------------------------------------------------------------------
Event log:
SQL Server Scheduled Job
'PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0'
(0x3710058FCC349B479E94B09297DF0AF4) - Status: Failed - Invoked on:
2009-04-01 20:35:30 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Run agent.).
22>>
DCOM was unable to communicate with the computer PINAAK-3 using any of the
configured protocols.
The certificate received from the remote server was issued by an untrusted
certificate authority. Because of this, none of the data contained in the certificate
can be validated. The SSL connection request has failed. The attached data
contains the server certificate.
33>>
SQL Server Scheduled Job
'PINAAK-3\PINAAK3-PubTest-TestPub-PINAAK-1\PINAAK1-WebSynch- 0'
(0x3710058FCC349B479E94B09297DF0AF4) - Status: Failed - Invoked on:
2009-04-01 20:31:40 - Message: The job failed. The Job was invoked by User
sa. The last step to run was step 1 (Run agent.).
Here Pinaak-3 is Publisher server, Pinaak-1 is subcriber server.
|
|
|
|
|
1. How can i get the first letter from name column
|
|
|
|
|
By using the SubString[^] function on the column.
It's usage;
SELECT SUBSTRING([column-name goes here], StartIndex, Length)
An example;
SELECT SUBSTRING([text], 1, 4)
FROM master.dbo.syscomments
You would require;
SELECT SUBSTRING([name], 1, 1)
Good luck 
I are troll
|
|
|
|
|
This query return first letter of table's column name
SELECT substring(column_name,1,1) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'TableName'
Or do you want to get first letter of values in one column?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
