Load image file

HI

how to load a image file in to a sql server.

do we need c# code for it or can we use just a t-sql procedure to upload it.

In my application we don't have ant c# or other application. we have only t-sql

thanks

sandipan

If you are planning to use SSIS, the Import Column transform could be useful for this purpose.

Thanks.

Load an XML doc from a file

I'm trying to do something similar to the code below, but I want to load the
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
Alain Quesnel wrote:

> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Load an XML doc from a file

I'm trying to do something similar to the code below, but I want to load the
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comAlain Quesnel wrote:

> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Works like a charm.
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OLcWpOogHHA.2368@.TK2MSFTNGP04.phx.gbl...
> Alain Quesnel wrote:
>
> One way is like this
> SET @.xmlDocument = (
> SELECT * FROM OPENROWSET(
> BULK 'C:\dir\subdir\subdir\file.xml',
> SINGLE_BLOB
> ) AS x
> );
> then you can use the variable @.xmlDocument as you have done before with
> the stored procedure.
> See the documentation here:
> <http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

load a report with a parameter...

I have a parameter on my report.

the report show someting in the load event.

the code in Load_ event is something like this:
{
m_strCode=txtCode.Text;
repDoc.SetParameterValue("stringP",m_strCode);
repDoc.SetDataSource(dsCodes1);
repViewer.ReportSource-repDoc;
repViewer.RefreshReport();
}

also i reload the Data when i push the button,so the code is the same:

Button1_click(...)
{
m_strCode=txtCode.Text;
repDoc.SetParameterValue("stringP",m_strCode);
repDoc.SetDataSource(dsCodes1);
repViewer.ReportSource=repDoc;
repViewer.RefreshReport();
}
also there is 2 lines in the repViewer_ReportRefresh(....)
{
repDoc.SetParameterValue("stringP",m_strCode);
repViewer.ReportSource=repDoc;
}

The problem is that,if i put this code in Load_ event too as shown above, because i want to show the data with that parameter on report, from the start, it still ask me to gave to the report the parameter.WHY?

ThanksYou need to supply the parameter value if any. Otherwise you have to suppress the popup by setting enable popup property to false|||I have supplied the value, if you follow closely the code, at the beginning of the message, i wrote, the LOAD event, where i give the parameter value.so even if i suply value, still ask me,for value.this was the problem! why?|||If you supply parameter values, you wont be prompted to give them. Open the report and do verify database

Llimit on Lines of code in Query Builder

Does anyone know if there is a limit on characters or lines of code that can be used to create your dataset?

I cut and pasted some sql code of a report in there and it's cutting off the last 100 lines or so.

If you select the report in the solution explorer, right click and 'view code', you can then find your dataset in the rdl and insert the sql directly in there.

Live communication Address book service

Hi, when I try to activate my address book, i get the error "There is no pool available for this operation. Exit the wizard. (Error Code: 0xc3EC78D9)".

I have verified my mmc shows that LCS is at SP1, it is activated, and
functioning properly (users able to interact via OC2005). It is locally
installed on the standard edition server.

As per the Technet reference :

Possible causes include:

?

The Live Communications Server service is not running on the pool or server.

?

No servers or pools in your topology have been upgraded to Service Pack 1.

?

The Standard Edition Server is not activated, or none of the Enterprise Edition Servers within a pool are activated. Check your servers.

?

You want to run the Address Book Service on a Standard Edition Server, but you have installed the Address Book Service on a separate computer. The Address Book Service must be installed locally on a Standard Edition Server to work in this deployment.

Live communication Address book service

Hi, when I try to activate my address book, i get the error "There is no pool available for this operation. Exit the wizard. (Error Code: 0xc3EC78D9)".

I have verified my mmc shows that LCS is at SP1, it is activated, and
functioning properly (users able to interact via OC2005). It is locally
installed on the standard edition server.

As per the Technet reference :

Possible causes include:

?

The Live Communications Server service is not running on the pool or server.

?

No servers or pools in your topology have been upgraded to Service Pack 1.

?

The Standard Edition Server is not activated, or none of the Enterprise Edition Servers within a pool are activated. Check your servers.

?

You want to run the Address Book Service on a Standard Edition Server, but you have installed the Address Book Service on a separate computer. The Address Book Service must be installed locally on a Standard Edition Server to work in this deployment.

