|
I am doing a data conversion from our old DB design to a new one using a stored procedure. In order to avoid changing the name of the source DB in the sproc (we get different db names with identical schemas from our customers--don't ask me why), I would like to pass in the name of the source DB. Can this be done? I've tried to pass in like this:
CREATE PROCEDURE convert_Customer
@DbName [varchar](255)
AS
DECLARE C_OLD_Customer_Data CURSOR
LOCAL FORWARD_ONLY STATIC
FOR
SELECT @DbName.dbo.Customer.CustomerID,
@DbName.dbo.Customer.LastCompanyName,
As I figured it would, it fails miserably. I think it's more and interpolation problem than anything else. Anyone know if and how this can be done?
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Friends, consider the following query:
select id, name, nick from blahtable where salary=2000
As you can see above i selected three fields from a table. Assume that the query returns only one record.
Now i want the values of three fields returned, to be assigned in variables and then i want to print the values of these variables. How can i do so.
|
|
|
|
|
Using which language(s) ?
|
|
|
|
|
|
Well, if you are doing this purely in SQL Server, then you can go into Query Analyser, run the SELECT query, and print the grid from there.
If you want to start assigning return values to variables and formatting printout (and perhaps changing the SELECT criteria), then you have to look at doing this from a client-side tool, such as VB.NET, or Crystal Reports.
Alternatively, you may be able to write a SQL Stored Procedure which takes criteria, and outputs the 3 fields you need (this gets complicated if you want to send the three fields directly to a printer from a stored procedure)
Is this part of a user application you're developing, or is it a once-off ad-hoc type query you're running?
John.
www.silveronion.com[^]
|
|
|
|
|
I believe this may be the solution
--Declare the Necessary Variables
DECLARE @ID AS int,
@Name AS nvarChar(15),
@Nick AS nvarChar(15)
--Incorporate those Variables
--Into the Select Statement
SELECT @ID = id,
@Name = name,
@Nick = nick
FROM blahtable
WHERE salary = 2000
PRINT @ID
PRINT @Name
PRINT @Nick
Buy the Book 'Advanced Transact-SQL for SQL Server 2000' by Itzak Bengan and Tom Moreau, If you plan on working with SQL, it will change your life
...just kidding but its a powerfull book
Gregory J Lynch
Hack
|
|
|
|
|
try this
declare @id int
declare @name varchar(30)
declare @nick varchar(30)
select @id = id, @name = name, @nick = nick
from blahtable where salary=2000
print @id
print @name
print @nick
Si 
|
|
|
|
|
I need to teach some 'users' a few basics of SQL. They are picking up most of the concepts fairly easily. That was until we came to JOINS. Because of the structure of the database, they'll often have to do some LEFT JOINS in their queries.
Does anybody know of a book / website that explains joins in a simple easy to understand way. (Pictures would be nice)
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
|
Hi!
:-OHow can I select from
InvId Company
------------------
1 a
23 b
56 c
12 a
21 c
this data set:
Company Invoices
-----------------------
a 1,12
b 23
c 56,21
Many thanks in advance!
_____________
stefan bornuz
|
|
|
|
|
|
Thanks Jeremy!
I just hoped that MS SQL has a trick for doing this with a 'simple' query, without the need of procedural processing.
Thanks again,
_____________
stefan bornuz
|
|
|
|
|
Typical situation; Project spec calls for MS SQL 2000, come to install at client, client is running MS SQL 7.
There should not be much of a problem, they are not that different (not like 6.5 was to 7.)
However there is a problem; I backed up the SQL 2000 database. When I come to restore it on SQL 7 it gives an error (Error 3624, tableIndex < *****_ARRAYLEN.)
So I need to know: Can I convert a 2000 backup to 7? Is there a switch I need to set in SQL 2000 when I backup?
Any other bright ideas? Doing a script is no good because there is plenty of data in the database that needs to be retained.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
Shog9:
Paul "The human happy pill" Watson
|
|
|
|
|
FYI.
The answer is: No, Microsoft SQL 7 cannot restore a backup made by Microsoft SQL 2000.
Doh.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
Shog9:
Paul "The human happy pill" Watson
|
|
|
|
|
Hi
I am using fallowing table, storedprocedure, function and event handlers.
My problem is that I am abled to update one row successfully only.
If there are rows more than one that satisfy the matching criteria (i-e status=1 in this case, let 9 rows) then
I get the fallowing Error Message:-
"Concurrency Violation : the updateCommand affected 0 records"
What should I do to avoid this Error?
My task was to run a storedprocedure and get the description of
updated rows though event handler but I am doing it for one only,
how multiple rows can be updated in my situation?
What am I doing wrong?
Regards
Mairaj
'////////////
Table1 :-
Column Type
---------------------------
id Auto-Number
Description varchar(50)
satus bit
'/////////////////// Storedprocedure:-
CREATE PROCEDURE dbo.[Test_Update]
AS
UPDATE Table1 SET status = 0, Description = "UpDate test"
WHERE status=1;
GO
'////////// function :-
Public Function Excecute_Test_Update()
Dim MyTrans As SqlTransaction
Dim strConnString As String = "data source=(local);initial catalog=" & "Test" & ";uid=sa;password=;"
Dim connDB As New SqlClient.SqlConnection(strConnString)
Dim da As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Dim dtDB As DataTable
Try
connDB.Open()
Dim cmdDB2 As New SqlClient.SqlCommand()
cmdDB2.CommandType = CommandType.Text
cmdDB2.CommandText = "SELECT status, Description, Id from Table1"
cmdDB2.Connection = connDB
da.SelectCommand = cmdDB2
da.Fill(ds, "Table1")
connDB.Close()
dtDB = ds.Tables("Table1")
Dim strExpr As String
strExpr = "status" & " = " & 1
Dim foundRows() As DataRow
' Use the Select method to find all rows matching the filter.
foundRows = dtDB.Select(strExpr)
da.UpdateCommand = New SqlCommand("Test_Update")
da.UpdateCommand.CommandType = CommandType.StoredProcedure
da.UpdateCommand.Connection = connDB
Dim Len As Integer = foundRows.Length - 1
Dim i As Integer
For i = 0 To Len
foundRows(i)("status") = 0
foundRows(i)("Descrition") = "UpDate test"
Next
MsgBox("Updated count:" & dtDB.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent).Length)
connDB.Open()
AddHandler dtDB.RowChanged, New DataRowChangeEventHandler(AddressOf Row_Changed)
AddHandler da.RowUpdated, AddressOf OnRowUpdated
MyTrans = connDB.BeginTransaction()
da.UpdateCommand.Transaction = MyTrans
da.Update(ds, "Table1")
MyTrans.Commit()
Catch exp As Exception
MyTrans.Rollback()
MsgBox("Exception: " & exp.Message)
End Try
RemoveHandler dtDB.RowChanged, AddressOf Row_Changed
RemoveHandler da.RowUpdated, AddressOf OnRowUpdated
MyTrans.Dispose()
connDB.Close()
End Function
'///////////// handlers :-
' handler for rowchanged event
Private Sub Row_Changed(ByVal sender As Object, ByVal e As DataRowChangeEventArgs)
MsgBox("row changed")
End Sub
' handler for RowUpdated event
Protected Sub OnRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
MsgBox("Id " & e.Row("Id").ToString() & " Description " & e.Row("Description").ToString())
End Sub 'OnRowUpdated
|
|
|
|
|
Hi all.
QUESTION 1:
I want to know how Microsoft's data mining solution measures up to those provided by other vendors.
QUESTION 2:
OLAP/Analysis Service comes with Microsoft SQL Server. In addition to the regular data drilling and presentation/charting layer support, it supports two analytical components: Microsoft Clustering (Expectation Maximization) and Microsoft Decision Tree (predictive, classification).
QUESTION 3:
I have no experience with Oracle, PL-SQL, digimine, Cognos, SAS... Can anyone tell me how these products compared to OLAP that comes with SQL server? I know some applications are more specialized for a very specific purpose - clickstream for example.
QUESTION 4:
Software like SPSS, minitab. mathematica offers a whole bunch of analytical tools - from simple column average to clustering to regression. Does any of the OLAP solutions comes with these capability?
QUESTION 5:
What are the most important features that one can expect from a BI solution?
QUESTION 6:
general/specific comment, case studies/application, price and licencing cost estimates will be helpful.
thanks a bunch.
norm
|
|
|
|
|
I have a list of names in my SQL database("Books"), datatable("BookName") that I would like to use to create directories on my computer. The following code work excellent using a .txt file of the book names, however the SQL database has the same identical list of names. I'd like to use the SQL instead of the .txt file approach... Help...........
string strSource = (@"C:\bookName.txt");
DirectoryInfo source = new DirectoryInfo(strSource);
string strTarget = (@"C:\books\");
DirectoryInfo target = new DirectoryInfo(strTarget);
StreamReader sr = File.OpenText(strSource);
string read;
while ((read = sr.ReadLine()) != null)
{
target.CreateSubdirectory(read);
}
sr.Close();
keivn@upgrade1.com
|
|
|
|
|
Try something like:
string strTarget = @"C:\books\";
string connection = "server=(local);database=books;trusted_connection=true;";
using(SqlConnection cn = new SqlConnection(connection))
{
cn.Open();
using(SqlCommand cmd = new SqlCommand("SELECT [Name] FROM Books", cn))
using(SqlDataReader rst = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while(rst.Read())
{
DirectoryInfo book = new DirectoryInfo(
Path.Combine(strTarget, rst.GetString(0));
if (!book.Exists)
book.Create();
}
rst.Close();
}
cn.Close();
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
you could try this
set @filepath = 'c:\windows\new_dir'
SET @String = 'mkdir ' + @filepath + '\'
exec master..xp_cmdshell @String
thanks
si
|
|
|
|
|
I have this code :
Static ConnectionString As String = SQL_CONNECTION_STRING
Dim WarehouseConnection As New SqlConnection(ConnectionString)
Dim strSQL As String = "SELECT * " & _
"FROM Orders " & _
"WHERE OrderID = '" & (CType(DataGrid1(currentrow, 0), String)) & "'"
Dim ProductAdapter As New SqlDataAdapter(strSQL, WarehouseConnection )
I get the order number from a double click on datagrid. Now .. I just want to "chain" out to my file .. Orders .. and bring back that single records data. I have the sqlcmd statement but my connection string keeps erroring out. I keep on getting errors ..
Thanks
__________________
William E Gollnick
Gollnick
|
|
|
|
|
What specific errors are you getting?
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hello.
Does MS Access support BatchUpdates?
I have a project in which im using following code but it gives me an error when there is
more than 2 records in the recordset which have been changed
IDispatch error # 3156 :Number of rows with pending changes exceeds the limit.
//////////
_variant_t v(m_pProject->m_ptrConnection.GetInterfacePtr());
// Open recordset
m_pRstBugs->CursorType = adOpenKeyset;
m_pRstBugs->LockType = adLockBatchOptimistic;
m_pRstBugs->Open((LPCTSTR)ProjectName, v,
adOpenKeyset,adLockBatchOptimistic,adCmdTableDirect);
if (m_pRstBugs->Supports(adIndex) && m_pRstBugs->Supports(adSeek))
{
// im making an index
m_pRstBugs->Index = DBtags::m_sProjectIndexName;
}
// ...
// here is the problem: Error -> "Number of rows with pending changes exceeds the limit."
m_pRstBugs->MoveFirst();
while(!m_pRstBugs->adoEOF)
{
_variant_t x("test");
m_pRstBugs->Fields->Item["description"]->Value = x;
m_pRstBugs->MoveNext();
}
m_pRstBugs->UpdateBatch(adAffectAll);
// end test
|
|
|
|
|
i am trying to develop a search function. user can search by entering a few types of information. the search will display all the results matching the words entered by the user.
the problem is:
if i enter the barcode number only, all the documents are displayed. and if i leave the txtBarCode blank and enter description, no document is displayed, even when the document table in my database contains the word. why is this happening?
<div align="left"><font face="Arial, Helvetica, sans-serif" size="2">Bar Code Number</font></div>
<td width="56%"><asp:textbox id="txtBarCode" runat="server" TextMode="SingleLine"></asp:textbox></td>
<td height="25"><font face="Arial, Helvetica, sans-serif" size="2">Description</font>
</td>
<td><asp:textbox id="txtDesc" runat="server" TextMode="SingleLine"></asp:textbox></td>
aspx.vb
-------
Dim strConn As String = "Provider=SQLOLEDB..."
Dim objConn As New OleDb.OleDbConnection(strConn)
Dim searchStr2, criteriaArray, queryCriteria,i, query1, rst1,rst2 objConn.Open()
searchStr2 = fp.Searchsyntax
criteriaArray = Split(searchStr2, ",")
queryCriteria = " [tblDocument].BarCodeNo IS NOT NULL"
For i = 1 To UBound(criteriaArray)
queryCriteria = queryCriteria & " AND "
If criteriaArray(i) = fp.Barcode Then
queryCriteria = queryCriteria & "[tblDocument]." & criteriaArray(i) & " LIKE '%" & fp.Barcode & "%'"
ElseIf criteriaArray(i) = fp.Desc Then
queryCriteria = queryCriteria & "[tblDocument]." & criteriaArray(i) & " LIKE '%" & fp.Majordesc & "%'"
End If
Next
query1 = "SELECT * FROM [tblDocument] WHERE " & queryCriteria & " ORDER BY [tblDocument].BarCodeNo"
Laine
|
|
|
|
|
Hi can anyone here help with this?
I have a dataset that is populated with data from two related tables. I create a DataViewManager to manage views of the data based on dynamically created queries which are passed to the DataViewManager.RowFilter. The DataViewManager is bound to a datagrid which displays the results of the queries. The datagrid also displays the child data relating to each row by clicking on the + sign at the start of each row.
Currently the queries I am adding to the RowFilter are based on the parent table, for example;
dvManager.DataViewSettings["client"].RowFilter = "((region = 'Blah') OR ((region = 'Bluh')) AND ((area = 'Here') OR (area = 'There')) AND (gender = 'Male')";
- This approach works well, however I now need to filter records based on values in the child table. If I wanted to do this by going back to the {MSAccess}database I would be creating an SQL statement such as the one below;
SELECT DISTINCT client.*
FROM clientcat AS clientcat_1, clientcat AS clientcat_2, client
INNER JOIN clientcat ON client.clientid = clientcat.clientid
WHERE ((client.clientid=[clientcat].[clientid]) AND (clientcat.category='Literature'))
AND ((client.clientid=[clientcat_1].[clientid]) AND (clientcat_1.category='Art Administration'))
AND ((client.clientid=[clientcat_2].[clientid]) AND (clientcat_2.category='Performance'));
According to MSDN the DataViewManager is capable of this functionality, although they do not offer any examples of exactly how to do it.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data06142001.asp[^]
My question is - Is it possible to query a DataViewManager in this way and can someone show me how?
|
|
|
|
|
Hi, This is a repost of mine from Lounge.
I would like to know the implications of using dynamic sql with in a stored procedure, how it affects execution plan, what is the pros and cons of EXECUTE IMMEDIATE, the oracle native dynamic sql execution command.
please give me pointers to relevant resources.
Cheers,
Venkatraman Kalyanam
Chennai - India
"Being Excellent is not a skill, it is an attitude"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
