|
Hi all ,
How is it possible to change the schema that a CLR Function/Stored Procedure/etc is deployed to? By Default , it is dbo.
Thanks in advance.
|
|
|
|
|
dbo is not the schema name, it is the owner name. It is an ALIAS that is always set to the currently connected user. If you are using integrated security then log on as the user that you want to own the resulting tables, sprocs, etc...
|
|
|
|
|
hi all. i have an XML file say Data.xml & i have to import data from this xml file into sql server 2005. i havr only this not XSD & TXD file. can any help me?
|
|
|
|
|
hi,I have write a similar code .I hope my code can help you!
using System;
using System.IO;
using System.Xml;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace InsertSql
{
/// <summary>
/// Class1 ??????
/// </summary>
class Class1
{
private static SqlConnection myConnection;
public static ArrayList myfilelist = new ArrayList();
//public static ArrayList DataType = new ArrayList();
public static int insercount;
public static int lostnum;
public static string TableName = ConfigurationSettings.AppSettings["TableName"].ToString();
/// <summary>
/// ??????????
/// </summary>
[STAThread]
static void Main(string[] args)
{
string filepath;
filepath = Console.ReadLine();
//filepath = @"f:\result\test\";
DirectoryInfo di = new DirectoryInfo (@filepath);
CheckDir(di);
Console.WriteLine("????????,?????????y/n");
string flag =Console.ReadLine();
if(flag.ToLower().Equals("y"))
{
//GetArrayList(DataType,string appname)
IEnumerator filelist = myfilelist.GetEnumerator();
while(filelist.MoveNext())
{
ReadXml(filelist.Current.ToString());
}
Console.ReadLine();
}
Console.WriteLine("????????,??????"+insercount+"???,????"+lostnum+"???!");
Console.ReadLine();
}
public static ArrayList GetArrayList(ArrayList arrname,string appname)
{
string Datafile = ConfigurationSettings.AppSettings[appname].ToString();
string[] temp = Datafile.Split('|');
for(int i=0;i<temp.Length;i++)
{
arrname.Add(temp[i].ToString());
}
return arrname;
}
public static void ReadXml(string filepath)
{
ArrayList XmlNodeNamelist = new ArrayList();
XmlDocument doc = new XmlDocument();
try
{
doc.Load(filepath);
if(doc.HasChildNodes)
{
XmlNodeList mylist = doc.GetElementsByTagName("item");
GetArrayList(XmlNodeNamelist,"XmlNodeName");
string[] datalist = new string[XmlNodeNamelist.Count];
foreach(XmlNode personElement in mylist)
{
for(int i=0;i<XmlNodeNamelist.Count;i++)
{
if(XmlNodeNamelist[i].ToString()==personElement.Attributes["name"].Value)
{
datalist[i]=personElement.FirstChild.Value;
}
}
}
InsertDate(datalist,filepath);
}
}
catch(Exception xmlerr)
{
Console.WriteLine(xmlerr);
}
}
private static void InsertDate(string[] data,string filepath)
{
ArrayList DataFilelist = new ArrayList();
GetArrayList(DataFilelist,"DataFile");
Open();
string SqlCmdTxt = "Insert into "+TableName+" (";
for(int i=0;i<data.Length;i++)
{
if(i>data.Length-2)
{
SqlCmdTxt=SqlCmdTxt+DataFilelist[i].ToString();
}
else
{
SqlCmdTxt=SqlCmdTxt+DataFilelist[i].ToString()+",";
}
}
SqlCmdTxt = SqlCmdTxt+")values(";
for(int i=0;i<data.Length;i++)
{
if(i>data.Length-2)
{
SqlCmdTxt=SqlCmdTxt+"'"+data[i].ToString();
}
else
{
SqlCmdTxt=SqlCmdTxt+"'"+data[i].ToString()+"',";
}
}
SqlCmdTxt = SqlCmdTxt+"')";
SqlCommand comm = new SqlCommand(SqlCmdTxt,myConnection);
try
{
comm.ExecuteNonQuery();
Close();
Console.WriteLine("??????--->"+data[1]);
insercount++;
FileInfo file = new FileInfo(filepath);
FileMove(file);
}
catch(Exception err)
{
lostnum++;
Console.WriteLine("???????!"+SqlCmdTxt);
Console.WriteLine(err);
}
}
private static void FileMove(FileInfo file)
{
string filepath = file.DirectoryName+"bak\\";
DirectoryInfo di = new DirectoryInfo(filepath);
if(!di.Exists)
{
di.Create();
}
file.MoveTo(filepath+file.Name);
}
private static void Open()
{
// ???????
if (myConnection == null)
{
myConnection = new SqlConnection(ConfigurationSettings.AppSettings["dbConnStr"]);
}
if(myConnection.State == ConnectionState.Closed)
{
try
{
///???????
myConnection.Open();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
///????????????
}
}
}
/// <summary>
/// ???????
/// </summary>
public static void Close()
{
///??????????
if(myConnection != null)
{
///???????????
if(myConnection.State == ConnectionState.Open)
{
myConnection.Close();
}
}
}
static ArrayList CheckDir(DirectoryInfo di)
{
foreach(FileInfo fi in di.GetFiles())
{
myfilelist.Add(di+"\\"+fi.Name);
System.Console.Write(di+"\\"+fi.Name+"\n");
}
foreach(DirectoryInfo dic in di.GetDirectories())
{
CheckDir(dic);
}
return myfilelist;
}
}
}
my english is very bad!
|
|
|
|
|
Your code is unnecessarily complicated and inefficient. SQL Server has had the ability to directly handle XML data since version 2000.
This article[^]explains how to use the OPENXML clause to pass XML data to a stored procedure.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
yeah,I know that I did not optimize my code,but I do not think that it is complicated and unnecessarily .
Thanks for your advice ,can you tell me ,if I want use the openxml ,since version 2000,all have this function itself?
if you say yeah,I would be very happay, opposition,if need me install it,I do not think so!
and if anybody need my help,please give me message
my english is very bad!
|
|
|
|
|
suyuan1984 wrote: but I do not think that it is complicated and unnecessarily
You're wrong. It is. By using the OPENXML clause, you could have cut out most of your code. It's a core part of the T-SQL language. Read the documentation to learn how to use it.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
pmarfleet wrote: Your code is unnecessarily complicated and inefficient
Did you actually look through it?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi friends
I have one Table called tblCategory.
I have three Column CatID, CatName, ParentID
I have many records in this Table
CatID CatName ParentID
1 Cat1 0
2 Cat2 1
3 Cat3 1
4 Cat4 2
5 Cat5 2
6 Cat6 0
7 Cat7 6
8 Cat8 6
9 Cat9 7
10 Cat10 7
11 Cat11 8
Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]
I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]
Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]
I want result looks like as
Cat1 Cat6 ->>>>>ParentID 0
- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
--No record --Cat11
Can anybody give me solution?
Thanks
please don't forget to vote on the post that helped you.
|
|
|
|
|
try it !!!
declare @cid int
set @cid = 2
declare @S int
declare @E int
insert into temptblCategory
select *,-1 from tblCategory where parentId in(@cid)
select @E=count(cid) from temptblCategory where status=-1
set @S=1
while @S<=@E
begin
select top 1 @cid=cid from temptblCategory where status=-1
update temptblCategory set status=1 where cid=@cid
insert into temptblCategory
select *,-1 from tblCategory where parentId in(@cid)
select @E=count(cid) from temptblCategory where status=-1
end
N.Rajakumar B.E.,
Application Developer
|
|
|
|
|
Hi, assume I have two tables called Orders (ID, DateCreated) and a child table called OrderDetails (ID, OrderID, ProductID, Quantity)
What I need to do is calculate the average last 6 orders. Now if there are only 5 dates worth of orders I would need to know this value as I need to divide the sum(Quantity) by the number of orders - which cannot be greater than 6 previous orders. I have tried a few different select statements, but cannot seem to get the result I am looking for. Oh one other thing, I would be selecting these records by ProductID.
Any suggestions at all?
Thanks
|
|
|
|
|
I am not quite sure what you want your result set to look like. It would be easier if you gave a list of the resultant columns.
Are you looking for the last 6 orders of a particular product or simply the last 6 orders?
|
|
|
|
|
I need some help with a stored procedure, I have a table in my database that I am writing records to, specifically a BeginningDate field and EndingDate field. I want to be able to run a stored procedure that selects records based on a range of dates. This range of dates begins with monday and ends the following sunday, it covers an entire week. When I run the stored proc the where clause goes something like this:
<br />
select<br />
UserID,<br />
BeginningDate,<br />
EndingDate,<br />
<br />
From tableName<br />
<br />
where UserID = @UserID and BeginningDate >= @BeginDate and EndingDate <= @EndDate<br />
<br />
I know that it will not return any records if there is a date that is less than the EndDate. My question is how can I adjust this so it selects the one record based on a BeginDate and EndDate parameters?
|
|
|
|
|
select
UserID,
BeginningDate,
EndingDate,
From tableName
where UserID = @UserID and ((BeginningDate >= @BeginDate and EndingDate <= @EndDate) OR (BeginningDate >= @BeginDate) OR (EndingDate <= @EndDate))
Hope thats what you want ?
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Hi, i have data like this in temp table
Owner - DocumentPath
1 - c:\reports\fileA
1 - c:\reports\fileB
1 - c:\reports\fileC
2 - c:\reports\temp\FileA
2 - c:\reports\fileA
2 - c:\reports\fileB
AND SO ON....
i would like to know...
Is it possible to concatenate all DocumentPath column's into single column for each Owner ?
so i would have a new temp table that looks like this (if possible... this is still theoretical)
Owner - DocumentPaths
1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC
2 - c:\reports\temp\FileAc:\reports\fileAc:\reports\fileB
AND SO ON....
The reason i want to do this is because i need all the file paths in one line in one field on a report later... I thought i could PIVOT the data and then concatenate them...
OR
Is it possible to select only the top 3 (ordered by Modified Date DESCENDING) for each Owner???
like:
SELECT TOP 3 Owner, DocumentPath<br />
FROM #tempTableDocPath<br />
ORDER BY Owner ASC,Modified DESC<br />
BUT for each owner show top 3 (if they have three)
AND then Is it possible ONCE AGAIN to CONCATENATE the DocumentPath column into one column (in a new table / tempTable)?
like this:
Owner - DocumentPath
1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC
2 - c:\reports\temp\FileAc:\reports\fileAc:\reports\fileB
AND SO ON....
Thank you in advance. I hope i put it all down clearly 
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
modified on Wednesday, December 05, 2007 9:19:40 AM
|
|
|
|
|
Hi,
1. You can always write a stored procedure, wherein you can use cursor to concatenate the output of some SQL query. This string can then be returned / stored in some temporary table, which will be used later.
Support123 wrote: Is it possible to select only the top 3 (ordered by Modified Date DESCENDING) for each Owner???
like:
SELECT TOP 3 Owner, DocumentPath
FROM #tempTableDocPath
ORDER BY Owner ASC,Modified DESC
BUT for each owner show top 3 (if they have three)
AND then Is it possible ONCE AGAIN to CONCATENATE the DocumentPath column into one column (in a new table / tempTable)?
like this:
Owner - DocumentPath
1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC
2 - c:\reports\temp\FileAc:\reports\fileAc:\reports\fileB
AND SO ON....
I didnt got this. Can you please clarify in details, what exactly you want in this case ??
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
i want to put all the rows where the owner is the same (example 1... owner 1 is repeated three times because he has 3 documents linked to him... so he would look like this in the db:
Owner - DocumentPath
1 - c:\reports\fileA
1 - c:\reports\fileB
1 - c:\reports\fileC)
what i want to do is take all the paths linked to this owner, and put them into one column... to look like this.
Owner - DocumentPath
1 - c:\reports\fileAc:\reports\fileBc:\reports\fileC
So you see, now i have all the values needed in one column. This makes it TONS easier to display on a report.
So Column 1 (Owner) would have the Owner id and the Column 2 (DocumentPath) will have all the DocumentPaths found linked to the owner in column 1. I hope this is more clear???
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Support123 wrote: I hope this is more clear???
Yes, it is clear now.
Can you provide some table structure for a more precise answer ?
It would help me allot.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
ALTER PROCEDURE dbo.SelectData2<br />
(<br />
@naam1 varchar(30),<br />
@naam2 varchar(30),<br />
@leeftijd int,<br />
@numberfound int OUTPUT<br />
)<br />
AS<br />
SET NOCOUNT OFF<br />
SELECT ID<br />
FROM vrienden<br />
WHERE Voornaam = @naam1 AND Achternaam = @naam2<br />
SET @numberfound = @@ROWCOUNT<br />
IF (@numberfound = 0)<br />
BEGIN<br />
INSERT INTO vrienden (Voornaam, Achternaam, Leeftijd)<br />
VALUES (@naam1, @naam2, @leeftijd)<br />
END<br />
IF (@numberfound > 0) <br />
BEGIN<br />
UPDATE vrienden<br />
SET Leeftijd = @leeftijd<br />
WHERE Voornaam = @naam1 AND Achternaam = @naam2<br />
END<br />
RETURN
I entered this Stored Procedure in VWD and it accepted it, however I wonder if it is possible to do a SELECT and an INSERT or UPDATE in the same procedure.
My question is, would this work? Or should I make two stored procedures one to find if a certain entry is already in the database and a second stored procedure to change the database?
Also I am not sure yet how to process an OUTPUT variable.
Please some advice.
Thanks! Ranger49
|
|
|
|
|
A stored procedure can contain as many CRUD operations as you like. However you should consider giving your stored procedures more meaningful names. For instance, your SP is called SelectData2 but it performs select, insert and update operations. Ideally, the name of the stored procedure should indicate its purpose.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
pmarfleet wrote: A stored procedure can contain as many CRUD operations as you like. However you should consider giving your stored procedures more meaningful names. For instance, your SP is called SelectData2 but it performs select, insert and update operations. Ideally, the name of the stored procedure should indicate its purpose.
You are right, but this was only my second attempt ever, so I called it SelectData2.
Would you know of an example where a stored procedure is called with OUTPUT parameter? I figure my attempt failed due to an error in the calling cs file, and that the Stored Procedure itself is fine.
I Googled it, and got some examples, but they weren't answering my question.
Ranger49
|
|
|
|
|
When you create the Sql parameter for the output parameter, you set its direction to Output or InputOutput. When you say it failed, what do you mean? Did you get an exception or did it not behave as you would expect?
|
|
|
|
|
Hi,
Can I connect with visual basic.net to a online server (I have a space on the server with my hosting company, the server has mySQL database on it.)?
If I can please tell me how (connection string, methods to use, namespaces etc...)
The idea was to totally work online using mySQL server?
Thanks.
|
|
|
|
|
You can and this[^] lists the connection strings you can use. Install the native MySql .NET provider and use this in your code. There are plenty of good tutorials for using this online.
|
|
|
|
|
Hi,
Thanks for your reply...
my problem is
iam using (MS-Access) database
ihave to store multiple items in one invoice number(primary)
ihave to design table like below format...
invoiceno items units
1 nokia1600 2
nokia6600 3
nokia6633 4
2 nokia1600 2
nokia1400 3
nokia1500 6
3 nokia1800 5
nokia1900 6
like this ....(database design) and coding in c# application
murali krishna
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