|||

What about that same error in Office Communications Server 2007? I get that same error code when trying to set up my edge server.

Little puzzle on data selection

I have the following data (very simplified version)

TransactionId Agent_Code
---- ----
191462 95328C
205427 000024C
205427 75547C

Agent Code 75547C is a corporate agent. The others are not. I have a
list of corporate codes so I can query against it, BUT what I want to
do is...

Return a unique TransactionId and max of the AgentCode, but if the
Agent is a corporate agent, I need to return max of the corporate agent
codes. We can have multiple agents against the transaction and
sometimes have a mix of corporate and none corporate agents. What we
need to do is see the corporate adviser if there is one. I only want 1
record per TransactionId.

We derive more data (sales hierarchy) from this, so are not interested
in anything other than the maximum, but need to know if it was
corporate which therefore gives me a different hierarchy later.

Ideally I want to do this in a view and not use an SP. I can then use
this in my main view. If I have to resort to an SP, then so be it, but
I would appreciate any helpful comments (or even better, the answer)
Thanks

Ryan"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1101996460.738947.289740@.z14g2000cwz.googlegr oups.com...
> I have the following data (very simplified version)
> TransactionId Agent_Code
> ---- ----
> 191462 95328C
> 205427 000024C
> 205427 75547C
> Agent Code 75547C is a corporate agent. The others are not. I have a
> list of corporate codes so I can query against it, BUT what I want to
> do is...
> Return a unique TransactionId and max of the AgentCode, but if the
> Agent is a corporate agent, I need to return max of the corporate agent
> codes. We can have multiple agents against the transaction and
> sometimes have a mix of corporate and none corporate agents. What we
> need to do is see the corporate adviser if there is one. I only want 1
> record per TransactionId.

I would think something like (Obviously untested):

--No Corp Agent
Select TransactionID, Max(Agent_Code) from Transtable a where NOT
EXIST(select * from TransTable b inner join CorpAgents c on b.Agent_Code =
c.Agent_Code where b.TransactionID = a.TransactionID)

UNION ALL
--Corp Agent
Select TransactionID, Max(Agent_Code) from TransTable a inner join
CorpAgents c on a.Agent_Code = c.Agent_Code

Good Luck

Jim|||[posted and mailed, please reply in news]

Ryan (ryanofford@.hotmail.com) writes:
> Return a unique TransactionId and max of the AgentCode, but if the
> Agent is a corporate agent, I need to return max of the corporate agent
> codes. We can have multiple agents against the transaction and
> sometimes have a mix of corporate and none corporate agents. What we
> need to do is see the corporate adviser if there is one. I only want 1
> record per TransactionId.

SELECT TransactionID, coalesce(maxcorp, maxanyone)
FROM (SELECT t.TransactionID, maxanyone = MAX(t.AgentCode),
maxanyone = MAX(a.AgentCode)
FROM transactions t
LEFT JOIN agents a ON t.AgentCode = a.AgentCode
GROUP BY t.TransactionID)

And as you surely know, had you included CREATE TABLE, INSERT and expected
output, the solution would have been tested. Now it's not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks ! Will give this a try.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95B3F3141F310Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Ryan (ryanofford@.hotmail.com) writes:
> > Return a unique TransactionId and max of the AgentCode, but if the
> > Agent is a corporate agent, I need to return max of the corporate agent
> > codes. We can have multiple agents against the transaction and
> > sometimes have a mix of corporate and none corporate agents. What we
> > need to do is see the corporate adviser if there is one. I only want 1
> > record per TransactionId.
> SELECT TransactionID, coalesce(maxcorp, maxanyone)
> FROM (SELECT t.TransactionID, maxanyone = MAX(t.AgentCode),
> maxanyone = MAX(a.AgentCode)
> FROM transactions t
> LEFT JOIN agents a ON t.AgentCode = a.AgentCode
> GROUP BY t.TransactionID)
> And as you surely know, had you included CREATE TABLE, INSERT and expected
> output, the solution would have been tested. Now it's not.|||>> Return a unique transaction_id and max of the agent_code, but if
the agent is a corporate agent, I need to return max of the corporate
agent codes. <<

Just for fun, try this version:

