Xquery - Database Discussion Boards

Re: @@identity

andyharman13-Jun-07 7:31

andyharman

13-Jun-07 7:31

If ID is an integer then you would do:

alter table MyTable alter column
  ID integer identity(1,1) not null

When you insert new records then you use @@identity to show the identity number that SQL-Server has allocated to your new record:

Insert into MyTable (FirstName, LastName, Email)
  values ('Andy', 'Harman', 'Andy@AndysWorld.com')
Select @@Identity

You can find out more about this in the SQL Server Books-Online.

Regards
Andy

Maximum length of a field in SQl SERVER

S.Rajeshwar13-Jun-07 6:13

S.Rajeshwar

13-Jun-07 6:13

Hi All,

I am working on SQL SERVER 2000.I have a problem of Characters maximum length.I declared a cloumn of length 2000 characters of Data type Char.But it is taking only 255 characters.even i tried with ntext,text,varchar,nvarchar datatyupes with 2000 length but it is accepting 255 characters only.No restriction from coding side as well.

Plase any help me to solve this problem.

Thanks & Regards
S.Raju.

Re: Maximum length of a field in SQl SERVER

Colin Angus Mackay13-Jun-07 6:24

Colin Angus Mackay

13-Jun-07 6:24

I've never seen this problem. I've happily inserted much larger amounts of text. You'll have to post your code. I'm guessing that somewhere or other you have something that is truncating the text at 255 chars and that is causing the problem rather than the database.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Maximum length of a field in SQl SERVER

Mike Dimmick13-Jun-07 12:15

Mike Dimmick

13-Jun-07 12:15

If Query Analyzer is only displaying 255 characters, that's because that's the maximum it asks for by default.

Go to Tools, Options, Results tab and increase Maximum characters per column.

Stability. What an interesting concept. -- Chris Maunder

Re: Maximum length of a field in SQl SERVER

Colin Angus Mackay13-Jun-07 13:11

Colin Angus Mackay

13-Jun-07 13:11

Mike Dimmick wrote:
If Query Analyzer is only displaying 255 characters...

Well thought of! I never thought that it might be what he was seeing in Query Analyzer. I just assumed his application was getting back less information that was being put in.

Re: Maximum length of a field in SQl SERVER

S.Rajeshwar13-Jun-07 19:58

S.Rajeshwar

13-Jun-07 19:58

Thanks.

Now i got the solution.

But Now In crystal Reports it is showing only 255 characters.what might be the problem?

Thanks & Regards

S.Rajeshwar

set-variable = max_allowed_packet=16777216 in mysql

WhiteGirl2313-Jun-07 3:46

WhiteGirl23

13-Jun-07 3:46

Hi,
I work on a project which convert a database access in mysql and I have a field
which has 9 Mb and when I try to convert this field in mysql I get the error:

Error:Unable to write to stream<br />

I put the mysql sintax:

set global max_allowed_packet=16777216

but it doesn't work.

Maybe I didn't put it right.
I put this command in my program.

Please help.
Thanks

Xquery

Neema Vikas13-Jun-07 1:21

Neema Vikas

13-Jun-07 1:21

Hi
I'm executing the following Xquery on SQLserver 2005.

select
policy_xml.query('/Policy/PolicyApplication/Inuserer/InsurerID'),
policy_xml.query('/Policy/PolicyApplication/Insurer/AccountIdentifier'),
policy_xml.query('/Policy/PolicyApplication/Insurer/Type'),
policy_xml.query('/Policy/PolicyApplication/Insurer/HolderName')
from policyTable

where
policy_xml.exist('/Policy/PolicyApplication/Insurer/PolicyOwner/EntityID[.="E_1"]') = 1

Its taking 50 Secs to search from 10000 records{without indexes}
The table has 3 columns sno,policy_id,policy_xml.

I have primary index on policy_id field and 1 secondary index(path index) on the table.

When I enable the index the query takes 380 secs.

The size of loan_xml column is about 110 Kb for each row. We need to keep the indexes for some more complex update Xqueries. Is there a way out to improve the performance of the XQuery we are using? Please let us also know the reasons of decrease in performance using indexes on the table. Do indexs have any issues related to XQuery performance?

Vikas Neema

Infragistics hierarchical grid

Revathij13-Jun-07 1:20

Revathij

13-Jun-07 1:20

i want to create hierarchical infragistics grid. anyone can u give me the idea about this?

Re: Infragistics hierarchical grid

Pete O'Hanlon13-Jun-07 2:17

Pete O'Hanlon

13-Jun-07 2:17

If you've installed the grid, there are a number of tutorials associated with it. Plus, the Infagistics forums are a better place to ask for help.

Deja View - the feeling that you've seen this post before.

Re: Infragistics hierarchical grid

Colin Angus Mackay13-Jun-07 3:11

Colin Angus Mackay

13-Jun-07 3:11

