|
if execute the following query
Select * from table1 where table1id in('100,101,102')
getting error....
Conversion failed when converting the varchar value '100,101,102' to data type int.
anybody tell me solution for this..
thanks
Ramana
|
|
|
|
|
Ramana. G.V wrote: table1id in('100,101,102')
why to enclose search expression in quotations
hope table1id column in the table is numeric
remove quotations and use
Select * from table1 where table1id in(100, 101, 102)
Regards
KP
|
|
|
|
|
if run like this...
declare @x varchar(100)
set @x = '100,101,102'
Select * from table1 where table1idin(@x)
getting that error.
how can i cast to int?
Ramana
|
|
|
|
|
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?
|
|
|
|