CREATE VIEW CorpTrans (transaction_id, agent_code)
AS
SELECT transaction_id,
COALESCE(
MAX(CASE WHEN T1.agent_code IN (SELECT agent_code FROM
CorpAgents)
THEN T1.agent_code ELSE NULL END) -- corp_agent,
MAX(CASE WHEN T1.agent_code NOT IN (SELECT agent_code FROM
CorpAgents)
THEN T1.agent_code ELSE NULL END) -- non_corp_agent
) AS agent_code
FROM Transactions AS T1
GROUP BY Transaction_id;

You could also drop the COALESCE (), if it would be more useful to see
both kinds of agents. id the number of corporate agents is small enugh
to fit into main storage, this might actually be a good way to do it!sql

Little Lock Symbols

WE are new to SQL Server 2005. My team memebers and I have the same login-in
as a group. One member developed some stored procedure code that I am trying
to review , but there are thse little lock symbols on the file icon. how can
I see the file?
Do you mean when you open the file in Management Studio, the tab at the top
has a lock icon? This means the file is read-only, possibly because it is
checked into source control. If this is not what you are talking about,
then you can explain (a) where you see the lock, and (b) what it means that
you can't "see" the file?
A
"Candyman" <Candyman@.discussions.microsoft.com> wrote in message
news:F8A96B55-7ED2-4913-94EF-FB215804AE17@.microsoft.com...
> WE are new to SQL Server 2005. My team memebers and I have the same
> login-in
> as a group. One member developed some stored procedure code that I am
> trying
> to review , but there are thse little lock symbols on the file icon. how
> can
> I see the file?
|||WE are in Management Studio. On the left hand side is the Obkect Explorer,
We have many listings under databases. The tree structure looks like
Databases\db_ReportSource\Programmability\Stored Procedures. . . Then there
is a file dbo.SQL_myquery listed and the adjacent icon looks like a light
blue piece of paper with a darker blue stripe at the top. Then there is a
little yelow lock on the right lower corner of the icon. My team mate can
right click on the file and see a Modify option to show me the code. That
Modify option is greyed out to me. ( and four other procs he created.) How do
I see the code? Is there another way to get to it?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do you mean when you open the file in Management Studio, the tab at the top
> has a lock icon? This means the file is read-only, possibly because it is
> checked into source control. If this is not what you are talking about,
> then you can explain (a) where you see the lock, and (b) what it means that
> you can't "see" the file?
> A
> "Candyman" <Candyman@.discussions.microsoft.com> wrote in message
> news:F8A96B55-7ED2-4913-94EF-FB215804AE17@.microsoft.com...
>
>
|||If you just want to see it and not modify it, then:
(1) Right-click the database name and select new query
(2) Make sure you are in Results to Text (Ctrl+T)
(3) Type:
EXEC sp_helptext 'SQL_myquery';
(4) Hit F5 (or the Execute button on the toolbar)
"Candyman" <Candyman@.discussions.microsoft.com> wrote in message
news:119BA461-C65C-4755-83CA-E7EF1C4CAE3B@.microsoft.com...[vbcol=seagreen]
> WE are in Management Studio. On the left hand side is the Obkect
> Explorer,
> We have many listings under databases. The tree structure looks like
> Databases\db_ReportSource\Programmability\Stored Procedures. . . Then
> there
> is a file dbo.SQL_myquery listed and the adjacent icon looks like a light
> blue piece of paper with a darker blue stripe at the top. Then there is a
> little yelow lock on the right lower corner of the icon. My team mate can
> right click on the file and see a Modify option to show me the code. That
> Modify option is greyed out to me. ( and four other procs he created.) How
> do
> I see the code? Is there another way to get to it?
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||if I 'check (green check)' it, the programs returns: "query excecuted
successfully".
Then when I try to run as suggested the command errors out saying "There is
no text for object 'SQL_myquery'"
Could the file actuallly be residing on the other users machine and not in
SQL Server? or network?
"Aaron Bertrand [SQL Server MVP]" wrote:

