|
Why bother using the @x variable at all?
If you are doing this by trying to pass the string as a parameter to the stored procedure, i'm afraid this won't work in quite this way.
|
|
|
|
|
then, to be used like this
<br />
declare @x varchar(100), @y varchar(100)<br />
set @x = '100,101,102'<br />
set @y = 'Select * from a where c1 in (' + @x + ')'<br />
exec (@y)<br />
Regards
KP
|
|
|
|
|
Hi friends
I am facing a problem while accessing database sqlserver 2005 from command prompt.
Here i am giving my code. I don't have any compilation errors,
But runtime errors as follows....
Unhandled Exception: System.Data.SqlClient.SqlException: An error has occurred w
hile establishing a connection to the server. When connecting to SQL Server 200
5, this failure may be caused by the fact that under the default settings SQL Se
rver does not allow remote connections. (provider: Named Pipes Provider, error:
40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
my code is :
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Demonstrates how to work with SqlConnection objects
/// </summary>
class SqlConnectionDemo
{
static void Main()
{
// 1. Instantiate the connection
string strConnection = "server=localhost; Trusted_Connection=yes; timeout=120";
SqlConnection Conn = new SqlConnection(strConnection);
SqlDataReader rdr = null;
try
{
// 2. Open the connection
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
// 3. Pass the connection to a command object
SqlCommand cmd = new SqlCommand("select * from Customers", Conn);
// 4. Use the connection
// get query results
rdr = cmd.ExecuteReader();
// print the CustomerID of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// 5. Close the connection
if (Conn != null)
{
Conn.Close();
}
}
}
}
Note: I am executing my program at cmd prompt.
Help please...........
|
|
|
|
|
mohanallam wrote: string strConnection = "server=localhost; Trusted_Connection=yes; timeout=120";
You haven't specified the database you want to connect to in your connection string.
Visit ConnectionStrings.com[^] to see how to include this information in your connection string.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
you should specify the database ,user ,password in you contention string
my english is very bad!
|
|
|
|
|
Hello,
I am trying to find as to what would cause a sql-agent to roll-back/fail a job, where-as same job (aka stored-proc) when executed via QA, runs ok.
Here are more details:
I have a stored-proc (SP) that gets executed via a sql-agent. There is a primary-key error that it runs into and hence fails, with appropriate primary-key constraint error-message.
Now, when i execute this same SP, via QA (exec sp-name), it also generates the primary-key related message, but it does populate the db tables.
Platform:
- Windows 2003 server.
- SQL Server 2000
Any clues/direction/suggestions?
Thanks.
|
|
|
|
|
Can you post both error messages (from the job and QA one)?
|
|
|
|
|
I have a MS Visual Studio 2005 C# project within which I've created and SQL Server database (my first one I've ever done). The database resides on my hard drive. OS is Windows XP.
Any records I add, edit, or delete database records within Server Explorer's Data Connections update accordingly. Any records I add, edit, or delete programmatically, the changes to the records are there while I query or debug as the program is running. However, when I close down my program, the database returns to its original state, as if I didn't do any adding, updating, or deleting.
I've tried using and not using System.Data.SqlClient.SqlDataAdapter, System.Data.SqlClient.SqlCommand (with and without using Parameters.Add), and System.Data.DataSet. I've also tried using IDbCommand using Transaction with Commit or Rollback.
Any ideas why my database is not updating? 
|
|
|
|
|
Permissions? But those should generate error returns. Any code you can show might help.
|
|
|
|
|
I suspect that it is updating. What's happening is that you have a local DB in your project which is being copied into the bin directory at compile time. Hence, it is being overwritten whenever you recompile/run your code.
|
|
|
|
|
Hi,
I am developing a windows application that needs to communicate with a remote SQL server 2005 database. Server allows remote connections and MSDTC service also running. Do I need to run MSDTC service on the client machine where I use desktop application ? any ideas ? It's throwing some error like
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. but my SQL server allows remote connections.
|
|
|
|
|
I was thinking it may be more of a case of needing MDAC on the clients.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Check wether all needed protocols are enabled in the SQL Server Configuration Manager.
|
|
|
|
|
hi there,
i wanna count the null rows in a column, but it always return 0,
the codes, i written :
select count(*) from table where c1 = null
i searched google and find that the count function only counts the NOT NULL rows, but i didnt find the other way to do that.
thanks
Becoming Programmer...
|
|
|
|
|
|
You cannot use = NULL.
The previous post gave you the correct answer and it is important to understand what NULL actually means.
You cannot compare NULL to another value.
This is because NULL is an indeterminate value.
In other words NULL=NULL will always return false because NULL means the absence of a value.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
I want to generate a script for tables and stored procedures using query analyzer.
for stored procedures i got it using -- sp_helptext spname
For tables how to do? using sp_help tablename -- giving structure of table and not generating a script. but i need script for this table using query analyzer
|
|
|
|
|
You need to use the correct tool. Use enterprise manager to generate scripts. It is designed for doing that, along with most other database design and maintenence tasks. Query Analyser is designed for only what it's name suggests.
.
|
|
|
|
|
In query analyzer press F8.
This should open a pane on the left where you can navigate via databases to tables and SPs.
From there right clicking on the SP or table should give you a menu with options to script the SP or table(Clicking on edit scripts SPs, I'm not sure about tables though).
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
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!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
