|
Few issues:
- you cannot select from stored procedure directly. Just execute it to get the results
- do not start the name in your own procedures with sp_ . That's reserved for system procedures.
- if you want to find foreign keys using a select statement, query sys.foreign_key_columns
The need to optimize rises from a bad design
|
|
|
|
|
you can do this with table functions
CREATE FUNCTION MyFunction()
RETURNS @tbl TABLE(COLUMN_NAME VARCHAR(50))
AS
SELECT
'A'
INTO @tbl
FROM someTable
then
SELECT * FROM MyFunction() WHERE COLUMN_NAME='A'
|
|
|
|
|
I saw the INTO @tbl and thought you were auto creating the column, as in Select * into MyTable from YourTable
Every had the hairs on the back of your neck stand up - that was my first reaction .
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I wanted to connect to a remote SQl 2005 database via VPN on Windows XP. After configuring VPN connection on my local area Network. I dont seem to login to the Sql server database.
I am running Service pack 2.Does it have any thing to do with Operating System?
Could you please advice?
Thank you
|
|
|
|
|
By default SQL Server does not permit remote connections. You have to enable TCP/IP on SQL Server.
If that is not the issue, then you might want to look at your firewall settings.
|
|
|
|
|
hello,
i am new to programming and i have gotten far in learning C# in the .Net environment. i am at the point where i want to create Databases with SQL and link them to my programs but i cannot seem to find any headway or pointers in how to do that.
can anyone help me on this issue. i am searching the forums for any clue on how to start using SQL but without luck.
any suggestion is welcome. thank you
|
|
|
|
|
Try searching Google for ADO.NET, that should give you loads of articles.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
download samples here about how to connect to a database
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
how do you extract a photo from database in a JPEG2000 format and binary version of the photo ,i have a table in my database that stored photos like this eg dbfec061-a467-4833-8db3-9badfe086e7a.jp2
thanks
regards paula
|
|
|
|
|
Basically you can do something like:
- you read the data in binary form
- store the data to a temp file
- open the temp file using relevant program
This article is a little bit old, but it should get you to start: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]
Hope it helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
download a sample code here, search for something like load/save picture in sql server. You can store in database picture or gifs as well, videos i dont know but i think it works as well.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
I want to retrieve all the column names by a query of a table in SQL SERVER 2005.
For Example: I Have a table: Table1, It has 5 columns: Column1,Column2,Column3,.......
So, I want to write a query that will display all these column names.
Table1
--------
Column1
Column2
Column3
Column4
Column5
I write this query to do the same:
Select column_name, data_type from user_tab_columns where table_name = user_details
But it is not working. Is this a right query or some mistakes are there.
|
|
|
|
|
astrovirgin wrote: But it is not working. Is this a right query or some mistakes are there.
Well, if its not working....
Where to start?
The error message you get is pretty obvious
Invalid object name 'user_tab_columns'.
I'll give you 1 hint (and there are several ways of doing this), look at information_schema.columns
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
The information_schema.columns technique only works for SQL Server.
I use a lot of different databases, and I don't have the constraint that it be "in SQL".
Here's a portion of my DatabaseAccessor[^]:
System.Data.DataTable result = null ;
try
{
result = this.ExecuteReader
(
string.Format
(
"SELECT * FROM [{0}] WHERE 0=1"
,
TableName
)
).GetSchemaTable() ;
result.TableName = "Columns" ;
|
|
|
|
|
Fair enough, although the OP did specify sql server. I suspect (although I have never tried it) that you could use SMO as well.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: the OP did specify sql server
Oh, yes, absolutely, and he wanted it to be an SQL statement too, but after finding one way for SQL Server, another way for Oracle, another way for Ingres, and then facing MySQL, Caché, FireBird, Access, etc. I realized I already had a way that works with all of them.
I aim for database agnosticism. (I do, however, prefer SQL Server.)
|
|
|
|
|
is there a way to do the above? i found solutions but limited to a single row only and i don't want to do a 'cursor'-like solution, having to iterate. also my requirement is the select includes a where clause something like below:
[CODE]select
*
from
myTable
where
dateReceived between @startDate and @endDate
[/CODE]
and get say, 10 random rows from the result set.
i've tried the order by rand() but having run it a couple of times (in intervals greater than clock ticks) i get the same set of results.
thanks in advance!
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
currently i have the following:
declare @milliSecNow int
set @milliSecNow = datepart(Ms, getdate())
print milliSecNow
select
top 100000 *
into
#randByDate
from
myTable
where
recv_date between '2007-01-01' AND '2007-12-31'
--order by
-- rand(@milliSecNow)
select
top 10 firstColumn
from
#randByDate
order by
rand(@milliSecNow)
drop table #randByDate
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
select
top 10 *
from
myTable
where
dateReceived between @startDate and @endDate
order by newid()
|
|
|
|
|
Hi
Can anyone tell is it possible use/store data to a sql database without having
database server running itself. I mean do you need to have sql installed
onto your machine if you want to use application program that stores/retrieves data from databases.
Or is just driver enough?
For example.
I made an application program that has a GUI, connects to sql database file.
So is it enough to install to client machine 1-)Exe 2-)Database Driver 3-)Database file(for example .mdb) to make it run correctly (no connection to server)?
Thanks...
|
|
|
|
|
as a stand alone program? the program was a database attached? yes
if you have to connect the a database, the server as to be on.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
SQL Server needs to be running somewhere, not just exist as an MDB file. If you are embedding a SQL Express file then (I think) it need to be installed. You should have 2 deployment packages, 1 for the DB and 1 for the app.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The functionality you described is possible with SQL Server Compact Edition. It needs no installing and the database is included in to the project and deployed with it.
However in CE you have few restrictions including:
- only one user can use it (not suitable for multi-user environments)
- no locking is possible
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
I understand for performance reasons, that the section in bold can be rewritten using a join, can anyone help me with the syntax. I am trying to select the most recent employee record where effective_date <= payperiod_enddate.
Thanks
select
t1.department,
t2.status,
t3.date_key as paycheck_date_key,
t4.date_key as start_date_key,
t5.date_key as end_date_key,
(select top 1 employee_key from d_employee where (employee_id = EEID and employee_number = EmpNum) and
(CAST(FLOOR( CAST( effective_date as float ) )as datetime) <= PeriodEndDate) order by effective_date desc) as employee_key,
t6.payment_type_key as payment_type_key,
from tblDetail t1
inner join tblHeader t2
left join d_date t3 on isnull(t2.PayDate, '01/01/1900') = t3.the_date
left join d_date t4 on isnull(t2.PeriodStartDate, '01/01/1900') = t4.the_date
left join d_date t5 on isnull(t2.PeriodEndDate, '01/01/1900') = t5.the_date
on t1.checkID = t2.checkID
left join d_PAYMENT_TYPE t6 on isnull(t1.PehEarnCode, '-999') = t6.payment_type_code
|
|
|
|