> If you just want to see it and not modify it, then:
> (1) Right-click the database name and select new query
> (2) Make sure you are in Results to Text (Ctrl+T)
> (3) Type:
> EXEC sp_helptext 'SQL_myquery';
> (4) Hit F5 (or the Execute button on the toolbar)
>
>
> "Candyman" <Candyman@.discussions.microsoft.com> wrote in message
> news:119BA461-C65C-4755-83CA-E7EF1C4CAE3B@.microsoft.com...
>
>
|||> if I 'check (green check)' it, the programs returns: "query excecuted
> successfully".
Yes, if you hold your mouse over the green check you decided to click, you
will see that means "parse" (syntax check). The success message means that
the command didn't have any syntax errors.

> Then when I try to run as suggested the command errors out saying "There
> is
> no text for object 'SQL_myquery'"
Are you sure your query is running in the correct database? When you
right-clicked "the database" was it db_ReportSource, or some other database?
Are you sure you were in an Object Explorer context of the correct server?
What happens when you execute the following in a properly created New Query
window:
USE db_ReportSource;
GO
EXEC SQL_myquery;
GO
EXEC dbo.SQL_myquery;
GO
?

> Could the file actuallly be residing on the other users machine and not in
> SQL Server? or network?
No, from your earlier description, this is a stored procedure in a database.
There are many differences between a stored procedure in the database and a
file in the file system. They are certainly not the same, and a file
somewhere on your network is certainly not going to magically appear under
the stored procedures node in object explorer.
A
|||Candyman (Candyman@.discussions.microsoft.com) writes:
> if I 'check (green check)' it, the programs returns: "query excecuted
> successfully".
> Then when I try to run as suggested the command errors out saying "There
> is no text for object 'SQL_myquery'"
> Could the file actuallly be residing on the other users machine and not in
> SQL Server? or network?
First of all, it is not a file. It's an object in a database, and there
should indeed be a file for it in the file system, or even better in
the version-control system. But if you co-worker just created the procedure
in a query window, and then closed the window without saving it, there is
no file at all. Just an object in a database.
Normally, though, stored procedures can be disassembled from the database
so you can edit them, which is popular in teams that don't believe in
files or version-control systems.
But this small lock indicates that you can't. There are two possible reasons
for this:
o The procedure was created with the clause WITH ENCRYPTION.
o It is a stored procedure created in a CLR language such as VB .Net or C#.
If you right-click the procedure and selecr Properties, you might be
able to deduce which of the cases it is. If there things like "Property
AnsiNullsStatus is not available..." it is a CLR stored procedure.
In either cases, you will have to ask your co-worker where he has the
source code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The application asked me for some date parameters, which I enterd and
recieved results back so there is something there, but I still can't see the
original code.
Erland might have hit it on found the solution as the file properties do
seem to be encrypted. This is something we will have to discuss withthe
group. Thanks so much for your input and patience.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, if you hold your mouse over the green check you decided to click, you
> will see that means "parse" (syntax check). The success message means that
> the command didn't have any syntax errors.
>
> Are you sure your query is running in the correct database? When you
> right-clicked "the database" was it db_ReportSource, or some other database?
> Are you sure you were in an Object Explorer context of the correct server?
> What happens when you execute the following in a properly created New Query
> window:
> USE db_ReportSource;
> GO
> EXEC SQL_myquery;
> GO
> EXEC dbo.SQL_myquery;
> GO
> ?
>
> No, from your earlier description, this is a stored procedure in a database.
> There are many differences between a stored procedure in the database and a
> file in the file system. They are certainly not the same, and a file
> somewhere on your network is certainly not going to magically appear under
> the stored procedures node in object explorer.
> A
>
>
|||BINGO!
Under properties in the Options section the file has Encrypted set to TRUE
and Replication set to FALSE also ( I guess which means I could not copy the
file which I tried to do also.)
We are baby new to this and I have to bring this up to the author of the
code and to our new “Weekly user group” . Both of which are out today, but
thank you for the info! This is helpful.
I will also suggest we start using Source Safe for version control.
Thanks!
"Erland Sommarskog" wrote:

