can I get attribute name in SQL XML? thanks

a xml string like

<field name="david" country="ca" age="13" />

in XSL , I can get each attribute name and value by name() and .

So in SQL 2005, if I don't know attribute name in head, how to loop through all attributes and get their name and value?

thanks

declare @.x xml
set @.x = '<field name="david" country="ca" age="13" />'

select x.value('local-name(.)', 'varchar(20)') as Name,
x.value('.', 'varchar(20)') as Value
from @.x.nodes('/field/@.*') as R(x)

Can I define a query governor limit in a connection string rather then the server based op

Hi,
Can I use a query governor option in a connection string?
if yes, what is the property name I have to add in the connection string?
I don't want to setup this at the server level but only in specific
connection strings used by my users when then do ad-hoc reporting using
Report Builder.
I want to limit these users only when they create reports. (to insure that
they don'T execute bad queries which can kill the server)
other reports generated by developpers and some complex queries must always
run. and some of them are really big; if I activate the query governor of
the server, then the server always refuse to execute them. So I can't
activate the query governor at the server level to allow the developpers to
do their job.
thanks.
Jerome.Hi Jerome
I don't think that this is possible, what you may want to do is report of a
snapshot rather than any live system to reduce the impact of such problems.
John
"Jéjé" wrote:
> Hi,
> Can I use a query governor option in a connection string?
> if yes, what is the property name I have to add in the connection string?
> I don't want to setup this at the server level but only in specific
> connection strings used by my users when then do ad-hoc reporting using
> Report Builder.
> I want to limit these users only when they create reports. (to insure that
> they don'T execute bad queries which can kill the server)
> other reports generated by developpers and some complex queries must always
> run. and some of them are really big; if I activate the query governor of
> the server, then the server always refuse to execute them. So I can't
> activate the query governor at the server level to allow the developpers to
> do their job.
> thanks.
> Jerome.
>
>

Can I convert/reference a string to a var?

hey, guys

I got a strange question. :)

In SQL Server,
declare @.Item8 as varchar(100)
declare @.str as varchar(100)
set @.str = '@.Item8'
Is there any way I can reference @.str to @.Item8 ?

Thanks

No. This is not possible. Variables are scoped to batch or module or dynamic SQL batch.

Can i connect to SQLExpress instance using (local)

Hello,

I am using the following connection string to connect to SQL Server 2005 Express Edition installed on my local PC.
string sqlstr = "Server=(local);Database=MyDB;User ID=sa;Password=sa;Trusted_Connection=False";

And it gives me the following error. The string works fine in computer with SQL Server 2000 instance but fails with 2005. The string also fails for "Server = localhost".
However the string works very fine if I use the name of Server 2005 i.e ./SQLExpress

===================================

Cannot connect to (local).

===================================

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Error Number: 2
Severity: 20
State: 0

Now the main problem is that, of course I could use a named instance, but since I am embedding the Connection String in a dll file so that any user can use it to connect it to the local instance of the SQL Server 2005 installed in his/her computer, I need to use "server = localhost". Doesn't SQL 2005 support this. If yes, where could i find option to enable it?

I have enabled every option available in surface area configuration for SQL Server 2005, but nothing seems to help out. I even use local as well as remote connections using both TCP/IP and named pipes.
Can nyone help me out?

By default SQL Server 2005 Express edition is installed as a named instance with name "SQLExpress". As a result, the connection string should specify the instance, e.g. ".\SQLExpress" or "(local)\SQLExpress" to distinguish it from a possible default instance of SQL Server that may be installed on the same machine. If you install SQL Express as a default instance then "." or "(local)" are the proper ways to specify in the connection string.|||Is there a way to convert the named instance to a default one apart from re-installation. I have only a single named instance on my computer.|||The only way to change the instance name is to re-install - uninstall and install as default instance.|||Thanks a lot, Peter. That solves it all.sql

Can I change the default CSV extract to reporting services

I have been searching and found the answer to my problems in the XML string
sent from the reporting services engine on scheduled jobs. I know I need to
add a Noheader command and also change to ASCII.
I wondering how do I do this to scheduled jobs with in the reporting
services environment and schedule it to run each day with the extra device
parameters. The default I want for this report will be the same default for
all csv files so can I change something in the report engine or the CSV
extract defaults?
Current address I use is
http://report01/ReportServer?%2fDevelopment%2fEIS%2fEIS02+BSMPurchaseOrderExtract&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=asciiI would love to know the answer to this because I have exactly the same
problem.
"John230873" wrote:
> I have been searching and found the answer to my problems in the XML string
> sent from the reporting services engine on scheduled jobs. I know I need to
> add a Noheader command and also change to ASCII.
> I wondering how do I do this to scheduled jobs with in the reporting
> services environment and schedule it to run each day with the extra device
> parameters. The default I want for this report will be the same default for
> all csv files so can I change something in the report engine or the CSV
> extract defaults?
> Current address I use is
> http://report01/ReportServer?%2fDevelopment%2fEIS%2fEIS02+BSMPurchaseOrderExtract&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
>

Can Grow crystal report option - does not grow.

Hi.

I am connecting my report file to the domino server. I have a string in the crystal report which is mapped to a text field. the "Can Grow" option is checked but still this string on the report is truncating the data after some 240+ characters.

Any of you have any idea? Your help would be highly appreciatedCheck whether the field has any junk data|||Hi Madhi.

Thanks for your reply. I have checked that there is no junk data in the field...its all simple text...but still the field does not display all of my data in the report. Any idea?.

thanks again for your help.|||Did you try to expand the filed?|||Yes. I expanded the field width. My field is available in the Details Section. I have increased the width but still the complete information does not show up and blank space is displayed from the point the information is truncated.

Can anyone tell me why this returns an empty value?

@.Names is a query string passed in, I need to count the number of records as a result of the below query/

Dim

testAsStringDim sqlConnection3AsNew SqlConnection("data Source=EQ-520-WEB\SQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")Dim cmdAsNew SqlCommand

Dim returnValueAsObject

cmd.CommandText =

"SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @.Names)"

cmd.CommandType = Data.CommandType.Text

cmd.Connection = sqlConnection3

cmd.Parameters.Add(

"@.Names", Data.SqlDbType.NVarChar)

sqlConnection3.Open()

cmd.Parameters(

"@.Names").Value = testIf test =""ThenResponse.Write("An error occured")ExitSubElsereturnValue = cmd.ExecuteScalar()

sqlConnection3.Close()

Label6.Text =

"Number " & returnValue

Hi.

your steps is right but you must to be sure the (test) value that have the correct value.

also you must be sure if there is data in the specific value.for example if you trace that test=7 check if there is data must retrive in the query

SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager =7)

