Help (require Code)
Mar 13, 2004
right im programming a database program and i need some code. (the database is in sql/msde)
This is what i am attempting to do:
I have a page with textboxes on it with buttons at the bottom
i need to program the database with the add function
this is the code that i have already
Imports system.data.sqlclient
'You need two objects
Dim conn as new sqlconnection
dim comm as new sqlcommand
conn.connectionstring = "Your UDL String here"
comm.text = "insert into books (isbn, title, author, publisher, description, price,)
'books is the name of the databse
values ( '1234' , 'Title', 'Author', 'Publisher' )
conn.open
comm.connection = conn
comm.executenonquery
comm.close
conn.close
now i think i need to insert this at the top of the page but im not 100% sure (btw i have the udl string)
so what do i need to add to this code to make the page submit data that has been enterd into textboxes into a database table(sql/msde) on the click of the button?
thanks for any help provided
View 1 Replies
ADVERTISEMENT
May 25, 2008
Hi All,First of all its very very urgent. Secondly I am not an expert. Now here is my problem.I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.CODE:USE [AdventureWorks]DECLARE @cmd varchar(1000);DECLARE @FileName varchar(100);DECLARE @FilePath varchar(100);SET @FileName = 'C: est'+@FilePath+'html';SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' + 'SELECT ' + @FilePath + ', FileType, * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'EXEC (@cmd);Select * from myTableJust for practice I am using Adventureworks db. I am using SQL Express 2005.My html files are named something like this:AC0234.htmlDB9803.htmlCG4571.htmlI cannot change my file names.Thanks in advance.Regards;
View 1 Replies
View Related
Nov 6, 1998
Hi everyone,
I have a table which have several fields. I have a field that is made required by mistake. I would like to change this field to not require. Can someone help with the syntax?
Any help is greatly appreciate.
View 1 Replies
View Related
Feb 11, 2002
Hi
1) I am facing the problem of database suspect how do I solve the problem.
2) When I tried to cofigure my SQL mail I am not getting the option or Icon of SQL MAIL in the enterprise manager. I think it probably got deleted , how do I retrieve it .
Thanks for the help in advance
Arya Sharma
View 2 Replies
View Related
Mar 27, 2006
Does anyone know what aspnet_regsql.exe requires to run stand-alone? I have to apply the .NET 2.0 Management/Role API to a sql2000 database on a machine that does not have the .net 2.0 framework installed.
View 3 Replies
View Related
Sep 1, 2004
Hi Champs!!
I am totally new in OLAP and want know more prior to start....pls tell me know the difference between these..
Regular/Virtual/Linked cubes..
Star schema/ snow flake/ parent-child/ virtual dimension/ mining model
Thanks in advance
-Deepak Kumar.
View 1 Replies
View Related
Sep 15, 2007
Hi,I have two tables TableA and TableB. I have a stored procedure whichruns every morning and based on some logic dumps rows from TableA toTableB. In Table B there are two additional colums ID and RunID. ID isa normal sequence applied for all rows. But the RunId should beconstant for a run of stored proc.So for e.g. say structure of Table A and Table BTable A Table Bcol1 ID RunID col1Now when I run stored proc I want rows copied as belowTableA TableBcol1 ID RunID col1row1 1 1 row1row2 2 1 row2The next day when stored prc runs I want data asTableA TableBcol1 ID RunID col1row1 1 1 row1row2 2 1 row2row1 3 2 row1row2 4 2 row2So for every run of stored proc each day I want the Run ID incrementedonly by one and ID is normal sequence which increments for allrowsinserted.Please help.Nick
View 1 Replies
View Related
Mar 12, 2008
Actually report server is in seperate server and db server is seperate.
Is it necessary to have sp2 on both the servers.
View 4 Replies
View Related
Jun 12, 2007
Is sql2005 backwards compatible to provide reports for FRX? sql2000 is what the software docs ask for. Do I have to migrate and transform the data as a work around? What are the options if any?
View 1 Replies
View Related
Dec 2, 2013
I have a view. When I select data, I have to use double quotation on selected column name, for instance:
select "assy_no" from myView;
if I try
select assy_no from myView
I will get error: ORA-00904: "ASSY_NO": invalid identifier
When I query on table, double quotation is not required.
Why the column name on view requires double quotation?
View 1 Replies
View Related
Jul 23, 2005
I have many stored procs in my database and I can call them just bytheir name uspMyProc with success always. However, I just created auser function ufnMyFunction as the same user that I created my procsbut when I call ufnMyFunction it fails unless I preface it with dbo. .How come the stored proc does not require this but the stored functiondoes?TFD
View 1 Replies
View Related
Jul 23, 2005
Hi,I can see that by using the object ID rather that the object name, thefollowing SQL query works. Has anybody got any idea what is causing theerror?-- Works OKselect o.id,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.id-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select object_name(o.id),checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by object_name(o.id)-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select o.name,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.name-- Workaroundselect getdate(),object_name(x.id),check_sumfrom (select m.id,checksum_agg(binary_checksum(m.text)) as check_sumfrom syscomments minner joinsysobjects oon m.id = o.idwhere o.xtype in ('FN','IF','P','TF','TR','V')group by m.id) as xRegardsLiam
View 1 Replies
View Related
Jun 13, 2007
My packages are now in the filesystem. I have given the parent package a password in the properties window, saved the package and moved it to the run location.
I am using the SQL Job Agent to schedule the execution. My step command line reads:
dtexec /FILE "C:Pathparentpackage.dtsx" /De mypass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Do the the 20 or so child and grandchild packages require passwords to run this way?
Thanks,
IanO
View 1 Replies
View Related
Apr 3, 2008
Hi,
I just found out that when I create a user defined scalar function, I must call it using dbo.[myFunctionName]. Why won't it work w/out dbo? Why are stored procedures able to use omit dbo?
Also, what is dbo specifying? I'm very unfamiliar with sql server security. Is this the user, schema, role? What's a schema? lol. Thanks.
View 5 Replies
View Related
May 8, 2001
I have a Stored Procedure I am trying to run that joins to a remote database. I am able to see everything in the QA just fine with this (courtesy of Anatha):
SELECT DISTINCT a.*
FROM LOCATION a,
LinkServer.MC_Card.webuser.LOCATION b
WHERE a.location_number = b.location_number
But I am trying to run this query in Stored Procedure(notice the 4-part name callout to the LinkedServer tables) which returns the error message:
Error 7405: Heterogeneous queries require ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Here is the Stored Procedure:
/****** Object: Stored Procedure dbo.spELRMCcardXtionByDate
Script Date: 4/24/2001 11:51:27 AM ******/
CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS
-- declare @dcid nvarchar(255)
-- set @dcid = '1032'
SELECT STORE.[Str#], STORE.[Dcid#], E.card_number, E.program_number
, E.start_date, E.end_date, E.card_number, E.event_number
, E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
, L.merchant_name
FROM (STORE INNER JOIN LinkServer.MC_Card.webuser.EVENT E ON STORE.[DemoID#] = E.event_number)
LEFT JOIN (LinkServer.MC_Card.webuser.LOCATION L RIGHT JOIN LinkServer.MC_Card.webuser.POS_TX P ON L.location_number = P.location_number)
ON E.event_number = P.event_number
WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY STORE.[Str#]
-- and E.card_number IS NOT NULL
GO
Any help greatly appreciated.
Thanks,
Bruce
View 2 Replies
View Related
Jun 13, 1999
pls .send me sql admin exam question and study notes.
View 1 Replies
View Related
May 14, 2001
I am trying to create a stored procedure that updates a table on another server. It give the me the error about requiring ANSI_NULLS and WARNINGS being set. How can I set these if they are not already set? I tried setting them within the stored procedure, but does not appear to be working. Unless I am doing something wrong. I am trying to add SET ANSI_NULLS ON and doing the same thing for WARNINGS. Any thougts or suggestion on what to do? Thanks for the help
View 2 Replies
View Related
Sep 5, 2007
Hi,
I have a problem with linked servers.
I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.
So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.
But when I try to access to the aplication database, I hav the following error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
I searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.
If anyone can help me, I'd be veri greatfull
Best regards, Ariel
View 2 Replies
View Related
Nov 17, 2007
I have a SQL200 stored proc that gives me the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query." when I try to execute it through the query analyzer.
I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
or else my vb6 recordset crashes.
When I created the sproc, I did what every one was telling me to do in the forums by putting in the
SET ANSI_WARNINGS ON
Go
SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure usp_SprocName
AS
SET ANSI_WARNINGS OFF
SET NOCOUNT OFF
SET ANSI_NULLS OFF
Can someone help me?
View 4 Replies
View Related
Sep 3, 2006
I just read that if you use an exchange server...the account that youstart SQL with needs a mail on the exchange box.Is this true?I know I didn't need an account when I've set it up before...but Imight have been using a POP3 server.I have Outlook 2003 installed on the server. Windows 2003 Enterprise.Thanks.
View 1 Replies
View Related
Oct 19, 2007
Have an SSIS package with a Web Service Task, downloaded the WSDL to a local file, all is well until we try to deploy the package to another machine at which point the package expects to find the WSDL in the same place.
Am I not understanding something? Yes I can make it work, BUT:
1. Couldn't the task just get the WSDL from the web service each time, instead of from a static file? Isn't that the idea? Latest and greatest...
2. Having packages dependent on files is extremely inconvenient in a production environment. Security issues abound, things are in different places on different servers. How are folks handling this? The Web Service Task is certainly not the only place in SSIS where a package can be dependent on a file system location.
View 3 Replies
View Related
Jan 17, 2007
Hi i am using MS SQL 2000 db ,while executing following prepared statement query its working fine ...BUT when i remove "top 300" then tomcat giving OUT OF MEMORY ERROR!!
i think that its because of the cross join....So i want to optimize this query in such a way that it will not give "Out of memory error"
How can i re write this query?
Query :-
"select top 300 haw.DeviceID as deviceid,haw.Description as Description,haw.SerialNo as SerialNo,haw.Suffix as Suffix,haw.HWRev as HWRev,haw.Type as Type,dev.Status as Status from Hardware_PlaceHolder haw,Device_PlaceHolder dev ,Accounts ac where dev.siteId = ac.siteId and ac.CustID = ? ";
thanks in advance..
View 4 Replies
View Related
Jan 17, 2007
Hi,
I am creating dataflow tasj using following
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
These refer to Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll. While these assemblies were already there in my dev machine I don't find these files in production enviornment for SQL server 2005. I am refering these assemblies from following path in my local machine : C:Program FilesMicrosoft SQL Server90SDKAssemblies.
How to install these assemblies in prod env, offcource one option is to copy it and then put it in GAC thru script . Why does not it gets installed while installation of SQL server 2005. Are these assemly dependent on SP1 ?
Thanks
Mohit
View 4 Replies
View Related
Jun 3, 2015
I know there's an option in the SSMS to remove the hook at the option "Prevent Saving changes that require table re-creation".
But how can I set the option silent. I mean for instance at the setup or by a secret T-SQL command or setting a value in a hidden file? I don't want the user to open the options dialog.
I logged the registry by a tool and alas there was it. So the thing is to set the registry entry by our own setup after installing the SSMS.
[HKEY_CURRENT_USERSoftwareMicrosoftSQL Server Management Studio12.0DataProject]
"WarnTablesReCreated"=dword:00000001
View 5 Replies
View Related
Apr 11, 2006
Connection to SQL server files (*.mdf) require SQL express 2005 to function properly. please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
I AM GOING TO RIP MY HAIR OUT WITH THIS PROBLEM. I have reinstalled both sql server express 2005 and VWD about 5 times with the same problem. please, please, please someone throw me a bone here and help me resolve this problem.
When I create a new EMPTY website and I rightclick on my website in the solution explorer and choose add item, I chooe SQL Database, I give it a name 'database.mdf' and click add. I get the following message:
you are attempting to add a database to an asp.net application. for a database to be gfenerally consumable in your site, it should be placed inside the 'app_data' folder. would you like to place the database inside the 'app_data' folder? I click YES (I know this message is normal)
then I get the following message:
Connection to SQL server files (*.mdf) require SQL express 2005 to function properly. please verify the installation of the component or download from http://go.microsoft.com/fwlink/?linkid=49251
I can add anything else but this damn mdf file.
thanks for all your help in advance.
View 2 Replies
View Related
Jan 13, 2013
Or can it record before and after column changes based on the LSN only?
An extract from a file based legacy accounting system is performed every night. The system does not have a primary key because transactions are managed through program code. (the more things change...). The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays.
In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in tables that remember the before and after content of each column tracked. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work. This is in contrast to replication, where the requirement for the existence of a primary key is made plain.
Is it possible to use Change Data Capture against a table without a primary key? How to use it to change the extract from kill and fill to incremental.
View 9 Replies
View Related
Sep 28, 2015
DBCC LogInfo command require SysAdmin rights?
View 2 Replies
View Related
Aug 10, 2015
Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.
View 0 Replies
View Related
Jul 27, 2006
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:
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")
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!
View 7 Replies
View Related
Mar 28, 2007
Dear Friends,
I am having 2 Tables.
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.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
View 4 Replies
View Related
Jun 10, 2007
I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.
I have two SQL Server instances installed.
1- UserLT (this is sql 2000)2- UserLTSQL2005 (this is SQL 2005 named instance)
But when i try to add a database to my VS website project I get the following error:
Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925
I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)
and changed the "SQLEXPRESS" to "USERLTSQL2005".
But I still get the same error message. Any ideas how i can resolve this issue?
View 23 Replies
View Related
Jan 11, 2006
hello,
i've installed SQL server 2005 express and Visual web developper 2005 express.
when i whant to create a database in VWD the following error occures:
connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251
i searched the internet but can't find the solution, i already reinstalled my complete workstation but the problem stays.
please help!
View 9 Replies
View Related
Oct 13, 2006
I have a VERY Legitmate use to install sql 4 workgroups on a pc without licensing it. I am making a disk image for MANY pcs to be imaged and load licensing after the imaging is completed.
I have been able to do this with Windows XP Professional, MS Office 2003 and a couple non-Ms programs. The information for MS software even came from their KB, so I know what I am doing is legit. I just can't find any information on doing this for my sql app.
Any suggestions??
Respectfully,
Frustrated
"Frustrated"
View 1 Replies
View Related