> Candyman (Candyman@.discussions.microsoft.com) writes:
> First of all, it is not a file. It's an object in a database, and there
> should indeed be a file for it in the file system, or even better in
> the version-control system. But if you co-worker just created the procedure
> in a query window, and then closed the window without saving it, there is
> no file at all. Just an object in a database.
> Normally, though, stored procedures can be disassembled from the database
> so you can edit them, which is popular in teams that don't believe in
> files or version-control systems.
> But this small lock indicates that you can't. There are two possible reasons
> for this:
> o The procedure was created with the clause WITH ENCRYPTION.
> o It is a stored procedure created in a CLR language such as VB .Net or C#.
> If you right-click the procedure and selecr Properties, you might be
> able to deduce which of the cases it is. If there things like "Property
> AnsiNullsStatus is not available..." it is a CLR stored procedure.
> In either cases, you will have to ask your co-worker where he has the
> source code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||> Under properties in the Options section the file has Encrypted set to TRUE
> and Replication set to FALSE also ( I guess which means I could not copy
> the
> file which I tried to do also.)
No, replication and copying a file are not the same thing.
A

Listing procdure/trigger SQL code

I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.

What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.

Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?

Does anybody have any alternative approach to this problem?

Thanks in advance,

MarkI don't understand you problem. EM allows you to script out all the objects of any particular type(s) to separate files. There is nothing that limits you to one object at a time.

listbox selected values

Hi. With VWD i've produced the following code.
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @.liedID)">
<SelectParameters>
<asp:ControlParameterControlID="ListBox1"Name="liedID"PropertyName="SelectedValue"Type="Int16"/>
But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?
Thanks, Kin Wei.

That's not really an easy thing to do. There are a number of "issues" to work around.
1) The WHERE clause would need to be changed to an equate to something like IN.
2) Using IN, you can't use a parameter to represent a list of values.
3) There is no way to get the list control to give you a comma delimited list of selected values.
You can get around the #2 issue by dynamically creating an executing SQL using the EXEC command like this:
DECLARE @.sql varchar(8000)
SET @.sql = 'SELECT * FROM ibs WHERE liedID IN (' + @.liedID + ')'
EXEC(@.sql)
You can get around #3 by building the string yourself by:
1)Use autopostbacks to keep track of the selected values, and store them in a hidden field or a session variable as a comma delimited string.
or
2)On postback, get the list of selectedindexes via listbox1.GetSelectedIndices like this:
dim mystring as string=""
for each x as Integer in listbox1.GetSelectedIndices
mystring &= ",'" & cstr(Listbox1.Items(x).Value) & "'"
next
mystring=mystring.trim(",")
HiddenField.text=mystring
|||Thanks for your post. But I'm not really good at this. Where in the code do I have to make the EXEC command?
<%@. Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns:t ="urn:schemas-microsoft-com:time">
<head runat="server">
<?import namespace="t"
implementation="#default#time2">
<style>
.time {behavior: url(#default#time2);}
</style>
<title>IBSTEST</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Panel ID="Panel1" runat="server" Height="50px" Width="125px" Visible="true">
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"
SelectCommand="SELECT * FROM [ibs] ORDER BY [liedTitel]"></asp:SqlDataSource>
<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource2" DataTextField="liedTitel"
DataValueField="liedID" Font-Names="Arial" Font-Size="X-Small" Height="300px"
SelectionMode="Multiple" Width="350px"></asp:ListBox><br />
<br />
<asp:Button ID="Button1" runat="server" Text="Play" OnClick="Button1_Click" Height="25px" Width="75px" /></asp:Panel>
<br />
<asp:Panel ID="Panel2" runat="server" Height="50px" Width="349px" Visible="false">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"
SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @.liedID) ORDER BY NEWID()">
<SelectParameters>
<asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue"
Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<t:seq repeatCount="1">
</HeaderTemplate>
<ItemTemplate>
<t:par>
<t:audio dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' src='<%# DataBinder.Eval(Container.DataItem, "liedID") %><%# DataBinder.Eval(Container.DataItem, "liedExtensie") %>' type='<%# DataBinder.Eval(Container.DataItem, "liedType") %>' />
<div dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' class="time" timeAction="display">
Titel: <%# DataBinder.Eval(Container.DataItem, "liedTitel") %><br />
Artiest: <%# DataBinder.Eval(Container.DataItem, "liedArtiest") %></div>
<img dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' class="time" timeAction="display" src='<%# DataBinder.Eval(Container.DataItem, "liedImage") %>.jpg' />
</t:par>
</ItemTemplate>
<FooterTemplate>
</t:seq>
</FooterTemplate>
</asp:Repeater>
<br />
<br />
<asp:Button ID="Button2" runat="server" Height="25px" OnClick="Button2_Click" Text="Stop"
Width="75px" /></asp:Panel>
<br />
</div>
</form>
</body>
</html>
and the .cs code is
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Panel1.Visible = false;
Panel2.Visible = true;
}
protected void Button2_Click(object sender, EventArgs e)
{
Panel1.Visible = true;
Panel2.Visible = false;
}
}
After your post, I think it's clear what have to be done. But I don't know where to put the code.
Thank you very much,
Kin Wei