Hopes that help.

Can an adapter handle a stored procedures?!

Can someone please help on this issue and tell me how to amend this code:
<WebMethod()> _
Public Function GetRecord(ByVal anyname As String) As DataSet
Dim adapter As New SqlDataAdapter
Dim result As New DataSet
adapter.SelectCommand.Connection = Conn
adapter.SelectCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand.CommandText = "GetSingleName"
adapter.SelectCommand.Parameters.Add("@.myname", SqlDbType.VarChar)
adapter.SelectCommand.Parameters("@.myname").Direction =
ParameterDirection.Input
adapter.SelectCommand.Parameters("@.myname").Value = anyname
adapter.Fill(result, "nabData")
Return result
This code is supposed to resturn dataset filled by an adapter that uses a
stored procedure called "GetSingleName". The Database table is "nabData". Th
e
connection is established through the GUI and is named Conn.
Many thanks in advance.Nab wrote:
> Can someone please help on this issue and tell me how to amend this code:
> <WebMethod()> _
> Public Function GetRecord(ByVal anyname As String) As DataSet
> Dim adapter As New SqlDataAdapter
> Dim result As New DataSet
> adapter.SelectCommand.Connection = Conn
> adapter.SelectCommand.CommandType = CommandType.StoredProcedure
> adapter.SelectCommand.CommandText = "GetSingleName"
> adapter.SelectCommand.Parameters.Add("@.myname", SqlDbType.VarChar)
> adapter.SelectCommand.Parameters("@.myname").Direction =
> ParameterDirection.Input
> adapter.SelectCommand.Parameters("@.myname").Value = anyname
> adapter.Fill(result, "nabData")
> Return result
> This code is supposed to resturn dataset filled by an adapter that uses a
> stored procedure called "GetSingleName". The Database table is "nabData".
The
> connection is established through the GUI and is named Conn.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
This is a question, better answered in a VB.NET newsgroup.
Wrist slapping done...
An example in C# (untested):
// create connection
String connString = "Data Source=(local);Integrated security=SSPI;" +
"Initial Catalog=Northwind;";
SqlConnection conn = new SqlConnection(connString);
// create a Command object based on a stored procedure
String selectSql = "MyStoredProcedure";
SqlCommand selectCmd = new SqlCommand(selectSql, conn);
selectCmd.CommandType = CommandType.StoredProcedure;
// create and set the parameter for the stored procedure
selectCmd.Parameters.Add("@.CustomerID", SqlDbType.NChar, 5);
selectCmd.Parameters["@.CustomerID"].Value = "VINET";
SqlDataAdapter da = new SqlDataAdapter(selecteCmd);
// create a new DataSet to receive the data
DataSet ds = new DataSet();
// read the data from stored procedure & load it into the DataSet
da.Fill(ds);
A good ADO.NET reference is _ADO.NET In a Nutshell_ (pub: O'Reilly).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhjdeoechKqOuFEgEQIg9QCgxmdNXsVA6uhC
wOZGYdYzwpAW+E4An2q8
rXQKtPYE2AAus6bK5zAe4beW
=IcRz
--END PGP SIGNATURE--

Can a datagrid display more than one table?

See this picture. I only see table '2' can add more table? how?
conn.Open()
Dim sql(3) As String

sql(0) = "select * from products;"
sql(1) = "select * from orders;"
sql(2) = "select * from orders;"
sql(3) = "select * from orders;"

Dim da As System.Data.OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim i As Integer

For i = 0 To sql.GetUpperBound(0)
Try
da = New OleDb.OleDbDataAdapter(sql(i), conn)
Catch e As Exception
MessageBox.Show(e.Message)
End Try
Try
ds = New DataSet("dsTable")
dt = New DataTable(i.ToString)
da.Fill(dt)
'ds.Tables.Add(dt)
ds.Tables.Add(dt)
ds.Merge(dt)
'grd.SetDataBinding(ds, i.ToString)
'MessageBox.Show(ds.Tables(i).TableName)
grd.DataSource = ds
Catch eds As Exception
MessageBox.Show(eds.Message)
End Try
MessageBox.Show(i)
Next
conn.Close()
conn = Nothinglet me answer it myself.

for i = 0 to n
da.Fill(dt)
ds.Merge(dt)
next
datagrid.datasource = ds