i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it. assume i have a query like this. Select A, B, C from Table1 where ID=xyz whats the easiest way to get A, B and C in to string variables? I know i can use the sqldatasource control but i just feel there is too much overhead there. whats your suggestions? Thanks Justin
Hi, I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005. All I've done is:
I got a problem using an OLE-DB Source in my data flow task. It is an Informix-Server using the newest OLE DB-Driver provided by IBM. However, when I set up the OLE DB-Source with connection manager and SQl-Query, I get the following warning message:
Warning at {2B334CA3-A792-4BC2-93AA-12FADCF1E696} [OLE DB Source [45]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Later when I try to execute the package, the destination adapter gets errors like
Error 3 Validation error. Data Flow Task: OLE DB Destination [2985]: Columns "antrag" and "Antrag" cannot convert between unicode and non-unicode string data types. Informix_OLEDB.dtsx 0 0
I guess that SQL Server cannot get the data types from the Source.
SQL query windows saves all the tabs in numerical sequence after you close each window. SO for example if you are typing query in window 1 and close it and open a new query windows it labels the new windows as SQL Query2 and so on.
What is the purpose of saving the SQLQuery windows like this? And how to retrieve all the windows.
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.
CREATE proc SProc_DeleteRecord @TableName varchar(50), @DeleteCondition nVarchar(200) As Begin Begin Transaction Declare @DelString nvarchar(4000) set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition execute sp_executeSql @DelString RollBack Transaction --because i donot want to delete the record in any case If @@Error <> 0 Begin select '1' End Else Begin Declare @SQLString nvarchar(4000) set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition execute sp_executeSql @SQLString select '0' End
Howdy folks! (I feel im creating too many topics...but all my questions are unrelated :-/)
Dev environment: SSCE, Native c++ OLE code, Win CE 5
I have a routine that calculates the size of a set of columns in order to create a byte buffer for retrieving data from an IRowset and inserting it into a custom class. This method works great for all data types except ntext. When I look at the column information from an ntext field, the DBCOLUMNINFO::ulColumnSize member holds 536,870,911 (the max length of an ntext type). Obviously I dont want to allocate 1GB of memory for each ntext column. Is there anyway (natively) to determine the actual number of characters in a field?
Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
I am trying to get source code of a view (SELECT part) into an TSQL variable, but I have no idea, where to find this source code and how to proceede the assignment ? Any idea ? thanks
Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA
I'm in the process of building various queries and I'm having issue calculation values in the following code. I need to calculate the maximum and minimum salary for exempt employees and include a column that calculates the difference in these numbers. I have no issue creating views and outputing data. When I try to calculate the values I receive errors:
My code is:
GO IF OBJECT_ID ('Salaries') IS NOT NULL DROP VIEW Salaries ; GO CREATE VIEW Salaries AS select max(Salary) from EMPLOYEE join JOB_TITLE on EMPLOYEE.JobID = JOB_TITLE.JobID where ExemptStatus='Exempt' Group By Salary, ExemptStatus; GO SELECT * FROM [Salaries]
I'm receiving the following Msg 4511, Level 16, State 1, Procedure Salaries, Line 3 Create View or Function failed because no column name was specified for column 1. Msg 208, Level 16, State 1, Line 1 Invalid object name 'Salaries'.
Before we release an SSIS package into our test environment and then eventually into our production environment, we  set the package protection level to "EncryptAllWithPassword"
The "View Code" feature is a nice tool to find things that might be buried in the package somewhere (e.g an complex expression to a variable).
PROBLEM : After one sets the package protection level to "EncryptAllWithPassword", one cannot see the xml source code any longer. It's like compiling and saving cs to the bin. Is their a way to view the code again??
need help with my problem i have this view this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4 like this evry 4 month it return to 1 >2.......... ------------------------------------------ for example
if i put unit_date = 01/05/2008 and unit=1 than new_unit=1
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit") and olso create stored procedure name "minus" + so if i run this name stored procedure name "minus" this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
, Hi In this code how can I create a new data source and new data source view and model and structure that it run dynamic. In this code I have a lot of errors, that they are about server and database don€™t have in current code, In this code, first I should definition server or no?
How can I create data source and data source view and model and structure? Please say code of that, and guide me. databasename and srv is unknown. Do I add other reference with analysis services? Please explain about these codes: ************************************************************************ 1) RelationalDataSource dsNew = new RelationalDataSource( datasourceName, Utils.GetSyntacticallyValidID( datasourceName, typeof(RelationalDataSource)));
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here: Function Main() on error resume next dim cn, i, rs, sSQL Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>" set rs = CreateObject("ADODB.Recordset") set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount sSQL = rs.Fields(0).value cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution rs.MoveNext Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script: - The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Table 1: AddressBook Fields --> User Name, Address, CountryCode
Table 2: Country Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Hello, I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls. I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems ( @eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar, @eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar, @outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar, @q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar, @levent nvarchar, @eventdate nvarchar, @eventtime nvarchar ) AS UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile, q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn, outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3, qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName, CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime WHERE (ePRN = @ePRN) and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text); cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text); ((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text); cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text); cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text); cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text); So is there any way to do it better than this way ?? Thank you
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
CREATE VIEW dbo.vwFeat AS SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4 FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application. Thanks for help
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.