|
SQL Server:
Is there a way to extract a value/result when you exec a sql string?
DECLARE @PaperCost money<br />
declare @PaperCategories nvarchar(30)<br />
set @PaperCategories = '3,7'<br />
<br />
DECLARE @sqlcmd varchar(255)<br />
<br />
select @sqlcmd = ' SELECT sum(amounts) FROM tbl_CostInfo<br />
where CatID in (' + RTRIM(@PaperCategories) + ') '<br />
<br />
SELECT @PaperCost = exec( @sqlcmd )
This sql block will reside inside of a stored proc, and the idea is to have a string passed in for the category values ('3,7' in this example) and return the corresponding cost.
However, sql doesn't like the last line. Is there a way to extract the resulting value from an exec command?
|
|
|
|
|
Why not pass the categories as XML, use OpenXML to turn them into data, and do a normal query ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You could try:
create table #temp1 (
SumAmounts money
)
set @SQL = 'SELECT sum(amounts) FROM tbl_CostInfo .....'
exec(@SQL)
select @PaperCost = SumAmounts from #temp1
drop table #temp1 However, a better way of acheiving this would be to use a UDF to turn your @PaperCategories string into a list of values. The resulting SQL would look a bit like:
select @PaperCost = SUM(Amounts) from tbl_CostInfo
where CatId in (
select Element from dbo.udf_split(@PaperCategories))You should be able to find the source code for a similar UDF using google.
Regards
Andy
|
|
|
|
|
I want to use xp_cmdshell to export the contents of a table to an xml file. Here's the sql I'm trying to use:
use MyDatabase
declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);
set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd = '2008-01-01 11:59:59.999';
set @sql = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
Convert(varchar(50), @dtStart, 121) + ''' and ''' +
Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher +
''' order by endtimestamp, serialnumber';
set @outpath = '\\SERVERNAME\program files\appfolder\' + @publisher + '_January01.xml';
set @params = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x -S SERVERNAME\SQLSERVER'
execute master..xp_cmdshell @params;
When I try to run this code, I get the following error
<br />
SQLState = HY000, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file<br />
Everything I've found on google says that the permissions for the specified path may not be sufficient, but sql server is running under the "network services" account. Doesn't that account have sufficient permissions to write a file?
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
NT AUTHORITY\NETWORK SERVICE has very few privileges and is a member of very few security groups. I'm not even sure it's a member (transitively) of the Users group. You'll have to add it to the ACL for that folder, or make SQL Server run as a different user. Microsoft recommend running SQL Server under a domain user account rather than one of the built-in accounts.
NETWORK SERVICE differs from LOCAL SERVICE in that it is allowed to authenticate on the network, using the computer's domain account (COMPUTERNAME$). LOCAL SERVICE cannot authenticate but can use null, unauthenticated sessions (e.g. where Everyone is permitted).
You have actually shared the Program Files folder on \\SERVERNAME? You're using UNC syntax there.
My colleagues would normally do this kind of task with DTS/Integration Services or by writing a custom program.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike Dimmick wrote: My colleagues would normally do this kind of task with DTS/Integration Services or by writing a custom program
I kinda did that too - I just let the service I'm writing do it. It queries the database and writes the returned dataset to an XML file.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
I want to use xp_cmdshell to export the contents of a table to an xml file. Here's the sql I'm trying to use:
use MyDatabase
declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);
set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd = '2008-01-01 11:59:59.999';
set @sql = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
Convert(varchar(50), @dtStart, 121) + ''' and ''' +
Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher +
''' order by endtimestamp, serialnumber';
set @outpath = '\\program files\appfolder\' + @publisher + '_January01.xml';
set @params = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x'
execute master..xp_cmdshell @params;
When I try to run this code, I get the following errors
<br />
SQLState = 08001, NativeError = 2 <br />
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. <br />
<br />
SQLState = HYT00, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Login timeout expired <br />
<br />
SQLState = 08001, NativeError = 2<br />
Error = [Microsoft][SQL Native Client]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.<br />
NULL
If I've logged into the sql server with Management Studio, and am running this from a query window, why do I get this error?
[EDIT]
I had to add "-S sqlservername" to the commandline
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
modified on Thursday, February 14, 2008 12:12 PM
|
|
|
|
|
I have a table that links 0 - many reports to a form, called ReportLinks. I'm wondering how best to do an atomic update after a web user changes the reports linked to the form, i.e., deselects and selects reports as linked to the form. Please can somebody suggest an elegant way of doing this update.
|
|
|
|
|
Hi guys,
I got a datatable from a excel file, and try to update this table onto sql server, in other words, the users can change the table in sql server based on excel file.
I use SqlDataAdapter to update the table, but the problem is no change happen on my database.
<br />
'...<br />
<br />
Dim cmdSql As SqlCommand = New SqlCommand<br />
cmdSql.Connection = connSql<br />
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@notes", SqlDbType.NText))<br />
cmdSql.Parameters("@notes").SourceColumn = "notes"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))<br />
cmdSql.Parameters("@id").SourceColumn = "ID"<br />
<br />
' modify the data for testing<br />
dtData.Rows(0)("notes") = "test2"<br />
<br />
Dim daSql As SqlDataAdapter = New SqlDataAdapter<br />
daSql.UpdateCommand = cmdSql<br />
daSql.UpdateCommand.Connection.Open()<br />
<br />
daSql.Update(dtData)<br />
<br />
'...<br />
Above is a piece of codes which i made for testing.
Any suggestion would be appreciated!
Ming
|
|
|
|
|
Bluebamboo wrote: cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"
I believe that the problem lies in here. You'll have to pass values with the variable enclosed between Quotes and Ampersand '" & notes & "'
Having said that,you might want to restructure your code as it is vulnerable to Sql Injections[^].
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Thanks for your reply
I don't think that causes the problem, you can check the solution provided by Micsoft support
http://support.microsoft.com/kb/308055[^]
and the solution declare a SqlCOmmand with such a query
DAUpdateCmd = New SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection)<br />
which is actually perform same function as what I did
|
|
|
|
|
Bluebamboo wrote: http://support.microsoft.com/kb/308055[^]
Apologize for my ignorance. I didn't know that before. I tried out that sample that you've guided me with, it works like charm.
Can you try providing a mapping table name when you fill and update the adapter (in case you are using a dataset) ?
da = New SqlDataAdapter("select * from CustTest order by CustId", cn)<br />
<br />
da.Fill(CustomersDataSet, "Customers") <br />
da.Update(CustomersDataSet, "Customers")
If you already have that in place, kindly update me on how you got it working once you achieve it.
Good Luck friend 
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Hi Joe,
Thanks for you suggestion,
Problem has been *solved*, I haven't found where exactly cause the problem, because it worked once suddenly, and now it always works. Amazing!
I think it is not a problem with the code I pasted, probably it was something wrong with other potential reasons, which I don't know exactly, but one of them what I guess could be this[^]
Thanks.
Ming
|
|
|
|
|
Glad that it works now. Thanks for sharing that piece of information
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Parameters are passed to Stored Procedure from VB.NET Application like
Dim param As SqlParameter
param = New SqlParameter("@name",DbType.String)
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But is there any way to specify parameters without specifying the
parameter name and type like
Dim param As SqlParameter
param = New SqlParameter
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But when i try like this i am getting an error saying that
"Parameter1 is not a parameter in the procedure"...
Can anybody tell me how to solve this .
Thanx in advance
|
|
|
|
|
hi dnsl
Number one, The name of your Parameter you are declaring in SQL, should be the same name as your Parameter you Add in your Vb code.
|dnsl Wrote:
But is there any way to specify parameters without specifying the
parameter name and type like..
No you cannot , that code you give, will give you an Exception
Hope it helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
modified on Friday, February 15, 2008 3:39 AM
|
|
|
|
|
What you are attempting to do is pass values by position. This is generally considered bad practice as a change to the stored proc (ie adding a new parameter but not to the end of the list) could result in values being passed to the wrong column.
You can always do:
command.Parameters.Add(new SQLParameter("@name",value))
This uses the overload for new SQLParameter(name as String, Value as Object)
Better still, write a set of overloaded functions such as:
Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
ByVal parameterValue As Boolean) <br />
AddParameter(parameterName, parameterValue, System.Data.DbType.Boolean, System.Data.ParameterDirection.Input)<br />
End Sub<br />
Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
ByVal parameterValue As Int32) <br />
AddParameter(parameterName, parameterValue, System.Data.DbType.Int32, System.Data.ParameterDirection.Input)<br />
End Sub<br />
<br />
Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
ByVal parameterValue As Object, _<br />
ByVal dbType As System.Data.DbType, _<br />
ByVal direction As System.Data.ParameterDirection)<br />
Try<br />
' Add new parameter to command object - if already exists then simply update the value<br />
If m_cmd.Parameters.Contains(parameterName) Then<br />
m_cmd.Parameters(parameterName).Value = parameterValue<br />
Else<br />
Dim prm As SqlParameter = New SqlParameter<br />
<br />
With prm<br />
.ParameterName = parameterName<br />
.Direction = direction<br />
.DbType = dbType<br />
.Value = parameterValue<br />
End With<br />
m_cmd.Parameters.Add(prm)<br />
End If<br />
<br />
Catch ex As Exception<br />
Throw New ApplicationException(String.Format("Unable to add parameter {0} to command", parameterName), ex)<br />
End Try<br />
End Sub
Bob
Ashfield Consultants Ltd
|
|
|
|
|
When i run this query in my local machine i get an n error --Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.
USE CustList_PGE_0131
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE CustList_PGE013108(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT PK_CustList_PGE013108 PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
But if irun the same query in my server it createsd the table without any problem.
So is there some thing that i am overseeing in my local.
Thanks
|
|
|
|
|
I believe it could be because of compatability issues. Your local box might be running Sql Server 2000 whereas your server might be running Sql Server 2005
Take a look at the Breaking Changes to database engine in Sql Server 2005[^]
On your local box, have the script modified in this fashion
USE CustList_PGE_0131
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE CustList_PGE013108(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT PK_CustList_PGE013108 PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)
)
ON [PRIMARY]
GO
Create Unique Index [IndexName] on [dbo].[CustList_PGE013108][(ClientCustID)]
With IGNORE_DUP_KEY on [Primary]
Go
SET ANSI_PADDING OFF
Ref: http://www.codeprof.com/dev-archive/75/19-85-759122.shtm[^]
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <author,,jiju>
-- Create date: <create date,14.2.2008,="">
-- Description: <description,stored procedure="" to="" create="" tables,="">
-- =============================================
alter PROCEDURE [dbo].[CreateTable]
-- Add the parameters for the stored procedure here
@databaseName nvarchar(100),
@databaseTable nvarchar(100),
@primaryKey nvarchar(100)
AS
declare @qry nvarchar(1500)
BEGIN
SELECT @qry =
'CREATE TABLE '+@databaseName +'.dbo. '+ @databaseTable +'(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT '+ @primaryKey +' PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)
) ON [PRIMARY]
'
print @qry
--To execute the query string
exec SP_Executesql @qry
END
GO
--To execute the stored procedure and create the tables
exec createTable 'CustList_PGE_0131','CustList_PGE013108','PK_CustList_PGE013108'
|
|
|
|
|
Hello,
I am trying to retrieve an image from SQL but the line didPreviouslyConnect = true; is giving an error.HELP!!ME!!
I have the code writen
private void RetrieveImage()
{
try
{
SqlConnection PicCon = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter("SELECT photo FROM Upload where Account_No = '" + Txt_balance.Text + "," + Txt_Bankcharge.Text + "," + Txt_Refundamount.Text + "," + Txt_TellerID.Text + "," + Txt_Tellername.Text + "'", PicCon);
SqlCommandBuilder cbd = new SqlCommandBuilder(da);
dsPictures = new DataSet();
da.Fill(dsPictures);
didPreviouslyConnect = true;
byte[] arrPicture = (byte[])dsPictures.Tables[0].Rows[0]["photo"];
MemoryStream ms = new MemoryStream(arrPicture);
pictureBox2_Accountholder.Image = Image.FromStream(ms);
pictureBox2_Accountholder.SizeMode = PictureBoxSizeMode.StretchImage;
pictureBox2_Accountholder.BorderStyle = BorderStyle.Fixed3D;
ms.Close();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message.ToString());
}
KIDO KIDI MINI REZA
HERE FIX DI MATURITY
SAMBA RAMBA DIBLO
STYLE ANTIDIZASTA
BLISH MANTIRIOLOJ
|
|
|
|
|
What is the error you are getting?
|
|
|
|
|
Hi,
Can anyone give me a idea to convert or replace the existing date content '1/1/1900' to NULL while insert and update a table.
Thanks,
John
|
|
|
|
|
UPDATE MyTable<br />
SET TheDateColumn = NULL<br />
WHERE TheDateColumn = '1900-01-01'
|
|
|
|
|
Hi all,
I am developing a calendar application in C# that will require to store certain data (like date events, calendar style and some other preferences)in a database.
My preference will be MS SQL 2005 (though could be MySQL, or similar), however I wish to know if I was to use MS SQL 2005, could I include some dlls or other resource in the install of the application, purely for the application to run, without having to install another copy of MS SQL 2005 in the client PC? Like a cut down version of MS SQL 2005 as client site install? Otherwise I may have to go for MySQL or MS Access 2003, to avoid extra cost of the application.
Any ideas an suggestions are welcome.
Thanks in advance.
Alain
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
