I am trying to create a database View using Visual Studio 2005. I have joined the tables and it creates the SELECT statement and WHERE statement which says:
WHERE (dbo.tblStoresStudios001.C_ID = @C_ID)
When I try to save it message comes up telling me to declare C_ID. I have tried int C_ID before the SELECT as if it were a stored procedure but that is not working. What dumb mistake am I making? I have not worked with views before.
There are 3 views in VS IDE, Designer, Code and Source. In source view, there are html codes, how do I add a variable into it ? For example: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:JJConnectionString %>" SelectCommand="SELECT * FROM [Tbl]" ... How to change the SelectCommand to like:SelectCommand="SELECT * FROM [Tbl] WHERE [CREATEDBY] = '" & User.Identity.Name & "'" The User.Identity.Name is not valid in source view, but i need it to work, are there any way ?
Hello, We have a need to create a variable within a View. We tried declaring our variable like we would in a stored procedure, but that doesn't seem to work. What is the proper way to do this? Thank you. Kay
I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================
Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".
Some one can tell me what is the reason, or give me some examples.
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo WHERE TicketNum = (SELECT TOP 1 TicketNum FROM CustomerInfo WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP) ORDER BY CreateDate DESC) SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp' CREATE DATABASE @DBName + ' ' + @From + ' ' + @To END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
We have different databases on the same server: Whrs001, Whrs002, etc. All databases have the same structure.
Suppose i have a table "location" create table location ( location_id int not null primary key, location_name varchar(50) not null )
I need to inserts data in "location" tables of different databases. But these inserts needs to be across databases. For example from the "Whrs003" database I need to insert data (location_id=123, location_name='markham') in "location" table of "Whrs001" database.
So my thinking is to defined a stored proc which has database name as an argument: create procedure location_insert @a_database_name varchar(100), @a_location_id int, @a_location_name varchar(50) as ...
So for the above example the call will be : exec location_insert 'Whrs001', 123, 'markham'
What would be the actual insert statement in this case? And what things i neeed to take care fo to make sure it always execute successfully?
Hi I have two SQL server 2000 machine in my corporateHow to create a SQL View if the database table is located on another SQL server?Example :I am currently working at SQL Server "S01", but need to create a sql view to table customer on "Marketing" database located on SQL Server "S02"if the table is reside on the same machine then it's simple for me, but this customer table is not located on "S01" but located on different machine "S02"how to do this? any tutorial?thanks a lotYonscun
Hi, I have a sql script that references a database explicitly, but the name of that database changes. Rather then have to find anywhere I reference that database and change it, i would like to just set it in one spot. For example.SELECT * FROM [V1_Database].[a_table]I then decide to upgrade from V1 to V2.Instead of having to find the above code in all of my stored procs and manually change it, I would like to do the following.SET dbName = 'V2_Database' (Database wide variable)SELECT * FROM [dbName].[a_table]But I don't want to have to make all of the places that reference a specific database into string executions. ( strSQL = 'SELECT * FROM [' + dbName + ']' )I hope i havent made this too confusing, but any help is appreciated. Thanks,Brian
I have tried just about everything i could find on the internet to fix this. Anyway.....I have a sql databasewith 3 colums: Client ID, Client Name, and Client Address I have no trouble reading the database through the gridview, but when i try to add a script that allows me to add to that database off of my site, I get error after error. Anyone who can figure this out must be a sql genius (or I am a complete idoit). Here is the code I have so far: <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %> <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1"> <script runat="server"> Dim strClient_Name As String Dim strClient_Address As String </script> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Client ID" DataSourceID="SqlDataSource1" Height="50px" Width="125px"> <Fields> <asp:BoundField DataField="Client Name" HeaderText="Client Name" SortExpression="Client Name" /> <asp:BoundField DataField="Client Address" HeaderText="Client Address" SortExpression="Client Address" /> <asp:BoundField DataField="Client ID" HeaderText="Client ID" InsertVisible="False" ReadOnly="True" SortExpression="Client ID" /> <asp:CommandField ShowInsertButton="True" /> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:clientInfoConnectionString %>" InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)" SelectCommand="SELECT [Client Name], [Client Address], [Client ID] FROM Clients"> </asp:SqlDataSource> </asp:Content>
One of my employes deleted a MSSQL Database instead of detach. Now i tried to recover the .mdf and .ldf file but when i trie to attach it it says. "File is not a primary database file"
I have a application that uses a MSDE database to store information. The application seems to hit into a database corruption problem. Now I would like to view all the datas and tables created in the database. How can I do that using OSQL ?? What are the commands to view all the tables in this database ? Or is there any other better applications to do the viewing ?? Please advise. Thanx a lot !!
I create a user then I create a database and assign my newly created user as owner
When I login as this new user everything works fine BUT my newly created user can still see other database names even though he is not allowed to use them.
How can I prevent him from seeing other database names.
Our MS SQL (SQL Server 2000) DBA has database privileges locked downpretty tightly. We end users/developers do not have administratorprivileges for most databases. That arrangement has worked out OK forthe most part. However, it's a bit aggravating that we can't evencreate our own database diagrams. When we attempt to do so (inEnterprise Manager), we get a dialog that says "You do not havesufficient privilege to create a new database diagram."Our DBA is so busy that it's difficult to get them to create ones forus. And even when they do, it seems that we can't even view it online-- we rely on the DBA to give us printed copies or screenshots for ourdocumentation. How ridiculous is that? We'd also like to be able tomanipulate the diagrams online so that we can more easily study ourtable structures, indexes, foreign key constraints, etc. In fact,there doesn't even seem to be any other way to easily view currentforeign key constraints.I realize that this might have to do with the diagramming tool havingthe ability to actually manipulate the database in ways we aren'tsupposed to do (e.g., like creating new tables). However, isn't therea "read-only" version of this tool that will give us what I'm lookingfor? Short of that, are there any other free or cheap tools that willprovide this? Thanks!(Please forgive my ignorance if this has been hashed out before.However, I did search news groups and the web before posting, figuringthis had been covered before, but I didn't find anything thatspecifically addressed this issue.)
Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.
We need to present hierarchical data on a web page, the same way thetree view shows files in Windows Explorer. Here's the catch: thattree view needs to be bound to a SQL Server database. How can this bedone?
What tool should I use to view SQL Server Compact Edition database?
I have VS2008 Professional, but I didn't find any tool in the installation. So I tried to install the SQL Server Developer Edition, which is included in the VS2008 package, but the installation has quit saying the that there is nothing to upgrade (there is probably newer version of SQL installed by VS2008 itself).
I also tried to download and install SQL Server Managment Studio Express. The installation went well, but then I wasn't able to select any SSCE database (there was only SQL Server Express or something like that selectable).
I am getting the following error when trying to create a view in a database.
If I have db_owner rights so I should not have an issue? Any thoughts
TITLE: Microsoft SQL Server Management Studio ------------------------------
Property DefaultSchema is not available for Database '[GiftTraq]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors)
I am trying to get data from a database through a select statment and I want to pass the return query to a c# string variable. Any idea how can I do this?
Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement
For Example: Instead of using the following with each database hardcoded in the SP:
select @dataused = sum(b.reserved) from DBSglep..sysindexes b where b.indid in (0, 1, 255) and segment != 2
I would like to loop for every database listed in sysdatabases and do this:
select @dbname = @dataname+'..sysindexes'
select @dataused = sum(b.reserved from @dbname b where b.indid in (0, 1, 255) and segment != 2
I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name).
Any assistance in resolving this problem would be greatly appreciated! :-)
I have a trigger which retrieves database names from a table. I need to use this retrieved database name in another sql insert statement as a variable e.g. set @mydbname = Select .... from.. (to get the database name) then.. insert into @mydbname.dbo.emplTable
At the moment it reads @mydbname as the string "@mydbname" not the value the variable holds
I need the database name as a variable because i have to write to the correct database (there are 15) Any help appreciated
I have a database called ‘Objects’ which has many field. One of its fields is called ‘Image’ and has a data type image. I want to add pictures to each one of my records offline, is this possible? i.e. by copying the address from my C drive such as C:Documents and SettingsfseyedarabiMy DocumentsMy Pictures
Hey everyone. I've linke an access database and I am able to query the tables like so:
SELECT * FROM QFinity...tblEmployees
I can do that to all the tables, however, I'd like to create a view to this linked database. Is this possible? I have a more complex query I'd like to run:
SELECT dbo_evaluations.eval_id, dbo_evaluations.quality_date, dbo_eval_questions.status FROM QFinity...dbo_evaluations INNER JOIN dbo_eval_questions ON dbo_evaluations.eval_id = dbo_eval_questions.eval_id;
I get the error "Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo_evaluations'."
I'm afraid I've reached the limit of my know how concerning sql server 2005... I think I read that I need to create a view? But I can't figure out how to do that.
The free one that i have downloaded doesn't seem to be able to view the database. Is there any free database which i can download? Please advise. Thanks.
I'm using the gui to create a view which requires my connecting to another database on the same server. I've tried to create synonyms; however, I can not access them (they do not display) on the synonym tab when I try to add them to the view.
I'm a novice so any suggestions to get me on my way is greatly appreciated.
