I've got a string that contains my Insert query
How can I execute it ?
declare @sql char(500)
Select @sql = '"Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' + @Key + ' not In (Select ' + @Key + ' From T74ACCO)"'
Thank you in advance
BK,BM or Snail
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
hi,
i have a question. is there a way to execute a string when there is a function in the string?
here is an example. (try to run this at query analyzer)
declare @teststring varchar(255)
declare @resultstring varchar(255)
set @teststring = 'getdate()'
select @resultstring = exec @teststring
usually i can just get the result by using
select @resultstring = getdate()
I'm trying to execute a parameterized SQL string but need a return parameter (a multiplier) to include in a later SELECT statement.
What i'm looking for is equivalent to this paraphrased statement:
EXEC('SELECT @val = from @column where Value = @Value')
Where I would later use @val something like:
Select Value * @val as Total Value
Is there an easy way to do this..i've read and read to no avail.
TIA,
Charles
I am running a SQL Server 2012 Express.
Due that I dont have SSIS and can not plan tasks. So I have to write a script to do the following:
1. Delete Data in Database1 - Table1 on Server 1, Database1 - Table2 on Server 1,Database1 - Table3 on Server 1
2. Delete Data in Database2 - Table1 on Server 1, Database2 - Table2 on Server 1,Database2 - Table3 on Server 1
3. Connect to Server 2
4.Copy Data from Database1 - Table1 on Server 2 to Database1 - Table1 on Server 1 ... and so on
5.Send me a message @xy.com when a error appears
Then I have to run the batch file every day from monday to friday night.
My main problem is the part with the connection. Iam able to write the connection string but I dont know how to execute the string.
Basically, I have a table with a column that stores mathematical formulas in string format. When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.
Look at the example below:
Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5. I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"
Now I want to execute this string and find the numeric result, placing it in a variable named @dResult. The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.
So I attempted to change the temporary table above into an instance of the TABLE data type. This didn't work either because EXEC cannot be used to populate instances of the TABLE data type. Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC?
Hi!
I try to execute SQL Task with simple statement
insert T (comment) values (@Comment)
where comment is varchar(1000). I map package variable User::Comment (type string) to parameter @Comment in SQL Task properties. But when length of User::Comment greater than 10 characters SSIS returns errors:
Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (@Comment)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The similar messages from this forum suggest using the OLEDB connection. I try this method too, but get the same result.
Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (?)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What is it? Is this a bug, I made a mistake or I have SQL Server old version?
I have SQL 2005 (Developer Edition) 09.00.3054
TIA, Max.
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.
CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS
declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)
[code]....
Hi there. I have occasional sincronization error.-XSUBSYSTEM Merge
-XSERVER MYSERVER
-XCMDLINE 0
-XCancelEventHandle 000006E0
2006-12-22 14:55:00.833 Connecting to Subscriber 'Subscriber01'
2006-12-22 14:55:00.895 Connecting to Distributor 'Publisher01'
2006-12-22 14:55:02.974 Initializing
2006-12-22 14:55:03.083 Connecting to Publisher 'Publisher01'
2006-12-22 14:55:06.005 Retrieving publication information
2006-12-22 14:55:06.130 Retrieving subscription information.
2006-12-22 15:00:07.222 The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-12-22 15:00:07.456 Error converting data type nvarchar to numeric.
2006-12-22 15:00:07.800 Category:SQLSERVER
Source: Subscriber01
Number: 8114
Message: Error converting data type nvarchar to numeric.
After manual syncronization it goes under control.
Could anybody explain the relationship between converting error and timed out query?
If this is poor connection's problem how can I increase the query timeout for the process?
Hi all, mister
I want create a function but I get this error: Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function
I think in a function, cannot use temp tables, or calling exec or store procedures.
Which is the best solution for my issue ? develop store procedure ??
thanks.
CREATE FUNCTION fnObtenerTablaMaestra ()
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @nexoUNION NVARCHAR(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
-- Cannot access temporary tables from within a function.
-- IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
-- DROP TABLE #tmpTable
-- CREATE TABLE #tmpTable ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
SET @nexoUNION = NULL
DECLARE c1 CURSOR for
SELECT [CD_NOMBRE_TABLA], [DS_CAMPO_DESCRIPCION], [DS_CAMPO_CIF] FROM [TABLA_MAESTRA]
OPEN c1
FETCH c1 INTO @NombreTabla, @Descripcion, @CIF
--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF
WHILE @@FETCH_STATUS >= 0
--WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
N'''+ @NombreTabla + '''
N'''+ @Descripcion + '''
N'''+ @CIF + ''''
SELECT @sql =
'SELECT ' + @Descripcion + ', '+ @CIF + ' FROM ' + @NombreTabla
IF @nexoUNION IS NULL
BEGIN
SET @nexoUNION = 'UNION'
END
ELSE
SET @sql = @nexoUNION + ' ' + @sql
-- EXECUTE (@sql)
--Exec(@sql)
FETCH c1 INTO @NombreTabla, @Descripcion, @CIF
--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF
END
CLOSE c1
DEALLOCATE c1
--SET @sql = 'SELECT Descripcion, CIF FROM #tmpTable'
-- Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function
EXECUTE(@sql)
RETURN
END
GO
Dear All
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
Here is my query sample for yours to understand.
SET NOCOUNT ON
DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)
Set @qstring = 'select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm'
PRINT '-------- ITEM Products Report --------'
DECLARE ITEM_cursor CURSOR FOR
execute (@qstring)
OPEN ITEM_cursor
FETCH NEXT FROM ITEM_cursor
INTO @ITEMCODE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From ITEM: ' +
@ITEMNAME
PRINT @message
-- Get the next ITEM.
FETCH NEXT FROM ITEM_cursor
INTO @ITEMcode
END
CLOSE ITEM_cursor
DEALLOCATE ITEM_cursor
Why i use @qstring? It is because the query will be changed by different critiera.
Regards
Edmund
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
Hi,
I want to log/watch the query executed by the "Execute query Task". It's a query with different variables, the different variables i can see in one of the debug windows and these looks OK, but the query doesn't do what's expected. So i want to see the whole query, is that possible?
Query logging on the database server isn't an option. The server is an oracle server and the dba doesn't want to change this setting.
Thanks,
Dennis
NOTE: I am using Brian Larsons book Delivering Business Intelligence with SQL Server 2005. I am stuck on page 588 - step 20. I am using Windows Vista Ultimate
Once all the Dimensions & attributes I have selected are dragged into the query area in MDX query builder no data is displayed. There is a message: "No rows found. Click to execute the query." - clicking does nothing.
How can I resolve this problem ???
Dear All,
I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?
Regards,
Strike
I am using execute pacakge task to execute another package . I am giving the Connection string for the package to execute. It works fine in my development machine but when i try to run in another server after i deployed it. It looks for the datasource path of the DTSX file in the same location.
how do i set the path according to each server where the dtsx file is stored. or any other method of storing it like connection string.
if i store it in theparent package variable where should i point to...
thanks
aa
Hi all,
I need some help.... I cannot execute any query from sql 2005, when I try to execute a query it stays executing and I never receive any result set. I uninstalled and re installed again SQL 2005 in my computer and also I installed the Service Pack and I am still getting the same. Could you please help me or advice on this situation?
Thanks in advance,
Mauricio.
I want to execute a query for each row in a report since a parameter in that query relies on a field in the row. Is there a way to do this?
View 1 Replies View RelatedIs there a way in sql server to find out how much time a sql server took to execute a query.
Thanx in advance.
Regards,
Samir
I believe all of us use SQL Server Management Studio to execute query. As I encountered serious problem with this method, due to typo error by accident, I am wondering if we can run the .sql file from dos and supply parameters with it.Possible?
View 4 Replies View RelatedRamesh writes "Hi,
Is there any possibility to execute a query in sql server 2000 without keep / using buffer.
Thanks"
I create some tables and I can't execute new query, I used to be able to do it, but I can't do it now. I'd like to find out that how can I do it again.
Thanks
I've got a sqldatasource with a update query in it. Now I'm trying to execute that query on button click. How do I go about doing so?
Here's my ASPX code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Data_Verification_Editor.aspx.vb" Inherits="Core_Data_Verification_Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDS_Valid" runat="server"
ConnectionString="<%$ ConnectionStrings:Test%>"
SelectCommand="Data_Validation_sp" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
<asp:SqlDataSource ID="Test" runat="server"
ConnectionString="<%$ ConnectionStrings:Test%>"
UpdateCommand="UPDATE [Data_Valid_Current_tbl] SET ID = '@selected_id', SET Title = '@selected_text' WHERE PrimID = '1'">
<UpdateParameters>
<asp:ControlParameter Name="selected_id" ControlID="Data_Ver_ddl" PropertyName="SelectedValue" />
<asp:ControlParameter Name="selected_text" ControlID="Data_Ver_ddl" PropertyName="SelectedText" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width: 320px; background-color: menu; border-right: menu thin ridge; border-top: menu thin ridge; border-left: menu thin ridge; border-bottom: menu thin ridge; left: 3px; position: absolute; top: 3px;">
<tr>
<td colspan="2" style="font-family: Tahoma; font-size: 10pt;">
Testing:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Data_Ver_ddl" runat="server" DataSourceID="SqlDS_Valid" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
</table>
</form>
</body>
</html>
Howdy
If I have serveral queries as shown below:
================================================== ==========
USE XXX
SELECT XX_ZZZZZ, XX_YYY, XX_XXX
FROM ZZZ_ZZZZ_ZZZZ
WHERE QQQQQQQQQQQQQ ='PGL'
USE XXX
SELECT AAA_AAAAA_AAAA,AA_BBB, CC_DDD
FROM SSSS_SSSSS_SSSSS
WHERE FFF_FFFF ='A'
================================================== ===========
Can anyone tell me if the queries execute concurrently or
is it one after the other?
Many thanks,
W.
Hello,
I would like to execute a sql-query on a table, but disable the triggers that whould fire on the change.
Thank you for telling me how to do this!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
Hi,
Is it possible to connect to a database and fetch data from it from within custom code inside a report?
Appreciate any help.
Regards,
Asim.
Hello,
I try SP2 and cannot get the Execute SQL task to work as before.
In this example I use an OLEDB Microsoft Oracle provider.
The script uses a parameter like that
select Cast( decode((
select
DECODE(TRUNC(c.END_DATE), TRUNC(SYSDATE),1,NULL) AS RESULT
from one_stage.load_complete c
where TRUNC(c.end_date) = TRUNC(sysdate)
and c.name = ?
and c.STATUS = 'SUCCEEDED')
,Null,0,1) as varchar(2)) AS Result from Dual
The script task is set to BypassPrepare = False
I note only one change, the addition of a parameter setting called Parameter Size set by default to -1
I tried different things like reading the new BOL, changing the Bypass prepare, Changing the Parameter Size and changing the ? , nothing works. the output variable is always set to a value of 0 which is the result of the query test if there is no match.
It looks like the input variable is no longer passed to the query parameter.
What should I do to get this to work with SP2?
Thanks
Philippe
I am trying to execute a stored procedure in Query anaylzer for SQL server 2005. I am not sure I am doing it correctly though, because I am getting an error message. Here is the command I am typing:
execute DetaHistoryGet(84,885,34,"EndDate")
Here is the error message I get:
Msg 102,Level 15, State 1, Line 1
Incorrect syntax near '84'
Here are a few lines from the stored procedure. I have not included it all here.
CREATE PROCEDURE [dbo].[DetaHistoryGet]
( @MarketId decimal, @OwnerId decimal, @QuarterId decimal, @SortExp nvarchar(50) )
AS SET NOCOUNT ON
Hi All,
I have a table which consists the leave details of an employee.
I have the columns like paid leaves,sick leaves,personal leaves in the above table
Problem :
For eg: An employee joined on 21 May 2008. After 6 months i.e., 21 Nov 2008 I need to update the above columns (i e., increase the no.of leaves)
So updation is to be done for every 6 months and for every 1 year.
Can anyone say me how to execute the update query based on the duration.
Thanks in advance.
Regards,
Praveen
Has anybody come across situations where queries take longer to execute the second time? The server is a dedicated sql server box with 1gb memory.
Thanks in advance.
Praveena
Dear All,
Got some problem...
how come i lost some space (hardisk) when
execute query...
(the query is listed below)
I want to know is it create some virtual tables or view when we execute query ???
N where's the location in our hardisk ??
thx
Listed Query
---------------------
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Absen.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.DateAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
HRAbsensiKaryawan Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
Union
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
ADMAbsensiKaryawan Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
UNION
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
ADMAbsensiBulananSite Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
Union
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.DateAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
HRAbsensiKaryawanNonStaf Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
UNION
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.DateAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
HRAbsensiKaryawanStaf Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
UNION
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
LCAbsensiBulananSite Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
UNION
SELECT Absen.BranchID, Bran.BranchName, Bran.BranchNote, Dis.DepartmentID,
Absen.SubDepartmentID, Dis.AfdelingID, Dis.WilayahID,
Dis.KemandoranCivilID, TekCiv.KemandoranCivilName, Absen.TglAbsensi as TglAbsensi,
Absen.AbsensiID, Absen.KaryawanID, Data.JabatanID1, Jab.JabatanName,
Data.GolonganID
FROM AMBranchMaster Bran RIGHT OUTER JOIN
HRDataKaryawan Data LEFT OUTER JOIN
HRDistribusiKaryawan Dis LEFT OUTER JOIN
TEKMasterKemandoranCivil TekCiv ON Dis.KemandoranCivilID = TekCiv.KemandoranCivilID ON
Data.KaryawanID = Dis.KaryawanID RIGHT OUTER JOIN
LCAbsensiIndirect Absen ON Data.KaryawanID = Absen.KaryawanID ON
Bran.BranchID = Absen.BranchID LEFT OUTER JOIN
HRMasterJabatan Jab ON Data.JabatanID1 = Jab.JabatanID
UNION
...................
----------------