list of tables without indexes

Using SS2000 SP4. I found this code:
USE SMCLMS_Dev;
GO
SELECT*
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY table_name;
GO
but when I run it I get "Invalid object name 'sys.tables'."
Thanks,
--
Dan D.That example uses the sys.tables catalog view and is only valid for SQL
Server 2005. For an equivalent example in SQL Server 2000, try this:
SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
ORDER BY name;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> Using SS2000 SP4. I found this code:
> USE SMCLMS_Dev;
> GO
> SELECT*
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> ORDER BY table_name;
> GO
> but when I run it I get "Invalid object name 'sys.tables'."
> Thanks,
> --
> Dan D.|||That worked. Thanks Gail.
--
Dan D.
"Gail Erickson [MS]" wrote:
> That example uses the sys.tables catalog view and is only valid for SQL
> Server 2005. For an equivalent example in SQL Server 2000, try this:
> SELECT *
> FROM sysobjects
> WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
> ORDER BY name;
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> > Using SS2000 SP4. I found this code:
> >
> > USE SMCLMS_Dev;
> > GO
> > SELECT*
> > FROM sys.tables
> > WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> > ORDER BY table_name;
> > GO
> >
> > but when I run it I get "Invalid object name 'sys.tables'."
> >
> > Thanks,
> > --
> > Dan D.
>
>

list of tables in a db

Hi All
can some one please tell me the transact sql code that can list all tables
in a db.
thanks
ToddThere are many ways to do this. You can query the sysobjects system table or
the INFORMATION_SCHEMA views. Perhaps the easiest one would be :

EXEC sp_tables

--
- Anith
( Please reply to newsgroups only )

List of Tables and Primary Key Foreign Key Names

Dear All,
I am trying to write some SQL that will give a list of all tables and
Primary Key/ Foreign Key constraints in a database. The code below goes part
of the way but not what I would like. It gives me:
Parent Table: Activities
Child Table: ActivitiesLocation
ForeignKey: 1
PrimaryKey: 17
TotalKeys: 1
(where 1 in the column name in the Activities table that is the Foreign Key
and column name 17 is where the Primary Key is to be found.)
Script:
SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS TotalKeys
FROM dbo.sysforeignkeys sf INNER JOIN
dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
dbo.sysobjects so2 ON so2.id = sf.fkeyid
ORDER BY so1.name
How can I get a list of the column name of column no 17 in the Activies
Table and column no 1 in the ActivitiesLocation table. Is there a better way
to get a listing of the PK/FK for a database along with table names?
Thanks again.
Alastair> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database.
http://www.aspfaq.com/search.asp?q=schema%3A|||Check out the information_schema.key_column_usage view. It should get you
started with what you need.
--Brian
(Please reply to the newsgroups only.)
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:9197A5C9-182B-451B-AE40-05DA1F98E62B@.microsoft.com...
> Dear All,
> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database. The code below goes
> part
> of the way but not what I would like. It gives me:
> Parent Table: Activities
> Child Table: ActivitiesLocation
> ForeignKey: 1
> PrimaryKey: 17
> TotalKeys: 1
> (where 1 in the column name in the Activities table that is the Foreign
> Key
> and column name 17 is where the Primary Key is to be found.)
> Script:
> SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
> Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS
> TotalKeys
> FROM dbo.sysforeignkeys sf INNER JOIN
> dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
> dbo.sysobjects so2 ON so2.id = sf.fkeyid
> ORDER BY so1.name
> How can I get a list of the column name of column no 17 in the Activies
> Table and column no 1 in the ActivitiesLocation table. Is there a better
> way
> to get a listing of the PK/FK for a database along with table names?
> Thanks again.
> Alastair
>|||Thanks Aaron and Brian for both your replies. This group is definately on th
e
ball. Exactly what I wanted.
Alastair
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.aspfaq.com/search.asp?q=schema%3A
>
>

