Database Discussion Boards

Re: Query help in SQL Server 2005

Syed Mehroz Alam4-Apr-09 3:10

Syed Mehroz Alam

4-Apr-09 3:10

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

CTE Result as nested Xml

Fayu3-Apr-09 5:30

Fayu

3-Apr-09 5:30

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.

Time difference?

Karthick_gc3-Apr-09 1:48

Karthick_gc

3-Apr-09 1:48

How to get the time difference between two dates in SQl?

Re: Time difference?

Giorgi Dalakishvili3-Apr-09 2:05

Giorgi Dalakishvili

3-Apr-09 2:05

DateDiff?

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

Access: link a table from a password protect database

drapacs2-Apr-09 23:04

drapacs

2-Apr-09 23:04

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

Re: Access: link a table from a password protect database

Mycroft Holmes3-Apr-09 0:26

Mycroft Holmes

3-Apr-09 0:26

Have a look into the information this site can supply Connectionstrings[^]

Never underestimate the power of human stupidity
RAH

ER_TRUNCATED_WRONG_VALUE while working with TIMEDIFF function in mysql

surender.m2-Apr-09 22:38

surender.m

2-Apr-09 22:38

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!.......

Re: ER_TRUNCATED_WRONG_VALUE while working with TIMEDIFF function in mysql

surender.m2-Apr-09 23:40

surender.m

2-Apr-09 23:40

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!.......

Creating Table dynamically in Oracle 9i

Mygrp2-Apr-09 21:09

Mygrp

2-Apr-09 21:09

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....

Re: Creating Table dynamically in Oracle 9i

Jörgen Andersson3-Apr-09 1:39

Jörgen Andersson

3-Apr-09 1:39

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.

Re: Creating Table dynamically in Oracle 9i

scottgp3-Apr-09 1:49

scottgp

3-Apr-09 1:49

Technically it's the SCOTT schema, not necessarily a SCOTT tablespace.
Scott (coincidentally)

Re: Creating Table dynamically in Oracle 9i

Jörgen Andersson3-Apr-09 1:53

Jörgen Andersson

3-Apr-09 1:53

Quite correct.

Re: Creating Table dynamically in Oracle 9i

Mygrp3-Apr-09 3:53

Mygrp

3-Apr-09 3:53

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.

Re: Creating Table dynamically in Oracle 9i

scottgp3-Apr-09 4:18

scottgp

3-Apr-09 4:18

Does Scott have 'create table' privileges?

Re: Creating Table dynamically in Oracle 9i

Jörgen Andersson3-Apr-09 4:33

Jörgen Andersson

3-Apr-09 4:33

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

Re: Creating Table dynamically in Oracle 9i

RussellT5-Jun-09 9:46

RussellT

5-Jun-09 9:46

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

how can i find value contain special character in sqlserver 2005

anup keshari2-Apr-09 0:12

anup keshari

2-Apr-09 0:12

'34523@#$@%^@^'
how can i find that above value contain special character in sqlserver 2005

Re: how can i find value contain special character in sqlserver 2005

SomeGuyThatIsMe2-Apr-09 3:30

SomeGuyThatIsMe

2-Apr-09 3:30

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.

Error with web synchronization

vyasdev20091-Apr-09 23:00

vyasdev2009

1-Apr-09 23:00

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.

Re: Error with web synchronization

vyasdev20091-Apr-09 23:00

vyasdev2009

1-Apr-09 23:00

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.

Initials

5fingers1-Apr-09 21:23

5fingers

1-Apr-09 21:23

1. How can i get the first letter from name column

Re: Initials

Eddy Vluggen1-Apr-09 22:35

Eddy Vluggen

1-Apr-09 22:35

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 Smile | :)

Re: Initials

Blue_Boy1-Apr-09 22:36

Blue_Boy

1-Apr-09 22:36

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.

conn string "max_allowed_packet": MySQL 6.0 and "MySQL Connector Net 6.0.0" [modified]

devvvy1-Apr-09 20:01

devvvy

1-Apr-09 20:01

hello

I'm using MySQL And "MySQL Connector Net 6.0.0" - I've updated "my.ini" (I ran into packet too large exception) as follows:

<br />
max_allowed_packet = 32M<br />

I think I hear they say you'd also need to make corresponding change on client side - the C# app uses MySQL .NET Connector 6.0 and I checked doc and www.connectionstrings.com on connection string format, it seems to be that you can't set max packet size in connection string? Is this true?

Reference:
1. MySQL .NET Connector 6.0 Doc, look under topic "MySqlConnection..::.ConnectionString Property"

2. Connectionstrings.com
http://www.connectionstrings.com/mysql[^]

3.
http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html[^]

4.
http://www.mysql.com/news-and-events/newsletter/2003-08/a0000000216.html[^]

5. Google for "MySql.Data.MySqlClient.MySqlConnection"
http://ftp.linux.cz/pub/mysql/Downloads/Manual/connector-net-a4.pdf[^]

dev

modified on Thursday, April 2, 2009 2:11 AM

MS ACESS setting cutureinfo

udikantz1-Apr-09 18:09

udikantz

1-Apr-09 18:09

hi,
is there anyway to set programaticly a connection to ms access .mdb file so the culture info in the connection would "en-us" or example if windows set to germany culture info ( dates are like 10.9.1982 )
i would like to insert rows through .net framework which contains dates in en-us format all the time and so acceess db would be able to parse them ,
or any alternatives?

thanks

Net

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.