|
Try:
declare @myid smallint <br />
select @myid = 53 <br />
<br />
declare @sql varchar(30)<br />
<br />
set @sql = 'kill ' + cast(@myid as varchar(20))<br />
<br />
exec (@sql)
Wout Louwers
|
|
|
|
|
Hi Guys..
I want to ask if there is a way to use *.sql files generated by MsSql Server (or written by someone )in the C#.
For e.g. i have a *.sql file and i want to create tables in my program using this script..
Is there any class in C# or u guys can give me a link ?
thx a lot..
good coding..!
|
|
|
|
|
|
hi all,
i have an procedure as follows:
ALTER PROCEDURE dbo.InsertNewSite
(
@param_url nchar(250),
@param_ip nchar(15),
@param_username uniqueidentifier,
@param_time datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO sites
(url, ip_address, username, access_time)
VALUES (@param_url,@param_ip,@param_username,@param_time)
RETURN SELECT id FROM sites WHERE sites.url = @param_url AND sites.access_time = @param_time
and it is called from code like this:
string newSiteId = "";
lock (this.selectAllSitesNotBannedTableAdapter)
{
newSiteId = (string)this.selectAllSitesNotBannedTableAdapter.InsertNewSiteNotBanned(host, requestor, new Guid("bcadf8d7-c96d-4981-bf26-de9915ac6d49"), DateTime.Now);
}
but newSiteId always returns null?
now to explain the problem i am trying to solve with this code:
i get some data i want to store into the db, the db generates the GUID for the new row, and i then need that GUID in my application. so rather than reloading the entire datagrid, i just add the row to the grid that i just added to the db.
is it possible to get the GUID of the row just added, in one call to the db, or do i need to call it again with the select that is the return statement above ?
kind regards,
g00fy
|
|
|
|
|
Hi all !
in asp.net(using c#) i Plan this question ,and I think it related to this section , because i get no reply yet , Hope anyone help me !
consider this :
First Record is --> firstname :Joe , lastname :Meech
(in asp.net)i wrote this code for save and add :
For Add :
txtName.Text=String.Empty;<br />
txtFname.Text=String.Empty;
and For Save :
DataRow dr=dt.NewRow();<br />
dr["firstname"]=txtName.Text;<br />
dr["lastname"]=txtFname.Text;<br />
dt.Rows.Add(dr);<br />
da.Update(dt);<br />
dt.AcceptChanges();<br />
int RowTablePosition=0;<br />
RowTablePosition =dt.Rows.Count-1;
My difficult is :
if Page.DataBind() , and if want to add a new Record , and then Save this Record --> the First Record is added to the end of the table , No this New Record !!
But if Page or Text is not bind , this new Record is Saved !
i want to save , when "Page.DataBind();"
thanks if anyone help me !
mostafa hosseiny b.
|
|
|
|
|
hello to all ..
i am trying to install MS SQL SERVER 2000 on my pc ...
i have the Standard Edition
but when i run the setup i recive an error that say
that this program is not supported on my OS ..
i have Windows XP Pro , Server Pack 2 .
|
|
|
|
|
microuser_2000 wrote: when i run the setup i recive an error that say
that this program is not supported on my OS ..
i have Windows XP Pro , Server Pack 2 .
The error message is correct. SQL Server 2000 Standard Edition will NOT run on Windows XP.
See the SKU Matrix on the SQL Server 2000 Product Overview[^] page for more details.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hello again, everyone.
I'm querying a table of usernames, some of which have single quotes in them. I'm hesitant to make a stored procedure to perform a search and replace on the entire table, so I was trying to find a way to make the query tolerate the single quote using the REPLACE() method, but I haven't met with any success so far.
I've tried the following:
SELECT REPLACE(Name, ''', '%#39;') FROM Table
which it obviously doesn't like because of not having matching sets of single quotes. I've also tried replacing it with the ASCII equivalent to no avail, as well.
The particular error message I'm receiving in my ASP.Net page is:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'BRIEN'. Unclosed quotation mark before the character string ''.
The record in question returns the string O'BRIEN.
It is worth noting that the operation is not aborted when this error happens, and it does in fact return the record in the proper format. It does, however, not return any records after that one, which exist.
Any ideas? Thanks in advance for your help.
-------------------
abort, retry, fail?
|
|
|
|
|
jszpila wrote: I'm querying a table of usernames, some of which have single quotes in them. I'm hesitant to make a stored procedure to perform a search and replace on the entire table, so I was trying to find a way to make the query tolerate the single quote using the REPLACE() method, but I haven't met with any success so far.
I'm not sure I understand the logic of your thinking. How would using REPLACE() tolerate single quotes?
It looks like the code is venturing into a high possibiliy of an Injection Attack. See SQL Injection Attacks and Tips on How to Prevent Them[^]
If you are issuing a SQL Statement from a .NET application like this:
SELECT * FROM MyTable WHERE Name = 'O'Brian' then you should use a paramter instead
SELECT * FROM MyTable WHERE Name = @Name
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hi
when i m working on reporting services' rdl file, following error is coming -
The table ‘Table1’ has rows that contain a different number of cells than the number of the columns in the table (including cells that span more than one column).
I think i m trying a bit too complex interface with rdl files, lot of merged cells are used, i may rpovide html prototype of the work i m doing but it'd b better for me if u can give me some hint by reading error only.
Thanks in advance
Utsav
|
|
|
|
|
I have a WebForm that will display paging data to customers.My purpose is
to let the customers can select any page number.The WebForm calls a stored
procedure in SQL2000:
ALTER PROCEDURE MyProc<br />
(@RecordPerPage int,<br />
@PageNumber int<br />
)<br />
AS<br />
select top @RecordPerPage *<br />
from MyTable<br />
where KeyField><br />
select Max(KeyField) from <br />
(select top @RecordPerPage*(@PageNumber-1) KeyField<br />
from MyTable<br />
order by KeyField) a<br />
order by KeyField
Unfortunately,this procedure has a syntax error.It seems that the 'TOP'
keyword can not be followed by an Expression.
How could I do?
I do not want to dynamicaly generate SQL string in client side(there are a dozen of reasons) and --
This is a large table,I will not use DataAdapter.Fill and DataGrid.AllowPaging.
Need your help!
Thanks a lot!
|
|
|
|
|
You should build your SQL statement as a nvarchar and then use sp_executesql:
<br />
DECLARE @query NVARCHAR(500)<br />
<br />
SET @query = N'select top ' + CAST(@RecordPerPage AS NVARCHAR(5)) + N'* from MyTable...<br />
<br />
sp_executesql @query <br />
look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
--> Nick
|
|
|
|
|
 hi
I am having problem in procedure i am getting error as 'Scope_Identity' is not a recognized function name.I read an article that @@IDENTITY should be used is it helpful.The procedure is for the inserting the details.
thanks
kal2na2
|
|
|
|
|
Sounds like you're using SQL Server v7 which does not support SCOPE_IDENTITY() - you must use @@identity however be careful, triggers will alter @@identity to reflect the result of the trigger and not your proc.
--> Nick
|
|
|
|
|
In queries I use the function, NVL quite a bit.
,nvl(some_column,'DefaultText')
Whenever some_column has a NULL value, the query always returns DefaultText for the column's value. Is there a smilar function for a varchar2 type column that would allow me to obtain DefaultText if some_column contained a string of all space characters of any length. I'm trying to develop a work around to a bug with a library that treats a column with all space characters as an empty string, regardless of how many space characters have been stored.
Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
We might end up with a large whack of code in stored procedures in our SQL2005 database and I was wondering if anyone knew of a code-coverage tool that could work against SPs? The SPs will be T-SQL and also use CLR.
I haven't looked into it that deeply but I am assuming the code-coverage tool in VS2005 can't do this.
regards,
Paul Watson
Ireland
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Excuse my ignorance, Paul, but what is a code-coverage tool? Are you looking for version control, or a SQL editor, or some other animal?
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
OK let me see if I can explain this without looking it up again 
Code coverage presents metrics on how much of your code is covered by tests (e.g. unit tests.) The idea is that with a large code base you can't be sure the unit tests you have are testing all aspects of the code. So you run the code coverage tool against your code, including your tests, and it returns metrics on what is and what is not covered.
Within that you get all sorts of variations such as block coverage, conditional coverage, line coverage, method coverage etc.
I currently only know of code coverage tools that work against code in assemblies, not in SQL databases.
regards,
Paul Watson
Ireland
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Hi all:
I'm pretty new at db programming, but over the past year have a come to a decent understanding of connections, data adapters, and datasets. Here's something though that I just can't think through.
Here's what I'd like to do.
1. My music collection and audio file information are in a MSDE database with several tables containing workid (PK), artist information, style, filename (location of the sound file), etc.
2. I created XML file to use as a playlist, something like
<playlist>
<workid>2</workid>
<workid>15</workid>
<workid>4</workid>
<workid>47</workid>
</playlist>
3. I saved the file something like saturdaynightmusic.xml
4. Using the Build Schema tool in VS Studio, I created a schema that joins the Music table with the XML playlist with workid as the key.
5. Now I have a .xsd file which contains the relationship, an .xml file to control the order of play, and a database table with fields pointing to the audio files, album and artist info.
However, I can't for the life of me, figure out how to make them work together.
Conceptually, I've got it - Read each element in the XML file, put them into a list (such as a list box), select first song in the list, it populates the fields with the artist info, title, then plays, the next song is selected, played .... until the end.
Here's how I think it could be done...
1. The XML playlist workid is stored as a variable.
2. The variable is added to a select command which is then run against a view or stored procedure
3. The database returns the information which is bound to textboxes, lists, and the filename variable to play the audio file.
4. Loop through the above steps until the end of the list
Am I on the right track???
Thanks for any help in clarifying this for me.
Norm
-- modified at 17:58 Tuesday 15th November, 2005
|
|
|
|
|
Hi,
I have two objects, both point to each other in OO.
e.g.
Class Father
{
private int id; //id of father
private Son son;
}
Class Son
{
private int id; //id of Son
private Father father;
}
so I need to have two tables in database as follow,
Table Father :
(id, SonId)
Table Son :
(id, FatherId)
The problem is, should I eliminate the one of the reference in one of the table, e.g.
Table Father :
(id)
Table Son :
(id, FatherId)
so I just have the fatherID in the son table.
But if I remove the sonID from the father table,
I need to join the two tables when I retrieve fathers
from the father table.
So what are the pros and cons of the two schema design ?
Thanks
|
|
|
|
|
Analyze a bit...
Without going into much trouble.. the relationship between father and son tables is as follows: A Father can have 1 or more sons, but a Son can only have a father at any one time. If you follow this, then only having idFather in the son table it's more than enough to make the searches you need.
Anything else you need to add?
daniero
|
|
|
|
|
daniero wrote: Father can have 1 or more sons
i would say so, but looking at his class definitions, a father have only one child
ppp001 wrote :
Class Father {
private int id;
private Son son; <-- see what i mean
}
Class Son {
private int id;
private Father father;
}
maybe a modeling mistake, but he wrote it !
TOXCCT >>> GEII power
[toxcct][VisualCalc]
-- modified at 11:29 Wednesday 16th November, 2005
|
|
|
|
|
i would like to know how that i can do something like this:
this is my query: select a.col2, a.col3, b.col2 from tableA a
INNER JOIN tableB
On a.col1 = b.col1
now if i use a fill from a data adapter i get a dataset with one table with my results.
what i want is a dataset: with 2 tables one with the nfo from table a, and one with the info from table b
how can i do this? or isn't this possible?
|
|
|
|
|
Yes, query tablea, and fill a 'tableA' in a dataset, and then query tableb, and fill a 'tableB' in the dataset, you can have a preset dataset with the relationship or generate it in code.
daniero
|
|
|
|
|
how do you mean a preset dataset with relationship?
the thing i want to avoid is: query table a and fill it in the dataset, than query table b and fill the same dataset, and then put the relationships on it.
I want to do this with one query to the db, is this possible? to execute your query, so your adapter will fill your dataset with different tables and relationships.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