List of current publications and subscribers

Is there a stored procedure or some other code ie sql-dmo or vb.net
that will list all the publications of a certain server and another to
list all the subscribtions either at a certian publication or at the
whole server?
Thanks
Shane Lim
Shane,
have a look at sp_helppublication and sp_helpsubscription
Rgds,
Paul Ibison (SQL Server MVP)

List availables tables

How to list available tables in a database using an sql
statement?
The following code does not work with ms-sql:
select table_name from user_tables;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
"joey32" <joey32@.total.net> wrote in message
news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> How to list available tables in a database using an sql
> statement?
> The following code does not work with ms-sql:
> select table_name from user_tables;
>|||i tried with your query, but i get 'table sysobjets not
recognized' from Access.
by the same time, i found a another query that looks like
the same style:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))<>"Msys"))
ORDER BY MSysObjects.Name;
but again i get an error from ms-access:
>> Records can not be read, no read permission on
MSysObjects
by the way, i'm queying via odbc with sql statements, if
that might help you
>--Original Message--
>select name from sysobjects where type='U'. This will
>give you all the names of the tables present in the
>database. Make sure you are in the database in which you
>want to run the query.
>HTH
>>--Original Message--
>>How to list available tables in a database using an sql
>>statement?
>>The following code does not work with ms-sql:
>>select table_name from user_tables;
>>
>>.
>.
>|||When were you planning on mentioning you're using Access? You said sql,
ms-sql, etc.
Try SELECT * FROM MSysObjects or SELECT * FROM MSSysObjects (forget
which)...
"joey32" <joey32@.total.net> wrote in message
news:044301c34725$1c648e60$a301280a@.phx.gbl...
> i get this following message when executing the request:
> >> Could not find '...\INFORMATION_SCHEMA.mdb"
> and that's all i have.
> >--Original Message--
> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> >
> >
> >
> >"joey32" <joey32@.total.net> wrote in message
> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> >> How to list available tables in a database using an sql
> >> statement?
> >>
> >> The following code does not work with ms-sql:
> >> select table_name from user_tables;
> >>
> >>
> >
> >
> >.
> >|||Well, look at the other thread in same post, information is
there, but i might be not very visible to you, sorry for
that mistake.
I runned the query and get this error:
>> no read access to 'MSysObjets' table
by the way, i am quering via odbc using sql statements on a
ms-access database, i think version is 2002 (xp).
so how to i get the MSysObjets table visible for read
access?
>--Original Message--
>When were you planning on mentioning you're using Access?
You said sql,
>ms-sql, etc.
>Try SELECT * FROM MSysObjects or SELECT * FROM
MSSysObjects (forget
>which)...
>
>
>
>"joey32" <joey32@.total.net> wrote in message
>news:044301c34725$1c648e60$a301280a@.phx.gbl...
>> i get this following message when executing the request:
>> >> Could not find '...\INFORMATION_SCHEMA.mdb"
>> and that's all i have.
>> >--Original Message--
>> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
>> >
>> >
>> >
>> >"joey32" <joey32@.total.net> wrote in message
>> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
>> >> How to list available tables in a database using an
sql
>> >> statement?
>> >>
>> >> The following code does not work with ms-sql:
>> >> select table_name from user_tables;
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I got this answer from another ms-access forum wich is
exactly what i was looking for.
It might help somebody.
Thanks for your help.
----
Look in the MSysObjects table (tools|options| check system
objects). You
don't have to unhide the table to run the query, but it
wouldn't hurt for
you to poke around those tables to see what info is
available. Native
access tables are type 1. Attached access tables are type
6.
Select name, type from msysobjects where type = 1 or type
= 6
Richard Bernstein
"swat42" <swat42@.bit.com> wrote in
news:ETjPa.16788$Tx.811910@.news20.bellglobal.com:
> How to list available tables in a db by their table name
with an sql
> query?
> The following piece of code don't work:
> select table_name from user_tables;
>|||Thanks a lot but it's not MS-Access forum (it's MS SQL Server one) so I
don't think this might help anyone here
"joey32" <joey32@.total.net> wrote in message
news:057d01c3473a$0fc65fc0$a301280a@.phx.gbl...
> I got this answer from another ms-access forum wich is
> exactly what i was looking for.
> It might help somebody.