I am trying to make up a SQL string which will be executed with the Exec command
I want to add a return column that is not in the table, and the table columns:
something like
Select @Ten As Ten, @Tablename.* From @Tablename (
where @Ten has an integer value. )
The statement was originally:
select @SQL = 'select * from ' + @TempName
exec (@SQL)
which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!
I am using the EXEC function in order to specify which view to access. However I can not get the datediff to work with it....does anyone have any clues?
I am using SQL Server 7 Beta 3 and I am having trouble with using a variable in an EXEC statement.
I have a stored procedure that is designed to retrieve a serial number from a table and then increment it by one(1) and return the original serial number to the calling function.
As long as the table name is hardcoded the following code works fine:
Following is the error message that I keep getting:
Server: Msg 170, Level 15, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near `=`.
No amount of testing with different qoutes or doublequotes and such, have made any difference.
I have tried numerous variations on the above SELECT statement with the same end result of the error on the "=" sign.
I cannot seem to assign one variable to another using the "=" (equal sign) in an EXEC statement.
Does anyone have example code of using the equal sign to assign one variable to another in an EXEC statement or can someone point me in the right direction?
I have been trying to find a solution to this for some time now. Was wondering if some1 had done is earlier and has a solution.
I have a 2 server machines. Namely: ServerOne and ServerTwo
ServerOne (main server, On 1 machine.) Table - Foofoo
ServerTwo (secondary server, on another machine) Table - Booboo
I want to be able to link these two servers and work with them.
At the moment I do something like this. NB. My Stored Procedure is on ServerOne
declare @server varchar(100)
Select @server=Servername from ServerOne.systemsettings where name='secondary' -- @server is not equal to 'ServerTwo' declare @str varchar(8000) set @str = ' select * from Foofoo f join ' + @server + '.myDB.dbo.Booboo b on b.id = f.id '
exec(@str)
My problem is that this works fine but I do not like working with long strings and then executing them at the end.
I have also been told that SQL's performance on this is not entirely that well as normal select's would be.'
Another thing that could be used is SQl's own linked servers method but apparently out system was designed some time ago and a lot of things have been developed around the current technic.
Our server names also change quite frequently making hadcoding server names difficult.
Using the string exec convention also hides from sql when you do a dependency search of a particular table.
Is there a way I can save the server name on @server and then just add it to the select stmt without using the long stringing idea.
Any feedback with ideas and solutions will be greatly appreciated.
What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?
Code snip:
declare @ErrMessage as varchar(1000) set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + '''' set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''
So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.
I understand sp_executesql might do the trick because it allows passing local params back and forth.
Any ideas on how to make this work? I appreciate the effort.
Hi, I have written a stored proc to bulk insert the data from a data file. I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc
The follwing statement works fine if i give the table name directly in the query
Code Snippet
DECLARE @LastUpdate varchar(20)
SET @LastUpdate = 'Dec 11 2007 1:20AM'
INSERT INTO Category SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt', FORMATFILE = '\remotemachineFormatFile.fmt', FIRSTROW = 2) AS a
To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below
Code Snippet
@Category - Will be passed as a parameter to the stored proc
Print @vsBulkSQL - This prints the folliwing statement
INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a
Exec @vsBulkSQL - This statement gives the following error
The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.
Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same
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?
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use: select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" then I'm getting a error message: @OldID not declare.
I need to know how to create a compound primary key in Transact-SQL It's really simple, it's a table with 3 column, two of those make the primary key Right now the CREATE TABLE look like this:/* SectionsContent Table */ CREATE TABLE SectionsContent ( SectionID Int NOT NULL CONSTRAINT FK_SectionsContent_SectionID FOREIGN KEY REFERENCES PsychoCMS.dbo.Sections(ID), DocumentID Int NOT NULL CONSTRAINT FK_SectionsContent_DocumentID FOREIGN KEY REFERENCES PsychoCMS.dbo.Documents(ID), Position int NOT NULL )
Hello,How can I stop/prevent SQL server from running compound SQLstatements. I do not want the server to run multipleupdate/delete/insert/select statements as a batch. Is there an option?/Kafwww.afiouni.com
Is it possible to force the use of a compound index in a query?create table Test (ColOne int, ColTwo int)The compound index is ColOne + ColTwo.I'm interested in searching on ColTwo, but I also know the value ofColOne will always be the number "1".How do you structure the SQL statement to concatenate the two INTs anduse the index? Note that I don't have any control over the creation ofthese indexes.
BEGINNER QUESTIONI have a table which has a compound primary key consisting of two columns.One of these columns is a foreign key which is generated in another table byan identity.I want to be able to generate the other primary key column valueautomatically when an insert occurs but assume that I cannot use an identitybecause it would have to be unique for this table.There will be potentially more than one user accessing this table so I wantto avoid generating the key on the client side.How can I do this? Will it require some hardcore T-SQL?I hope this is clear (I suspect it isn't) I'd be happy to supply more info.I would be extremely grateful for any help!Mark.
Hello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?
I have a sql 2000 db which has a table that has a compound key, the problem is that I would like to create a Full Text Catalog for this table. However I noticed that i need a single primary key... but I dont have one.
I created another field on my table called "ftcID" as an int with identity set to Yes
However when I try and create a catalog it doesnt detect that this field is unique.
Does my unique field have to be a Primary Key, I cant remove the compound primary key as it will break my application.
Hi all, I've had this problem for a while now and I'm looking for a better solution.
I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.
I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.
Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?
I have been working in DW for a while, but using SSIS as an ETL tool is new for me. I worked extensively on Informatica.
Coming to my question, right now I am trying to do SCD type 1. But my dimension table has a compound key. i.e more than 1 column makes a key for the table. But SCD wizard allows to select only 1 attribute as a business key. Does any one have any suggestions on how to implement SCD if the target table has compound key.
Thanks in advance for your suggestions and answers.
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
Hi, I will give someone a script that creates a database using : create database mydatabase my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737 go use mosaikDB737
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[FileListInput]( [FileName] [char](50) NULL ) ON [PRIMARY] END
use master select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb') select * from mosaikDB737.dbo.DBlistOutput
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this. If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems FROM Table1 Inner join on table1.assettag = table2.assettag where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1 SET query1.ScheduledItems = True Where query1.Status = Scrubbed
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference? Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is successful, my return value is coming back correctly (to whatever i set it)....but if there is an error such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP. It comes back blank. (The literature I've read says that processing should continue in the SP, so you can perform error processing...is that right?)
I set the return var in my ASP as: objCommand.Parameters.Append objCommand.CreateParameter("return",_ adInteger,adParamReturnValue,4) and read it back as: strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah if @@error = 0 return(100) else return(200)
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database. The database will be replicated to an central web server. That will say one web server and x numbers of laptops that has same GPS program. :) All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'xp_sendmail'.
How can I use xp_sendmail using a dabase other than Master?? Please Help.
I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together. So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?
select somedata, somedata, somedata, somedata From kpi..temptablel l left outer join @temps s on l.x = s.x left outer join @tempf f on l.x = f.x left outer join kpi..temptablee e on l.x = e.x left outer join @tempn n on l.x = n.x
where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname fromemp wheresex = 'F' and salary>(selectavg(salary) fromemp group by sex havingsex='M')
selectlastname fromemp wheresex = 'F' and salary>(selectavg(salary) fromemp wheresex='M')
also is it wise to use Group by and having in sub-queries?
Hi I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X. Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?
I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.