Pete O`Hanlon wrote:
the Infagistics forums are a better place to ask for help.

Better watch out. I got voted down the other day for suggesting that the OP use the forums supplied by the third party vendor.

*mumble...grumble...ungrateful...mumble...grumble*

Re: Infragistics hierarchical grid

Pete O'Hanlon13-Jun-07 3:13

Pete O'Hanlon

13-Jun-07 3:13

Ah - I see you're fluent in mumble as well. I like to tinge mine with a side helping of grumpy.

Deja View - the feeling that you've seen this post before.

Week Calculation

Tauseef A13-Jun-07 0:48

Tauseef A

13-Jun-07 0:48

Hello all

i have a min date and max date, and between these dates

i want to calculate all the weeks , and i also need

start date and end date of each week betweeen this start and
end date,, can any body guide me how to get that in sql,

thanks in advance

hello

Re: Week Calculation

Revathij13-Jun-07 1:52

Revathij

13-Jun-07 1:52

for the week calculation in sql,use this code

declare @fromdate datetime
declare @ToDate datetime
declare @Days int
declare @week int
set @fromdate='2007-02-04 00:00:00.000 '
set @todate='2007-12-31 00:00:00.000 '
set @Days=datediff(day,@fromdate,@todate)
select @Days
set @week=(@Days-(@Days%7))/7
select @week

Returning Column Names Only

Brendan Vogt12-Jun-07 23:34

Brendan Vogt

12-Jun-07 23:34

Hi,

I need to return column names only. My code works, but I am changing the initial column name to something more meaningful, like the ID field I changed to SKU like:

ID AS 'SKU'

Here is my code how I returned back only the column names:

' Create SQL statement
Dim strSQL = "SELECT ID AS 'SKU', ProductName AS 'Product Name' FROM bvc_Product;"
' Create instance of connection and command object
Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim command As New SqlCommand(strSQL, conn)
Dim dr As SqlDataReader
Dim i As Integer

' Mark the command as a stored procedure
command.CommandType = CommandType.Text

' Execute the command
conn.Open()
dr = command.ExecuteReader(CommandBehavior.SchemaOnly)

For i = 0 To dr.FieldCount - 1
Response.Write("Column: " & dr.GetName(i).ToString() & "<br />")
Next

My problem is is that I need the result to return the original field name, and the new field name, in this case I need it to return ID and SKU, and ProductName and ProductName.

How do I do this?

Can someone please advise?

Regards
ma se

Re: Returning Column Names Only

Harini N K13-Jun-07 1:58

Harini N K

13-Jun-07 1:58

Hi
It is not possible to get actual column names if alias name is added. If you are passing SQL Statment from the code behind, you can write to split the query as array and extract using substring 'as' then you will get an actual column name. Make sure that you have added 'as' for every column. I think this is bit complicated.

Just try it out.

Harini

Re: Returning Column Names Only

Pete O'Hanlon13-Jun-07 2:16

Pete O'Hanlon

13-Jun-07 2:16

Please don't cross post.

Deja View - the feeling that you've seen this post before.

Data Recovery - After Drop a table and creating the same table

adnanrafiq12-Jun-07 22:57

adnanrafiq

12-Jun-07 22:57

Hi All,
i did a serious mistake, and loss all the data of one financial year in sql server.

I create the script of all tables of a database, and i was inteneded to execute this script on a new database, but unfortunately i executed on the same database, now all my tables are blank (no data).
One more thing, i did not planned any backup model for this now i only have .mdf & .ldf files.
HOw i can recover it, is there any way, please suggest.

Many Thanks & Best Regards,
adnan

Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com

Re: Data Recovery - After Drop a table and creating the same table

Krish - KP13-Jun-07 0:49

Krish - KP

13-Jun-07 0:49

If transaction log is enabled, then you can try restoring from transaction log.
however i've never tried this. you have to refer to some articles on how to do.

Regards
KP

Re: Data Recovery - After Drop a table and creating the same table

adnanrafiq13-Jun-07 2:20

adnanrafiq

13-Jun-07 2:20

Thanks for Repley.
I tried google a lot, but dont find any suitable solution, actually i did blunder, becuase script also have drop database, and create databse with the same name, please reffer me to some article.

Many Thanks & Best Regards,
adnan

Many Thanks,
Adnan Rafiq
muhammadadnanrafiq@gmail.com

Re: Data Recovery - After Drop a table and creating the same table

Pete O'Hanlon13-Jun-07 3:40

Pete O'Hanlon

13-Jun-07 3:40

Sorry to say, but you are stuffed here.

Deja View - the feeling that you've seen this post before.

Re: Data Recovery - After Drop a table and creating the same table

adnanrafiq13-Jun-07 9:19

adnanrafiq

13-Jun-07 9:19

Is there any option to recover, if i tried to recover old files by using data recovery software like easyrecovery.......